Collections:
UNIQUE Constraint Creating Default Index in SQL Server
Does the UNIQUE Constraint Create an Index in SQL Server?
✍: FYIcenter.com
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".
⇒ Difference Between Clustered and Non-Clustered Indexes in SQL Server
⇐ Primary Key - Default Indexes of Tables in SQL Server
2016-11-13, 3216🔥, 0💬
Popular Posts:
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
How to convert a JSON (JavaScript Object Notation) quoted string into a regular character string usi...