CODEWITHSUNDEEP
sql-serverindexing
sports
sports

Reputation: 8157

Composite index or single index?

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

Answers (1)

M.Ali
M.Ali

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

enter image description here

One Composite Index

CREATE INDEX IX_DateMonth_DateYear7
ON DateTable(DateMonth, DateYear)
GO

enter image description here

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:

enter image description here

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

Related Questions