Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I create a Primary Key as so:

ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID()

And then proceeded to create the following index:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA] 
(
    [COL_1] ASC,
    [COL_2] ASC,
    [COL_3] ASC,
    [COL_4] ASC,
    [COL_5] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

The following exception is thrown:

Column [Col_1] is partitioning column of the index PK_TABLE_A_1234. Partition columns for a unique index must be a subset of the index key.

I think that in order to solve this issue, all I need to do is:

A. Include column MY_KEY as part of the IDX1_TABLEA or ...

B. Include all columns from IDX1_TABLEA as part of the PK as well as including the PK (MY_KEY) as part of the IDX1_TABLEA?

Unfortunately I cannot test either option reliably since our TABLE_A in our DEV environment is not partitioned whereas in our TEST environment the same table is partitioned.

The exception actually occurred while the script to create the PK and the INDEX was being executed in our TEST environment but the same script ran perfectly fine in DEV.

FYI: I already tried option A (adding the MY_KEY column as part of the index IDX1_TABLEA) and it worked in DEV, but again, I'd like to confirm with some expert here that this will work fine when I execute the modified script in the TEST environment again.

So my modified version is this:

CREATE NONCLUSTERED INDEX [IDX1_TABLEA] ON [dbo].[TABLEA] 
    (
        [MY_KEY] ASC,
        [COL_1] ASC,
        [COL_2] ASC,
        [COL_3] ASC,
        [COL_4] ASC,
        [COL_5] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

Can anyone confirm whether this is enough to get rid of that exception?

Thanks.

Update

Issue solved. See my own answer below.

In summary, I was creating a new PK in a table that was already partitioned on one of its columns, the new PK needed to include the partitioned column as part of it.

share|improve this question

migrated from stackoverflow.com Sep 12 '12 at 18:23

This question came from our site for professional and enthusiast programmers.

    
I think you have a typo in the error message, I'm assuming the column listed should be MY_KEY not COL_1 which is already the first index key column. –  JNK Sep 12 '12 at 18:46
    
I think this should work but I'll let someone else who is sure weigh in. If your indexes are partition aligned my understanding is the partition key(s) need to be the first keys in the NC indexes. –  JNK Sep 12 '12 at 18:46
    
@jnk I tried option A and the f***ing thing ran for over 4 hours to then throw the same Exception. Apparently Col_1 is a partitioned column in the table so I must make it part of every index I create, clustered or non-clustered. The problem for me is that this has been trial and error since I don't even have access to see table definitions in that environment. At least I think I have a better understanding of what's going on now. :\ –  Icarus Sep 13 '12 at 1:34
add comment

1 Answer 1

up vote 1 down vote accepted

Since the TABLE_A is partitioned on COL_1, the new Primary Key being added must include COL_1 as part of it.

So the PK ends up being created as so:

-- Add the MY_KEY column 
ALTER TABLE dbo.TABLEA
ADD [MY_KEY] UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWSEQUENTIALID() NOT NULL

-- Create the PK constraint 
ALTER TABLE dbo.TABLEA
ADD CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (MY_KEY,COL_1)
share|improve this answer
    
Note that my_key is now no longer a key for the table. The table now allows (1, 1) and (1, 2) as my_key and col_1 values, for example. If you need to guarantee my_key is unique across all partitions, you will need a non-partitioned constraint or index to achieve that. –  Paul White Sep 14 '12 at 11:42
    
@SQLKiwi thanks for your comment. Yes, I am aware of this and it's a valid scenario for me. –  Icarus Sep 14 '12 at 11:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.