Primary Key - Default Indexes of Tables in SQL Server

Q

Is the PRIMARY KEY Column of a Table an Index in SQL Server?

✍: FYIcenter.com

A

If you define a primary key on a table, an index for the primary key column will be created by default. The tutorial exercise below shows you the index created as part of the primary key column of "fyi_links":

USE FyiCenterData;
GO

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

-- Create a table with primary key
CREATE TABLE fyi_links (
  id INT PRIMARY KEY,
  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

PK__fyi_links__239E4DCF  clustered, unique, primary
                         key located on PRIMARY      id

Notice that the index created as part of the primary key is named by SQL Server as "PK__fyi_links__239E4DCF".

 

Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-13, 502👍, 0💬