SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioned compound clustered indexes


Partitioned compound clustered indexes

Author
Message
Dave-644455
Dave-644455
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 51
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5338 Visits: 4076
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;-)
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2115 Visits: 3232
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
Dave-644455
Dave-644455
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 51
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search