Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Dense / Spares Indexes

    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by MCrowley View Post
    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?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
     select object_name(object_id), name, filter_definition
     from sys.indexes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •