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

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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