Reputation: 8157
In a table I have a Date
column and I want to group by month and year.
I've created two columns: DateMonth
and DateYear
and I've filled them with the corresponding month and years:
UPDATE t SET DateMonth = DATEPART(month, Date), DateYear = DATEPART(year, Date)
This way the groupby query wont use DATEPART()
(sql functions can't take advantage of indexes).
The groupby query is the following:
SELECT DateMonth, DateYear, COUNT(*) AS RowsCount FROM t GROUP BY DateMonth, DateYear
Given that information, what type of index will give a better performance to the pervious groupby query?
Each column indexed:
CREATE INDEX IX_DateMonth ON t(DateMonth)
CREATE INDEX IX_DateYear ON t(DateYear)
or one index that covers the two columns:
CREATE INDEX IX_DateMonth_DateYear ON t(DateMonth, DateYear)
Upvotes: 1
Views: 587
Reputation: 69594
Well they say the best way to find out is to actually do it :)
Created a table with 100,000 rows .
CREATE TABLE DateTable (DateColumn DATETIME , DateMonth INT , DateYear INT)
GO
declare @FromDate date = '1970-01-01'
declare @ToDate date = '2015-12-31'
INSERT INTO DateTable(DateColumn)
select TOP 100000 dateadd(day, rand(checksum(newid())) *
(1+datediff(day, @FromDate, @ToDate)),@FromDate)
FROM master..spt_values v cross join master..spt_values v2
GO
UPDATE DateTable
set DateMonth = MONTH(DateColumn)
,DateYear = YEAR(DateColumn)
GO
Query
Executed the following query with a single index on two columns and two separate indexes on both columns.
SELECT DateMonth, DateYear, COUNT(*) AS RowsCount FROM DateTable GROUP BY DateMonth, DateYear
Two separate Indexes
CREATE INDEX IX_DateMonth ON DateTable(DateMonth)
GO
CREATE INDEX IX_DateYear ON DateTable(DateYear)
GO
execution plan with two separate indexes
One Composite Index
CREATE INDEX IX_DateMonth_DateYear7
ON DateTable(DateMonth, DateYear)
GO
Two Tables with different Indexes
Finally I created two tables , DateTable with two separate indexes on both columns and DateTable2 with one composite index on two columns and compared the execution plans:
Moral Of the Story
Stick to one composite index since you are grouping by both columns and composite index will result in a Non-clusterd index scan which will be cheaper than a table scan that you would get if you had two separate indexes on two columns.
Upvotes: 4