Assuming something like so:
select * from table where DatePart(YEAR, dateColumn) = 2012
Reasonable to have an index on dateColumn?
Edit*
For SQLServer
Sql-server – Good idea to have an index on datetime column
indexsql server
Related Question
- Sql-server – Is it a good idea to have an index when the column value changes several times over record lifetime
- Sql-server – Identity column as clustered index bad idea
- Sql-server – Is it a good idea to include a value column (e.g. from measurements) in an index
- Sql-server – Filtered index error when using two column condition
- Sql-server – Conditionally add an index to a temp table
- Oracle index with REGEXP_REPLACE is it a good idea
- Sql-server – Why is this datetime index not being used
- Sql-server – Full text index for a column that stores json which contains guid – Is this a good idea, space-wise
Best Answer
Adding an index is definitely a good idea for this. However, DATEPART(YEAR, datecolumn) = 2012 isnt' sargable so it will still do a scan of the index.
If you want it to use the index then you will need to do:
Please note the placement of the >= and the < signs to get the correct bounding box.