50 Best SQL Server Interview Questions and Answers

Structured Query Language or SQL is the programming language for manipulating relational databases. Several popular tools combine this technology with productive, proprietary applications. One such example is the Microsoft SQL Server.

Owing to the immense popularity enjoyed by SQL Server, many SQL job interviews demand a good understanding of SQL Server. Learning SQL Server demands a robust understanding of:

• Databases
• Servers
• Database management systems, such as MySQL, PostgreSQL, and MongoDB, and
• Programming

Top SQL Server Interview Questions and Answers

Here, we have compiled a list of 50 best SQL server Interview questions that SQL/database management aspirants will find immensely helpful. These questions cover everything from the basics of SQL Server to some of its advanced concepts. Here they are:

Question: Please explain the two authentication modes in the SQL Server.

Answer: Authentication modes are used for authenticating the user in Microsoft SQL Server. Although users are prompted for it during the database engine setup, it can be changed later. We can use one of the following two authentication modes in SQL Server:

1. Windows Mode - Enables Windows Authentication and disables SQL Server Authentication. For authenticating, the server takes the computer’s username and password. SQL Server authentication is disabled in the Windows Mode.
2. Mixed Mode - Enables both Windows Authentication and SQL Server Authentication. Every database requires a unique username and password in this authentication mode.

To switch between the authentication modes, go to SQL Server Configuration Properties -> Tools -> Security page.

Question: Explain how you will create a table in SQL Server?

Answer: We can create a table in SQL Server using the following code:

create table TableName (column1 datatype, column2 datatype,..., columnN datatype)

For example, the following code:

create table Dummy
(
Name varchar(20), Address varchar(40), PhoneNo. nvarchar(12)
)

Will create a table Dummy with 3 columns; Name, Address, and PhoneNo.

Question: Can you explain SQL Server Profiler?

Answer: System administrators use the SQL Server Profiler tool to monitor events in the SQL Server. It is used for:

• Creating a trace.
• Finding out bugs in queries and fixing the same.
• Starting, stopping, pausing, and modifying the trace results.
• Storing the trace results in a table.
• Watch the trace results once the same is running.

The SQL Profiler is a GUI that is primarily used for capturing and saving data of one or every event of a file or table for real-time or future analysis.

Question: Do you know what is a recursive stored procedure?

Answer: SQL Server offers a kind of stored procedure that calls itself. This is called a recursive stored procedure. Here, the solution arrives repetitively. It is possible to nest stored procedures up to 32 levels. A recursive stored procedure can be called directly or indirectly.

We can achieve recursion in stored procedures in the following two ways:

1. Chain Recursion: For achieving a chain recursion, the mutual recursion process is extended.
2. Mutual Recursion: Indirect recursion is achieved using the mutually recursive stored procedure.

Question: Draw a comparison between local and global temporary tables.

Answer: Global temporary tables are visible to all users. These are deleted when the connection creating them is closed. Local temporary tables, on the other hand, are visible only when there is a connection. These are deleted once the connection is closed.

Question: What are the constraints in SQL Server?

Answer: Constraints are the set of rules governing the kind of data permissible for database tables. There are a total of 6 constraints in Microsoft SQL Server:

1. Check
2. Default
3. Foreign Key
4. Not Null
5. Primary Key
6. Unique

Question: Please explain the CHECK constraint.

Answer: The CHECK constraint enforces integrity. It is applied to a column in a table for limiting the values that can be inserted in the same. A column upon which the CHECK constraint is applied can only have some specific values. Following is an example of applying the CHECK constraint in a SQL Server database:

CREATE TABLE Dummy
(
S.No. int,
Name varchar(255),
Age int,
CONSTRAINT CHK_Dummy CHECK (Age>17)
);

Question: Is it possible to link SQL Server to other servers?

Answer: Yes, it is possible to connect SQL Server to any database server supporting the OLE-DB provider. Database servers supporting the OLE-DB provider are:

• IBM Informix
• Microsoft Access
• Oracle Database

Question: Please explain the subquery and its properties.

Answer: A query that can be nested inside the main query, such as a Select or Update statement, is called a sub-query. Subqueries can be used when an expression is allowed. A subquery is also known as an INNER query. Properties of sub-queries are:

• It can, optionally, add GROUP BY, HAVING, and WHERE clauses.
• It can be added to the FROM, SELECT, and WHERE clauses.
• It must be placed on the right side of the comparison operator of the main query.
• It should be enclosed in parenthesis. This is because it is executed before the main query.
• It should not have an ORDER BY clause.
• There can be more than one subquery.

Question: Can you explain the types of subqueries?

Answer: Subqueries are of three types:

1. Single-row subquery - Returns only a single row.
2. Multiple row subquery - Returns multiple rows.
3. Multiple column subquery - Returns multiple columns.

The Complete SQL Bootcamp 2023: Go from Zero to Hero

Question: What is the purpose of the SQL Server agent?

Answer: The purpose of the SQL Server agent is to implement the tasks in-line with the scheduler engine. This makes the jobs run at a scheduled date and time. The SQL Server agent is a background tool, i.e., it continues running in the background as a Windows service.

Hence, the SQL Server agent plays an essential role in accomplishing common tasks by a SQL server administrator. As such, it is an essential component of the SQL Server.

Question: Please explain scheduled tasks in the Microsoft SQL Server.

Answer: For automating processes that can run on a scheduled time at specified regular intervals, we use scheduled tasks or jobs. This helps in reducing the required human intervention. The user can also decide the execution order of such scheduled tasks.

Question: Explain how exceptions are handled in the SQL Server?

Answer: We use TRY----CATCH constructs to handle exceptions in the SQL Server. Conditions are written inside the TRY block, and the exceptions are caught in the CATCH block.

Question: What are the functions in SQL Server?

Answer: Functions in SQL Server are predefined methods of doing something. The following points must be noted about SQL Server functions:

• They are compiled every time.
• They can be used anywhere.
• They don’t allow using try and catch statements.
• They must return some value or result.
• They only work with input parameters.
• They work only with SELECT statements.

There are two types of functions in SQL Server; built-in and user-defined. An SQL Server function must return a result. When we use a function in SQL Server, it must specify a return value type. Microsoft SQL Server has a range of inbuilt functions. They are classified as follows:

• Table-valued Functions
• Scalar-valued Functions
• Aggregate Functions
• System Functions
• Aggregate functions
• Configuration functions
• Cursor functions
• Data and time functions
• Mathematical functions
• Metadata functions
• Other functions
• Hierarchy Id functions
• Rowset functions
• Security functions
• String functions
• System Statistical functions
• Text and Image functions

Question: What is the use of the FLOOR function?

Answer: When there is a need to round up a non-integer value to the previous integer, the FLOOR function is used. For example, FLOOR (12.8) will return 12.

Question: How will you check locks in a SQL Server database?

Answer: Microsoft SQL Server comes with an inbuilt stored procedure called sp_lock to check locks in databases.

Question: Why do we have the SIGN function? What does it return?

Answer: The SIGN function checks whether a given number is positive, negative, or zero. It returns:

• +1 when the number is positive,
• -1 when the number is negative, and
• 0 when the number is zero.

Question: Please differentiate between UNION and UNION ALL commands.

Answer: The UNION command is used for selecting similar information from two tables. The UNION ALL command does the same but selects all available values and doesn’t eliminate duplicate rows.

Question: What is the difference between CHARINDEX and SUBSTR functions?

Answer: While the SUBSTR function returns some specific portion of a given string, the CHARINDEX function returns a certain character's character position from the given string. For example, SUBSTRING ('String', 1, 3) will return Si, and CHARINDEX ('i', 'String', 1) will return 4.

Question: How is the dynamic SQL different from stored procedures?

Answer: Both stored procedures and dynamic SQL are a set of statements. While the stored procedures are stored in a compiled form in the database, dynamic SQL is dynamically constructed at the run time. They aren’t stored and executed at the run time post their dynamic construction.

Question: Can you explain Collation?

Answer: Collation specifies the sort order in a database table. It is of three types:

1. Binary
2. Case sensitive
3. Case insensitive

Question: How can we get the version information of the Microsoft SQL Server?

Answer: We use the following command in SQL Server to fetch the version information:

Select SERVERPROPERTY('productversion')

Question: Why do we use the SET NOCOUNT ON/OFF statement?

Answer: The NOCOUNT is set to OFF by default. It returns the total number of records that are affected when a command is executed. It can be explicitly turned on when there is no need to display the number of records affected.

Question: Can you explain Magic Tables in the SQL Server?

Answer: SQL Server automatically creates temporary tables while undergoing DML operations, such as Delete, Insert, and Update. These are called Magic Tables. Triggers use them for data transactions.

Question: What do you mean by triggers in SQL Server?

Answer: In SQL Server, triggers are database objects similar to stored procedures. These special types of stored procedures fire when some specific event occurs in their respective databases. Triggers are of three types:

1. DDL Trigger - Fires in response to DDL command events starting with Alter, Create, and Drop.
2. DML Trigger - Fires in response to DML command events starting with Delete, Insert and Update.

Triggers are bound to tables and execute automatically. There is no provision for explicitly calling them. They offer data integrity and are used to access and check data before and post the modification.

Question: How do you update a database in SQL Server?

Answer: We can do so by using the Update command. It can be used to add or delete new columns, replace information in a column, etc. The general syntax for the UPDATE command is:

UPDATE TableName SET ColumnName = NewData where Condition

Question: Can you explain relationships in SQL Server?

Answer: Relationships are used in SQL Server to link columns of different tables. These are of three types:

1. One-to-One - A single column in a table has one dependent column in some other table.
2. One-to-Many/Many-to-one - A single column in a table has more than one dependent column in the other table (One-to-many). More than one column in a table has a single dependent column in the other table (Many-to-one).
3. Many-to-Many - Multiple columns in a table have multiple dependent columns in some other table.

Question: Please explain the concept of database normalization.

Answer: Database normalization refers to the process of organizing tables in a relational database for minimizing redundancy and dependency. It also improves the integrity and performance of tables.

Normalization involves dividing bigger tables into smaller tables and defining relationships among the same. The database normalization process follows the following hierarchy:

1. 1NF (First Normal Form)
2. 2NF (Second Normal Form)
3. 3NF (Third Normal Form)
4. BCNF (Boyce-Codd Normal Form)
5. 4NF (4th Normal Form)
6. 5NF (5th Normal Form)
7. 6NF (6th Normal Form)

Question: Please explain the concept of an index in the Microsoft SQL Server?

Answer: When data is enormous, database tables aren’t enough for efficiently working with the same. So, we index the columns (or views) in them. Technically, an index is a database object created and maintained by the DBMS.

Columns that are indexed are called indexed columns. These are ordered and, thus, supports fast searching. A database table can have multiple indexes. SQL Server has two types of indexes:

1. Clustered Index - This type of index sorts and stores the data based on keys. As rows can be sorted in only a single order, defining a clustered index in a table only once is possible. Doesn’t allow Image, Text, and nText data as a clustered index.
2. Non-clustered Index - This is an index that is created outside a database table. Each table can have as many as 999 non-clustered indexes in SQL Server, where each index can have 1023 columns at max. Like clustered indexes, these don’t support Image, Text, and nText forms of data.

Question: Can you explain joins in SQL Server?

Answer: We use joins in database operations when there is a need to retrieve data from multiple tables. The data that is retrieved depends on two things:

• The type of join used, and
• The relationships among the columns of different tables.

SQL Server has the following types of joins:

1. Full Join
2. Inner Join/Self Join/Simple Join
3. Outer Join
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Question: What do you know about the database engine in Microsoft SQL Server?

Answer: The SQL Server Database Engine, just like SQL Server Agent and other SQL Server components, runs as a database service. Database services start as soon as the operating system starts.

What database services will start by default is defined during the SQL Server setup. Services offer core operating system features like event logging, file serving, and web serving.

Question: Please explain Microsoft SQL Server Analysis Services.

Answer: Microsoft SQL Server Analysis Services or SSAS is a web-based analytical processing and data mining tool. It intends to allow database administrators to:

• Make sense out of data spread across numerous databases, tables, and files.
• Provide decision-makers with easy and quick access to the information available.

SSAS offers OLAP by allowing DBAs to create, design, and manage multi-dimensional structures containing data aggregated from various data sources.

The built-in SQL Server tool also features a range of data mining algorithms to dig out data from different sources. SSAS offers quick results from different data sources as soon as a query is applied. Analysis Services relies on a client-server architecture.

• Client Architecture - SASS features a thin client component architecture. As all calculations and queries are resolved only by the server, a server to client connection is required for each request. Several providers offer SSAS for supporting various programming languages. The communication between these providers is made possible using SOAP packets.
• Server Architecture - SSAS has an application named Msmdsrv.exe. It is a server component that runs as a Windows service. It consists of many components, including a query processor and an XMLA listener. Msmdsrv.exe performs the following things:
• Caching objects,
• Creating aggregations,
• Handling transactions,
• Managing metadata,
• Managing server resources,
• Parsing statements received from clients,
• Processing calculations,
• Scheduling queries, and
• Storing dimension and cell data.

Question: What is denormalization in databases?

Answer: Denormalization is the process of intentionally introducing redundant data in database tables for optimizing performance. This process trades-off enhancement with the reading performance for some degradation in the write performance.

P.S. - It should be noted that un-normalized and denormalized databases are completely different concepts. An unnormalized database is one that hasn’t been normalized. On the other hand, a denormalized database is a database that is first normalized and then denormalized.

Question: Please explain the concept of a standby server. What are its types?

Answer: When the primary database server goes offline and a reliant application needs it continuously, a standby database server fills its shoes. Standby servers are of three types:

1. Hot standby - Both primary and standby servers run in-line. Both servers contain identical information in this case.
2. Warm standby - Here, the standby server runs in the background of the primary server. Data contained by the primary server is mirrored to the standby server at regular intervals.
3. Cold standby - In this scenario, the standby server is called only when the primary server experiences a failure. This type of standby server is used in cases where there is:
1. No frequent change in data, or
2. The reliant application is non-critical.

Question: What TCP/IP port does the Microsoft SQL Server run in? Is it possible to change the same?

Answer: The SQL Server runs on port 1433. Yes, it is possible to change it. We can change it through the network utility TCP/IP properties.

Question: What is DBCC in SQL Server? Why do we use it?

Answer: DBCC means Database Consistency Checker. It is a SQL Server command meant for checking the consistency of a database. It helps to:

• Monitor and review the maintenance of databases and tables, or
• For validating operations performed on a database or table.

When a DBCC command is executed, the database engine creates a database snapshot and continues in a consistent transactional state. Afterward, the database engine runs checks against this stored database snapshot and drops the same after completing the command. Following are some examples of using the DBCC command:

• DBCC CHECKALLOC - Checks and ensures that all pages in a database are allocated correctly.
• DBCC CHECKDB - Ensures that all tables in a database and the indexes are linked correctly.
• DBCC CHECKFILEGROUP - Checks all table file groups for damage.

Question: Can you explain Log Shipping?

Answer: Log shipping refers to the process of automating the backup and transaction log file of a database on a primary database server and then restoring the same on a standby server. This technique is supported by several popular database servers, such as:

• 4D Server
• Microsoft SQL Server
• MySQL
• PostgreSQL

The main intent of log shipping is to increase database availability. The log backups are applied, particularly on each secondary database. Log shipping involves the following 3 steps:

• Step 1 - Take a backup of the transaction log file on the primary server instance.
• Step 2 - Copy the log file on the secondary server instance.
• Step 3 - Restore the log backup file onto the secondary server instance.

Question: Please explain the different types of replication in the Microsoft SQL Server.

Answer: Replication is a technique in database servers for enhancing database availability. It is of three types in the Microsoft SQL Server:

1. Merge Replication - Groups the data from different sources into a single, centralized database. Generally used in the server to the client environment, merge replication is suitable in scenarios where several subscribers update the same data at different times.
2. Snapshot Replication - It distributes data in the same manner as it appears at some point in time. It is the most suitable replication technique in scenarios where data doesn’t change frequently.
3. Transactional Replication - It is the process of distributing data from the publisher to the subscriber. Usually used in the server to the server environment. Ideal for cases where there is a requirement for incremental change propagation to the subscriber.

Question: What are some popular third-party tools for SQL Server?

Answer: Microsoft SQL Server is one of the leading database management systems. Hence, there is no scarcity of third-party tools available for the same. Here are some of the most popular ones:

• LiteSpeed by Quest Soft - Used for preparing backup and restore.
• SQL Backup 10 by RedGate - Used for automating the SQL Server backup process.
• SQL Check by Idera - Used for monitoring server activities and memory levels.
• SQL Doc 2 by RedGate - Used for documenting the databases.
• SQL Prompt by RedGate - Offers IntelliSense for SQL Server.

There are several benefits of using third-party tools in Microsoft SQL Server, including:

• Better insights and information.
• Enhanced visibility into the transaction log and transaction log backups.
• Enterprise view of the backup and recovery environment.
• Faster backup and restore.
• Flexible backup and recovery options.
• Recovery of individual database objects.
• Saves time.
• Secure backups with encryption.

Question: What do you understand by Patches and Hotfixes in the SQL Server?

Answer: A patch is a program used for fixing a certain problem(s) in the system and ensuring the same security. Hotfixes are proprietary patches released by Microsoft. These are designed to address different issues, mostly recently-discovered security flaws.

Hotfixes respond proactively against bugs. This is because they are designed to be applied to live systems. A Hotfix can be a single file or multiple files.

Question: Please enumerate the most common trace flags used in SQL Server.

Answer: Trace flags are for SQL Server what the if statement is for programming languages. A trace flag sets specific characteristics for the SQL Server. Some of the most common trace flags used in the SQL Server are:

• 1118 - Do Force uniform extent allocations instead of mixed page allocations
• 1204, 1205, 1222 - Deadlock Information
• 1807 - Network Database files
• 4013 - Log Record for Connections
• 4022 - Skip Startup Stored Procedures
• 8755 - Disable Locking Hints

Question: What are the various encryption mechanisms in the SQL Server?

Answer: SQL Server provides support for a range of encryption mechanisms to safeguard data. These are:

• Asymmetric keys
• Certificates
• Symmetric keys
• Transact-SQL functions
• Transparent Data Encryption

Question: Please explain the Filtered Index.

Answer: An index created with a WHERE clause is called a Filtered Index. It is used to filter certain rows in a table. This helps in improving index maintenance and query performance and reducing index storage costs.

Question: Can you explain some services in SQL Server?

Answer: Microsoft SQL Server features a range of services for working efficiently with gigantic databases. Some of these are:

• Data Quality Services - DQS facilitates performing a range of data quality tasks, such as correction, enrichment, and standardization. It offers the following features for resolving data quality issues:
• Data cleansing
• Knowledge base
• Matching
• Profiling and monitoring
• Reference data services
• Integration Services - A platform for developing high-performance data integration and workflow solutions, such as ETL operations for data warehousing. Includes graphical tools and wizards for building and debugging packages.
• Master Data Services - MDS addresses challenges about analytical and operational master data management. It provides a master data hub for centrally maintaining, managing, and organizing the master data. MDS is built on top of the SQL Server and the WCF (Windows Communication Foundation) APIs.
• Reporting Services - A platform for reporting that includes processing components based on the multilayered architecture of the SQL Server Reporting Services. Processing components interact among themselves to retrieve data and deliver a report. Reporting services has two basic components:
• Processors, and
• Extensions

Question: Please explain COALESCE in Microsoft SQL Server.

Answer: The COALESCE function returns the first non-null expression within the arguments having multiple columns. The function accepts all kinds of values but returns only the non-null present in the expression. General syntax:

COALESCE (expression1, expression2, expression3,.......,expressionN)

Question: Do you know which SQL Server table holds the stored procedure scripts?

Answer: The stored procedure scripts are stored in the Sys.SQL_Modules table. The name of the stored procedures, however, are stored in the Sys. Procedures table.

Question: Please explain COMMIT and ROLLBACK commands.

Answer: Statements accompanying the COMMIT command become persistent in the database. Statements, on the other hand, with the ROLLBACK command are reverted to a previous state.

Question: How is nvarchar different from varchar?

Answer: Both varchar and nvarchar data types are almost the same. The difference between the two is that nvarchar can store Unicode characters for different languages, while varchar can’t. Also, nvarchar requires more space in comparison to the varchar data type.

Question: Can you tell the difference between GETDATE and SYSDATETIME functions?

Answer: Both the commands perform the same operation, i.e., fetching the present date and time. The difference between the two is that while GETDATE gives out time in milliseconds, SYSDATETIME gives out nanoseconds. Therefore, SYSDATETIME is more accurate.

Question: Do you know where the SQL Server stores usernames and passwords?

Answer: Usernames and passwords in the SQL Server are stored in sys.server_principals and sys.sql_logins. While usernames are stored in a normal text form, passwords are stored in a cryptic form.

Conclusion

That completes our list of the 50 best SQL Server interview questions. These questions will help you prepare for an upcoming database/SQL-based interview or self-assess yourself against your DBMS/SQL Server knowledge. You may also want to check out SQL books and SQL courses for your reference.

Do you like it? Let us know via comments. All the best!

People are also reading:

• Best SQL Server Certifications
• Best SQL Certifications
• SQL Cheat Sheet
• Difference between NoSQL vs SQL
• What is MongoDB?
• DBMS Interview Questions
• Differences between MongoDB vs MySQL
• MongoDB Interview Questions
• MariaDB vs MySQL
• PL-SQL Interview Questions & Answers