Is there any way to index a varchar column as a datetime?

Question

I'm querying a varchar column that contains all valid datetimes. In the query, I cast the column to datetime.

Keep in mind, changing the column's datatype definition to datetime is not an option. Just trust me, it's not an option.

Through query analysis, I see that the query would be much faster if I could add an index on this column. But it's varchar. If I added an index, it would index based on it's varchar value, right? But I'd want it to index on the datetime value, yes?

Is there anything I can do here for an index?

Answer 1

Have you considered building an indexed view on top of these generated tables? You could define your varchar-to-datetime conversion in the view, and then index that resulting column.

See Improving Performance with SQL Server 2005 Indexed Views for more information. I'm not an expert on using indexed views, I've only read about them. So I'll leave it up to you to figure out if it will be the right solution in your case.

Answer 2

Valid data is the first priority, because any approach that converts the data to DATETIME will return a value of January 1, 1900 at midnight when the value doesn't match an accepted format.

SQL Server doesn't have function based indexes, so your options are:

1. A computed column, assuming you can use ALTER TABLE statements to add columns to the table. You could index this column after that.
2. An indexed view (AKA materialized view), assuming your data meets the requirements
3. A staging table, cloned from the original (and tweaked if you like) where you import data from the existing table so you can index and manipulate it