primary key and index concept

I create a column as primary key , does this crea开发者_运维知识库te indexes automatically ? or do i need to create indexes explicitly. i was under assumption that primary key also maintains indexes

In SQL Server creating a Primary key will create a Unique Clustered Index on that Column. or more specifically from here

Note PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

This should clear out some air.

--creating a table without any primary key
CREATE table understanding_indexes
 (P_Id int,
 LastName varchar(255),
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255)
 )
 --
 --checking for indexes
 sp_helpindex understanding_indexes

OUTPUT

 The object 'understanding_indexes' does not have any indexes, or you do not have permissions.

--ADDING A NOT NULL CONSTRAINT
ALTER TABLE UNDERSTANDING_INDEXES 
ALTER COLUMN P_Id INTEGER
NOT NULL

--ADDING A PRIMARY KEY Constraint, can only be done on column which are not null.
ALTER TABLE UNDERSTANDING_INDEXES
ADD PRIMARY KEY (P_Id)

sp_helpindex understanding_indexes

OUTPUT

PK__understa__A3420A5702084FDA  clustered, unique, primary key located on PRIMARY  P_Id

Overall, As soon as you add a primary key constraint on a table, it automatically adds clustered indexes on the table.

This illustration is on SQL Server 2008 R2.