I have many tables with a nullable DeletedDate column. Every query I write needs to check that the record is not deleted, but doesn’t care when it was deleted.
I want to add an index to this column, but it seems like it would be more efficient if there was a way to index it in a way that only cared if it was null or not instead of trying to group by date. Is this possible to do or is SQL Server smart enough to handle this kind of optimization on its own?
Advertisement
Answer
You can add a computed column, which is a binary indicator of the deletion. However, indexing a binary column is not usually very useful.
If you want to speed SELECT queries, then including the delete flag (or deletion date) as the first column in a clustered index can be helpful. Queries that use the flag would only scan the pages with undeleted records. For this purpose, using the date itself is probably fine, assuming that the date is set to the current date in a deletion.
The downside, of course, is that the data has to physically move when it is deleted. If the deletions only go one way (i.e. no “undeletes”), then the overhead might not be too bad.