CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlindexing
tugberk
tugberk

Reputation: 58494

SQL Server Indexes of DateTime Parts such as YEAR, MONTH

Is it possible to have indexes on DateTime parts such as DATEPART(YEAR, bp.[CreatedOn]) and DATEPART(MONTH, bp.[CreatedOn])? For example, I have the following T-SQL query:

DECLARE @year AS INT = 2012;
DECLARE @month AS INT = 8;

SELECT bp.Title, bp.CreatedOn FROM BlogPosts bp
WHERE (DATEPART(YEAR, bp.[CreatedOn]) = @year) AND (DATEPART(MONTH, bp.[CreatedOn]) = @month)
ORDER BY bp.CreatedOn;

And this is the execution plan I have: https://gist.github.com/3551450

Currently, there are not many records so it is not a big problem in terms of perf but the records will grow over the time.

Upvotes: 3

Views: 9481

Answers (3)

Aliostad
Aliostad

Reputation: 81700

I suggest you store date parts on different fields. Clearly this is a requirement which exist in your domain as such needs to be part of your model.

What you lose is that you have to construct the datetime for every model you load or you set the value but that would be a few CPU cycles.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Yes is it possible to put index on year and month. Here is an example:

create table testt(d datetime)
alter table testt add year as year(d) PERSISTED -- after marc_s advise. Thx
alter table testt add month as month(d) PERSISTED --

create index idx_year on testt(year)
create index idx_month on testt(month)

However I would always use a variation of Nikola's solution, so +1 to Nikola.

This is how you can rewrite month and year into a date:

DECLARE @year AS INT = 2012;
DECLARE @month AS INT = 8;
DECLARE @from DATE = dateadd(month, (@year-1900)*12 + @month - 1, 0)

Upvotes: 5

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You'd better construct criterion using datetime field:

declare @startMonth datetime = '20120801'

SELECT bp.Title, bp.CreatedOn 
  FROM BlogPosts bp
 WHERE bp.[CreatedOn] >= @startMonth
   AND bp.[CreatedOn] < dateadd (month, 1, @startMonth)
ORDER BY bp.CreatedOn;

This way query executor will be able to use index on CreatedOn.

Upvotes: 9

Related Questions