25/Oct/2021 | 12 minutes to read
Here is a List of essential SQL Server Interview Questions and Answers for Freshers and mid level of Experienced Professionals. All answers for these SQL Server questions are explained in a simple and easiest way. These basic, advanced and latest SQL Server questions will help you to clear your next Job interview.
These interview questions are targeted for SQL Server for developers and DBA. You must know the answers of these frequently asked SQL Server questions to clear the interview. This list includes questions based on joins, complex queries, performance tuning, cluster etc.
1. What are the ACID properties? Explain each of them.
ACID is a set of four properties. Let's understand each of them.
2. What is UNION in SQL Server? How will you differentiate it from UNION ALL?
UNION merges the contents of two tables which are structurally compatible into a single combined table.
The difference is that UNION removes duplicate records whereas UNION ALL includes duplicate records.
UNION ALL has better performance then UNION, since UNION requires the server to do the extra work of eliminating any duplicates. So, in the cases where it is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for better performance.
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 an Index? Define Clustered and Non-Clustered index.
5. How to delete Duplicate Records in SQL Server?
id - is the column by which you find duplicates.
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.
ROW_NUMBER, RANK, DENSE_RANK can be used with partition or without it but not without an over clause.
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
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 a self join query with the following table structure.
Table select query is
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
9. What is SQL Server Profiler?
10. Write a query to select all the Even and Odd number records from a table.
To select all even number records:
To select all odd number records:
SELECT * FROM TABLE WHERE ID % 2 = 0
Select * from table where id % 2 != 0
11. Why are stored procedures fast compared to running queries by c#?
Stored procedures are fast because these are in compile form, meaning no need to compile when we run it. Whenever we run some query by C# (ORM or ado.net) then first the query gets compiled and execution plan is created but in case of stored procedures execution plan already exists as it was already created at the time of writing stored procedures.
12. What is Self Join and why is it required?
Self Join provides 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 the employee and his manager in the 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.
14. How can you improve Stored Procedure Performance?
You should focus on certain points for your Stored Procedure performance as below.
15. How to concatenate text from multiple rows into a single text string in SQL server? Consider the rows below.
You can achieve about output using below SQL query:
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
Coalescecan 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
16. How to UPDATE from a SELECT in SQL Server?
Sometimes, We need to update table data from other tables data. In this case we prefer to use select with update command as below.
For more visit Update from a Select command in SQL Server.
UPDATE Emp SET Emp.PersonCityName=Address.City, Emp.PersonPostCode=Address.PostCode FROM Employees Emp INNER JOIN AddressList Address ON Emp.PersonId = Address.PersonId
17. How to insert the result of a stored procedure into a temporary table?
18. How to convert rows to columns in SQL Server?
19. How to split a comma-separated value to columns in SQL Server?
20. How to check Query Execution Plan in SQL Server?
For more about execution plan visit Execution Plan in SQL Server.
21. What is the difference between
22. How to escape a single quote in SQL Server?
23. What is the difference between
NOT IN vs
24. How to return only the Date from a DateTime datatype in SQL Server?
25. How to get column values whose value starts with 'a' letter?
26. What is the use of CTE in SQL Server?
26. Explain the Magic Tables in SQL Server.
26. Differentiate Functions and Stored Procedures in SQL Server.
26. What is the OPTION clause in SQL Server.
26. Explain the use of Coalesce function in SQL Server.
For more visit Coalesce function in SQL Server.
26. How will you improve database performance in SQL Server.
1. How much will you rate yourself in SQL Server?
When you attend an interview, Interviewer may ask you to rate yourself in a 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 the 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 depends on the candidate whether you have done any SQL Server training or certification. Certifications or training are not essential but good to have.
We have covered some frequently asked SQL Server Interview Questions and Answers to help you for your Interview. All these Essential SQL Server Interview Questions are targeted for mid level of experienced Professionals and freshers.
While attending any SQL Server Interview if you face any difficulty to answer any question please write to us at email@example.com. Our IT Expert team will find the best answer and will update on the portal. In case we find any new SQL Server questions, we will update the same here.