locked
Index uniqueness RRS feed

  • Question

  • I have index which was unique. Now, the key value can be inserted twice, so, the index can't be unique any more.
    But if I add date column to this index it will assure uniqueness again.
    But on the other hand I don't need this column in my select statement. The additional column will produce larger index.

    So, what is better in general for searching the index?
    Adding additional column and mark index as unique(this will speed up search since index is unique) or don't add this column(since i don't need it) and have non-unique index(this will speed up the index since index is smaller?

    What is suggested approach in such cases? I guess non-unique index for most of the cases, but I'm not sure.

    Monday, October 14, 2013 8:40 AM

Answers

  • There is no reason to add this column if you won't use it in the WHERE clause. It won't help anything, it will only cost (ass to the index key length).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by simonxy Monday, October 14, 2013 12:17 PM
    Monday, October 14, 2013 11:49 AM

All replies

  • >>>But on the other hand I don't need this column in my select statement

    But does it use in WHERE clause?  I am confused , you said it is used in search but you do not need it in SELECT.... If you are sure that it speeds up the query why not having that?



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 14, 2013 9:06 AM
    Answerer
  • No, this column is never use. It's purpose in index is only to assure uniqueness.

    Monday, October 14, 2013 9:27 AM
  • Ok,if you need to enforce uniqueness you can create an UNIQUE Constraint which creates unique index on those column.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 14, 2013 9:43 AM
    Answerer
  • There is no reason to add this column if you won't use it in the WHERE clause. It won't help anything, it will only cost (ass to the index key length).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by simonxy Monday, October 14, 2013 12:17 PM
    Monday, October 14, 2013 11:49 AM
  • If it's not used in you where clause, then adding the date column to this index and making the index unique will not help with the query and may very well slow it down.  BUT, it's usually true that the most important question is not performance of a particular query, but rather your database integrity.  And if it will break your database if someone adds multiple rows with the same values in these columns, then you want a unique index.  In almost all cases database integrity is more important than performance.  And in most cases where enforcing database integrity causes unacceptable performance, there are better fixes for the performance problem which preserve the database integrity rules for your database.

    Tom

    Monday, October 14, 2013 4:02 PM