none
Unique, Clustered and Non Clustered indexes on same column

    Question

  • Hi ,

    My question might be a silly. I have tried to create one unique index, one clustered index and non-clustered index on the same column, which SQL server has allowed. Now my question is , what will be the actual structure of the indexed table??

    Tuesday, January 12, 2010 7:14 AM

Answers

  • So you created three separate indexes, right?

    The clustered index you created means that the table is now sorted based on that column. The leaf pages of the index tree are your data pages. And you also have the higher levels for the index (up to the root level).

    The unique index then need to be a non-clustered index, since you already have a clustered. This is just like any non-clustered index, with the leaf pages containing the index key and the clustering key as bookmark/pointer for the row. Since it is unique, you cannot insert several rows with the same key values.

    The non-unique index also need to be non-clustered for the same reason as above. Actually, is has the same properte above, except for the uniqueness constraint.

    In real life, I would combine these three into one index. A unique clustered index.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, January 12, 2010 9:52 AM
  • I agree with Tibor that one unique clustered index should be enough. I think I also recently read this interesting article
    explaining the usage of INCLUDE clause

    http://www.databasejournal.com/features/mssql/article.php/3787021/Exploring-SQL-Servers-Index-INCLUDEs.htm

    Recommendations from this article:

    Recommendations
    •Indexes with INCLUDEs are a big win for frequently-called range queries.
    •If access is always per-record on the clustered index, don’t add indexes with INCUDEs.
    •For frequently called relatively-static data, consider using multiple indexes with INCLUDEs to cover different queries even if the KeyColumns are the same.
    •Where you have created covering indexes where all the columns are in the KeyColumns, consider changing these indexes to have the minimal set of KeyColumns and the other columns in the IncludedColumns. This will lower the space requirements and speed the retrieval of records.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 13, 2010 4:35 AM
  • Hello,

    If you do not have clustered index created, the non-clustered indexes will have pointers to the heap. But since you have clustered index, the data is stored in the leaf pages of the clustered index & hence the non clustered index which you created before(the unique non-clustered one)  will have pointers to the clustered index.

    Note: Clustered index can be non - unique as well, in this case sql server will generate its own uniqueifier column to make it as unique. The uniqueifier column is hidden from users.

    As Noam mentioned we could use Included columns starting SQL 2005, where the nonkey columns can be added to the leaf level of the nonclustered index. The nonclustered index can be unique or nonunique.

    Hope this helps !

    Thanks,
    Krishna
    www.sqlserver.in
    http://blogs.sqlserver.in

    Wednesday, January 13, 2010 5:48 AM

All replies

  • So you created three separate indexes, right?

    The clustered index you created means that the table is now sorted based on that column. The leaf pages of the index tree are your data pages. And you also have the higher levels for the index (up to the root level).

    The unique index then need to be a non-clustered index, since you already have a clustered. This is just like any non-clustered index, with the leaf pages containing the index key and the clustering key as bookmark/pointer for the row. Since it is unique, you cannot insert several rows with the same key values.

    The non-unique index also need to be non-clustered for the same reason as above. Actually, is has the same properte above, except for the uniqueness constraint.

    In real life, I would combine these three into one index. A unique clustered index.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, January 12, 2010 9:52 AM
  • Thanx for the reply.

    What I have understood from your reply is that If one has created all three indexes on the same column then unique clustered index will over-shadow the properties/features of other types.

    Please correct if I am wrong.

    Wednesday, January 13, 2010 4:25 AM
  • I agree with Tibor that one unique clustered index should be enough. I think I also recently read this interesting article
    explaining the usage of INCLUDE clause

    http://www.databasejournal.com/features/mssql/article.php/3787021/Exploring-SQL-Servers-Index-INCLUDEs.htm

    Recommendations from this article:

    Recommendations
    •Indexes with INCLUDEs are a big win for frequently-called range queries.
    •If access is always per-record on the clustered index, don’t add indexes with INCUDEs.
    •For frequently called relatively-static data, consider using multiple indexes with INCLUDEs to cover different queries even if the KeyColumns are the same.
    •Where you have created covering indexes where all the columns are in the KeyColumns, consider changing these indexes to have the minimal set of KeyColumns and the other columns in the IncludedColumns. This will lower the space requirements and speed the retrieval of records.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 13, 2010 4:35 AM
  • Hello,

    If you do not have clustered index created, the non-clustered indexes will have pointers to the heap. But since you have clustered index, the data is stored in the leaf pages of the clustered index & hence the non clustered index which you created before(the unique non-clustered one)  will have pointers to the clustered index.

    Note: Clustered index can be non - unique as well, in this case sql server will generate its own uniqueifier column to make it as unique. The uniqueifier column is hidden from users.

    As Noam mentioned we could use Included columns starting SQL 2005, where the nonkey columns can be added to the leaf level of the nonclustered index. The nonclustered index can be unique or nonunique.

    Hope this helps !

    Thanks,
    Krishna
    www.sqlserver.in
    http://blogs.sqlserver.in

    Wednesday, January 13, 2010 5:48 AM