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:
[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
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?
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.