SQL Server Interview Questions and Answers Part 2

9. What is PRIMARY KEY?

Answer:

A PRIMARY KEY constraint is a unique identifier for a row within a database table and is used to enforce entity integrity. We can have only one primary key constraint for each table.

10. What is UNIQUE KEY constraint?

Answer:

A UNIQUE constraint is created so no duplicate values are entered and thus, enforces the uniqueness of the values in a set of columns.

11. What is FOREIGN KEY?

Answer:

A foreign key is a key used to link two tables together. A foreign key in one table points to a primary key in another table.

They are used to enforce referential integrity and prevent any actions that would destroy links between tables with the corresponding data values.  

12. What is CHECK Constraint?

Answer:

The check constraints are used to enforce domain integrity and are used to limit the values that can be placed in a column.

13. What is NOT NULL Constraint?

Answer:

A NOT NULL constraint enforces that the column will not accept null values.

14. Explain the advantages of using Stored Procedures.

Answer:

Stored procedure can reduced network traffic and provide improved performance. It can have multiple SQL statements but requires single call to the database.

SQL Server caches stored procedure execution plan in its memory and reuse for subsequent calls which helps reducing server overhead.

Stored procedures provide better security to your data. For example, you can restrict access to data by allowing users to manipulate the data only through stored procedures and deny access to the table.

15. What is BCP? When does it used?

Answer:

The Bulk Copy Program (BCP) is a command-line utility that ships with Microsoft SQL Server.

Using BCP, you can import and export large amounts of data in and out of SQL Server databases quickly and easily.