Create an Index with a WHERE clause

Question

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?

Grant Fritchey · Accepted Answer · 2012-02-21 11:33:30Z

1

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.

answered Feb 21, 2012 at 11:33

Grant Fritchey

2,84125 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

u07ch · Accepted Answer · 2012-02-21 09:34:42Z

0

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.

answered Feb 21, 2012 at 9:34

u07ch

13.8k5 gold badges45 silver badges48 bronze badges

Collectives™ on Stack Overflow

Create an Index with a WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest