Dense / Spares Indexes

Breako · #1 (**permalink**) 02-07-12, 06:52

Hi,
Does SqlServer have a concept of dense / sparse indexes?

And if so ho do I check how dense or how sparse the index is?

Thanks. info on dense / sparse indexes here: Database index - Wikipedia, the free encyclopedia

Thanks.

MCrowley · #2 (**permalink**) 02-07-12, 09:41

You can add a where clause to an index, and the index will only contain the values defined in that where clause. If you have a column that is 90% null values, you can index just the ones that have values:

Code:

create index ind_test on tablename (sparsecolumn) where sparsecolumn is not null

I have not tried this out myself, so I do not know how extravagant the where clause can get.

Breako · #3 (**permalink**) 02-08-12, 04:25

Quote:

Originally Posted by MCrowley

You can add a where clause to an index, and the index will only contain the values defined in that where clause. If you have a column that is 90% null values, you can index just the ones that have values:

Code:

create index ind_test on tablename (sparsecolumn) where sparsecolumn is not null

I have not tried this out myself, so I do not know how extravagant the where clause can get.

Thanks and how do I check if an index created some time ago is sparse or dense?

MCrowley · #4 (**permalink**) 02-08-12, 09:23

Code:

 select object_name(object_id), name, filter_definition
 from sys.indexes

Internet Services

Web Development

Digital Marketing

Consumer Tech