SQL Server interview question and answers

1. What is primary key?

• A Primary Key in a table identifies each and every row uniquely.
• It should not allow null values.
• We could assign primary key on only column or more than one column also.

2. What is the difference between primary key and unique key?

• Primary should not allow null; where as unique key will allow nulls.
• By default Primary key is created as clustered index; whereas unique key is created as non clustered index.

3. What are the different levels of data integrity in SQL Server?

• Entity Integrity
• Domain Integrity
• Referential integrity
• Entity Integrity ensures that there are no duplicate rows in a table.
• Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
• Referential integrity ensures that rows cannot be deleted, which are used by other records.

4. Explain different constraints to maintain data integrity in SQL Server?

• Check constraints: 

Check constraints will be useful to limit the range of possible values in a column.

We could create check constraints at two different levels

a) Column-level check constraints are applied only to the column and cannot reference data in another other column

b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables

Default constraints:

Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.

Unique constraints:

A unique constraint restricts a column or combination of columns from allowing duplicate values.

Primary key constraints:

Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.

Foreign key constraints:

Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.