InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:15:17 GMT20http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxOhh my bad....Thank you for the correction.Mon, 21 Feb 2011 01:32:14 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]sqlchanakya (2/18/2011)[/b][hr]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))[/quote]p.s. A varchar(20) is not 20 bytes in size. It's anything from 2 to 22 bytes in size.Sat, 19 Feb 2011 12:00:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxNow am clear, Thank you very much!!!Sat, 19 Feb 2011 11:49:03 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]sqlchanakya (2/19/2011)[/b][hr]Sorry for the incorrect usage "Issue" . I meant, if we are adding ROWID to eliminate the uquifier in the non clustered index(because of non uniqueness in clusterd index), the size of the b tree and leaf level again would increase. So I would like to know that how come making a clustered index unique would be beneficial (offcourse 4 byte uquifier can be replaced with 8 byte ROWID(4 byte is still excess.).[/quote]The guideline for a clustered index is [b]narrow[/b] AND [b]unique[/b]. If you can't make the chosen clustering key unique without making it wide, then stick with a non-unique cluster or find another location for the clustered index or make it wide and accept the effects.These are guidelines, not hard rules that must be followed. Just understand what it is that you're trading off if you chose one over the otherSat, 19 Feb 2011 11:24:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxSorry for the incorrect usage "Issue" . I meant, if we are adding ROWID to eliminate the uquifier in the non clustered index(because of non uniqueness in clusterd index), the size of the b tree and leaf level again would increase. So I would like to know that how come making a clustered index unique would be beneficial (offcourse 4 byte uquifier can be replaced with 8 byte ROWID(4 byte is still excess.).Another approach, just a thought, if am taking the date field out of the cluster combination and making as an include column of the non-clustered index, only my leaf would have the date(8 byte) not at the b tree level (again,my non- clustered is non-unique, so uquifier would be present). But none of my btree would have Date , I can save 8 byte in non-clusterd index as well as btree of clustered index)Could you please give your suggestions on the same.Sat, 19 Feb 2011 09:55:19 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxI don't understand what you're asking. What 'issue'?Sat, 19 Feb 2011 07:14:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxThanks 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.Sat, 19 Feb 2011 05:33:21 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]sqlchanakya (2/18/2011)[/b][hr]If this is the case, how a unique non-clustered index would be beneficial over a non-unique non-clusterd index.[/quote]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.Sat, 19 Feb 2011 02:53:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxHere 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 BytesLeaf - RID + TAID + Date + Uniquifier (20+8+8+4) total of = 40 BytesSay, 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 BytesLeaf - RID + TAID + Date + RowID (20+8+8+8) total of = 44 BytesMy 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?Fri, 18 Feb 2011 20:41:10 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]GilaMonster (2/17/2011)[/b][hr][quote][b]Craig Farrell (2/17/2011)[/b][hr]Isn't the RID and the "uniquifier" pretty much equivalent from an overhead standpoint, being a 4 byte identifier?[/quote]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.[/quote]Thank you for the clarification. I have some DBCC PAGE work to do.Again. :pinch:Thu, 17 Feb 2011 11:28:56 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]Craig Farrell (2/17/2011)[/b][hr]Isn't the RID and the "uniquifier" pretty much equivalent from an overhead standpoint, being a 4 byte identifier?[/quote]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.Thu, 17 Feb 2011 11:25:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]GilaMonster (2/17/2011)[/b][hr][quote][b]Wildcat (2/17/2011)[/b][hr] (if the clustered index is not unique, it must have the RID attached for the uniqueness).[/quote]Nope. The only time the RID is used in a nonclustered index is when the base table is a heap (no clustered index)[/quote]: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.Thu, 17 Feb 2011 11:04:28 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]Wildcat (2/17/2011)[/b][hr] (if the clustered index is not unique, it must have the RID attached for the uniqueness).[/quote]Nope. The only time the RID is used in a nonclustered index is when the base table is a heap (no clustered index)Thu, 17 Feb 2011 10:44:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspx[quote][b]sqlchanakya (2/17/2011)[/b][hr]I was just trying to convey like whats the significance of unique key while creating indexes...[/quote]mmhhh... to enforce uniqueness perhaps? :-DThu, 17 Feb 2011 10:18:30 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxWhat 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).Thu, 17 Feb 2011 10:17:57 GMTWildcathttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxI 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 keyIf 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 keyIf 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....Thu, 17 Feb 2011 09:52:10 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxCould you explain what that means please? I don't understand what you posted.Thu, 17 Feb 2011 04:58:54 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxOkei, 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 }Thu, 17 Feb 2011 03:22:14 GMTsqlzealot-81http://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxThe 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?Thu, 17 Feb 2011 01:40:16 GMTIan Scarletthttp://www.sqlservercentral.com/Forums/Topic1065445-391-1.aspxWhats the significance of Nonclustered Versus Unique NonClustered indexes if at all possible to have a unique key(one of the column is identity)?Wed, 16 Feb 2011 23:32:14 GMTsqlzealot-81