Top 29 SQL Server Interview Questions

Looking for an SQL Server job? Review these 29 must-know SQL Server interview questions and you’ll be well-prepared. And don’t worry about looking up the answers – we provide them, too!

Microsoft SQL Server's first version was released in 1989 and has a rich history. It has come a long way since then, becoming one of the most used database engines in the data industry. In the last ten years, it’s been the third most popular database engine.

 

Source: https://db-engines.com/en/ranking_trend

If your (next) job requires working with relational databases, there’s a good chance you’ll need to do that in SQL Server. For example, data modelers design and implement databases with the help of database administrators. To do that, data modelers will use SQL Server to create tables and their attributes, define relationships between the tables, define constraints, data types, and so on. The database must then be filled with data, which is a data engineer’s job. They use SQL Server to query, integrate, transform, and load data.

Once everything is in place, the database administrator ensures data access and security: they provide or revoke access to data, maintain and backup the database, monitor its performance, and update and even recover it when necessary.

There’s also a whole range of data jobs concerned mainly with using the data from the database. This includes data analysts, financial analysts, quantitative analysts, BI developers, statisticians, and data scientists. They’re expected to be able to fetch the data independently – which mandates SQL skills.

SQL Server and T-SQL: What You Need to Know

While the required SQL Server knowledge varies from job to job, one aspect they all have in common is knowledge of T-SQL – the SQL Server’s dialect of SQL. To be ready for the job interview, you should be fluent in T-SQL. This is best achieved through working experience, solving actual interview questions, and leveraging interactive SQL courses. For the latter category, we suggest our SQL from A to Z in MS SQL Server learning track. It consists of seven interactive courses covering everything from the basics of T-SQL to GROUP BY extensions, recursive queries, and Common Table Expressions.

There are many good courses available; here are the five we suggest for SQL Server beginners.

Other than that, you should also brush up on your technical SQL Server knowledge, as the interview will also include non-SQL-related questions.

We’ll cover as many SQL Server interview questions as possible here. However, no data job requires only SQL Server knowledge.

So, how do you prepare for the other parts of the interview? The starting point should always be the job description. Carefully read the requirements. They could include other programming languages (e.g., Python), statistical analysis, ETL, database design, machine learning, and so on. Envisage the action plan that will have you prepared on all these fronts. And use all the available sources – such as articles, YouTube videos, courses, practice datasets, platforms with interview questions, and all kinds of different tutorials.

Now, let’s get you started on the SQL Server interview questions.

General SQL Server Interview Questions

Interviewers often like to ask you some general questions about SQL Server as a warm-up. Let’s start with some easy ones..

1. What Is SQL Server?

Answer: SQL Server is Microsoft’s relational database management system (RDBMS). It ensures data storage, integrity, security, and access. In other words, it allows creating, storing, updating, deleting, and retrieving data.

2. Can You Explain the Different Editions of SQL Server?

Answer: There are five editions of SQL Server. The entry-level is SQL Server Express, a free database for learning and building small desktop and server applications. It’s mainly used by individuals and small organizations that require only basic RDBMS functionalities.

SQL Server Developer is licensed to one user. It is used as a development and test system, not as a production server. It has all the functionalities of the Enterprise edition, only in the non-production environment. Developers and testers use it.

SQL Server Web is designed for Web hosts and Web Virtual Access Points (VAPs). It allows them to share (at low cost) their services and applications running on SQL Server.

SQL Server Standard provides core data management and Business Intelligence features for small organizations with minimal IT resources. It offers almost everything SQL Enterprise does but with some limitations.

SQL Enterprise is the go-to edition for large organizations. It provides unlimited virtualization, access to the cloud via Azure, and the best performance, data analytics, and security. It is also the priciest SQL Server edition. 

3. What Are the Main Components of SQL Server?

Answer: SQL Server consists of several components.

1. The database engine handles data storage, security, and transformation.
2. The SQL Server application starts, stops, and pauses the work of other components. Its executable is sqlservr.exe.
3. SQL Server Agent is a task scheduler that’s activated with any event. Its executable is sqlagent.exe.
4. SQL Server Browser takes the incoming requests for SQL Server and provides the information about the SQL Server instances installed on the computer. Its executable is ssqlbrowser.exe.
5. SQL Server Full-Text Search allows running full-text lookups against the character-based data stored in the database tables. Its executable is fdlauncher.exe.
6. SQL Server VSS Writer makes database backup and restoration possible by creating shadow copies when the SQL server isn’t running. Its executable is sqlwriter.exe.
7. SQL Server Analysis Services (SSAS) is a data mining and analysis component, as well as reporting and data visualization through connectivity with tools such as PowerBI and Excel. It also allows for the use of Python and R for advanced analyses. Its executable is smsmdsrv.exe.
8. SQL Server Reporting Services (SSRS) is a reporting component that allows you to create, deploy, and manage graphical, mobile, and printed reports. Its executable is ReportingServicesService.exe.
9. SQL Server Integration Services (SSIS) is an ETL tool for warehousing, extracting, transforming, loading, and integrating data. Its executable is MsDtsSrvr.exe.

SQL/T-SQL Interview Questions

These are the SQL Server interview questions that directly relate to T-SQL and its commands. Most of them don’t require any coding demonstrations, but you still need strong T-SQL knowledge to provide an answer. If you’re interested in some pre-interview coding practice, here are some SQL coding interview questions.

4. What Is T-SQL? What Is the Difference Between SQL and T-SQL?

Answer: T-SQL is an SQL dialect used for communicating with SQL Server database.

SQL is an open-source language developed by IBM, while T-SQL is created and owned by Microsoft. SQL is a non-procedural language, meaning it executes one SQL query at a time. On the other hand, T-SQL is a procedural language. This means the queries are executed as a block in a logically structured order. Also, T-SQL has some statements, keywords, and functions that don’t exist in standard SQL. For more details on those, take a look at the article highlighting the 14 differences between SQL and T-SQL.

5. What Are the Different Types of Joins in SQL Server?

Answer: There are five different JOINs in SQL Server:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN is an inner join that returns the matching rows from both joined tables.

LEFT JOIN is an outer join that returns all the rows from the first (left) table and only the matching rows from the second (right) table. RIGHT JOIN does the same, but from the right; it returns all the rows from the right table and matching rows from the left table.

FULL JOIN is an outer join that combines LEFT JOIN and RIGHT JOIN: it returns all the rows from the left table and all the rows from the right table.

CROSS JOIN returns a Cartesian product – i.e., it returns all the combinations of all the rows from the joined tables.

Our SQL JOIN Cheat Sheet nicely explains each of these joins using examples. If you want to go deeper, here are 10 SQL JOIN interview questions.

6. What Is the Purpose of the GROUP BY and HAVING Clauses in T-SQL?

Answer: GROUP BY organizes rows with the same values in one or more columns into groups. It is often (but not always) used with aggregate functions.

You can find more details in this GROUP BY article, and there are 5 GROUP BY examples in another article.

HAVING is used for filtering groups based on a specified condition. Since it filters groups, aggregate functions are allowed in this clause.

Here’s an article that explains HAVING in more detail.

7. What Is a Subquery in T-SQL?

Answer: A subquery or an inner query is a query placed within another query (called an outer query). In T-SQL, subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements. They are placed in the FROM, WHERE, or HAVING clauses of the allowed T-SQL statement.

8. What Are the Different Types of Subqueries?

Answer: There are several types of subqueries.

A scalar subquery is a subquery that returns precisely one value (one row and one column) as a result. It is used in the FROM, WHERE, and HAVING clauses.

A multiple-row subquery is a subquery that returns multiple rows and one or more columns. They are used in the same clauses as scalar subqueries but with the IN, NOT IN, ANY, ALL, EXISTS, or NOT EXISTS operators.

Correlated subqueries use a value from the outer query. It is evaluated once for each row from the outer query.

Non-correlated subqueries work independently from the outer query; they don’t depend on the outer query results.

You can read more about the different types of subqueries.

9. What Is the Difference Between UNION and UNION ALL?

Answer: Both UNION and UNION ALL merge the results of two or more queries into one result set. The difference is that UNION doesn’t include duplicates, while UNION ALL does.

You can learn more about their syntax and use in this article about UNION vs. UNION ALL.

10. List the Most Common Functions in SQL Server.

Answer: Here are the most commonly used function categories and functions in SQL Server.

 

String functions:

• CONCAT() – Merges two or more strings.
• LEFT(), RIGHT() & SUBSTRING() – Returns the specified number of characters from the left or right end of a string or a stated position in the string.
• LEN() – Returns the number of characters in the string.
• LTRIM(), RTRIM() & TRIM() – Removes spaces or other characters from the left, right, or a specified position in the string.
• REPLACE() – Replaces all the occurrences of a specified substring with another string.
• LOWER() and UPPER() – Turns all the characters lowercase or uppercase, respectively.

Numeric functions:

• ABS() – Returns the absolute value.
• ROUND() – Rounds a number to a specified decimal place.

Date and time functions:

• DATEDIFF() – Returns the difference between two specified dates.
• CURRENT_TIMESTAMP() – Returns the current date and time.
• DATEADD() – Adds a date or time interval to a date and returns a new date.
• DAY(), MONTH(), YEAR() – Returns the day, month, or year of a date.

Aggregate functions:

• COUNT() – Counts the number of rows returned by a query.
• SUM() – Returns the sum of values.
• AVG() – Returns the average value.
• MIN() – Returns the minimum value.
• MAX() – Returns the maximum value.

Other functions:

• CAST() & CONVERT() – Convert a value from one data type to a specified data type.
• COALESCE() – Returns the first non-NULL
• ISNULL() – Returns the specified value if the expression is NULL or the expression is otherwise.
• NULLIF() – Returns NULL if two expressions are the same.

11. What Is the Purpose of the COALESCE() and NULLIF() Functions?

Answer: COALESCE() returns the first non-NULL value. NULLIF() returns NULL if two expressions are the same.

Here’s how you can use COALESCE() in SQL.

12. What Are Window Functions in SQL Server?

Answer: These functions perform calculations across the set of rows related to the current row. They’re similar to aggregate functions, but unlike them, window functions don’t condense the result into one row. This allows for the simultaneous display of the aggregated and individual rows’ values.

Window functions in SQL Server are used for ranking, aggregation, and analytics.

You can review the most important window functions concepts with our SQL Window Functions Cheat Sheet. You can practice what you learn there on these 11 SQL window function exercises. And if you want to get ready for the job interview, test your knowledge of these 10 SQL window function interview questions.

13. Find the Top N Rows in SQL Server.

Answer: If we want to return the top 5 rows from the album table, we could do it like this:

SELECT TOP 5 *
FROM albums;

14. What Do ROLLUP, GROUPING SETS, and CUBE Do in T-SQL?

Answer: They are all extensions of GROUP BY. ROLLUP allows you to create multiple grouping sets and add subtotals and grand totals. With GROUPING SETS, you can define multiple grouping sets, which is like combining several GROUP BY clauses in one query. CUBE allows you to create groups for possible combinations of columns and add subtotals.

15. What Is a CTE in SQL Server?

Answer: A CTE or a Common Table Expression is the named temporary result of a SELECT statement. They are similar to subqueries, but unlike them, CTEs can be used multiple times within the same query. They are also similar to temporary tables. However, the CTE result is not stored in the database memory; the CTE has to be run every time its result is used by a query.

CTE results can be referenced in SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Look for more details in the article explaining CTEs in SQL Server. To further your knowledge, here are seven CTE examples that you can learn from.

16. Explain What a Recursive CTE Is and How to Write One in SQL Server.

Answer: A recursive CTE references itself. Like a regular CTE, a recursive CTE in SQL Server is written using the WITH clause.

The first part of a recursive CTE is an anchor member, which outputs the CTE’s base result.

This result is merged with the result of another query, which is called a recursive member because it references the anchor member. This merging is done using UNION ALL. Using UNION wouldn’t work (unlike in some other databases).

The third part of a recursive CTE is the termination condition, which specifies the conditions that will stop recursion; otherwise, it could go on indefinitely.

This article about recursive CTEs will teach you how to turn the above answer into code.

Intermediate SQL Server Interview Questions

We’ll now have a look at questions for intermediate SQL Server users.

17. How Do You Create a Table in SQL Server?

Answer: Tables in SQL Server are created using the CREATE TABLE command. In it, you specify the table name, column names, and the data type each column will accept.

The syntax looks like this:

CREATE TABLE table_name (
   column_1 datatype,
   column_2 datatype,
   column_3 datatype,
   …
);

18. What Is a Primary Key? What Is a Foreign Key?

Answer: The primary key is a column (or a combination of columns) that ensures the unique identification of  rows in a table. A foreign key is a column or a combination of columns in one table that references the primary key in another table.

19. What Is the Difference Between DELETE and TRUNCATE Commands?

Answer: While they may seem similar, they belong to different categories of SQL commands. DELETE is a Data Manipulation Language (DML) command used for deleting all or some of the rows (using the WHERE clause) from the table.

TRUNCATE is a Data Definition Language (DDL) command. It deletes all the rows from the table; you cannot  use it for deleting only some rows. Another difference is that TRUNCATE doesn’t record each row’s changes to the log, so it’s faster than DELETE. DELETE erases row by row, with each deletion recorded in the log.

19. What Is the Difference Between a Table and a View in SQL Server?

Answer: A table is a database object that is the primary storage of data in relational databases. It consists of rows and columns and is stored in memory.

A view is a stored query that results in a virtual table containing rows and columns of data. A view doesn’t contain any data in itself and outputs it only when it’s run. This improves ease of use and data security. It also saves memory, since the view result is not stored in memory.

20. Can You Explain the Different Types of Indexes in SQL Server?

Answer: These indexes are supported in SQL Server:

• Hash
• Memory-Optimized Nonclustered
• Clustered
• Nonclustered
• Unique
• Columnstore
• Index with Included Columns
• Index on Computed Columns
• Filtered
• Spatial
• XML
• Full-text

The two main index categories are clustered and nonclustered.

Clustered indexes are part of the table structure and determine the physical order of storing and sorting based on the index key value. There can be only one clustered index per table. When looking for the data, the query goes through the table row by row until it finds the specified index.

Nonclustered indexes contain key values, with each key value having a pointer to the data row that contains the key value. This means that the query goes directly to the data row with a specified index; no need to go through all the data before it. These indexes are structured and stored separately from the indexed tables.

21. What Is the Difference Between a Function and a Stored Procedure?

Answer: Functions are routines that return calculated values and can’t make changes to database objects. They must have at least one parameter and return some result.

In SQL Server, there’s a distinction between built-in functions and user-defined functions. Built-in functions come with SQL Server; they are divided into the following categories:

• Aggregate functions perform a calculation on a group of rows (i.e. using GROUP BY and shared column values) and return a single value for the entire group.
• Analytic functions perform calculations across the set of rows related to the current row. These are aggregate window functions.
• Bit manipulation functions make working with binary information more efficient.
• Ranking functions (i.e. ranking window functions) rank rows and/or data in a particular order.
• Rowset functions contain sets of rows with all the column values. They can be referenced instead of tables in T-SQL statements.
• Scalar functions take one parameter and return one value.

User-defined functions have all the characteristics of the built-in functions, only they’re created by users. You can create them, store them, and call them any time you want.

Stored procedures are pre-compiled SQL statements that repeatedly perform a set of actions. Unlike functions, stored procedures don’t need a parameter, don’t have to return a result, and can modify database objects.

INSERT, UPDATE, and DELETE commands are allowed in the stored procedures but not in functions.

Functions work with SELECT, WHERE, and HAVING; stored procedures don’t.

Stored procedures can use transactions and call functions. However, functions can’t use transactions, nor can they call stored procedures.

22. What Is the Difference Between a Temporary Table and a Table Variable?

Answer: Temporary tables are the temporary storage of a query result. They’re only available for the session they’re created in.

A table variable is a local variable that, like the temporary table, stores data temporarily. Table variables are used in functions, stored procedures, and batches.

As temporary tables are real tables, they are more suitable for larger amounts of data than table variables. This means you can use the SELECT INTO, ALTER TABLE, and TRUNCATE commands with temporary tables. You can create indexes or do anything you would do with a ‘regular’ table. Temporary tables can also be used in transactions and stored procedures, but can’t be used in the functions.

23. What Is a Trigger? When Would You Use One?

Answer: A trigger is  a type of stored procedure that runs automatically (i.e. is triggered) when a specified event occurs.

DML triggers are activated when a user tries to modify the data in the table using INSERT, UPDATE, or DELETE.  DDL triggers run when users use CREATE, ALTER, or DROP. There are also logon triggers that are activated when a user establishes a connection with SQL Server.

One example of using a DML trigger is on a table that stores data about employees, including their salary. You could create a trigger for every time somebody changes info about an employee; the trigger will store changed information in a separate table. That way, you could have a historical overview of each employee's information changes.

24. What Is the Difference Between Commit and Rollback?

Answer: COMMIT TRANSACTION permanently saves all the data modifications a transaction makes.

ROLLUP TRANSACTION allows you to undo all the changes that occurred since the last commit of transaction changes.

Advanced SQL Server Interview Questions

Let’s go deeper into SQL Server knowledge and cover several advanced interview questions. You can find more advanced questions and coding examples in this article.

25. Can You Explain the Different Types of Replication in SQL Server?

Answer: Transactional replication is a near real-time replication of the main database copy (publisher) to the database that it’s replicated unto (subscriber).

In merge replication, all the changes made offline are made whenever the two databases are connected. It’s similar to transactional replication, but the changes are made to both the publisher and subscriber.

Snapshot replication snapshots the main database copy at a certain point in time and replicates its data to another database.

Peer-to-peer replication is a variation of transaction replication that allows for replication across any number of servers.

Bidirectional transactional replication replicates data between two servers, with both servers publishing and subscribing to the data from the other server.

Updatable subscriptions replication is when, after the change has been made on the subscriber database, this data is first passed on to a main database copy. Only after that is it replicated to other subscribers.

26. Explain the Different Types of Locks in SQL Server.

Answer: In database management, locks are a means for SQL Server to ensure transaction concurrency in an environment where multiple users are accessing the same database at the same time. When SQL Server processes a query, it decides which data resources will be used and how. While completing a query, it also decides on the type of lock that will be applied to protect the data resource.

Here are the types of lock modes in SQL Server:

• Shared (S) lock mode is used for read-only statements, e.g., SELECT. It makes data available only for reading. This means while the query is being executed, it won’t allow for data to be changed.
• Update (U) lock mode is used on data that is going to be updated. Unlike the S lock mode, only one transaction can hold a U lock; this ensures deadlocks are avoided. Once the transaction is ready to update data, the lock mode changes to X lock.
• Exclusive (X) lock mode is used to ensure that only one transaction can update the same data at a time. It’s used with commands such as INSERT, UPDATE, and DELETE.
• Intent (I) lock mode ensures a lock hierarchy is established. It informs other transactions about the intention to impose an S or X lock, thus preventing another transaction from imposing them. There are three intent locks: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
• Schema (Sch) lock mode is used for schema-dependent operations. The schema modification (Sch-M) lock for DDL statements (CREATE, ALTER, DROP) prevents data access while the object’s structure is being modified. The schema stability (Sch-S) lock is used when schema-dependent transactions are being executed. It will allow all other locks except Sch-M.
• Bulk Update (BU) lock mode is imposed when data is copied in bulk, and the TABLOCK hint is specified.
• Key-range lock mode is used in serializable transaction isolation levels. This prevents phantom reads, i.e., when the same query returns different results every time it runs.

27. What Is the Difference Between a Deadlock and a Livelock?

Answer: A deadlock is when two or more processes block each other from acquiring a lock. In other words, it’s when each process has a lock on one piece of data and tries to acquire a lock on another piece of data that is locked by another process. SQL Server will terminate one of the processes. Otherwise, the deadlock would continue forever.

A livelock occurs when the shared locks overlap, preventing another process from acquiring an exclusive lock.

28. What Is the Difference Between a Materialized and a Non-Materialized View?

Answer: Materialized views are logical views of data with its output saved in a table, thus requiring a query to run only once. They increase performance, as the result can be indexed (it’s a table!). On the other hand, the result might not be fresh if the materialized view output hasn’t been updated.

Non-materialized views are also logical views of data, but their output is not saved anywhere. A drawback is that you must run the view each time you want to use its result. On a more positive note, you’re guaranteed to get the latest data.

29. What Is the Difference Between Implicit and Explicit Transactions?

Answer: Explicit transactions are initiated with BEGIN TRANSACTION; the change to data is committed with COMMIT TRANSACTION.

For the implicit transactions, SQL Server takes the initiative to begin them implicitly (without the BEGIN TRANSACTION command). Still, they have to be committed or rolled back explicitly by the user.

If the implicit mode is activated, these are the keywords that will start the implicit transaction:

• BEGIN TRANSACTION
• CREATE
• UPDATE
• DELETE
• DROP
• FETCH
• INSERT
• OPEN
• GRANT
• REVOKE
• SELECT
• ALTER
• TRUNCATE

Getting the Hang of SQL Server Interview Questions?

We sure hope you are! The 29 interview questions we presented (and answered) include general, T-SQL, intermediate, and advanced SQL Server interview questions.

The category of T-SQL questions contains the most questions, which reflects its importance. As we said at the beginning, writing T-SQL queries is a shared requirement for all the data jobs that work with relational databases in any form.

So, when preparing for an interview, you shouldn’t brush off other questions as unnecessary. In terms of time management, you should focus on mastering T-SQL. The SQL from A to Z in MS SQL Server is a perfect platform for that!

Now, it’s up to you to practice and ace that job interview!