Bsrgvty

Currently, we have an existing database and application that is fully functional. I do not have the ability to change the architecture at this point. Today, each table in the database has an "IsDeleted" NOT NULL BIT field with a default of '0'. When the application "deletes" data, it simply updates the IsDeleted flag to 1.

What I'm having trouble understanding is how the indexes on each of the tables should be structured. Right now, every query/join/etc always implements the IsDeleted check. It's a standard that our developers must follow. That being said, I'm trying to determine if all of my clustered primary key indexes on each of the tables need to be altered to include the primary key AND the IsDeleted BIT field. Also, since EVERY query/join/etc. must implement the IsDeleted check, is it an appropriate assumption that EVERY SINGLE index (non-clustered as well) should include the IsDeleted field as the first field of the index?

One other question I have is around filtered indexes. I understand that I could put filters on the indexes such as "WHERE IsDeleted = 0" to reduce the size of the indexes. However, since every join/query will have to implement the IsDeleted check, would that prevent the filtered index from being used (since the IsDeleted column is used in join/query)?

Remember, I do not have the ability to change the IsDeleted approach.

The easiest approach here is to leave your keys and clustered indexes alone, and use filtered indexes for your non-clustered indexes.

Additionally you can migrate some large tables to partitioned heaps or partitioned clustered-columnstores (SQL Server 2016+), leaving the primary key and unique indexes un-partitioned. This would enable you to push the non-key columns for IsDeleted rows to a seperate data structure, which could additionally be compressed differently or stored on a different filegroup.

And make sure the developers use a literal instead of a parameter to filter the IsDeleted rows out. With a parameter SQL Server has to use the same query plan for both cases.

EG

SELECT ... WHERE ... AND IsDeleted=0

And not:

SELECT ... WHERE ... AND IsDeleted=@IsDeleted

Using a paramter will prevent use of filtered index, and can get you into trouble with parameter sniffing.

This might be an unpopular opinion, but I don't think there is a "do this everywhere" / one size fits all answer to your question.

If you have queries that are scanning lots of IsDeleted rows for no reason, one solution is to create a filtered, nonclustered index to satisfy that query.

Another option is to create an indexed view that could be leveraged by a number of different queries, which is filtered to just the non-deleted rows. This could be especially useful on Enterprise Edition, where automatic indexed view matching works without providing a NOEXPAND hint.

For small tables, or tables that are read heavily, adding filtered nonclustered indexes or views or anything really might just be adding unnecessary overhead to your database.

Under the reasonable assumption that deletes are rare, no changes to the indices is an appropriate solution.

I found that sooner or later one must query for references to deleted rows, and the rows being in the indices is suddenly very worth it.

Please note that unless you are using views, you have to edit all of your queries to include the filters anyway.

I have seen a system where the IS_DELETED flag is either 0 or the value of the PK. In other systems it was the negative of the PK.

Since most queries retrieved values by the "natural" or business (sometimes multi-field) key, they never queried by PK except through joins; but they did always add an AND IS_DELETED = 0 at the end for the main table and for any joined tables.

This system also had an audit table for every transactional table that tracked changes; and the application had a feature to display all the data changes including the deleted data.

Hope you have right and ability to change query.

However, since every join/query will have to implement the IsDeleted
check, would that prevent the filtered index from being used (since
the IsDeleted column is used in join/query)?

I wanted to say one important point,hope i am able to explain it.

In complex query where Transaction table and Master tables both are use.

Use IsDeleted=0 only in Transaction table.
Do not use in Master table.

Example,

Select * from dbo.Order O
inner join dbo.category C on o.categoryid=o.categoryid
inner join dbo.Product P on P.Productid=o.Productid
where o.isdeleted=0

There is no point in c.isdeleted=0 (using in Category table).It is unnecessary.

Similarly is there any point in using P.isdeleted=0 ?

Because I want all undeleted Order and their details.

How can Product be deleted when Order is Active or wherever Productid is reference.

So this way if you debug carefully in important query, then may be you can remove some of the isdeleted=0.

Don't blindly Create Filtered Index, first select all those very important and slow query.

Optimize those slow query then only decide about Filtered Index or tune Index.