November 14, 2012 at 10:53 pm
Hi
I have a table with 125 columns, and 250 million rows. Unfortunately the ID column is NVARCHAR(15), and is non-unique. The table undergoes 000's of updates and inserts every day. The clustered index is on fields which assist the reads, however it rapidly gets fragmented and inserts are slow.
I'm attempting to fix the clustered index, and like the idea of partitioning the table...
I considered placing the clustered index on ID & InsertedLogID to make it unique, and would also support partitioning on ranges of InsertedLogID. However 34 (potential) bytes fails the 'Narrow' key objective.
Then I began to think a new Identity column may be a better choice, as it achieves the objective of Ever-Increasing, and reduces page splits. However partitioning on this Identity column wouldn't be very useful.
Could I make the clustered index a compound index of Identity and InsertedLogID columns, and still achieve the Ever-Increasing objective?
Also does UNIQUE need to be specified in the clustered index declaration so SQL knows the key is unique (and doesn't add the uniquifier)?
Thanks in advance,
Dave
November 14, 2012 at 11:54 pm
A Clustered Index doesnt need to be unqiue, in fact the only case where you have to have the Unique index (to my knowledge) is on a PK which by default shuold be unique.
I'm never sure about partitioned tables and clustered Index, Instinct tells me to use the column that is specified in the Partition schema as the Primary column on the cusltered index as, with all other columns in the index as subordinates, but I might be wrong on this, please correct me.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 15, 2012 at 2:59 am
Dave-644455 (11/14/2012)
However partitioning on this Identity column wouldn't be very useful.
Why you think that partitioning wouldnt be useful here ? i think it will help you here as you are handling high volume of data
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 15, 2012 at 7:08 am
Bhuvnesh (11/15/2012)
Dave-644455 (11/14/2012)
However partitioning on this Identity column wouldn't be very useful.Why you think that partitioning wouldnt be useful here ? i think it will help you here as you are handling high volume of data
Why would you want to partition data on an Identity column best practice for partitioning is to ensure like data is grouped together, eg Time sliced data, divisional groupings, or something similar.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 15, 2012 at 1:56 pm
I understand the clustered index doesn't need to be unique, however the column chosen for partitioning (InsertedLogId) is very dense (only about 300 values so far) so wouldn't make a good clustered index on its own.
The reason for choosing InsertedLogId as the partitioning column is because A) it is static, never updated, and B) it is often used in WHERE clauses and could hopefully allow partition elimination.
Does anyone agree this leads to needing a compound clustered index? If so, any suggestions on which fields, and what order they should go in?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply