SQL Server Interview Question and Answers with Example - Part 3

Hits: 7734 Print

Below is top 100 SQL Server interview question and answers with examples for freshers and intermediate developers.

What are the different Ranking functions available in SQL Server?

Different ranking functions are: Row_Number, Rank and Dense_Rank.
Similarities between Row_Number, Rank and Dense_Rank are shown below.

Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties).
ORDER BY clause is required.
PARTITION BY clause is optional.
When the data is partitioned, the integer value is reset to 1 when the partition changes.

ROW_NUMBER: Returns an increasing unique number for each row starting at 1, even if there are duplicates.
RANK: Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
DENSE_RANK: Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence.
Consider the below table for understanding rank functions.

Notice 3 employees have the same salary 8000. When you execute the following query you can clearly see the difference between RANK, DENSE_RANK and ROW_NUMBER functions.

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

How to delete duplicate record in SQL Server?

We can delete duplicate record with the help of CTE, ROW_NUMBER function and Partition by as shown below.

create table #Employee  
(
Id int,
Name varchar(20),
Gender varchar(10),
City varchar(20),
Salary int
)
insert into #Employee values(5,'Mike','Male','Mumbai',50000)
insert into #Employee values(6,'Rahul','Male','London',85000)
insert into #Employee values(7,'Deepak','Male','Delhi',85000)
insert into #Employee values(8,'Mary','Female','Mumbai',75000)
insert into #Employee values(9,'Deepak','Male','Mumbai',60000)
insert into #Employee values(10,'Deepak','Male','Delhi',15000)
insert into #Employee values(2,'Sara','Female','Mumbai',50000)
;with CTE as(
select *, ROW_NUMBER() over (Partition by Name order by Name ) as RNumber from #Employee
)
delete from CTE where RNumber>1

Here, in above example, we have deleted record based on Name column. We can delete records with multilpe column as shown below.

;WITH CTE AS (
    SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
      ROW_NUMBER() OVER(PARTITION BY col1,col2 ORDER BY col1,col2) as RNumber
    FROM Table_Name
)
DELETE FROM CTE WHERE RNumber > 1

How to find nth highest or nth lowest Salary in SQL Server?

create table #Employee  
(
Id int,
Name varchar(20),
Gender varchar(10),
City varchar(20),
Salary int
)
insert into #Employee values(5,'Mike','Male','Mumbai',50000)
insert into #Employee values(6,'Rahul','Male','London',85000)
insert into #Employee values(7,'Deepak','Male','Delhi',85000)
insert into #Employee values(8,'Mary','Female','Mumbai',75000)
insert into #Employee values(9,'Deepak','Male','Mumbai',60000)
insert into #Employee values(10,'Deepak','Male','Delhi',15000)
insert into #Employee values(2,'Sara','Female','Mumbai',50000)
SELECT top 1 Salary
FROM
  (
SELECT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) As DR  -- DESC for Highest salary and ASC for lowest salary
FROM #Employee
   ) As tbl
WHERE tbl.DRDN =1   ---  use 1, 2 or 3 for first, second or third (highest or lowest)

How to update existing record and insert new record using merge and match?

In below example, we are updating existing record of table testA from table testB and adding new record using merge and match.

create table #testA
(
id int,
name varchar(20)
)
create table #testB
(
id int,
name varchar(20)
)
insert into #testA values(1,'A')
insert into #testA values(2,'B')
insert into #testA values(3,'C')
insert into #testB values(1,'x')
insert into #testB values(4,'y')
insert into #testB values(5,'z')
merge into #testA
using #testB
on #testA.id = #testB.id
when matched then
update 
set name=#testB.name
when not matched then
insert (id,name) values(#testB.id,#testB.name);

Page 3 of 3

Interview