SQL Server Interview Questions and Answers

Structured Query Language (SQL) is a domain-specific language used to communicate with the Relational Database Management System (RDBMS) and manipulate data stored in the database. With RDBMS, we store data in the database in the form of tables.

Moreover, a table holds data entries in the form of rows and columns. In 1986, American National Standards Institute (ANSI) and then, in 1987, International Organization for Standardization (ISO) announced SQL as a standard language for relational database management systems.

All systems that utilize E. F. Codd’s relational model are relational database management systems. Some typical examples of databases that use the relational model are Oracle, Microsoft SQL Server, Sybase, and Ingres.

In this article, we shall discuss briefly what Microsoft SQL Server is and what career opportunities it has to offer. Further, we will discuss some popular SQL Server interview questions and answers:

Microsoft SQL Server: A Brief Introduction

Developed by Microsoft, the Microsoft SQL Server is a powerful and industry-recognized relational database management system. It is a robust RDBMS that enables applications running on the same computer or a remote computer to store and retrieve data. In addition, this software product is compatible to work with Microsoft Windows Server, Linux, and Microsoft Windows operating systems.

A wide range of Microsoft SQL Server editions is available that aim to meet the varying needs of different users. The Microsoft SQL Server editions are available across three categories - Mainstream, Specialized, and Discontinued. The Mainstream category includes Standard, Enterprise, Business Intelligence, Web, Express, and Workgroup editions.

On the other hand, Azure, Compact, Evaluation, Developer LocalDB, Fast track, Data Warehouse Application, and Analytics Platform System are the editions that belong to the Specialized category. Finally, the Discontinued category consists of Datacenter, MSDE, and Personal editions.

Microsoft SQL Server supports a standardized language called SQL. We can use Microsoft SQL Server to create and maintain databases, analyze data via SQL Server Analysis Services, perform ETL operations using SQL Server Integration Services, and develop reports through SQL Server Reporting Services.

Ranked among the most reliable and safest database technologies, both individuals and organizations trust Microsoft SQL Server to store, maintain and retrieve data. In addition, organizations and businesses hire SQL Server professionals to manage their data operations.

You can find a wide range of career opportunities in Microsoft SQL Server, as it is among the top three globally utilized database technologies. Some popular job roles associated with Microsoft SQL Server are Database Administrator, SQL Server Writer, BI Developer, Data Scientist, ETL Developer, Data Analyst , Big Data Architect, and many more.

Best SQL Server Interview Questions and Answers

Pursuing a career in SQL Server requires you to possess at least a Bachelor’s Degree in Computer Science. In addition, it would be a plus point to hold certification in SQL Server. If you are a fresher, an interviewer may ask questions on fundamental topics of SQL Server. Also, the level of questions asked by interviewers depends on your level of experience.

Below are some commonly asked SQL Server interview questions that you need to know to excel in your interview. We have divided these SQL Server interview questions into three levels, namely Beginner-level, Intermediate-level, and Advanced-level. So, let us begin!

Beginner-Level SQL Server Interview Questions

1. What is SQL Server?

Answer: SQL Server is one of the leading relational database management systems developed by Microsoft. It enables other applications running on the same computer as the SQL Server or other remote computers to store and retrieve data from a relational database.

2. List and explain types of commands used in SQL Server.

Answer: SQL Server supports four different types of commands, as given below:

• Data Definition Language (DDL):

We use DDL commands in SQL Server to deal with the structure of entities or objects. There are three DDL commands in SQL Server:

• Create: This command creates an object or entity.

• Alter: It makes changes in an object or entity.
• Drop: It deletes an object or entity.

Here, an object or an entity is a database, trigger, table, view, etc.

• Data Manipulation Language (DML):

DML commands in SQL Server are used to perform manipulations on the data stored in entities, like a table, view, etc. The three DML commands in SQL Server are:

• Insert: It adds new data into a table. Also, this command supports the insertion of bulk data into a table.
• Delete: This command deletes the specific data from the table.
• Update: It updates the values of data in a table.

• Data Control Language (DCL):

In SQL Server, we use DCL commands to ensure the security of the data stored in objects or entities. Two DCL commands in SQL Server are:

• Grant: It grants database access permission to a user.

• Revoke: This command takes back the database access permission from a user.

• Transactional Control Language (TCL):

We use TCL commands in SQL Server to manage transactions. Three TCL commands in SQL Server are:

• Commit: It saves a transaction in SQL Server permanently.

• Rollback: This TCL command undoes all changes made to the data in a database made before committing.
• Save Tran: It saves a transaction in SQL Server and rolls it back to the desired point.

3. Explain the method of creating a database in SQL Server.

Answer: In computer programming, a database is a well-organized collection of data. Alternatively, a database is a set of various components used for storing data, like tables, schemas, procedures, etc. Furthermore, Structured Query Language (SQL) is used in Microsoft SQL Server to access the data and perform manipulations. We use the CREATE DATABASE command to create a database in the SQL Server. The syntax for creating a database in the SQL Server is:

Example: Consider we need to create a database having the name Students, so we need to run the following command:

Alternatively, we can use the SQL Server Management Studio to create a database; go to the left side of the window, right-click on the Databases option and then click on the New Database option.

4. What is SQL?

Answer: SQL stands for Structured Query Language. All relational database management systems use this domain-specific language to access and manipulate the stored data. It enables us to perform various tasks, such as creating a database , retrieving data from a database, creating records, tables, views, and procedures in a database, etc. All these operations make use of SQL queries.

5. What is the latest version of SQL Server?

Answer: The latest version is Microsoft SQL Server 2019 which was released on November 4th 2019.

6. Explain PL/SQL.

Answer: An extension to SQL is PL/SQL. It stands for Procedural Language extensions to Structured Query Language. PL/SQL combines the SQL’s data manipulation feature and the procedural language’s processing power. Moreover, it is one of the most powerful programming languages that improve a database’s security and sturdiness.

PL/SQL instructs the Oracle compiler about ‘what actions to perform using the SQL’s manipulation feature and ‘how to perform actions’ using the procedural language’s processing power. As PL/SQL is a procedural language, it involves looping and conditional statements.

7. Differentiate between SQL and PL/SQL.

Answer: The below table highlights some of the key dissimilarities between SQL and PL/SQL:

8. Explain a Relational Database Management System (RDBMS).

Answer: A database management system based on E. F. Codd’s relational model is called a Relational Database Management System (RDBMS). Alternatively, RDBMS is a collection of programs and functionalities that enables organizations and individuals to interact or communicate with a relational database. Data in RDBMS is stored in the form of tuples, i.e., rows and columns. It supports a Structured Query Language (SQL) to access data from a relational database.

Some vital characteristics of RDBMS are:

• It allows multiple users to access data.
• RDBMS is a robust type of database model that can handle every data size, whether small, medium, or large-scale.
• It uses the ACID property to ensure data consistency in a database.
• RDBMS supports distributed databases and normalization.

9. List out properties of RDBMS.

Answer: The below list explains the properties of RDBMS:

• All values in a table are atomic.
• Each tuple in a table is unique.
• The sequence of attributes and tuples does not matter in SQL Server.
• Each attribute in a table should have a unique name.
• All field values in a single column are of the same type.

10. What do you understand about a database table?

Answer: A database table in SQL Server stores all data in the form of horizontal rows and vertical columns. In addition, a cell in a table is a unit where a row and a column intersect. A database table has a specific number of columns or attributes, whereas it may have any number of rows or tuples. We use DDL commands for a database table, as a table is an object. The CREATE command creates a database table, whereas the DROP command deletes it. Moreover, the ALTER command modifies the previously defined structure of a table, and the SELECT command displays data from a table.

11. Tell the syntax for creating a table in SQL Server with an example.

Answer: The syntax for creating a table in SQL Server is:

Let us see one example of creating a Students table in SQL Server. Consider that the Students table has four columns or attributes, like Name, Student_ID, Mobile_No, and City.

After you hit enter, you will get a message ‘Table created successfully.’

12. Write down the syntax for deleting a table in SQL Server.

Answer: To delete a table from a database in SQL Server, we use the DROP command. The syntax of the DROP command is:

Example: Let us consider that we have a table named Employees, and we need to delete it from a database.

13. Tell the syntax for updating a table in the SQL Server.

Answer: In SQL Server, we use the ALTER command to update a table. While creating a table, we specify column names and their corresponding data types. Also, if we want to add, delete, or alter any column after creating a table, we can again use the ALTER command. Therefore, the ALTER command is used in three different ways, as follows:

• To add a column:

Syntax:

• To delete a column:

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

• To modify a column:

Syntax:

14. Explain different kinds of relationships in SQL Server.

Answer: SQL Server supports three kinds of relationships in a database, as explained below:

• One-to-one:

The one-to-one relation in SQL Server has rare usage. In this type of relationship, a record or data in one table correlates with only one record or data present in another table. The primary advantage of using one-to-one relationships is better security. For example, consider a Student database. Here, only one Student_ID is assigned to a single student. Conversely, each student has a unique ID assigned.

• One-to-many and Many-to-one:

When one record in a table correlates with multiple records in another table, it is a one-to-many relationship. Conversely, when multiple records in a table are associated with a single record in another table, it is a many-to-one relationship. For example, consider a Customer database. Here, a single customer can place any number of orders. On the other hand, a specific order belongs to a single customer.

• Many-to-many:

When multiple records in a table are correlated to multiple records in another table, it is a many-to-many relationship. For example, multiple customers can purchase different products.

15. Explain the primary key in SQL Server.

Answer: A primary key in SQL Server can be a single attribute or field or combination of attributes or fields that uniquely identify a record or a tuple in a table. There is only one primary key for a database table. In addition, we can set a primary key for a table while creating or updating it. However, fields of an attribute or a combination of attributes that are considered a primary key cannot be NULL. Below is the syntax for creating a primary key for a table:

16. What is a foreign key?

Answer: We use a foreign key in SQL Server to connect data between two different tables; one table will be a child, and another will be a parent. Alternatively, if an attribute or column of one table points to the primary key of another table, then that attribute or column is said to be a foreign key. Let us go through one example to understand clearly the foreign key. Consider two tables: Student_record and Student_Marks.

Student_record

Student_Marks

In the above tables, Student_ID is the foreign key. The Student_ID attribute is a primary key of the table named Student_record and is present as an attribute in Student_marks.

17. Name the TCP/IP Port on which SQL Server runs.

Answer: The SQL Server runs on the 1433 TCP/IP port by default.

18. Explain database normalization and list its forms.

Answer: The technique used to organize data present in a database to reduce data redundancy is called database normalization . In addition, database normalization eliminates Update, Insertion, and Deletion Anomalies from a relation. The fundamental idea of using the database normalization technique is to divide a table into smaller ones and connect them using database relationships. There are six forms of database normalization, as listed below:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF or 4NF)
• Fifth Normal Form (5NF)
• Fifth Normal Form (6NF)

19. Explain functions in SQL Server.

Answer: A function in SQL Server is analogous to a function in programming languages. It is a sequence of SQL statements written to accomplish a definite task. In addition, a function in SQL Server accepts input parameters and returns the desired output. More importantly, SQL functions are not used to insert, update, or delete data from a database. Every function in SQL Server has a specific name and does not begin with special symbols, such as $, #, or @. SQL Server has two distinct kinds of SQL Server: predefined and user-defined.

• Predefined Functions:

Functions defined by SQL Server are called pre-defined functions. Aggregate functions and scalar functions are two different kinds of predefined functions in SQL Server. Aggregate functions are min(), sum(), count(), max(), and avg(), whereas scalar functions are round(), ucase(), lcase(), format(), mid(), len(), and now().

• User-defined Functions:

A block of SQL statements written by a programmer or developer to accomplish a particular task is called a user-defined function.

20. Explain Aggregate functions in SQL Server.

Answer: Aggregate functions in SQL Server group values from multiple rows of a table under specific criteria and return a single value. There are five aggregate functions in SQL Server, as given below:

• sum():

It takes all values from different rows and returns their sum value.

• max():

This function accepts values from multiple tuples, compares them, and returns the maximum value.

• min():

This function accepts values from multiple tuples, compares them, and returns the minimum value.

• count():

This function returns the total number of rows or records present in a table.

• avg():

This function accepts values from multiple tuples and returns an average of all values.

Intermediate-Level SQL Server Interview Questions

21. List out and explain Scalar functions in SQL Server.

Answer: Functions that accept a single value and also produce a single value output are called scalar functions. Below are some scalar functions used in SQL Server:

• mid():

We use the mid() function to retrieve a substring from an input string.

• ucase():

This function returns the uppercase of the given input.

• lcase():

The lcase() function returns the lowercase of the given input.

• format():

This function returns the input value in a specific format.

• len():

The len() function returns the length of the given input.

• round():

This function rounds off the given numerical input up to three decimal places.

• now():

We use the now() function to fetch the system’s current time and date.

22. What is a stored procedure in SQL Server?

Answer: A collection of T-SQL statements grouped together as a single logical unit is called a stored procedure in SQL Server. We can store stored procedures in a database and use them whenever required. If a certain program requires a code block, again and again, we can store that code block as a stored procedure. The syntax for creating a stored procedure in SQL Server is:

To execute a created stored procedure, use the below syntax:

23. What do you mean by the trigger?

Answer: A trigger in SQL Server is analogous to a stored procedure. Alternatively, we can call a trigger a database object. When an event takes place in a database, a trigger is fired. However, we cannot fire or activate a trigger in SQL Server. It gets invoked automatically. In other words, we can use a trigger in SQL Server to make an event happen in a database automatically. DML triggers and DDL triggers are two kinds of triggers in SQL Server.

• DML Triggers:

When we use any DML command like INSERT, UPDATE, and DELETE on a table’s or a view’s data, DML triggers are fired. These triggers help preserve a database’s consistency and integrity.

• DDL Triggers:

DDL triggers are raised when several DDL events take place. These events are associated with Transact-SQL statements that begin with CREATE, DENY, REVOKE, ALTER, GRANT, or DROP.

24. Explain a view in a database.

Answer: A view in SQL Server is a virtual table consisting of the data from multiple tables. In addition, a view also stores data in rows and columns. However, a view is not present physically in a database as a stored procedure. Therefore, it does not require storage in a database. Like a database table, a view should also have a unique and unrepeated name. The syntax for creating a view in SQL Server is:

25. What is an index in SQL Server?

Answer: In SQL Server, an index is used to fetch row data quickly from a database’s view or table. It is an on-disk structure linked to a view or a table in a database. In addition, there are keys in an index developed using one or more attributes or columns of a view or a table. A structure called B-Tree stores index keys, enabling SQL Server to retrieve corresponding rows rapidly and efficiently. The two different kinds of indexes in SQL Server are:

• Clustered Index:

A clustered index in SQL Server represents the order in which the data in the database should be stored physically. However, data in a database table can be sorted in only one way. Therefore, there is only one clustered index for a single table. Moreover, a clustered index is created automatically when we declare a primary key constraint on a table’s attribute.

• Non-Clustered Index:

Indexes that do not sort physical data within a database are non-clustered indexes. More importantly, the table data and non-clustered index are not present in one place.

26. Explain the difference between clustered and non-clustered indexes.

Answer: The below table shows dissimilarities between clustered and non-clustered indexes:

27. List out SQL Server’s two authentication modes.

Answer: Two authentication modes in SQL Server are Mixed Mode and Windows Mode.

28. Define global and local temporary tables in SQL Server.

Answer:

Global Temporary Table: A table that is noticeable to all users when the connection is established and gets deleted when the connection is closed is called a global temporary table.

Syntax:

CREATE TABLE ##<tablename>

Local Temporary Table: A table that is visible to only the creator of the connection and gets deleted when the connection is closed is called a local temporary table.

Syntax:

29. Tell the query used to get triggers’ list in a database.

Answer: Query to receive the list of triggers is:

30. Which query will you use to get the SQL Server’s current version?

Answer: The SQL query to fetch SQL Server’s current version is:

31. Enlist all three different ways to fetch the total count of records present in a table.

We can retrieve the total number of records in a table by using the following three ways: First Method:

SELECT * FROM <tablename>

Second Method:

SELECT count(*) <tablename>

Third Method:

SELECT rows from sysindexes

WHERE id=OBJECT_ID(tablename) and intid<2

32. List out different types of backup in SQL Server.

The following are the different types of backup in SQL Server:

• Full Backup
• Differential Backup
• Transaction Log Backup
• File Backup
• FileGroup Backup
• Partial Backup
• Copy-Only Backup
• Mirror Backup

33. What is the difference between SQL Server and MySQL?

The following table highlights the differences between SQL Server and MySQL:

Advanced-Level SQL Server Interview Questions

34. What do you know about SQL Injection?

SQL Injection, often referred to as SQLI, is one of the most common web hacking techniques. In SQL Injection, hackers inject malicious SQL statements into an entry field for execution and access confidential information from databases. In other words, SQL injection is an attack where hackers manipulate SQL code to gain unauthorized access to a web application’s database.

35. Explain magic tables in SQL Server.

Whenever there are data manipulation operations, such as insert, delete, or update, SQL Server creates temporary logical tables, which are magic tables.  Any operation that you perform recently on a particular row of a table, SQL Server stores it in a magic table. However, magic tables are only temporary internal tables and not physical tables.

36. Do you know what hotfixes and patches are in SQL Server?

Hotfixes in SQL Server are updates to fix certain issues that are not released publicly. On the other hand, patches in SQL Server are updates to fix certain issues that are known publicly.

37. What do you know about SSRS in SQL Server?

SSRS stands for SQL Server Reporting Services. It is a set of on-premises tools and services that enables users to create and manage paginated and mobile reports. Alternatively, it is a set of tools and services that lets you generate formatted reports with tables in the form of images, charts, and graphs.

38. What is a SQL Server Agent?

A SQL Server Agent is a Microsoft Windows service that is responsible for executing the scheduled administrative tasks. These administrative tasks are referred to as jobs in SQL Server, which include scheduling backups, log-shipping tasks, or handling reporting services.  A SQL Server Agent runs a job on schedule on-demand or as a response to a specific event.

39. Explain SQL Server Log Shipping.

SQL Server Log Shipping is an automated way of backing up and restoring databases from one server instance to another or more server instances. The primary purpose of log shipping is to increase the availability of databases by maintaining their backup in other server instances. As a result, we can say that log shipping is one of the ways to disaster recovery.

40. What is SQL Server Profiler?

A SQL Server Profiler is a graphical user interface (GUI) tracing tool that Microsoft has introduced in SQL Server 2000 version. It is responsible for tracing activities and operations that you carry out on a specific SQL Server database engine. It allows us to monitor, analyze, troubleshoot, and trace problems in SQL databases.

41. Enlist all the editions of SQL Server.

There are five different editions of SQL Server that are as follows:

• Standard: This edition provides fundamental data management and business intelligence database for small organizations so that they can run their applications.
• Enterprise: This edition is the premium offering that delivers comprehensive data center capabilities with lightning-fast performance and unlimited virtualization. It allows end-user access to data insights and high-level services for mission-critical workloads.
• Web: The Web Edition is a low total cost-of-ownership option ideal for Web hosters and Web VAPs to offer scalability, manageability, and affordability capabilities.
• Express: It is a free and entry-level database. This is widely used for learning and building small data-driven applications.
• Developer: With this edition, developers can build any type of application on the top of SQL Server.

42. Can you tell the difference between the HAVING and WHERE clauses?

We use the WHERE clause to filter the records from a particular table or join two tables based on certain conditions. Only those records will be displayed that meet the condition specified in the WHERE clause. The HAVING clause filters the records from the groups based on the condition specified in the HAVING clause. Some other differences between the HAVING and WHERE clauses are as follows:

43. Can you tell the purpose of the UPDATE STATISTICS and the SCOPE_IDENTITY() function?

UPDATE STATISTICS: It updated query optimization statistics on a table or an indexed view. The primary purpose of this statement is to optimize query performance.

Syntax:

UPDATE STATISTICS table_or_indexed_view_name

SCOPE_IDENTITY(): This function returns the last identity values that were generated in any table in the current session.

Syntax:

SCOPE_IDENTITY()

44. Enlist a few encryption mechanisms in SQL Server.

The following are a few encryption mechanisms in SQL Server that encrypt data in a database:

• Transparent Data Encryption
• Symmetric Keys
• Asymmetric Keys
• Transact SQL functions
• Certificates

45. How will you allow the usage of optimistic models?

There are two options that we need to set to allow the usage of optimistic models. These two options are as follows:

• READ_COMMITED_SNAPSHOT for the read committed optimistic model
• ALLOW_SNAPSHOT_ISOLATION for the snapshot isolation level

46. Can you tell the use of the SIGN() function in SQL Server?

The SIGN() function in SQL Server returns the sign of a number. It will return one of the following:

• If a number is greater than 0, it returns 1,
• If a number is equal to 0, it returns 0,
• And, if a number is less than 0, it returns -1.

Syntax: SIGN(number)

47. Which command will you use to check the locks in SQL Server?

There is a built-in stored procedure named 'sp_locks' in SQL Server to check the locks.

Syntax:

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ] [ ; ]

48. Explain the COALESCE() function in SQL Server.

The COALESCE() function returns the first non-null expression within the arguments that you pass to it. Syntax: SELECT COALESCE(CustID, CustName, Amount) from Customers;

49. What are SQL joins? List out the types of joins.

SQL joins are used to combine rows from two or more tables based on one related column between those two tables. The following are the types of joins in SQL:

• Inner Join: It returns records that have matching values from both tables.
• Right Join: It returns all the records from the right table and the matched values from the left table.
• Left Join: It returns all the records from the left table and the matched values from the right table.
• Full Join: It returns records when there is a match in either the left or right table.

50. Enlist the most common trace flags used in SQL Server.

We use trace flags in SQL Server to set the specific characteristics of the server. The following are the common trace flags used in SQL Server:

• Log Record for Connections: 4013
• Skip Startup Stored Procedures: 4022
• Deadlock Information: 1204, 1205, 1222
• Do Force uniform extent allocations instead of mixed page allocations 1118 (SQL 2005 and 2008).
• Disable Locking Hints: 8755
• Network Database files: 1807

Conclusion

This brings us to the end of the SQL Server interview questions. Though SQL Server is one of the oldest RDBMS, it is likely to thrive in the future as well. Therefore, pursuing a career in the SQL domain offers many career opportunities in various domains such as Finance, Healthcare, and IT.

If you wish to shape your career in SQL and aim to appear for an interview, this article will surely assist you. We have listed the most common SQL Server interview questions and answers. You can be more confident about what to answer if an interviewer asks any of the above questions related to SQL Server.

People are also reading:

• SQL Interview Questions
• What is a Relational Database?
• What is NoSQL Database?
• SQL vs MySQL
• SQL Commands
• Date Functions in SQL
• SQL Cheat Sheet
• RDBMS Concepts
• SQL – RDBMS Databases
• How to Create a Database?