Skip to main content
4 of 5
added 2294 characters in body

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

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