I need to search a table with millions of records for all records where
YEAR(ACTIVITYDATE) = some integer
I can add an index over ACTIVITYDATE and then apply the YEAR function to my filter.
However, wouldn't I be better off storing a computed column ACTIVITYYEAR that is persisted where the column is defined as YEAR(ACTIVITY) and have and index over ACTIVITYYEAR?
I know that if there aren't enough distinct values over an indexed column in the table, SQL will do a table scan anyway. I don't yet have enough data to perform tests, and I trying to define the table layout, as much as I can, now.
Your thoughts?
There is no need to have a computed column to "search a table" for rows that match one year. Write you where clause using an interval instead and have an index on your date column.
select SomeColumn
from YourTable
where ActivityDate >= '20110101' and
ActivityDate < '20120101'
If you want to use an int (year) as argument to the query instead of two strings you can use dateadd. Just make sure that you don't apply any functions/manipulations to the ActivityDate column because SQL Server won't be able to use the index if you do.
declare @Year int = 2011
select SomeColumn
from YourTable
where ActivityDate >= dateadd(year, @Year-1900, 0) and
ActivityDate < dateadd(year, @Year-1899, 0)
It is better for performance to use the computed column and then index it.
Create a computed column on ACTIVITYDATE and then index that:
create table aTest
(
id int not null,
activityDate datetime null,
computedYear as YEAR(activityDate)
)
create nonclustered index NC_TEST on aTest(computedYear)
GO
insert into aTest (id, activityDate) VALUES (1, '2011/010/18')
insert into aTest (id, activityDate) VALUES (2, '2011/010/06')
insert into aTest (id, activityDate) VALUES (3, '2011/010/23')
Ref.: Creating Indexes on Computed Columns
If you have few distinct dates, then you could also create a filtered index to make it's use more likely.
