CODEWITHSUNDEEP
sql-serverperformanceindexing
dark_perfect
dark_perfect

Reputation: 1478

Best index for particular column often filtered with IS NULL?

I'm a bit unsure over the best index to use for a particular column in my table.

I have a [Deleted] column that is a DateTime, and represents the moment that the record was "deleted" from the system (it's a soft delete, so the physical record itself is not deleted).

Almost all queries hitting the table will have a 'WHERE [Deleted] IS NULL' filter to it. I am not worried about the performance of queries that do no have this filter.

What would be the best index to construct for this scenario? A filtered Index WHERE [Deleted] IS NULL? An index on a computed column of definition IsDeleted = Deleted IS NOT NULL ? I'm a bit unsure.

Upvotes: 3

Views: 690

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294307

This is a lost battle. You can add a filter to all other indexes, see filtered indexes. But you won't be able to filter the clustered index.

A filter on Deleted column will be too unselective for NULLs and all queries would hit the index tipping point. And a bit Deleted column is worse, with a 0/1 selectivity it will be always ignored.

I would recommend you investigate partitioned views instead. You can store current data in one table, deleted data in another one, and operate over a view that unions the two. When properly designed (this is why reading the link is critical, to understand the 'proper' part) this scheme works fine and you never scan the 'cold' data.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

As you have said almost all the queries will have this clause in them. I would suggest to add a BIT column to your table, once a row is deleted (Soft deletion) set it to 1. Also keep this Datetime column probably as Deleted_Datetime to keep track of when records are being deleted.

Then Create a Filtered Index on that BIT field something like 

CREATE NONCLUSTERED INDEX NCIX_Deleted_Data
ON dbo.Table(Deleted)
WHERE Deleted = 0

Bit being the smallest datatype, and having a filtered index on it will give you a massive performance boost.

You can create this filtered index on your Datetime column as well. But the logic is in that case sql server will have to go through 8 bytes of data on index to see if row is filtered or not.

Whereas if you have Bit column sql server will have to go through 1 byte of data to filter it out or to keep it.

Smaller data quick processing, Bigger data more time to process it :).

Upvotes: 3

Related Questions