Top 23 SQL Server Interview Questions and Answers

• Home
• Interviews
• SQL Server Interview Questions

07/Jul/2020 | 10 minutes to read

Best Answers to SQL Server Interview Questions

QFLES is listing some Essential SQL Server Interview Questions and Answers for Freshers and mid level of Experienced Professionals prepared by Industry Experts. All answers for these SQL Server interview questions are given based on standard documents and tried to explain in simple and easiest way.

1. What are the ACID properties? Or list and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID (A - Atomicity, C - Consistency, I - Isolation, D - Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

Atomicity

Consistency

Isolation

Durability

2. What is UNION in SQL Server? What is the difference between UNION and UNION ALL?

3. What is the difference between the WHERE and HAVING clauses? What is Group by clause?

Select * from Employee Where Id > 10;

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

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

4. What is Index? Define Clustered and Non-Clustered index.

5. How to delete Duplicate Records in SQL Server?

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

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)

6. Explain Row_Number, RANK and DENSE_Rank in SQL Server.

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) asRN 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 fromEmployee


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 fromEmployee


-- 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 fromEmployee)
select top 1 * from CTE1 where RN=2  -- 2nd highest from

7. How to select top nth Records?

Let's take the example to select top 5th record from down

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

8. Write self join query with following table structure.

Table select query is

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

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

9. What is SQL Server Profiler?

SQL Profiler is an graphical user interface (GUI) tool that is used for tracing, recreating and troubleshooting the problems in SQL Server. It's used to identify slow executing queries and production problems by capturing the events. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.
SQL Profiler is used for following type of activities.

• Finding and diagnosing slow-running queries.
• Correlating performance counters to diagnose problems.
• Stepping through problem queries to find the cause of the problem.
• Monitoring the performance of SQL Server to tune workloads

10. Write a query to select all the Even and Odd number records from a table.

To select all even number records:

SELECT * FROM TABLE WHERE ID % 2 = 0 

Select * from table where id % 2 != 0

11. Why stored procedures are fast as compare to run query by c#?

Stored procedures are fast because these are in compile form means no need to compile when we run it. Whenever we run some query by c# (ORM or ado.net) then first query get compiled then create execution plan but in case of store procedures execution plan does not get created again as it's already created when we write store procedures.

12. What is Self Join and why is it required?

Self Join provide the capability of joining one table with itself. for example, you have one table 'employee' with three columns id, name, manager_id. Now you want to print the name of employee and his manager in same row.

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

13. What is the difference between Truncate, Delete and Drop commands?

All these are the SQL commands used on the basis of different needs as below.

Truncate

Delete

Drop

14. How can you improve Stored Procedure Performance?

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

Some General Interview Questions for SQL Server:

1. How much will you rate your self in SQL Server?

When you attend an interview, Interviewer may ask you to rate your self in specific Technology like SQL Server, So It's depend on your knowledge and work experience in SQL Server.

2. What challenges did you face while working on SQL Server?

This question may be specific to your technology and completely depends on your past work experience. So you need to just explain the challenges you faced related to SQL Server in your Project.

3. What was your role in last Project related to SQL Server?

It's based on your role and responsibilities assigned to you and what functionality you implemented using SQL Server in your project. This question is generally asked in every interview.

4. How much experience do you have in SQL Server?

Here you can tell about your overall work experience on SQL Server.

5. Have you done any SQL Server Certification or Training?

It's depend on candidate like you have done any SQL Server training or certification. Certifications or trainings are not essential but good to have.