Today’s post is about a SQL Server design aspect that’s not intuitive, yet is quite logical when you think about it.
All Key Constraints in Microsoft SQL Server are implemented as Indexes.
What the above statement means is that whenever we create a key constraint on a table (primary/foreign or unique key), internally an index is created by Microsoft SQL Server. We can see the use of these indexes when executing queries against the table when we look at the execution plan. When we think about it, we create key constraints so that they help us in establishing referential and data integrity. These keys therefore help us in retrieving data efficiently – which is also the job of indexes on the table. Hence, it quite logical that SQL Server implements key constraints as indexes.
Demo
To demonstrate this point, let me take a small example. The script below creates a table within the AdventureWorks2012 database, and create primary, foreign and unique key constraints on the table (Do keep in mind that this particular table design is for demonstration purposes only).
USE AdventureWorks2012;
GO
–Safety Check
IF OBJECT_ID(‘dbo.KeysAsIndexTest’,’U’) IS NOT NULL
DROP TABLE dbo.KeysAsIndexTest;
GO–Create Table
CREATE TABLE dbo.KeysAsIndexTest (RecordId INT NOT NULL,
EmployeeId INT NOT NULL,
RecordValue VARCHAR(50) NOT NULL
);–Primary Key
ALTER TABLE dbo.KeysAsIndexTest
ADD CONSTRAINT pk_KeysAsIndexTest
PRIMARY KEY CLUSTERED (RecordId);
GO–Foreign Key
ALTER TABLE dbo.KeysAsIndexTest
ADD CONSTRAINT fk_Employee_KeysAsIndexTest
FOREIGN KEY (EmployeeId)
REFERENCES HumanResources.Employee (BusinessEntityId);
GO–Unique Key
ALTER TABLE dbo.KeysAsIndexTest
ADD CONSTRAINT uk_KeysAsIndexTest
UNIQUE (RecordValue);
GO
After creating the table and the constraints, let us query the sys.indexes catalog view to see how many indexes have been created against the table:
USE AdventureWorks2012;
GO
SELECT OBJECT_NAME(si.object_id) AS ObjectName,
si.name AS IndexName,
si.type_desc AS IndexType,
si.is_unique AS IsUnique,
si.is_primary_key AS IsPrimaryKey,
si.is_unique_constraint AS IsUniqueConstraint,
si.index_id AS IndexId
FROM sys.indexes AS si
WHERE si.object_id = OBJECT_ID(‘dbo.KeysAsIndexTest’,’U’);
GO
The output of the above query is shown in the screenshot below:
As you can see, both the primary key and the unique key are implemented as indexes on the test table – dbo.KeysAsIndexTest.
However, where’s the index for the Foreign key?
The answer is quite simple and lies in the definition of a foreign key. Per Books On Line, “FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.” This means that if a foreign key constraint was successfully defined, it will be referring to a primary key or a unique key on the referenced table which in-turn would have been implemented as indexes.
Foreign keys help ensure that if a value exists in the table, it must exist first in the referenced table column. Hence, the real index and lookup requirements are on the referenced table and not on the referencing table. So, as far as foreign keys are concerned, SQL Server goes the smart way by not creating what would be a duplicate index.
Concluding…
To me, it is an interesting aspect on the overall SQL Server design – it tells me how SQL Server works and how to get the most advantage by defining the correct keys. Because key constraints are implemented as indexes, they should also be defined to be the most selective in a join or a real-world scenario – an design point which many designs consider when defining primary and foreign keys, but miss when creating unique keys.
Further Reading
- Clustered Indexes may not be unique, and unique indexes may not be clustered [Link]
- Primary Keys without clustered indexes [Link]
- Create Table [Books On Line, Link]
- sys.indexes Catalog View [Books On Line, Link]
Until we meet next time,
SQLTwins by Nakul Vachhrajani 