Prepare to ace your following SQL Server interview with our Top 100 SQL Server Interview Questions! We’ve covered from basics to advanced topics with clear, concise answers and practical insights.
Table of Contents
Basic Level SQL Server Interview Questions
A few basic-level SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What is the use of SQL Server in any organization?
Ans: Microsoft SQL Server is a powerful and popular relational database management system (RDBMS) tool that helps manage large databases with multiple tables and create relationships between them. SQL Server supports T-SQL as well as ANSI SQL.
Q: What are SQL Server’s primary features?
Ans: Some features include ACID compliance, XML and JSON compatibility, integration services, full-text search, Always On availability groups, and in-memory processing.
Q: What is the primary key (or PK) in SQL Server?
Ans: Each entry in a table is uniquely identified by its primary key, which is why it cannot include NULL values.
Q: What distinguishes CHAR from VARCHAR, please?
Ans: Compared to VARCHAR, which has a variable length, CHAR has a fixed length.
Q: What is meant by a foreign key?
Ans: A foreign key is a mechanism to create a relationship between the parent-child table by referencing the parent table’s primary key in the child table.
Q: Create a query to retrieve a table’s first ten records.
Ans: To fetch the first 10 records from a table, we can use the below query:
SELECT TOP 10 * FROM TableName.Q: What does normalization mean in the database?
Ans: The practice of arranging the data in the database is known as normalization. It helps us to reduce redundancy.
Q: What does A stored procedure do in SQL Server?
Ans: A stored procedure (or SP) in the SQL server is a set of T-SQL commands that perform a specific task. It is precompiled code that can help it run fast.
Q: What distinguishes TRUNCATE from DELETE, please?
Ans: TRUNCATE eliminates all rows without recording individual deletions, whereas DELETE removes rows one at a time and logs each deletion.
Q: To identify duplicate rows in a table, write a query.
Ans: To find the duplicate value in a table, we can use the below query:
SELECT <Column 1>, <Column 2>, COUNT(*) AS [RowCount]
FROM <TableName>
GROUP BY <Column 1>, <Column 2>
WHERE COUNT(*) > 1Intermediate Level SQL Server Interview Questions
A few Intermediate-level SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What distinguishes non-clustered indexes from clustered indexes?
Ans: A non-clustered index keeps pointers to the data rows, while a clustered index uses key values to sort and store the data rows in the table.
Q: What distinguishes GROUP BY from ORDER BY?
Ans: While ORDER BY organizes the result set in ascending or descending order, the GROUP BY clause groups the rows with the same values into summary rows.
Q: What is a CTE or common table expression?
Ans: A SELECT, INSERT, UPDATE, or DELETE query can refer to a CTE, a temporary result set defined in a WITH clause.
Q: To update several rows in a table, write a query.
Ans: To update several rows in a table, use the below query:
UPDATE <NameOfTable>
SET <NameOfColumn>= 'NewValue'
WHERE <Condition>;
For Example:
UPDATE Employee SET BasicSalary = BasicSalary + 5000 WHERE BasicSalary > 50000 ;Q: What distinguishes a left join from an inner join in SQL Server?
Ans: Whereas LEFT JOIN yields all data from the left table and matching rows from the right table, INNER JOIN yields matching rows from both tables.
Q: Describe transactions and illustrate.
Ans: When we execute a list of T-SQL commands in the database as a single batch, it is known as transactions.
BEGIN TRAN;
UPDATE Employee SET BasicSalary = BasicSalary + (BasicSalary0.05) WHERE BasicSalary > 50000 AND BasicSalary <= 100000;
UPDATE Employee SET BasicSalary = BasicSalary + (BasicSalary0.03) WHERE BasicSalary > 100000 AND BasicSalary <= 200000; UPDATE Employee SET BasicSalary = BasicSalary + (BasicSalary*0.02) WHERE BasicSalary > 200000 AND BasicSalary <= 500000;
COMMIT TRAN;Q: What is the use of the temp table in SQL Server?
Ans: To store temporary data/results during data processing, we can use the temp table in SQL Server. Database Engine creates the temp tables in the TempDB database.
CREATE TABLE #TempTable (ItemID INT PRIMARY KEY, ItemName VARCHAR(100));Q: What is the use of SQL Server Agent?
Ans: Microsoft SQL Server has provided a feature to create, schedule & manage the Agent Job. We can specify multiple lines of T-SQL commands for automatic execution and schedule the job to run as per our requirements. Raise notifications to a set of users.
Q: What is the use of correlated subqueries in SQL Servers?
Ans: A subquery that utilizes values from the outer query is a correlated subquery. Three typical methods for locating the Nth highest pay in an Emp table are listed below.
--Create Table Script to create a sample table
CREATE TABLE Emp (
EmpId INT PRIMARY KEY,
EmpName VARCHAR(100),
Salary decimal(12,2)
);
--Insert Script to insert sample data in the emp table
INSERT INTO Emp (EmpId, EmpName, Salary)
VALUES (1, 'Ili', 50000),
(2, 'John', 60000),
(3, 'David', 70000),
(4, 'Peter', 80000),
(5, 'Phillip', 60000),
(6, 'Joe', 90000),
(7, 'Kally', 95000),
(8, 'Britney', 85000),
(9, 'George', 75000),
(10, 'Bill', 67000);
--Select command to see all rows in the table
SELECT * FROM Emp ORDER BY Salary DESC
--Way - 1: Script to get Nth highest Salary from Emp Table
DECLARE @N INT = 3;
SELECT Salary AS [3rdHighestSalary-Way1]
FROM (
SELECT DISTINCT Salary
FROM Emp
) AS DistinctSalaries
ORDER BY Salary DESC
OFFSET (@N - 1) ROWS FETCH NEXT 1 ROW ONLY;
--Way - 2: Script to get Nth highest Salary from Emp Table
DECLARE @N INT = 3;
WITH RankedSalaries AS (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Emp
)
SELECT Salary AS [3rdHighestSalary-Way2]
FROM RankedSalaries
WHERE SalaryRank = @N;
--Way - 3: Script to get Nth highest Salary from Emp Table
DECLARE @N INT = 3;
SELECT DISTINCT E1.Salary AS [3rdHighestSalary-Way3]
FROM Emp E1
WHERE (
SELECT COUNT(DISTINCT E2.Salary)
FROM Emp E2
WHERE E2.Salary >= E1.Salary
) = @N;
Q: What distinguishes WHERE from HAVING in SQL Server?
Ans: We use the HAVING clause with GROUP BY, whereas WHERE can be used with a standard query. HAVING runs & filters the data after data aggregation, whereas WHERE runs & filters the data before grouping the data.
--Example of Group By clause
SELECT Salary,COUNT(*) AS [CountOfEmpWithSameSalary]
FROM Emp
GROUP BY Salary
--Example of Having clause
SELECT Salary,COUNT(*) AS [CountOfEmpWithSameSalary]
FROM Emp
GROUP BY Salary HAVING COUNT(*)>1Advanced Level SQL Server Interview Questions
A few advanced-level SQL Server Interview Questions with answers are given below for your interview preparation:
Q: What are the types of indexing?
Ans: Data retrieval is accelerated through indexing. Clustered, non-clustered, unique, and full-text indexes are among the types.
Q: What is meant by a deadlock?
This is a deadlock when two or more processes block one another while awaiting resources.
Q: How can a SQL Server deadlock be resolved?
Ans: To reduce conflicts, use the SET DEADLOCK_PRIORITY or TRY-CATCH block.
Q: What does sp_executesql mean?
Ans: It allows parameterized queries and runs a dynamically constructed SQL statement.
Q: Describe how to use DBCC CHECKDB.
Ans: By examining tables and indexes for corruption, DBCC CHECKDB guarantees database consistency.
Q: What distinguishes ISNULL from COALESCE, please?
Ans: Whereas COALESCE delivers a list’s first non-NULL value, ISNULL substitutes a specified value for NULL.
Q: To determine the 2nd highest pay, write a T-SQL query.
Ans: To fetch 2nd highest salary, use the below query:
DECLARE @N INT = 2;
SELECT Salary AS [3rdHighestSalary-Way1]
FROM (
SELECT DISTINCT Salary
FROM Emp
) AS DistinctSalaries
ORDER BY Salary DESC
OFFSET (@N - 1) ROWS FETCH NEXT 1 ROW ONLY;Q: What is partitioning in SQL Server?
Ans: Partitioning splits a table or index into smaller, easier-to-manage components to increase performance.
Q: What are the groups that are always available?
Ans: A high-availability feature that guarantees failover and database replication.
Q: Describe SQL Server replication.
Ans: Replication in SQL Server is an object-level synching mechanism that distributes an object’s data among multiple databases.
Q: What makes VARCHAR and NVARCHAR different from one another?
Ans: While NVARCHAR uses twice as much storage space per letter and saves Unicode data, VARCHAR stores non-Unicode data.
Performance Optimization & Troubleshooting SQL Server Interview Questions
A few performance optimization & troubleshooting related SQL Server Interview Questions with answers are given below for your interview preparation:
Q: How may slow-running queries be identified?
Ans: Use features like Dynamic Management Views (DMVs), Execution Plans, and Query Store.
Q: What does an execution plan entail?
Ans: A query’s execution plan is a road map for SQL Server.
Q: How can SQL queries be made more efficient?
Ans: Utilize indexing, steer clear of SELECT *, create effective joins, and reduce the number of subqueries.
Q: What is the purpose of table partitioning?
Ans: By breaking up big tables into smaller, more manageable components, table partitioning enhances query efficiency.
Q: How is the performance of the SQL Server monitored?
Ans: Use programs like Extended Events, SQL Profiler, and Performance Monitor.
Q: What distinguishes a table with a clustered index from a heap?
Ans: A table with a clustered index has rows ordered according to the index, but a heap has no clustered index.
Q: What are the best practices for tempdb?
Ans: Use numerous data and pre-size tempdb files to monitor allocation page contention.
Q: What is a snapshot of a database?
Ans: A static, read-only representation of a database at a particular moment in time.
Q: What does SQL Server query optimization mean?
Ans: It applies strategies like indexing and rewriting queries to make them run faster.
Q: How is blocking in SQL Server handled?
Ans: Set appropriate isolation levels, optimize searches, and use WITH (NOLOCK) hints.
Review the articles below
DBCC FREEPROCCACHE: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server
SQL Server Pivot: Top 5 Concepts
A Powerful Merge Statement in SQL Server
Dynamic Data Masking in SQL Server
DBCC SQLPerf (LogSpace): Top 15 Usage
A Powerful SQL Server Developer Edition
Unveiling the Power of SQL Server CharIndex
SQL Server Convert: An Important Function
SQL Server Configuration Manager