My database currently has a primary Key/Clustered index on the ID
column for each table. However, the application that connects to the database is always looking at views defined as:
SELECT * FROM Table WHERE DeletedDate IS NULL
Would it be best to add the DeletedDate
to the clustered index or is it better practice to have a nonclustered index with the DeletedDate
as an include?
Best Answer
Since we are talking about the clustered index, just because you defined the CI key column as
ID
, you still have theDeletedDate
data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data.Because you are typically having queries that look like:
You will likely benefit from a filtered index.
I didn't explicitly put the key columns here (and nonkey columns through the use of the
INCLUDE
clause) because you didn't publish the DDL of your table.As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.
If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies). Not to mention, if you have other
WHERE
clauses (besides yourNULL
check onDeletedDate
) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek. And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.