how to create unique nonclustered index on partitioned table without including partition column

Question

Post reply

how to create unique nonclustered index on partitioned table without including partition column

vamshikrishnaeee

SSCrazy

Points: 2519
More actions
August 2, 2010 at 6:28 am

#223955

I have a table 'fddb' with 4 million rows. For better performance we thought of partitioning the table.It has clustered index on primary key column and an unique nonclustered index on four unique key columns. I had partitioned table with primarykey as partition column. But while creating unique nonclustered index on the columns it throwing me error like ''Partition columns for a unique index must be a subset of the index key.''
Is there any way to create unique nonclustered index on a partitioned table without including the ''Partition column.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 1

Can anyone please suggest me a solution on the above problem

Adiga One Orange Chip Points: 27284 More actions · Answer 2

One more cross-post. I have responded to your question here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Adiga (8/2/2010)
One more cross-post. I have responded to your question here

That link points to a thread on XML by a different poster.

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 4

GilaMonster (8/2/2010)
Adiga (8/2/2010)
One more cross-post. I have responded to your question here
That link points to a thread on XML by a different poster.

Sorry 😉 I posted it in the wrong forum.

Vamshi,

Please check if this article meets your requirement.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 5

vamshikrishnaeee

SSCrazy

Points: 2519

August 3, 2010 at 12:28 am

#1201371

Hi Pradeep,

Thanks for your response.

vamshikrishnaeee SSCrazy Points: 2519 More actions · Answer 6

Is there any other way other than using DML triggers?

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 7

I know this thread is a couple of months old but I'm extremely sympathetic to this problem.

According to Books Online (and I haven't actually tried it, yet), you CAN create a separate non-aligned unique index on a partitioned table. You will also lose the some of the advantages of partitioning the table. Of course, you also lose the extremely inconvenient problem of having to include the partitioning column in the index definition which {drum roll please} inherently makes the column non-unique (hell of a Catch 22 there). It also means that you don't have to change all of your FK's that may point to the table, either. The bad part (if you need such a thing) is that you can't (if I read Books Online correctly) do a SWITCH with such an index in place.

My recommendation would be to dig deeper into all of the caveats of partitioning BEFORE you partition your tables using either PARTITIONED VIEWS or PARTITIONED TABLES because they can really mess things up for you if you haven't adequately planned or have missed one of the caveates of partitioning. Of course, I believe that bit of advice is way too late for the OP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)