4

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.

0

1 Answer 1

1

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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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