SQLServer Interview Questions and Answers

1. Define Structured Query Language (SQL).

SQL is a programming language which is used for managing data stored in Relational Data Base Management System (RDBMS). Data can be accessed and manipulated using SQL. With the use of SQL, the data can be created, inserted, deleted, updated, retrieved from the data base. SQL is an ANSI (American National Standards Institute) standard.

2. What do you understand by Relational Database Management System (RDBMS)?

RDBMS is a Data Base Management System which is based on the relational model invented by E. F. Codd. It is used to store the data in the forms of tables, fields and records. The first developed RDBMS was Oracle in 1979 by Oracle Corporation.

3. Could you enumerate the properties of the Relational tables?

Relational tables has six properties as below

  1. Their values are atomic
  2. Column values are of the same kind
  3. Each row is unique
  4. The sequence of columns is insignificant
  5. The sequence of rows is insignificant
  6. Each column must have a unique name

4. Would you state the difference between a primary key and a unique key?

There will be only one Primary key in a table but there can be more than one Unique keys in a table. Clustered index on the column are created by the Primary key whereas non-clustered index are created by Unique key. Primary key doesn't allow NULLs but Unique key allows one NULL. Primary key supports auto increment value to generate ID automatically whereas Unique key doesn't supports auto increment value.

5. Enumerate the different index configurations that a table can have.

A table can have following different index configurations-

  1. No indexes
  2. A clustered index
  3. A non-clustered index
  4. A clustered index and many non-clustered indexes
  5. Many non-clustered indexes

6. Could you define bit data type and what information can be stored in it.

Bit data type is the smallest data type used in a language. It can store the boolean information of the form 1 (true) or 0 (false). The earlier versions of SQL server did not support NULL state in this data type but recent versions from SQL server 7.0 onwards support NULL state as well.

7. Define Magic tables in SQL Server.

In SQL Server, there are two system tables "Inserted" and "Deleted" called Magic tables. These tables are created and managed automatically by SQL Server to hold recently inserted, deleted and updated values in a database table. These are the virtual tables which are used with the triggers to retrieve the inserted, deleted or updated rows. When a record is inserted in the table then the record will be in Inserted Magic table. When a record is deleted from that table then record will be in Deleted Magic table. When a record is updated in the table then the existing record will be in Deleted Magic table and modified data will be in Inserted Magic table.

8. Could you explain RANK() function in SQL Server.

RANK() is one of the Ranking functions which is used to give rank to each row. It assigns rank to each record starting with 1. For using this function first specify the function name, followed by the empty parentheses. Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. If it receives more than one record having same ORDER BY values, it is said to be a tie and all of them will get the same rank. The clause specifies the column(s) that you are going to rank.

9. Define Identity.

Identity is a column that automatically generates numeric key values. A start and increment value can be set but most DBA leave these at 1. Identity columns need not be indexed. Each new value for a particular transaction is different from other concurrent transactions on the table.

10. Would you describe Linked Server.

Linked Server enables you to connect to other database instances on the same server or on the remote servers. It is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server data bases using T-SQL Statements. The remote servers can be either SQL Server or Oracle or any one that supports OLE DB to be used as linking servers. With the help of a linked server, we can create and execute SQL statements that allow remote data to be retrieved, joined and combined with local data. We can add new Linked Server through Stored Procedure sp_addlinkedserver and sp_addlinkedsrvlogin.