February 16, 2011 at 11:32 pm
Whats the significance of Nonclustered Versus Unique NonClustered indexes if at all possible to have a unique key(one of the column is identity)?
February 17, 2011 at 1:40 am
The simple answer is that SQL Server will make sure that only one row exists for a given value if you declare the index as UNIQUE, otherwise you can have as many rows as you want with the same value.
Is there more to your question?
February 17, 2011 at 3:22 am
Okei, thank for your post. I found something very interesting as below:
I just changed the post little bit to have a better reading....(oops fed up with formating the same...)
In our table,
If (Unique Clustered)
If (Non - Clustered - unique)
{
Btree- Non-clustered Key
Leaf-Non Clustered Key + Clustered Key
}
else if (Non - Clustered = NonUnique )
{
Btree- Non-Clustered Key + Cluster Key
Leaf-Non-Clustered Key + Cluster Key
}
if (Non-Unique Clustered)
If(Non - Clustered = Unique)
{
Btree- Non - Clusetered Key
Leaf - Non - Clusetered Key + Cluster Key + UQI
}
else if (Non - Clustered= non unique)
{
Btree- Non - Clusetered Key + Cluster Key + UQI
Leaf- Non - Clusetered Key + Cluster Key + UQI
}
February 17, 2011 at 4:58 am
Could you explain what that means please? I don't understand what you posted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2011 at 9:52 am
I was just trying to convey like whats the significance of unique key while creating indexes. To me, while I was checking with DBCC IND and PAGE, I could see the above situation.
The non-clustered index key structure would be as follows:
If I have a unique clustered index index and non-unique non-clusterd index indexthen my root level will have non-clustered key + clustered key. At leaf level, non-clustered key + clustered key
If I have a unique clustered index index and unique non-clusterd index indexthen my root level will have non-clustered key . At leaf level, non-clustered key + clustered key
If I have a non-unique clustered index index and non-unique non-clusterd index indexthen my root level will have non-clustered key + clustered key + Uniquifier. At leaf level, non-clustered key + clustered key + Uniquifier.
If I have a non-unique clustered index index and unique non-clusterd index indexthen my root level will have non-clustered key . At leaf level, non-clustered key + clustered key +Uniquifier.
Correct me if am wrong....
February 17, 2011 at 10:17 am
What you saw is CORRECT. And I am sure you know the structure of non-clustered index: the leaf level must have a clustered index key attached (if the clustered index is not unique, it must have the RID attached for the uniqueness).
February 17, 2011 at 10:18 am
sqlchanakya (2/17/2011)
I was just trying to convey like whats the significance of unique key while creating indexes...
mmhhh... to enforce uniqueness perhaps? 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2011 at 10:44 am
Wildcat (2/17/2011)
(if the clustered index is not unique, it must have the RID attached for the uniqueness).
Nope. The only time the RID is used in a nonclustered index is when the base table is a heap (no clustered index)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2011 at 11:04 am
GilaMonster (2/17/2011)
Wildcat (2/17/2011)
(if the clustered index is not unique, it must have the RID attached for the uniqueness).Nope. The only time the RID is used in a nonclustered index is when the base table is a heap (no clustered index)
:blink:
That little off the cuff statement has me doing some research...
Isn't the RID and the "uniquifier" pretty much equivalent from an overhead standpoint, being a 4 byte identifier?
That and I've seen some conflicting information about said "uniquifier" being only applied to duplicated index rows. Every time I think I've got this nailed down it slips sideways a little bit on me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 11:25 am
Craig Farrell (2/17/2011)
Isn't the RID and the "uniquifier" pretty much equivalent from an overhead standpoint, being a 4 byte identifier?
No. To start with, the RID's not 4 bytes.
The RID is the row identifier. An 8 byte combination of file, page and slot. Every row has one, it is unique always. The uniquifier is a 4-byte sequential value that only appears on the rows that have duplicate clustered index key values (the first row SQL encounters won't have a uniquifier, any rows subsequent with the same clustering key will gain one, sequential value, starting at (I believe) 1. It's ony unique in combination with the clustering key, not by itself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2011 at 11:28 am
GilaMonster (2/17/2011)
Craig Farrell (2/17/2011)
Isn't the RID and the "uniquifier" pretty much equivalent from an overhead standpoint, being a 4 byte identifier?No. To start with, the RID's not 4 bytes.
The RID is the row identifier. An 8 byte combination of file, page and slot. Every row has one, it is unique always. The uniquifier is a 4-byte sequential value that only appears on the rows that have duplicate clustered index key values (the first row SQL encounters won't have a uniquifier, any rows subsequent with the same clustering key will gain one, sequential value, starting at (I believe) 1. It's ony unique in combination with the clustering key, not by itself.
Thank you for the clarification. I have some DBCC PAGE work to do.
Again. :pinch:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 18, 2011 at 8:41 pm
Here comes a question,
Say my tale has the following structure:
(
RID varchar(20 - 20bytes
TAID Bigint - 8 bytes
Date DateTime - 8 bytes
RowID Bigint - 8 bytes(identity)
)
Index structures are as follows:
non-unique clustered index - (TAID,Date)
non-unique non clustered - (RID)
If we look at the non clustered index structure,
BTree - RID + TAID + Date + Uniquifier (20+8+8+4) total of = 40 Bytes
Leaf - RID + TAID + Date + Uniquifier (20+8+8+4) total of = 40 Bytes
Say, if am trying to make unique non clusterd index adding RowID in the non-clusterd index,
my structure would change as follows:
BTree - RID + RowID (20+8) total of = 28 Bytes
Leaf - RID + TAID + Date + RowID (20+8+8+8) total of = 44 Bytes
My doubt is, even there would be a huge difference in the byte at btree, I can see an increase in Leaf node on modified index structure. If this is the case, how a unique non-clustered index would be beneficial over a non-unique non-clusterd index. The same question will apply also if I try to make it clustered as unique too.
Anythoughts or Am I missing something somewhere?
February 19, 2011 at 2:53 am
sqlchanakya (2/18/2011)
If this is the case, how a unique non-clustered index would be beneficial over a non-unique non-clusterd index.
It generally isn't.
The cluster should be unique, because of the need for a uniquifier if it isn't. For nonclustered indexes, define them based on the queries yu run. If you have a nonclustered index that is unique, specify the unique keyword, but don't go adding unnecessary columns to get the index unique.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2011 at 5:33 am
Thanks Gail!!! Now I understand what you are saying about the non-clustered index. However,asmall doubt remains as in the above example, as you said, if am trying to add RowID to the non unique clustered index,again there would be the same issue right?
Only thing is uqifier(4 bytes) can be replaced with RowID(bigint 8 bytes) , 4 bytes are again excess in all btree and leaf nodes of non-clustered indexes of the table...
Please correct me if am wrong.
February 19, 2011 at 7:14 am
I don't understand what you're asking. What 'issue'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply