CODEWITHSUNDEEP
sql-server-2008indexing
Neil Knight
Neil Knight

Reputation: 48537

Create an Index with a WHERE clause

I've read the documentation on the MSDN, but it doesn't answer what I'm after. If I create an index on specified columns using a WHERE clause for certain IDs, will this index include any new IDs that are inserted into the database? Or will it only add those records with the IDs in the WHERE clause?

Upvotes: 0

Views: 971

Answers (2)

Grant Fritchey
Grant Fritchey

Reputation: 2775

Let's assume that you're creating an index to eliminate NULL values from a column (one of the classic examples). As new values come in that are not NULL, they will be added to the index in the same way as any non-filtered index. But any new NULL values will not be added to the index because you've created it in such a way as to filter out NULL values. Any other filters you apply will work exactly the same way, including or excluding values as they get added/updated based on the criteria of the WHERE clause defined within the index.

Upvotes: 1

u07ch
u07ch

Reputation: 13702

The where clause on an index works the same way it would in a query so if your index has X = 'ABC' then that is all the index will cover.

Upvotes: 0

Related Questions