What is the difference between an index and a key? How are they related?
A primary key by default creates a clustered index
A unique constraint/key by default creates a non-clustered index
To understand the basics of indexes/keys read this or simply google:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-clustered-indexes?view=sql-server-2017
There seams to be some confusion between what a Primary Key is, and what an Index is and how they are used.
The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null.
An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index.
1 Like
An index is a (logically) ordered list of rows. For example, an index on LastName means all values are already sorted in LastName order. Usually index rows contain far fewer columns in them than the table itself (except the clustered index, which is the table).
A key is a column or columns that defines the order of an index. For example, on an index ordered by ( LastName, FirstName ), then LastName and FirstName are the keys.
Btw, a primary key is a physical object, not a logical one. The db engine needs physical rows in order to insure unique values in the index.
The Primary Key is a logical object. By that I mean that is simply defines a set of properties on one column or a set of columns to require that the columns which make up the primary key are unique and that none of them are null. Because they are unique and not null, these values (or value if your primary key is a single column) can then be used to identify a single row in the table every time. In most if not all database platforms the Primary Key will have an index created on it.
An index on the other hand doesn’t define uniqueness. An index is used to more quickly find rows in the table based on the values which are part of the index. When you create an index within the database, you are creating a physical object which is being saved to disk.