What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns?

My manager said this is well known practice and query performance is good. Is this true?

Thanks in advance

Edit: and this columns are put under a clustered index.

Edit: This is not a accounting database. There is no need for thirteen month or 30 Feb.

Edit: table script

/****** Object:  Table [dbo].[tbl_PlayerActionLog]    Script Date: 14/8/2014 9:47:28 AM ******/



CREATE TABLE [dbo].[tbl_PlayerActionLog](
    [LogId] [bigint] IDENTITY(1,1) NOT NULL,
    [LogYear] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogYear]  DEFAULT (datepart(year,sysdatetime())),
    [LogMonth] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogMonth]  DEFAULT (datepart(month,sysdatetime())),
    [LogDay] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogDay]  DEFAULT (datepart(day,sysdatetime())),
    [LogTime] [time](7) NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogTime]  DEFAULT (sysdatetime()),
    [PlayerActionTypeId] [int] NOT NULL,
    [MerchantId] [int] NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [BetModeId] [int] NULL,
    [OSVersion] [varchar](20) NULL,
    [SessionId] [varchar](30) NULL,
    [IpAddress] [varchar](15) NOT NULL,
    [LogDetail] [nvarchar](max) NOT NULL,
    [LogId] ASC



ALTER TABLE [dbo].[tbl_PlayerActionLog]  WITH CHECK ADD  CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType] FOREIGN KEY([PlayerActionTypeId])
REFERENCES [dbo].[tbl_LookupPlayerActionType] ([PlayerActionTypeId])

ALTER TABLE [dbo].[tbl_PlayerActionLog] CHECK CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType]
/****** Object:  Index [tbl_PlayerActionLog_idx]    Script Date: 14/8/2014 10:00:23 AM ******/
CREATE CLUSTERED INDEX [tbl_PlayerActionLog_idx] ON [dbo].[tbl_PlayerActionLog]
    [LogYear] ASC,
    [LogMonth] ASC,
    [LogDay] ASC,
    [LogTime] ASC,
    [MerchantId] ASC,
    [PlayerActionTypeId] ASC
  • 2
    Almost always better to use the semantic datatype, in this case date, datetime, timestamp or similar, depending on DBMS. – Colin 't Hart Aug 12 '14 at 8:38
  • 1
    Most likely your manager was attempting to convey partitioning based on date ranges, not physically splitting the date column into separate columns. Please see the excellent answer on this question: dba.stackexchange.com/q/29420/10832 – Max Vernon Aug 12 '14 at 13:09
  • 1
    Can you add some context to your question? Is the topic an OLTP database or data warehouse for example? – Mark Storey-Smith Aug 12 '14 at 18:46
  • 3
    One advantage I can think of, is that you are able to store "partial dates" like "March, 2014", or "April, 4th" because you can leave some elements at NULL - which you can not do using a date or datetime data type. But if that is not a requirement, I don't see an advantage at all – a_horse_with_no_name Aug 13 '14 at 10:53
  • 1
    @user45398 Shows us the CREATE TABLE statement, please. What you added, "and this columns are put under a clustered index together with Id" is rather vague. – ypercubeᵀᴹ Aug 13 '14 at 13:16

I guess it depends on how you want to use that data. There may be some advantage to the application using it. For example it might make reporting or filtering by year or month easier.

But I've never heard of that as some kind of best practice on its own.

The downside of this approach is that you lose the ability to use the standard date functions in SQL. Simple things like duration between 2 dates or times is simple using datetime fields but a nightmare with ints. You also lose the inherent validation of a proper date datatype. Ask your boss if there is anything stopping you from inserting 30th of February or a 13th month.

| improve this answer | |

Horses for courses. If you want to be able to query on data in sliding ranges, like "sum of hours recorded for the last 2 weeks" then sticking with a date format may be better.

If you think you'll be querying for things like "number of hours recorded in January (any year)" then go ahead and store it like that.

In my experience, any small performance benefit gained through this kind of activity is generally offset by the work required to get the data in this shape in the first place from whatever source you have.

| improve this answer | |
  • Most of the time filter by date range (between). – user45398 Aug 13 '14 at 3:10

I think it is never reasonable. Its like a create a latitude column, and longitude column, instead of using geographic datatypes.

With the semantic datatypes, you will have timezone support, range types, and sooo much examples of good and advanced use, much, much more than have 3 columns. And then if you want (or your boss want...) you can use datepart

| improve this answer | |

The DateTime2(0) datatype takes up less space on the harddisk (6 bytes per row)

The DateTime2 datatype is searchable, as it is possible to write a query that will span from December 30th 2013 to January 2nd, where the SQL Server will disregard dates before and after. Such a range query is not easy to write with individual columns.

The DateTime2 datatype is easy to calculate on a date or DateTime2 datatype. Last day of month, etc.

And most important; The DateTime2 datatype travels well; there are many different date-time formats around the world. You never know if your software goes aboard, or your company is taken over by somebody why writes dates as yyyy-mm-dd or mm-dd-YYYY or dd/mm/YYYY or one of the 20 odd other formats that SQL Server support.

Joe Celko has written a lot about this, today I saw this : http://www.sqlservercentral.com/articles/Database+Design/69926/ one quote: "But one of the most common design errors is to use strings for date and time data."

| improve this answer | |
  • 5
    What is it? Please edit your answer to make it = date. At least, I hope that is what you meant! – Colin 't Hart Aug 12 '14 at 8:32
  • 1
    Date requires three bytes and datetime 8. YYYY will require smallint (2 bytes) and MM, dd, hh, mm, ss 1 byte each and 2bytes for nn. The separated columns are 1 byte longer than the built-in types. If you make the mistake of allowing any of them to be NULL the overhead is even larger. – Michael Green Aug 13 '14 at 5:05
  • 1
    He replaced "a" datetime field in every tables with 3 int(4 bytes) and 1 time(5 bytes), and together with Id formed the clustered index. So the overhead is (17 - 8 = 9 bytes). I explained that it will stress the memory/disk, but he still insisted the performance is better. – user45398 Aug 13 '14 at 5:55
  • 1
    plus 9 bytes per row times the number of non-clustered indexes, as the clustered key is copied to non-clustered index (except non-leaf level part of non-clustered index, but that is <1%) – Henrik Staun Poulsen Aug 13 '14 at 12:44
  • 1
    @Colin 't Hart; "it" replaced. – Henrik Staun Poulsen Aug 13 '14 at 13:50

Just out of interest, how would you get records between say 28 Feb 2012 and Today? Ask your manager to do it being as it's their idea.

Spoiler warning! For me, I would just skip the ( AND ( OR )) nonsense and go straight to a calendar table. You could also use DATEFROMPARTS in SQL 2012 but that will probably result in a table scan:

-- Ugly; hard to debug, hard to parameterise.
-- There is a deliberate mistake here, can you see it?
FROM dbo.tbl_PlayerActionLog
WHERE ( logYear = 2012 AND logMonth = 2 AND logDay = 28 )
  OR ( logYear = 2012 AND logMonth > 2 )
  OR ( logYear = 2013 )
  OR ( logYear = 2014 AND logMonth < 8 )
  OR ( logYear = 2014 AND logMonth = 8 AND logDay <= 14 )

-- DATEFROMPARTS; ok not bad to maintain, will probably result in a 
-- table scan, and why are we reconstructing DATE again?
-- Could do computed column, and why are we reconstructing DATE again?
FROM dbo.tbl_PlayerActionLog
WHERE DATEFROMPARTS( logYear, logMonth, logDay ) Between '28 Feb 2012' AND GETDATE()

-- Calendar table, easy to maintain and parameterise
FROM dbo.tbl_PlayerActionLog pal
        SELECT 1
        FROM calendar.main c
        WHERE pal.logYear = c.calendarYear
            AND pal.logMonth = c.calendarMonth
            AND pal.logDay = c.calendarDay
            AND c.calendarDate Between '28 Feb 2012' AND GETDATE()

FROM dbo.tbl_PlayerActionLog2
WHERE logDate Between '28 Feb 2012' AND GETDATE()

In my test rig, your manager's option was actually quite efficient, but you can see there is a problem built in to the design; it would be hard to parameterise and hard to debug. The most efficient was the normal DATE column...

| improve this answer | |
  • By the way, SQL allows WHERE (logYear,logMonth,logDay) BETWEEN (2012,2,28) AND (2014,8,14) but SQL-Server has not implemented this syntax. – ypercubeᵀᴹ Aug 14 '14 at 12:26
  • 1
    Let us continue this discussion in chat. – wBob Aug 14 '14 at 13:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.