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, 1408🔥, 0💬
Popular Posts:
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...