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

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

  delete from CTE where RN>1;

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}.

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

Test-1
Test-2
Test-3

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

    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

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

-- 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'
);

-- 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;
    

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

• 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);
    

-- 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';

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';
    

• 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 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
);

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 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);
    

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

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;
    

• 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.

• 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.

• 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.

-- 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