1

I have a table which contains a non-clustered primary key. I intend to remove this primary key and create a unique Covering Index on the same column instead.

Then The table won't have a primary key, but a unique covering index.

I searched google but could not find a relevant topic. Is that fundamentally wrong? or it's ok to be done.

Update:

Why do I need to convert this index to a covering index?

Because this is the primary key and I have many select queries based on this key. At the moment for each select, there will be a need for a RID Lookup because this is a non-clustered key. If I convert it to a covering index, this RID Lookup will be gone for those select queries and hopefully there will be better performance (at least the excecution plan says that).

Of course integrity is also intended and that's why I will make it a unique covering index. The primary key is a uniqueidentifier, generated by the application (which explains why we don't make it clustered)..

What I am wondering is if that has any negative effects on anything else.

Execution Plan Before:

enter image description here

Execution Plan After:

enter image description here

  • Just to clarify - why not make the primary key clustered? Is it a bad candidate (not sequentially increasing value)? That said - you can apparently set up foreign keys against a unique index even if it's not the primary key. – RDFozz Jul 26 '17 at 16:23
  • @RDFozz The primary-key is a uniquidentifier which is generated in the code (application). In load tests we came up with the conclusion of making it none clustered. – Mori Jul 27 '17 at 8:22
  • It is good practice that uniquidentifier should not be PK+CI,but if that is the only option you are left with then there is no harm in keeping that columns as PK+CI.What was the load test conclusion ? – KumarHarsh Jul 31 '17 at 6:47
2

By removing the primary key and adding a unique covering index with more columns, you are fundamentally changing how the table accepts data. It may be that with the covering index in place, the table design no longer fits the purpose it was designed for.

Take for instance:

USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime2(7) NOT NULL
    , CONSTRAINT PK_T1
        PRIMARY KEY NONCLUSTERED
        (ID1, ID2)
);

INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

The output:

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK_T1'. Cannot insert duplicate key in object 'dbo.T1'. The duplicate key value is (1, 1).
The statement has been terminated.

The table design is only allowing rows where the tuple (ID1, ID2) is unique.

Then look at this, where the primary key does not exist, and has been replaced with a wider unique covering index:

IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
DROP TABLE dbo.T2;
CREATE TABLE dbo.T2
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime NOT NULL
);

CREATE UNIQUE INDEX IX_T2
ON dbo.T2 (ID1, ID2, SomeDate);

INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

(1 row(s) affected)

(1 row(s) affected)

Table T2 has both rows, where arguably, the second insert should have failed and the table should only have a single row. In this design, the table is now accepting rows where the tuple (ID1, ID2) are not necessarily unique.

I'd think carefully about your table design, and potentially just add the covering index without removing the primary key.

  • Thanks for your answer. But I did not understand why you created the index like this "ON dbo.T2 (ID1, ID2, SomeDate)". I would create it like this "ON dbo.T2 (ID1, ID2) INCLUDE (SomeDate) – Mori Jul 27 '17 at 8:35
  • Good point, including the column is a good choice too. Depending on your requirements, a better choice would likely be converting the nonclustered primary key to a clustered primary key, with no other changes. – Max Vernon Jul 27 '17 at 13:28
1

You can change the primary key default to newsequentiaid(), then replace the non-clustered pk with a clustered pk. This solves the non-sequentially increasing primary key issue while retaining unique identifier as the primary key column type.

Compared against a clustered key solution, the biggest downside (so long as you are careful with your unique index logic) is that creating a covering non-clustered index requires you to store the table data twice. inserts and updates have to maintain both copies as well, affecting performance.

With a clustered primary key, a single copy of the table data is stored with the clustered key, in index order (so no key lookups).

  • I can't use newsequentiaid() since the key is generated in the code. But do you have a reference that states "data is stored twice and inserts and updates have to maintain both copies as well, affecting performance"? I could not find anything in the internet. That could potentially be a big problem. – Mori Jul 27 '17 at 9:42
  • technet.microsoft.com/en-us/library/ms190806(v=sql.105).aspx. The additional included columns are stored on leaf pages of the non-clustered index, as well as in original table. They use the same space as additional key columns, but are not considered during seeks and other ops on keys. – Xingzhou Liu Jul 27 '17 at 14:39
  • quick screenshot confirm - using small table on the TPCC schema. drive.google.com/file/d/0ByPNL6sCkWNydzkwMlBMbDM1OWs/… – Xingzhou Liu Jul 27 '17 at 14:43
  • whats the read/write mix of your load? using non-sequential unique id's is very bad for fragmentation when writing, especially inserts. Factor in impact of frequent rebuilds or low fill factors before deciding if the load has a significant amount of writes. – Xingzhou Liu Jul 27 '17 at 23:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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