Top 23+ SQL Server Interview Questions and Answers

Question

1. What are the ACID properties? Explain each of them.

Answer 1

ACID is a set of four properties. Let's understand each of them.

Answer 2

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.

Answer 3

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

Answer 4

Index is an on-disk structure associated with a table or view that speeds the getting of rows from a database table or view. Index contains keys built from one or more column(s). These keys are stored in a special form of data structure known as B-tree that enables the SQL Server to find the rows associated with these keys very quickly. For more visit Index in SQL Server
SQL Server has 2 types of Indexes.

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.

For more about indexing questions visit SQL Server Indexes Questions.

Answer 5

We can use CTE, Row_Number() with an 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 the column by which you find duplicates.
OR you can also use below code if the table has an identity column to identify duplicate data.


DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Answer 6

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

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

ROW_NUMBER, RANK, DENSE_RANK can be used with partition or without it but not without an over clause.

Answer 7

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

Answer 8

Table select query is

Answer 9

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 identify slow executing stored procedures which are affecting performance in the production environment. SQL Profiler is used for the 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

For more visit SQL Server Profiler.

Answer 10

To select all even number records:

Answer 11

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.

Answer 12

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.

Answer 13

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

Answer 14

You should focus on certain points for your Stored Procedure performance as below.

Answer 15

Sometimes, We need to update table data from other tables data. In this case we prefer to use select with update command as below.

Answer 16

For more about execution plan visit Execution Plan in SQL Server.

Answer 17

For more visit Coalesce function in SQL Server.

Answer 18

For more visit Improve Performance of SQL Server Database and Improve Performance of SQL Server Database

Answer 19

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.

Answer 20

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.

Answer 21

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.

Answer 22

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

Answer 23

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.

Answer 24

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 the portal. In case we find any new SQL Server questions, we will update the same here.

Programming

AI/ML

Automation (RPA)

Software Design

JS Frameworks

.Net Stack

Java Stack

Django Stack

Database

DevOps

Testing

Cloud Computing

Mobile Development

SAP Modules

Salesforce

Networking

BIG Data

BI and Data Analytics

Web Technologies

Top 23+ SQL Server Interview Questions and Answers

SQL Server Interview Questions and Answers

Some General Interview Questions for SQL Server

Conclusion