Top 23+ SQL Server Interview Questions and Answers

• Where clause is used to filter the records from a result set. Filtering occurs before any grouping is made. It works with select clauses. it does not work with aggregate functions or GROUP BY statements.

  
  Select * from Employee Where Id > 10;
  

• Having Clause is used to filter the records from groups. It works with group by clause and works on aggregate functions.

  Select Name, Salary from Employee   
  Group by Name, Salary   
  Having SUM(Salary)  > 10000; 

• Group by clause is used to display the data in the form of identical groups. It is used with a SELECT Clause to group the result set by one or more columns.

  Select col1, col2, from table 
  group by col1, col2

We can use CTE, Row_Number() with an over clause.

With CTE AS (
  select *,RN = ROW_NUMBER() over(partition by id Order by id) from Employee1
  )

  delete from CTE where RN>1;

id - is the column by which you find duplicates.
OR you can also use below code if the table has an identity column to identify duplicate data.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

• Row_Number() is used to return a unique sequential number for each row starting from 1. If the partition clause is used with Row_Number then it reset sequential numbers for each partition. It does not skip or repeat the numbers in result.
• RANK() is used to return a unique number for each distinct row starting from 1, within the partition if a partition clause is used. It starts at 1 in each partition. It sets the same rank for duplicate data and leaves the gaps in the rank sequence after duplicate values.
• DENSE_RANK() has similar behavior like the RANK function but there is one difference that it does not leave the gaps in sequential rank after duplicate values.
  For example, consider the set {5, 5, 10, 15, 15, 20}. So here RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped because of duplicate values assigned same rank and after duplication values there will be gap in rank), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

Example:

USE [Practice]
GO

/****** Object: Table [dbo].[Employee] Script Date: 31-05-2019 10:50:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee] (
    [Id]     INT          NOT NULL,
    [Name]   VARCHAR (50) NULL,
    [Salary] VARCHAR (50) NULL
);


INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (1, N'bhanu',N'100')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (2, N'bhanu',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (3, N'Faizan',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (4, N'Faizan',N'200')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (5, N'Kap', N'300')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (6, N'Jap', N'100')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (7, N'Abhi', N'500')
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary]) VALUES (8, N'Shan', N'200')


;with CTE AS(select *, ROW_NUMBER() over(partition by Name,Salary order by Id) as RN from Employee)
select * from CTE
--select * from CTE where RN>1     -- here you can delete duplicate records where RN>1
-- delete from CTE where RN>1


select *, ROW_NUMBER() over(order by Id) as RN from Employee
select *, ROW_NUMBER() over(partition by Name,Salary order by Id) as RN from Employee


select *, RANK() over(order by Name) as rn from Employee
select *, RANK() over(partition by Name order by Name) as rn from Employee


select *, Dense_RANK() over(order by Name) as rn from Employee
select *, Dense_RANK() over(partition by Salary order by Name) as rn from Employee


-- find nth highest salary - always use dense rank as rank will skip some numbers.
-- so it will not give any result for those skipped numbers
select * from Employee
;with CTE1 as (select *, RANK() over (order by salary desc) as RN from Employee)
select top 1 * from CTE1 where RN=4   -- 4th Highest from highest to lowest

select * from Employee
;with CTE1 as (select *, DENSE_RANK() over (order by salary desc) as RN from Employee)
select top 1 * from CTE1 where RN=2  -- 2nd highest from

ROW_NUMBER, RANK, DENSE_RANK can be used with partition or without it but not without an over clause.

select top 1 id from (select top 5 id from employee1 order by id desc)sub order by id asc

    SELECT [id],[ename],[managerId] FROM [Practic].[dbo].[employee1]

    // Self Join Example: we will fetch emp name and manager name from the same table using self join.

    select e.ename, m.ename from employee1 e inner join employee1 m on e.managerId = m.id

SELECT * FROM TABLE WHERE ID % 2 = 0 

Select * from table where id % 2 != 0

SELECT e.name EMPLOYEE, m.name MANAGER
FROM EMPLOYEE e, EMPLOYEE m
WHERE e.manager_id = m.id 

Test-1
Test-2
Test-3

Expected O/p from above 3 rows should be - Test1, Test2, Test3

You can achieve about output using below SQL query:

    DECLARE @Names VARCHAR(8000)
    SELECT @Names = COALESCE(@Names + ', ','') + Name
    FROM Common.Category
    Select @Names

    // If row contain null values then Coalesce can give wrong results so handle null case as below:
    DECLARE @Names VARCHAR(8000)
    SELECT @Names = COALESCE(@Names + ', ','') + Name
    FROM Common.Category Where Name IS NOT NULL
    Select @Names

    // OR
    DECLARE @Names VARCHAR(8000)
    SELECT @Names = COALESCE(@Names + ', ','') + ISNULL(Name, 'N/A')
    FROM Common.Category
    Select @Names

Sometimes, We need to update table data from other tables data. In this case we prefer to use select with update command as below.

UPDATE Emp
SET
    Emp.PersonCityName = Address.City,
    Emp.PersonPostCode = Address.PostCode
FROM
    Employees Emp
INNER JOIN
    AddressList Address ON Emp.PersonId = Address.PersonId

For more visit Update from a Select command in SQL Server.

Correlated and Non-Correlated Subqueries: A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row in the outer query, while a non-correlated subquery is independent of the outer query and is executed only once.
Example:

-- Correlated Subquery
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
);

-- Non-Correlated Subquery
SELECT e.EmployeeID, e.FirstName, e.LastName
FROM Employees e
WHERE e.EmployeeID IN (
    SELECT EmployeeID
    FROM Orders
    WHERE ShipCountry = 'USA'
);

The EXCEPT operator returns distinct rows from the left input that are not present in the right input.
Example:

-- Find those customers who have not placed any orders
SELECT CustomerID, CompanyName
FROM Customers
EXCEPT
SELECT c.CustomerID, c.CompanyName
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
    

Updating Table with Subquery: You can use a correlated subquery in the UPDATE statement to update a table based on values from another table.
Example:

UPDATE Employees e
SET e.ManagerID = (
    SELECT TOP 1 EmployeeID
    FROM Employees
    WHERE Title = 'Manager'
    ORDER BY EmployeeID
)
WHERE e.Title = 'Sales Representative';
    

Index is an on-disk structure associated with a table or view that speeds the getting of rows from a database table or view. Index contains keys built from one or more column(s). These keys are stored in a special form of data structure known as B-tree that enables the SQL Server to find the rows associated with these keys very quickly. For more visit Index in SQL Server
SQL Server has 2 types of Indexes.

• Clustered Index - When you create a table with a Primary key, SQL Server automatically creates the clustered index based on columns included in the primary key. Clustered indexes sort and store the data rows in the table or view as per their key values. These are the columns which are the part of the index definition. A data table has only one clustered index, because the data rows themselves can be sorted in only one order.
  A table with a clustered index is called a clustered table. Data rows of a table with no clustered index are stored in an unordered structure called heap. A table stores the data in sorted order only when it contains a clustered index. If you add a primary key constraint to an existing table that already contains a clustered index, SQL Server will enforce the primary key using a non-clustered index.
  For more visit Clustered Index.
• Non clustered indexes have a structure separate from the data rows. A Non-clustered index includes the Non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.
  In a Non-clustered index, the pointer from an index row to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a clustered table or a heap. If data pages are stored in a heap then row locator is a pointer to the row and if data pages are stored in a clustered table then row locator is the clustered index key. A table may have one or more non clustered index and each non clustered index may have one or more columns.
  For more visit Non Clustered Index.

-- Creating a clustered index
CREATE CLUSTERED INDEX IX_Employees_LastName
ON Employees (LastName);

-- Creating a non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_City
ON Employees (City);
    

A clustered index determines the physical order of data rows in a table, and there can only be one clustered index per table. In contrast, non-clustered indexes are separate structures that provide fast access to data, but the data rows themselves are not sorted based on the non-clustered index.
For more about indexing questions visit SQL Server Indexes Questions.

Determining Index Usage: You can use the STATISTICS IO and STATISTICS TIME options in SQL Server Management Studio to see if a query is using an index efficiently. Additionally, you can examine the query execution plan to identify any missing or unused indexes.

Covering Index: A covering index is a non-clustered index that includes all the columns referenced in a query. This eliminates the need to access the data pages, improving query performance.
Example:

-- Creating a covering index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_ShipCity
ON Orders (CustomerID, ShipCity)
INCLUDE (OrderDate, Freight);

-- Query that can benefit from the covering index
SELECT CustomerID, ShipCity, OrderDate, Freight
FROM Orders
WHERE CustomerID = 'ALFKI';

Stored Procedure with Parameters and Result Set:

CREATE PROCEDURE GetEmployeesByCity
  @City NVARCHAR(30)
AS
BEGIN
  SELECT EmployeeID, FirstName, LastName, City
  FROM Employees
  WHERE City = @City;
END;

-- Executing the stored procedure
EXEC GetEmployeesByCity @City = 'London';
    

You should focus on certain points for your Stored Procedure performance as below.

• Use Proper indexing on tables.
• Set NOCount ON|OFF - it will control some messages like - after running some update query you see messages - '0 rows affected'.
• Use Select count(1) instead of count(*) for count function.
• do not use prefix 'SP' while creating stored procedures as default system stored procedures also starts with prefix 'SP'.
• Whenever it's required fetch data from the table with 'NOLOCK' keyword WITH(NOLOCK).
• Set ANSI_Nulls ON|OFF- When it's ON it means a select statement will return zero results even if there are null values in the column. When It's OFF means select statement will return the corresponding rows with null values in columns. When you are setting it OFF means comparison operators do not follow ISO standards.
• Set Quoted_Identifier ON|OFF - When it's ON means identifiers are delimited by double quotes and literals are delimited by single quotes. But when it's OFF means identifiers can not be delimited by quotation.

Scalar vs. Table-Valued User-Defined Functions: A scalar function returns a single value, while a table-valued function returns a result set that can be treated like a virtual table.
Example:

-- Scalar Function
CREATE FUNCTION GetDiscountedPrice
  (@Price MONEY, @DiscountPercentage FLOAT)
RETURNS MONEY
AS
BEGIN
  RETURN @Price * (1 - @DiscountPercentage);
END;

-- Table-Valued Function
CREATE FUNCTION GetOrdersByYear
  (@Year INT)
RETURNS TABLE
AS
RETURN (
  SELECT OrderID, CustomerID, OrderDate
  FROM Orders
  WHERE YEAR(OrderDate) = @Year
);

Error Handling and Transactions in Stored Procedures: You can use TRY...CATCH blocks and RAISERROR statements to handle errors in stored procedures. For transaction management, you can use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.
Example:%

CREATE PROCEDURE InsertOrder
  @CustomerID NCHAR(5),
  @OrderDate DATETIME
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      INSERT INTO Orders (CustomerID, OrderDate)
      VALUES (@CustomerID, @OrderDate);
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    RAISERROR ('Error inserting order', 16, 1);
  END CATCH
END;
    

MERGE Statement: The MERGE statement allows you to perform insert, update, or delete operations based on conditions in a single statement.
Example:

MERGE INTO Employees AS target
USING (SELECT 'John', 'Doe', 'Sales Representative', 5000 AS SalaryRate) AS source (FirstName, LastName, Title, SalaryRate)
ON (target.LastName = source.LastName AND target.FirstName = source.FirstName)
WHEN MATCHED THEN
  UPDATE SET target.Title = source.Title, target.SalaryRate = source.SalaryRate
WHEN NOT MATCHED THEN
  INSERT (FirstName, LastName, Title, SalaryRate)
  VALUES (source.FirstName, source.LastName, source.Title, source.SalaryRate);
    

Referential Integrity Constraints: Referential integrity constraints ensure that relationships between tables are maintained by enforcing rules on foreign key values. You can define referential integrity constraints using FOREIGN KEY constraints and cascading update/delete actions.
Example:

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID NCHAR(5) FOREIGN KEY REFERENCES Customers(CustomerID),
  OrderDate DATETIME
);
    

Triggers: Triggers are special types of stored procedures that automatically execute when specific events occur, such as insert, update, or delete operations on a table. They are useful for enforcing complex business rules, auditing data changes, or maintaining data integrity across multiple tables.
Example:

CREATE TRIGGER tr_EmployeeSalaryCheck
ON Employees
AFTER INSERT, UPDATE
AS
BEGIN
  IF EXISTS (
    SELECT 1
    FROM inserted i
    WHERE i.SalaryRate < 0
  )
  BEGIN
    RAISERROR ('Salary cannot be negative', 16, 1);
    ROLLBACK TRANSACTION;
  END
END;
    

Database monitoring is crucial for maintaining optimal performance, identifying potential issues, and ensuring the availability of your database systems. SQL Server provides various tools and utilities for monitoring, such as:

• Performance Monitor: Allows you to monitor various performance counters related to CPU, memory, disk usage, and database-specific metrics.
• SQL Server Profiler: Captures and analyzes SQL Server events, including queries, stored procedures, and errors.
• Dynamic Management Views (DMVs): Provide a wealth of information about the current state of SQL Server, including sessions, locks, queries, and more.
• Extended Events: A highly configurable and low-overhead event monitoring system for capturing and analyzing SQL Server events.

To identify and resolve performance bottlenecks, you can follow these steps:

• Monitor and Collect Baseline Data: Use the monitoring tools mentioned in above question to capture relevant performance metrics during normal operations.
• Analyze Performance Data: Examine the collected data to identify areas of concern, such as high CPU or memory usage, disk bottlenecks, or long-running queries.
• Use Query Execution Plans: Analyze the execution plans of problematic queries to understand how they are being executed and identify potential optimization opportunities.
• Tune Indexes and Statistics: Ensure that appropriate indexes are in place, and update statistics regularly to improve query performance.
• Optimize Queries: Rewrite inefficient queries, avoid cursors, and consider using set-based operations or window functions where appropriate.
• Implement Caching Strategies: Consider caching frequently accessed data or query results to reduce database load.

SQL Profiler is an graphical user interface (GUI) tool that is used for tracing, recreating and troubleshooting the problems in SQL Server. SQL Profiler is a tool used to identify slow-running queries and performance issues in production environments by capturing and analyzing database events. It is particularly useful for detecting slow-executing stored procedures that may be impacting system performance.
SQL Profiler is a tool used for identifying and troubleshooting performance issues in SQL Server. Its key applications include:

• Detecting and analyzing slow-running queries.
• Correlating performance counters to diagnose performance bottlenecks.
• Step-by-step debugging of problematic queries to pinpoint the root cause.
• Monitoring SQL Server's performance to optimize workloads and tune the system.

For more visit SQL Server Profiler.

Query Execution Plans: are graphical representations of the data retrieval methods chosen by the SQL Server query optimizer. They provide insights into how SQL Server processes a query, including the operations involved, the order of execution, and the estimated costs.
Example:

-- Get the estimated execution plan for a query
SET SHOWPLAN_TEXT ON;
GO

SELECT c.CustomerID, c.CompanyName, COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

SET SHOWPLAN_TEXT OFF;
GO

By analyzing the execution plan, you can identify potential bottlenecks, such as expensive operations, missing indexes, or suboptimal join strategies, and make appropriate optimizations. For more about execution plan visit Execution Plan in SQL Server.