SQL Server FAQ - UNIQUE Constraint Creating Default Index

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

Does the UNIQUE Constraint Create an Index?

If you add the UNIQUE constraint on a column, SQL Server will automatically add a non-clustered index for that column. The tutorial exercise below shows you the index created as part of the UNIQUE column, "id", of "fyi_links":

USE FyiCenterData;
GO

-- Drop the old table, if needed
DROP TABLE fyi_links;
GO

-- Create a table with a UNIQUE constraint
CREATE TABLE fyi_links (
  id INT UNIQUE,
  url VARCHAR(80) NOT NULL,
  notes VARCHAR(1024),
  counts INT,
  created DATETIME NOT NULL DEFAULT(getdate())
);
GO

-- Create an index for column "url"
CREATE INDEX fyi_links_url ON fyi_links (url);
GO

-- View indexes
EXEC SP_HELP fyi_links;
GO
index_name               index_description           keys
-----------------------  --------------------------  ----
fyi_links_url            nonclustered located        url
                         on PRIMARY

UQ__fyi_links__4222D4EF  nonclustered, unique
                         key located on PRIMARY      id

Notice that the index created as part of the UNIQUE constraint is named by SQL Server as "UQ__fyi_links__4222D4EF".

(Continued on next topic...)