All Interviews

Top 23 SQL Server Interview Questions and Answers

29/Sep/2020 | 10 minutes to read

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.

Best Answers to SQL Server Interview Questions

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

Atomicity ensures that each transaction be "all or nothing" means if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Consistency

The consistency property means that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

Isolation

The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - Serializability), the effects of an incomplete transaction might not even be visible to another transaction.

Durability

Durability ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

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

UNION

it merges the contents of two tables which are structurally compatible into a single combined table.

Difference

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing 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?

Where clause:

Where clause is used to filter the records from a result set. Filtering occurs before any grouping made. it works with select clause. it does not work with aggregate functions or group by.
Where clause example: 

Select * from Employee Where Id > 10;

Having Clause

Having clause is used to filter the records from groups. it works with group by clause and works on aggregate functions.
Having clause example: 
Select Name, Salary from Employee   
Group by Name, Salary   
Having SUM(Salary)  > 10000;

Group By clause

Group by clause is used to display the data in the form of identical groups. it is used with select clause to group the result set by one or more columns.
Group by clause example: 
Select col1, col2, from table 
group by col1, col2

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

Index

Index is an on disk structure associated with table or view that is built from one or more columns in table or view. An Index speeds the retrieval of data from table or view. Index contains key built from one or more column(s). These keys are stored in the form of B-tree data structure that enables the SQL Server to find the rows associated with these keys in a quick and efficient way.
For more Index In SQL Server there are 2 types of Indexes.
1. Clustered Index
2. Non Clustered Index

Clustered Index

When you create a table with Primary key, SQL Server automatically create the clustered index based on columns included in primary key. Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. Data rows in a table are stored in sorted only when a table has a clustered index. If you add a primary key constraint to an existing table that already has a clustered index, SQL Server will enforce the primary key using a non-clustered index.
For more Clustered Index

Non Clustered Index

Non clustered index also speeds the retrieval of data from the tables but it sorts and stores the data separately from the data rows in the table. It is a copy of selected columns of data from a table with the links to the associated table. So Non-clustered indexes have a structure separate from the data rows. A Non-clustered index contains the Non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.

The pointer from an index row in a Non-clustered index 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 heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the 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 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;
id - is column by which u find duplicates OR you can also use below code if table has identity column to identify duplicate data. 

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

It's used to return unique sequential number for each row starting from 1. if partition clause is used with Row_Number then it reset sequential number for each partition. It does not skip or repeat the numbers in result.

RANK()

It's used to return a unique number for each distinct row starting from 1, within the partition if partition clause is used. It starts at 1 in each partition. It sets same rank for duplicate data and leaves the gaps in the rank sequence after duplicate values.

DENSE_RANK()

It has similar behavior like 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 {25, 25, 50, 75, 75, 100}. 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) 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
ROW_NUMBER, RANK, DENSE_RANK can be used with partition or without it but not without over clause.

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]
Self Join Example: we will fetch emp name and manager name from 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?

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
Fore more follow 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: 

SELECT * FROM TABLE WHERE ID % 2 = 0
To select all odd number records: 
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

- It's Data Definition Language (DDL) command in SQL Server. That's why Truncate operation can not be rolled back. It's used to delete the content of a table and free the space.

Delete

- It's Data Manipulation Language (DML) command. That's why Delete operation can be rolled back. Delete command is used to delete the records from a table.

Drop

- It's Data Definition Language (DDL) command. That's why Drop operation can not be rolled back. Drop command is used to remove the object from Database.

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.

15. How to concatenate text from multiple rows into a single text string in SQL server? Consider below rows.


Test-1
Test-2
Test-3

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

16. How to convert rows to columns in SQL Server?

17. How to UPDATE from a SELECT in SQL Server?

18. How to insert the result of a stored procedure into temporary table?

19. How to split a comma-separated value to columns in SQL Server?

20. How to check Query Execution Plan in SQL Server?

21. What is the difference between Count(*) and Count(1)?

22. How to escape a single quote in SQL Server?

23. What is the difference between NOT IN vs NOT EXISTS?

24. How to return only the Date from a DateTime datatype in SQL Server?

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.

Conclusion

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 info@qfles.com. Our IT Expert team will find the best answer and will update on portal. In case if we find any new SQL Server questions, we will update the same here.

Check it