Nonclustered Versus Unique NonClustered

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Nonclustered Versus Unique NonClustered

20 posts, Page 2 of 2

««1 2

Nonclustered Versus Unique NonClustered

Rate Topic

Display Mode

Topic Options

Author

Message

Evil Kraig F

Posted Thursday, February 17, 2011 11:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

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

- 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

sqlzealot-81

Posted Friday, February 18, 2011 8:41 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

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

GilaMonster

Posted Saturday, February 19, 2011 2:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678, Visits: 29,933

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

sqlzealot-81

Posted Saturday, February 19, 2011 5:33 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

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

GilaMonster

Posted Saturday, February 19, 2011 7:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678, Visits: 29,933

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

sqlzealot-81

Posted Saturday, February 19, 2011 9:55 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

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

GilaMonster

Posted Saturday, February 19, 2011 11:24 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678, Visits: 29,933

sqlchanakya (2/19/2011)

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

sqlzealot-81

Posted Saturday, February 19, 2011 11:49 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

Now am clear, Thank you very much!!!

Post #1066801

GilaMonster

Posted Saturday, February 19, 2011 12:00 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678, Visits: 29,933

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

sqlzealot-81

Posted Monday, February 21, 2011 1:32 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

Ohh my bad....Thank you for the correction.

Post #1066962

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions