Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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,
 CONSTRAINT [PK_tbl_PlayerActionLog] PRIMARY KEY NONCLUSTERED 
(
    [LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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

ALTER TABLE [dbo].[tbl_PlayerActionLog] CHECK CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType]
GO
/****** 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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
share|improve this question

closed as primarily opinion-based by Paul White, Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Kin Aug 14 at 13:50

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

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 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 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 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 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 at 13:16

5 Answers 5

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.

share|improve this answer
1  
Good one; most people can stop the 30th of February, but 31st April... Is that valid? DateTime or DateTime2(0) is a lot better in my book. –  Henrik Staun Poulsen Aug 13 at 12:57
    
Thanks. This puzzle me when he told me this is common practice. I am a programmer and now I have to write awkward sql filter clause. –  user45398 Aug 14 at 2:41

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.

share|improve this answer
    
Most of the time filter by date range (between). –  user45398 Aug 13 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

share|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."

share|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 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 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 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 at 12:44
1  
@Colin 't Hart; "it" replaced. –  Henrik Staun Poulsen Aug 13 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?
SELECT *
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?
SELECT *
FROM dbo.tbl_PlayerActionLog
WHERE DATEFROMPARTS( logYear, logMonth, logDay ) Between '28 Feb 2012' AND GETDATE()


-- Calendar table, easy to maintain and parameterise
SELECT *
FROM dbo.tbl_PlayerActionLog pal
WHERE EXISTS
    (
        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()
    )

SELECT *
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...

share|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 at 12:26
    
Oh, and that '28 Feb 2012' should really be '20120228'. See Aaron Bertrand's blog: Bad habits to kick : mis-handling date / range queries –  ypercube Aug 14 at 12:29
    
Can you post your full proposed WHERE clause please @ypercube? –  wBob Aug 14 at 12:45
    
Yeah sorry, I see what you mean, but it's just an extra WHERE clause ( 6 versus 5 ) and compares slightly less favourably with my query in my rig. Also, I think my point is, the best option is the normal DATE column, and actually having to use 5 or 6 clauses just to implement a simple Between is, I think, awkward to maintain. –  wBob Aug 14 at 12:58
    
But that is my point. This pattern is awkward to extend for comparisons between 1-n years. Yours isn't any easier to extend - you would almost have to use dynamic SQL or have templates for same or multi- year comparisons. Think about writing a proc for this. It's messy. –  wBob Aug 14 at 13:05

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