How to index a DateTime Column?

_{Monday, June 16, 2008 1:04 PM}

We store in our tables some audit fields like Created_By, Creation_Date, Last_Updated_By and Last_Updated_Date. We now have a stored procedure that will query lot's of tables returning their records if their Last_Updated_Date is >= than a given date.

I'm wondering if it would be usefull to create an index on the Last_Updated_Date column and if so what sort of index? Clustered, non-clustered, unique?... I don't know much about indexing :-) So if you can give me a hint on what to do in this case it would be much appreciated!

_{Monday, June 16, 2008 2:15 PM ✅Answered}

You should definitely create an index on this column if it is going to be used as you suggest.  Without knowing anything else I would suggest that this be a nonclustered index.  The main reason for this is that you only get one one clustered index per table and it is likely that this is not the best choice for the clustered index.

More information is needed about the general composition of the tables to be specific.

_{Monday, June 16, 2008 2:29 PM ✅Answered}

Indexing is a pretty broad topic, but we'll go for a general overview.  You can have either no clustered index, or one on a table. If there is a clustered index, then your rows will be stored on that index.  If you want to look up any specific row, then looking up the clustered index will give you all the columns for that row.

Indexes don't have to be unique, as SQL Server will add in a "uniqueifier" if necessary.  However, primary keys must be unique.  The default index is a clustered index built on the primary key, but it is possible to cluster on a different index. 

Without seeing your table structure, the queries run against it, and the frequency of data modification statements, it's hard to make a call about the best option.  However, given that you've mentioned that your existing tables have this audit data, then it's likely that they are clustered on something else.  A simple nonclustered non-unique index on Last_Updated_Date will allow you to run your Last_Updated_Date >= '2008-06-16' type queries:

CREATE NONCLUSTERED INDEX ncix_index_name ON TableName (Last_Updated_Date)

Note that whenever you add an index, you also need to realise that the index must be maintained.  If there's only one or two indexes, this isn't a problem, but if you have many indexes, or your table needs to have fast inserts/updates/deletes, an extra index can drop performance below what is needed.  It basically comes down to what's more important - spending an extra few milliseconds when an insert/update/delete is performed on the table, or waiting for a table scan when you want to report and you don't have an index (and the subsequent destruction of your buffer cache if your table is larger than your available memory).

_{Monday, June 16, 2008 2:19 PM}

okay and what sort of index should it be?