February 19, 2011 at 9:55 am
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.
February 19, 2011 at 11:24 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2011 at 11:49 am
Now am clear, Thank you very much!!!
February 19, 2011 at 12:00 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2011 at 1:32 am
Ohh my bad....Thank you for the correction.
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply