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.