Non Clustered Index on Primary Key Again
In today's weblog posting I desire to talk most a very important topic in SQL Server that ever confuses people when they first work with SQL Server. It is about the difference betwixt the Primary Key constraint and the Amassed Index.
What is a Primary Key?
Let's talk first about the Primary Key constraint itself. Every bit the proper name implies information technology is just a constraint, and with that constraint you tell SQL Server that yous want to have unique values in a specific column or in a specific group of columns. The following listing shows a very unproblematic table definition where the Chief Key constraint is specified on the first column Col1.
CREATE Tabular array Foo ( Col1 INT NOT Zip PRIMARY Key, Col2 INT Not Nada, Col3 INT Not NULL ) Go
Now when you insert records into that table, SQL Server makes certain that you always take unique values in the column Col1. If you try to insert a duplicate value, SQL Server returns error message.
-- Try to insert a duplicate value INSERT INTO Foo Values (i, one, 1), (1, 2, 2) Become
Msg 2627, Level fourteen, State i, Line 9 Violation of Master KEY constraint 'PK__Foo__A259EE544224D12A'. Cannot insert duplicate cardinal in object 'dbo.Foo'. The duplicate fundamental value is (one). The statement has been terminated.
The Primary Cardinal constraint itself is defined on a logical level – you lot just tell SQL Server that yous want to take unique values in a specific cavalcade. But SQL Server as well has to enforce that uniqueness on the physical level – in the data structures where you store your table information. In the example of SQL Server, the uniqueness on the concrete level is enforced with an alphabetize structure – with a Clustered Alphabetize or Not-Amassed Index. Let's accept a more detailed look at this.
Enforcement of the Primary Key constraint
When you specify the Chief Key constraint, SQL Server enforces it by default with a Unique Clustered Index on the physical level. When you look at sys.indexes, you can see that under the covers SQL Server has generated a Unique Clustered Index that is used to enforce the Master Central constraint.
-- SQL Server generates past default a Unique Clustered Index SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Foo') GO 
Every bit I have said, the Unique Clustered Index is created by default. You can too enforce a Primary Primal constraint with a Unique Non-Clustered Alphabetize every bit shown in the following listing.
-- Enforces the Master Primal constraint with a Unique Non-Clustered Index CREATE TABLE Foo1 ( Col1 INT NOT Zero Master Key NONCLUSTERED, Col2 INT NOT NULL, Col3 INT NOT NULL ) GO
When y'all specify the Primary Key constraint, you tin specify the following 2 options:
- Amassed
- NONCLUSTERED
The pick CLUSTERED is the default ane, and therefore yous don't accept to specify it. When yous look now again at sys.indexes, you tin come across now that you have a heap table in front of y'all (a table without a Clustered Index), and that SQL Server has generated an additional Unique Non-Clustered Index to enforce the Master Key constraint.
-- SQL Server has generated at present a Unique Non-Amassed Index to -- enforce the Primary Key constraint SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Foo1') GO 
Primary Key <> Amassed Index
Therefore information technology doesn't mean that a Primary Key and a Amassed Index is always the aforementioned in SQL Server. Information technology is the same by default, only you tin can modify this behavior if you lot want. The Primary Key constraint is always on the logical level, and the index construction is on the concrete level to enforce the constraint itself.
The question at present is when does it make sense to enforce the Primary Primal constraint with a Unique Not-Clustered Index? Back in April 2014 I wrote virtually a problem in SQL Server that is chosen Terminal Page Insert Latch Contention: an ever-increasing Clustered Primal cavalcade like an INT IDENTITY cavalcade doesn't really calibration in SQL Server.
If you hit that specific trouble, it *could* make more sense to physically cluster/sort your table data on a random value – like a UNIQUEIDENTIFIER column. In that instance you can all the same employ the Primary Key constraint on the original ever-increasing key cavalcade, merely enforce information technology with a Unique Non-Clustered Index, and cluster your table on a random key column. The following listing shows this approach.
-- Create the Primary Key constraint on an ever-increasing -- key column CREATE Table Foo2 ( Col1 INT Non Cipher Primary KEY NONCLUSTERED, Col2 UNIQUEIDENTIFIER NOT Goose egg, Col3 INT Not NULL ) GO -- Create the Clustered Index on a random fundamental column CREATE UNIQUE Amassed Index ci_Col2 ON Foo2(Col2) Go
When you await over again at sys.indexes, yous can run across now that you have created a Clustered and Non-Clustered Index. Merely only the Not-Amassed Index is used to enforce the Primary Cardinal constraint.
Summary
A Chief Central constraint and a Clustered Alphabetize is not really the same in SQL Server. By default SQL Server enforces the Master Key constraint with a Unique Clustered Index. Simply if y'all want you could instead enforce information technology with a Unique Not-Amassed Index. Only that approach wouldn't really make sense every bit a default, considering you need to have specific problems to justify that approach.
Thanks for your fourth dimension,
-Klaus
Source: https://www.sqlpassion.at/archive/2015/06/29/primary-key-vs-clustered-index/
0 Response to "Non Clustered Index on Primary Key Again"
Post a Comment