Difference between Index and Primary Key – MSDN TSQL forum | SQL with Manoj

Difference between Index and Primary Key – MSDN TSQL forum

–> Question:

What is the difference between the Index and the Primary Key?

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered

– Spatial

– Full Text

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)

Check the video on Primary Keys:

Ref link.