Meaning of Primary Key to Microsoft SQL Server 2008

What meaning does the concept of a primary key have to the database engine of SQL Server? I don't mean the clustered/nonclustered index created on the "ID" column, i mean the constraint objec开发者_开发问答t "primary key". Does it matter if it exists or not?

Alternatives:

1. alter table add primary key clustered
2. alter table create clustered index

Does it make a difference?

In general, a KEY is a column (or combination of columns) that uniquely identifies each row in the table. It is possible to have multiple KEYs in a table (for example, you might have a Person table where both the social security number as well as an auto-increasing number are both KEYs).

The database designer chooses one of theses KEYs to be the PRIMARY KEY. Conceptually, it does not matter which KEY is chosen as the PRIMARY KEY. However, since the PRIMARY KEY is usually used to refer to entries in this table from other tables (through FOREIGN KEYs), choosing a good PRIMARY KEY can be relevant w.r.t. (a) performance and (b) maintainability:

(a) Since the primary key will usually be used in JOINs, the index on the primary key (its size, its distribution, ...) is much more relevant to performance than other indexes.

(b) Since the primary key is used as a foreign key in other tables, changing the primary key value is always a hassle, since all the foreign key values in the other tables need to be modified as well.

A PRIMARY KEY is a constraint - this is a logical object that says something about the rules that your data must adhere to. An index is an access structure - it says something about the way the machine can search through the data. To implement a PRIMARY KEY, most RDBMS-es use an index.

Some RDBMS-es (fe. MySQL) do not make the distinction between PRIMARY KEY or UNIQUE constraint and the index that is used to help implement it. But for example, Oracle does: in oracle you can do something like: ALTER TABLE t DROP pk KEEP INDEX. This is useful if you want to change the definition of the primary key (for example, you are replacing a natural primary key with a surrogate primary key) but you still want to have a unique constraint on the original primary key columns without rebuilding the index. That makes sense if the index is very large and would take considerable table and resources to rebuild.

From what I can see, MS SQL does not make the distinction. I mean a tool like Management studio does display "Keys", "Indexes" and "Constraints" in differrent folders, but changing the name of one immediately changes the name of the corresponding objects in the other folders. So I think here the distinction is not really present in this case.