Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to create unique nonclustered index on partitioned table without including partition column Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 13, 2013 5:03 AM
Points: 137, Visits: 661
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.

Post #962125
Posted Monday, August 2, 2010 6:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 13, 2013 5:03 AM
Points: 137, Visits: 661
Can anyone please suggest me a solution on the above problem
Post #962135
Posted Monday, August 2, 2010 7:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
One more cross-post. I have responded to your question here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #962194
Posted Monday, August 2, 2010 7:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 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 #962207
Posted Monday, August 2, 2010 8:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #962211
Posted Tuesday, August 3, 2010 12:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 13, 2013 5:03 AM
Points: 137, Visits: 661
Hi Pradeep,

Thanks for your response.
Post #962617
Posted Tuesday, August 3, 2010 4:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 13, 2013 5:03 AM
Points: 137, Visits: 661
Is there any other way other than using DML triggers?
Post #962702
Posted Sunday, December 15, 2013 1:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1523062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse