Does the UNIQUE Constraint Create an Index?
Submitted by: AdministratorIf 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 "ggl_links":
USE GlobalGuideLineDatabase;
GO
-- Drop the old table, if needed
DROP TABLE ggl_links;
GO
-- Create a table with a UNIQUE constraint
CREATE TABLE ggl_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 ggl_links_url ON ggl_links (url);
GO
<pre>-- View indexes
EXEC SP_HELP ggl_links;
GO
index_name index_description keys
----------------------- -------------------------- ----
ggl_links_url nonclustered located url
on PRIMARY
UQ__ggl_links__4222D4EF nonclustered, unique
key located on PRIMARY id</pre>
Notice that the index created as part of the UNIQUE constraint is named by SQL Server as "UQ__ggl_links__4222D4EF".
Submitted by: Administrator
USE GlobalGuideLineDatabase;
GO
-- Drop the old table, if needed
DROP TABLE ggl_links;
GO
-- Create a table with a UNIQUE constraint
CREATE TABLE ggl_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 ggl_links_url ON ggl_links (url);
GO
<pre>-- View indexes
EXEC SP_HELP ggl_links;
GO
index_name index_description keys
----------------------- -------------------------- ----
ggl_links_url nonclustered located url
on PRIMARY
UQ__ggl_links__4222D4EF nonclustered, unique
key located on PRIMARY id</pre>
Notice that the index created as part of the UNIQUE constraint is named by SQL Server as "UQ__ggl_links__4222D4EF".
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Select Some Specific Rows from a Table in MS SQL Server? |
☺ | What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How to Create a View using Data from Another View? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |