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.
Best Answer
Since the
TABLE_A
is partitioned onCOL_1
, the new Primary Key being added must includeCOL_1
as part of it.So the PK ends up being created as so: