Included Columns on Unique Non-Clustered Indexes are not part of the Unique Constraint

Included Columns on Unique Non-Clustered Indexes are not part of the Unique Constraint

I needed to add a Unique Constraint today to a table. We could go ahead and just add a constraint, but the data I wanted to constrain on was already indexed. Could I just make that a Unique Index and be done with it? Let’s find out with the following table:

CREATE TABLE Greg 
(
	ID INT PRIMARY KEY IDENTITY(1, 1), 
	Col1 INT NOT NULL, 
	Col2 INT NOT NULL,
	IncludedValue VARCHAR(5) NULL,
	NotIncludedValue1 VARCHAR(MAX) NULL,

	NotIncludedValue2 VARCHAR(MAX) NULL
)

Nice simple table. Let’s say that I frequently search on Col1 and Col2, and want to find the value of IncludedValue. Something like this:

SELECT	Greg.IncludedValue
FROM	Greg
WHERE	Greg.Col1 = 1
  AND	Greg.Col2 = 2

I could create this index on my table to make the search faster:

CREATE NONCLUSTERED INDEX Col1Col2_Incldues
ON Greg (Col1, Col2)
INCLUDE (IncludedValue)

Nice! Everything works well, I can do an Index Seek and get back IncludedValue. The business model means that you know that the combination of Col1 and Col2 is meant to be unique. How do you enforce that in the database? You could add a constraint, but could you just make this index unique? Let’s create it and find out:

CREATE UNIQUE NONCLUSTERED INDEX Col1Col2_Incldues_Unique
ON Greg (Col1, Col2)
INCLUDE (IncludedValue)

Then we’ll insert a record:

INSERT INTO Greg (Col1, Col2, IncludedValue)
VALUES
(	1,
	1,
	'1st'
)

Now if you’re playing along at home, what will happen when I run the following command? Will it let me insert a record because the IncludedValue is different, or will it block the record because Col1 and Col2 are the same?

INSERT INTO Greg (Col1, Col2, IncludedValue)
VALUES
(	1,
	1,
	'2nd'
)

Made a guess? Or did you just look below?

SQL blocks the record.

The indexed key is what is being checked for uniqueness. The included columns are not checked.

Is this better than a unique constraint? It’s not better or worse, it’s exactly what a unique constraint does anyway! From the docs:

The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. 

The database would have created this index anyway, just without my included column. So if you already have an index with all of the unique columns, or you can expand an existing index to only have the unique columns in its key then make that a unique index instead of just letting SQL add another index.