tcronin 95651 (6/29/2015)
Dealing with 3rd party database. When analyzing tables/indexes found 2 indexes on one table. Which would not be bad butindex #1 clustered index on column a.
index #2 non clustered unique index on column a.
For the life of me I can't think of a scenario why you would not want just a clustered unique index and drop index #2, anyone think of any scenario where this would not be true?
It depends upon the query.
Imagine a large table that has 8000 bytes per row. Each row will be on a separate page, and a lot of pages may need to be read to get satisfy a query.
Now if that column is an integer (4 bytes), about 2000 rows could be on that single page.
If the query needs more than one row, and only needs the indexed column, which one would be more efficient?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes