SQL Server Interview Questions and Answers

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is designed to run on a central server so that multiple users can access the same data simultaneously. Generally, users access the database through an application. This article contains the top 20 SQL Server interview questions and answers, in order to prepare you for the interview.

1. What is SQL Server?

   Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is designed to run on a central server so that multiple users can access the same data simultaneously. Generally, users access the database through an application.

2. What are the different versions of SQL Server?

   There are the following versions of SQL Server have been released at the time of writing this book:
   Version

   Year

   Release Name

   8.0

   2000

   SQL Server 2000

   8.0

   2003

   SQL Server 2000 (64-bit)

   9.0

   2005

   SQL Server 2005

   10.0

   2008

   SQL Server 2008

   10.5

   2010

   SQL Server 2008 R2

   11.0

   2012

   SQL Server 2012

   12.0

   2014

   SQL Server 2014

   13.0

   2016

   SQL Server 2016

   14.0

   2017

   SQL Server 2017

3. What new features are coming to SQL Server 2019?

   At the time of writing this book, SQL Server 2019 RTM was not released. The following features are coming in SQL Server 2019 release.
   • The single virtual data layer
   • Data virtualization and Integrating Data
   • No data replication and Managing all data
   • Spark Built-In
   • Unified platform for big data analytics
   • Spark jobs
   • Train machine learning models

4. Can you access or query remote SQL Server database from a Mac, Linux or Ubuntu machine?

   Yes, you can connect or query your remote SQL Server database from your Mac, Linus or Ubuntu machines using Azure Data Studio tool.

5. What is Azure Data Studio?

   Azure Data Studio is an alternative way to SQL Server Management Studio (SSMS) which you can run only on windows machines to query, editing and data development tasks. Azure Data Studio offers a modern editor experience to connect with a remote SQL Server database. It helps us to query and manage data across multiple sources with intellisense.

6. What is the difference between DBMS and RDMS?

   DBMS and RDBMS, both are used to store, manage and query the data. But both have some important differences as listed below-
   DBMS (Database Management System)

   RDBMS (Relational Database Management System)

   DBMS stands for Database Management System and treats data as files internally.

   RDBMS stands for Relational Database Management System and treats data as relations means tables.

   It defines the relationship between the files programmatically.

   It defines the relationship between the relations called tables at the time of table creation.

   It does not impose any constraints or security with regard to data manipulation.

   It imposes constraints or security with regard to data manipulation.

   It does not support distributed architecture.

   It supports distributed architecture

   It does not support Client-Server architecture

   It supports Client-Server Architecture

   Only one user can access data at a time

   Multiple users can access the data at the same time

   It satisfies maximum 6 to 7 rules of E.F. Codd (Edgar Frank "Ted" Codd) out of 12 rules.

   It satisfies more than 6 to 7 rules of E.F. Codd out of 12 rules.

   Example- File System, XML, FoxPro, IMS

   Example – SQL Server, Oracle, DB2, MySQL

7. What is normalization?

   Normalization or data normalization is a process to organize the data into a tabular format (database tables) keeping two goals in mind.
   • Reducing data redundancy.
   • Ensuring data dependency.
     A good database design includes the normalization, without normalization, a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.

8. What are the different normal forms?

   We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. There are following normal forms:
   1. First Normal Form (1NF)
   2. Second Normal Form (2NF)
   3. Third Normal Form (3NF)
   4. BCNF
   5. Fourth Normal Form (4NF)
   6. Fifth Normal Form (5NF)
   Generally, we organize the data up to third normal form. We rarely use the fourth and fifth normal form.

9. What are the differences between char and nchar?

   These data type is used to stores characters but these are different in many cases as given below:

   char

   This is a fixed length characters data type. It takes one byte per character and used to store non-Unicode characters. Suppose, you declare a field with char(20) then it will allocate memory for 20 characters whether you are using only 10 characters. Hence memory for 10 characters which is empty will be wasted.

   nchar

   This is like as char data type but it takes two bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese etc.) characters in the database.

10. What are the differences between varchar and nvarchar?

    There are following differences between varchar and nvarchar:

    varchar

    This is a variable length characters data type. It takes one byte per character and can store non-Unicode characters (like English). This data type allocates the memory based on number of characters inserted. Hence, no wastage of memory.

    nvarchar

    This is like as char data type but it takes 2 bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese etc.) characters in the database.

11. What is SQL key?

    A key is a single or combination of multiple fields in a table. It is used to retrieve records or data-rows from data table based on the condition. Keys are also used to create a relationship among different database tables or views.

12. What are different types of SQL Keys?

    There are following types of SQL Keys –
    1. Super Key - the Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. For Example Primary key, Unique key, Alternate key are a subset of Super Keys.
    2. Candidate Key - A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key. For Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.
    3. Primary Key - the Primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.
    4. Alternate key - A Alternate key is a key that can be work as a primary key. Basically, it is a candidate key that currently is not the primary key. For Example:In below diagram, RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.
       
    5. Composite/Compound Key - Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
    6. Unique Key - the Unique key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It is like a Primary key but it can accept only one null value and it cannot have duplicate values.
    7. Foreign Key - Foreign Key is a field in a database table that is Primary key in another table. It can accept multiple null, duplicate values.For Example We can have a DeptID column in the Employee table which is pointing to the DeptID column in a department table where it a primary key.
       Note- Practically in the database, we have only three types of keys Primary Key, Unique Key and Foreign Key. Other types of keys are only concepts of RDBMS that we need to know.

13. What are SQL Commands?

    SQL commands are a set of instructions that are used to interact with the database like SQL Server, MySQL and Oracle etc. SQL commands are responsible to create and to do all the manipulation on the database. These are also responsible to give or take out access rights on a particular database. There are following types of commands:
    • Data Definition Language (DDL)
    • Data Manipulation Language (DML)
    • Data Query Language (DQL)
    • Transaction Control Language (TCL)
    • Data Control Language (DCL)

14. What is Database Table?

    An RDBMS store the data using one than a database table. A database table manages the data in row and columns format. Each row in a table has its own primary key which uniquely identifies that row or record. The data associated with tables are physically stored in the database memory.
    

15. What is a transaction?

    A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to the database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

16. Why use transaction in SQL Server?

    We use transaction in that case when we try to modify more than one table or views that are related to e each other. Transactions affect SQL Server performance greatly. Since, when a transaction is initiated then it locks all the tables’ data that are used in the transaction. Hence during transaction life cycle, no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

17. What are different types of transaction in SQL Server?

    There are following types of transactions in SQL Server as given below:
    • Implicit Transaction
    • Explicit Transaction

18. Write SQL queries to get the nth highest and lowest salary of an employee?

    The queries are given below-
    Query to get nth (3rd) highest Salary:

    
    Select TOP 1 Salary as '3rd Highest Salary' 
    from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 
    a ORDER BY Salary ASC
    
    

    Query to get nth (3rd) lowest Salary:

    
    Select TOP 1 Salary as '3rd Lowest Salary' 
    from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
    a ORDER BY Salary DESC 
    
    

19. Write SQL query to get field name, data type and size of a database table?

    The query is given below-

    SELECT column_name as 'Column Name', data_type as 'Data Type',
    character_maximum_length as 'Max Length'
    FROM information_schema.columns
    WHERE table_name = 'tblUsers' 
    

    

20. What are SQL Joins?

    SQL joins are used to retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. In SQL Server, there are three types of joins as given below:
    • Inner Join
    • Outer Join
    • Cross Join