In SQL Server, a composite primary key is a primary key that consists of two or more columns in a table. It is also known as a compound primary key. The primary key is used to uniquely identify each row in a table, and a composite primary key allows for more than one column to be used to create this unique identifier.

To create a composite primary key in SQL Server, you must first create a table with the columns you wish to include in the primary key. Then, you can use the ALTER TABLE statement to add the primary key constraint.

Syntax

The syntax for creating a composite primary key is as follows:

ALTER TABLE table_name
ADD CONSTRAINT pk_name PRIMARY KEY (column1, column2, ...)

In this syntax, table_name is the name of the table you wish to add the primary key to, pk_name is the name you want to give to the primary key constraint, and column1, column2, etc. are the names of the columns you want to include in the primary key.

One advantage of using a composite primary key is that it can provide better performance when querying the table. Because the primary key is used to index the table, using a composite primary key that includes the columns commonly used in queries can result in faster query times.

However, there are some limitations to using composite primary keys. For example, all of the columns included in the primary key must be of the same data type. Additionally, if one of the columns in the primary key is frequently updated, this can lead to performance issues due to the overhead of updating the index.

In summary, a composite primary key in SQL Server is a primary key that consists of two or more columns in a table. It can provide better performance when querying the table, but has some limitations that should be considered when deciding whether to use one.