Index on result of function

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)

The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

Yes. You can achive this using the indexed views.

Create different views for each year & index it.

|||Bes, this table sounds like a good candidate for the new table partitioning method of SQL 2005. You could partition by the year and month... There would be separate indexes on each partition slice and SQL Server would direct a query to just the partition needed and the query would run much faster... but... you need Enterprise Edition for paritioning. If you have Enterprise, then it's something to check out... Bruce|||

Bruce,

It's good to know there is another way to do it. The little I've read about Indexed Views indicates they'll increase my maintenance and I should only use them in special cases.

We're not running Enterprise (too much $ for dual CPUs), but if depending on how we use this data maybe we'll be able to justify it.

Thanks!

Brian

|||

I think creating a couple of computed column(s) and creating an index on those field(s) will give you the best combination of query performance and maintenance. Lots of modifications to data in the base table in an indexed view could cause a server to grind to a halt. The index maintenance on the computed columns should be minimal.

alter table MyTable add MyDateYear AS YEAR(MyDate)

alter table MyTable add MyDateMonth AS Month(MyDate)

CREATE INDEX IX_MyTable_Year_Month ON MyTable(MyDateYear, MyDateMonth)