December 23, 2016 at 10:08 am
All,
Two part question:
1) Given a table with N columns (N1, N2, N3, N4, ... etc) and assume that column N1 is a clustered PK and column N2 has a unique index/constraint defined on it. Let's further assume for simplicity that these are all INT columns. Is it the case that any index defined that includes column N2 (the column defined as unique) can also be defined as unique? For example, an index that has key columns defined as N2, N3, N4 or as N4, N2, N3 could be defined as unique even if columns N3 & N4 aren't necessarily unique. I'm pretty sure the answer is yes, but want to make sure. 🙂
2) Given the answer to #1 is yes, would it be beneficial to define any index that has column N2 as a key column to be defined unique? Will it help the optimizer generate more efficient query plans having the unique index vs non-unique?
Thanks!
Peter
December 23, 2016 at 10:13 am
1. Yes, every one other index having N2 as a column will be also a unique index.
2. Yes definitely, it helps the optimizer.
Igor Micev,My blog: www.igormicev.com
December 27, 2016 at 10:56 am
100% agree. Definitely define as index as "UNIQUE" whenever possible.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 27, 2016 at 12:25 pm
AZ Pete (12/23/2016)
All,Two part question:
1) Given a table with N columns (N1, N2, N3, N4, ... etc) and assume that column N1 is a clustered PK and column N2 has a unique index/constraint defined on it. Let's further assume for simplicity that these are all INT columns. Is it the case that any index defined that includes column N2 (the column defined as unique) can also be defined as unique? For example, an index that has key columns defined as N2, N3, N4 or as N4, N2, N3 could be defined as unique even if columns N3 & N4 aren't necessarily unique. I'm pretty sure the answer is yes, but want to make sure. 🙂
2) Given the answer to #1 is yes, would it be beneficial to define any index that has column N2 as a key column to be defined unique? Will it help the optimizer generate more efficient query plans having the unique index vs non-unique?
Thanks!
Peter
You can go even further than that. It's pointless to add any additional columns in the index after N2 has been specified, because at that point, your index is unique. (You will probably want to add subsequent columns to the include instead). So in your example, an index on N4, N2, N3 is essentially the same as N4, N2 INCLUDE(N3).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply