indexing null values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

indexing null values

Author

Topic

kjackson
Starting Member

1 Post

Posted - 2009-03-27 : 18:12:59

One question I have is, does SQL Server index null values? For example, let's say I have a table:

Person:
name varchar
age integer

I have an index on name.

If I issue

SELECT name FROM person where name is null

will this make use of the index?

Thanks for any help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-27 : 18:29:47

Yes, it will index them. It may or may not use the index depending on how many nulls you have in your data. If you have a 10 million row table and 9 million are null, then it won't bother with that index. Same applies to non-null values, depending on their uniqueness/cardinality.

In SQL 2008 you can add filters to indexes to avoid indexing nulls, or any other conditions. See Books Online for more details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources