Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Nonclustered Versus Unique NonClustered Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1065877
Posted Friday, February 18, 2011 8:41 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:14 AM
Points: 731, Visits: 1,943
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?






Post #1066742
Posted Saturday, February 19, 2011 2:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1066762
Posted Saturday, February 19, 2011 5:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:14 AM
Points: 731, Visits: 1,943
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.
Post #1066771
Posted Saturday, February 19, 2011 7:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
I don't understand what you're asking. What 'issue'?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1066782
Posted Saturday, February 19, 2011 9:55 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:14 AM
Points: 731, Visits: 1,943
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.).

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.
Post #1066796
Posted Saturday, February 19, 2011 11:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
sqlchanakya (2/19/2011)
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.).


The guideline for a clustered index is narrow AND unique. 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 other



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1066800
Posted Saturday, February 19, 2011 11:49 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:14 AM
Points: 731, Visits: 1,943
Now am clear, Thank you very much!!!
Post #1066801
Posted Saturday, February 19, 2011 12:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
sqlchanakya (2/18/2011)
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)
)


p.s. A varchar(20) is not 20 bytes in size. It's anything from 2 to 22 bytes in size.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1066802
Posted Monday, February 21, 2011 1:32 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:14 AM
Points: 731, Visits: 1,943
Ohh my bad....Thank you for the correction.
Post #1066962
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse