TOP 200 SQL SERVER Interview Questions

VIEWS in SQL Server

A View is a method of Encapsulating a Complex query into a simple form, For example, your Client has no idea how to write Complex Queries with multiple joins, Aggregate functions, and Complex SQL Functions, so you can create a View and now your client has to run a SIMPLE select statement against a view and the problem is solved.

A view can be used to Focus on Specific data or a view can be used to Simplify Complex data which can be presented in a simple format and the view can be used to Customize data

With CHECK OPTION in a View.

WITH CHECK OPTION allows the view to check that all modifications to data through the view should fall within the scope of the view.

In the below example, we have created a view with the condition in where clause “WHERE Location_ID=1” so now if we try to update the view and try to update the location_Id =2 where location_Id = 1 then without the WITH CHECK OPTION all the Location_ID which were previously Equals to 1 now becomes 2, remember? our view is made from the where condition “Where Location_Id=1” so in this case when all the Location_Ids are updated to 2 the existence of the view vanishes. to stop this kind of behavior we use WITH CHECK OPTION.

With “WITH CHECK OPTION” in place, SQL Server will throw an error as shown in the screenshot below and will not allow us to update the Location_ID from 1 to 2.

“The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint“

Difference between a Materialized view and a NORMAL view

A Materialized or Indexed View is similar to a normal view, However, the Materialized view is stored in the database similar to an index on a table.

NORMAL VIEW	MATERIALIZED VIEW
A Normal view is just a virtual table and is not stored in the database as an actual table A Normal view is just a virtual table and is not stored in the database as an actual table	A Normal view is just a virtual table and is not stored in the database as an actual table
A Normal view is just a virtual table and is not stored in the database as an actual table A Normal view is just a virtual table and is not stored in the database as an actual table	A Normal view is just a virtual table and is not stored in the database as an actual table

How to use a variable in a view?

Parameterized views can be achieved by Inline User Defined functions i.e. that return a table.

Difference between a VIEW and a simple query , which one is faster?

Can we create a view on the Temp Table?

No, We cannot create a view on temp tables. let’s test this scenario with an example.

In the below example, we will create a temp table “##EMPLOYEES“

IF OBJECT_ID(N'tempdb.dbo.##EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE tempdb.dbo.##EMPLOYEES;
CREATE TABLE tempdb.dbo.##EMPLOYEES
(ID INT PRIMARY KEY,F_NAME VARCHAR(20),L_NAME VARCHAR(20),
SALARY INT,dept varchar(20));
INSERT INTO tempdb.dbo.##EMPLOYEES 
VALUES(1,'SHANE','RAY',5000,'IT'),
(2,'JACK','STRONG',7000,'IT'),
(3,'OLGER','PHILLIPS',9000,'HR');
select * from tempdb.dbo.##EMPLOYEES;

Now let’s try to create a view on this temp table. As expected the view creation on a temp table will fail with the following error.

“Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.”

create view Temp_Table_Test ASselect * from tempdb.dbo.##EMPLOYEES;

CTE in SQL Server

CTE and types of CTEs

Print 1 to 100 in SQL without using a loop

with CTE as

(   select 1 Number

union all

select Number +1

from CTE where Number<100  )

Can we call a CTE within a CTE, what is the recursion level of a CTE

STORE PROCEDURES in SQL Server

Error Handling in Store Procedure

can we call a store proc from a user-defined function?

What is a Recursive Stored Procedure? What is the Maximum Level we can do recursion?

Exception Handling in Store Procedure?

Try and catch block in-store procedure

Can we use Store proc inside a Function and can I use a Function inside an SP?

DATABASE KEYS in SQL Server

PRIMARY KEY, FOREIGN KEY, CANDIDATE KEY, COMPOSITE KEY, SUPER KEY, ALTERNATE KEY, UNIQUE KEY

What is normalization and why do we need normalization with a real-life example

Can we Create a PRIMARY Key as a NON-CLUSTERED Index?

What are PRIMARY KEY and FOREIGN KEY Constraints?

Can we delete the Primary key if there is a Primary key / foreign key Relation? Can I update something to delete the Primary key / foreign key Relation To delete the foreign key rows along with the primary key rows, you need to use ON DELETE CASCADE

Types of Keys in a Database and their Uses.

What is the difference between the primary key and the unique key how many nulls can be there in the unique key?

What are the Primary and Unique Key

Can I create a Primary Key on two Columns?

FUNCTIONS in SQL Server

There are two types of functions in SQL Servers, BUILT-In Functions, and USER Defined Functions.

A function Should always return a value it can be a SCALAR Value or TABLE Value.

The function can only take the INPUT Parameter and cannot take the OUTPUT parameter as you can do in a Store Procedure.

Functions can be used in the SELECT Statements and can be used in a join condition if it returns a table.

FUNCTIONS can use table variables inside them and can use schema binding

CREATE OR ALTER FUNCTION

DBO.SUPERADD_SCALAR (@A INT,@B INT)

RETURNS INT

WITH SCHEMABINDING

AS BEGIN

RETURN @A+@B;ENDGOSELECT DBO.SUPERADD_SCALAR(4,4)

Can we call a Store Proc in a UDF?

No, we cannot call/use a Store Procedure inside a UDF

Can we Use DML Operations inside a UDF?

You can perform DML Operations on table variables inside a function, In the below example we are creating a Multiline Table Valued Function, we will insert 3 Rows into the Table Variable “@SUM” and will later delete these 3 rows using the DELETE statement.

CREATE
ORALTER FUNCTION dbo.addition1 (@x INT,
@y                               INT)
returns @SUM TABLE
                   (
                                      sum1 INT
                   )
                   BEGIN
/* NOW WE TRY TO INSERT MULTIPLE VALUES IN TABLE @SUM*/INSERT INTO @SUM
            (
                        sum1
            )
SELECT @X+@YINSERT
INTO   @SUM (sum1)SELECT @X+@Y

INSERT
INTO   @SUM (sum1)

SELECT @X+@Y
/* WE CAN DELETE FROM A TABLE VARIABLE*/

DELETE
FROM   @SUM;

RETURN

END;

go--USE THE TABLE VALUED FUNCTION AS A TABLE

select * FROM DBO.addition1 (2,2);

Can we use a Temp Table Inside a UDF?

No, we cannot create a temp table inside a UDF

Can we use Table variables Inside a UDF?

Yes, Table Variables are allowed in UDF

How many Input parameters can we have in a user-defined functions

A function can have a maximum of 1024 input parameters.

Can we use transactions in functions?

Transactions are not allowed in UDF

How many types of User Defined Functions in SQL SERVER?

There are 3 types of User Defined Functions SCALAR Functions, Multi-Statement Table Valued Functions, and Inline Table Valued Functions.

Is it possible to add an output parameter in the function

Output Parameters are not allowed in the User Defined Functions.

DETERMINISTIC vs NON-DETERMINISTIC Functions in SQL Server

A DETERMINISTIC Function will always return the same output on a given input value. For example, 2+2 will always give an output of 4, likewise adding any two numbers will always give the same result set each time.

A NON-DETERMINISTIC Function will not return the same output on a given input value. For example, select GETDATE () when executed will always output a different value

Update a table that exists in 150 database

TRY CATCH Block in SQL Server

IF EXISTS Function in SQL SERVER

What are STATISTICS and why does Query Optimizer need STATISTICS

What are LINKED Servers? What are the Security options available in LINKED SERVER?

What is the difference between UNION, UNION ALL, INTERSECT, EXCEPT, and MINUS Operators?

Triggers and types of Triggers in SQL Server.

ANSWER CLICK HERE

We have a large table and lots of DML Operations are going on, We have an IDENTITY Column and it's getting exhausted, We need to change the data type from INT to BIG INT.

Triggers and types of Triggers in SQL Server.

What is the order of Query Execution in the SQL server?

what is the difference between UNION and UNION ALL?

UNION or UNION ALL Which one is good for performance

Apart from DISTINCT Operator other ways to get distinct records

How to get a count of records without using COUNT Functions

How to get Non-matching records from the Two/Three tables.

IF OBJECT_ID('tempdb..#one') IS NOT NULL 
DROP TABLE #one
GO
create table #one (id int,FNAME nvarchar(25))
insert into #one (id , FNAME) values(1,'one') 
insert into #one (id , FNAME) values(2,'two') 
insert into #one (id , FNAME) values(3,'three') 

IF OBJECT_ID('tempdb..#two') IS NOT NULL 
DROP TABLE #two
GO
create table #two (id int,FNAME nvarchar(25))
insert into #two (id,FNAME) values(1,'one') 
insert into #two (id,FNAME) values(2,'two') 
insert into #two (id,FNAME) values(4,'four')

select * from #one;
select * from #two;

(select * from #one 
EXCEPT 
select * from #two)
UNION
(select * from #two 
EXCEPT 
select * from #one)

Suppose an employee leaves an organization we need to delete the traces of that employee from all 50 tables

Difference between OLTP and OLAP System

Temp Tables and CTEs, Which is better to use and where?

Index and types of indexes?

Rank, Dense Rank, and Row Number?

We need to delete duplicates from the table and keep the latest one which function to use?

GROUP BY Clause in SQL

HAVING Clause in SQL

DATA WAREHOUSE Concept:- STAR SCHEMA and SNOWFLAKES SCHEMA.

Difference between OLTP and OLAP

Count of rows without using the count function (Using row number and doing MAX row number)

The customer table has 10 records, customer ID 2 I, and then I want to again insert customer ID 2 with the insert command, How will you restore that customer ID 2.

The procedure has been working fine since yesterday and there is no change in the SP Code, now it's giving performance issues, how will you fix that?

What is the difference between a save Transaction and a Commit Transaction?

EXISTS and NOT-EXISTS Which one will you prefer?

What is pivot and unpivot and what are the alternatives of it?

What are triggers and when should we use the triggers, what are the different types of triggers

what are the joins, which one should we use, and which one we should avoid at any cost?

What is the difference between full join and cross join which one will be the faster

if you have a very large database, what steps do you take to increase the performance of the database?

ACID Properties in SQL server

ISOLATION levels in SQL servers where can be used Read committed and read uncommitted

Difference between functions and procedure

Can we use a try-catch block from UDF?

Remove duplicate records from a table, ways to remove duplicate records

Types of Triggers in SQL Server What are the magic tables in Triggers

MERGE statement in SQL server

System-defined functions for handling nulls

What is collision in the database system?

The sequence of select statements in SQL query, when we execute a query what is the order of query execution

What is the difference between Union and Union all?

Remove Duplicate records

What are WINDOWED Function

I have a table Table 1 which has 3 columns “col a, col b, col c” want to convert them into a column?

Difference between clustered and non-clustered indexes

for XML path and STUFF Functions

What are Update Stats and will they do

What is the Difference Between Table Scan and table seek?

What is the Difference Between Index Scan and Index Seek, which one is faster?

Explain the Working of index Seek?

What is the page size of any index?

What is the fill factor?

Top 5 best Query Tuning Approaches?

Which one is faster Distinct or a normal Query?

Is Using a Having clause good for Performance tuning or not?

Tool to monitor the performance of a Query

Which one is faster Cluster Index or Non Clustered index?

Is there anything else you have learned from a performance perspective

How many clustered indexes are in a table?

How many non-clustered Index

Can I create an index while running the query on the go, Apply a specific index during a join

Global and local Temporary tables?

Scope of Global and Local Temporary Table.

What is the table variable what is the scope of that table variable?

What is the difference between Normal Queries and Sub Queries?

One way to handle the Exception, and one two or three-way to throw the error to the calling function

What is the syntax of Exception handling?

What is the name of the function that will raise the exception for the user?

What are the kind of triggers and what is the purpose of the triggers

Magic tables in Triggers?

What is “SET NO COUNT ON” do?

What is the use of “IS NULL” in SQL queries?

Suppose you have a column in a table that has a different value and you want to show them in a single column as comma-separated which function will we use for example I have 10 rows i want it in a single column like 1,2,3,4.

If you have duplicate records what is the preferred way to remove these duplicate records?

What is the use of the NULLIF Function in SQL server?

XML Data Types in SQL Query?

Importing data from a text file using SQL Query Apart from Import Export Wizard

Can we use a case statement in the Partition BY Clause While using Windowed functions like ROWNUM, RANK, and DENSE RANK?

What is the difference between Cursor and Loop?

Which is Faster Cursor or LOOP?

Why is LOOP Faster than a CURSOR?

Can we interchange CURSOR and LOOP?

Suppose I have SP abc1 and there is a local temp table in that SP, there is another SP abc2 can I use the data for the temp table from SP “abc1” in abc2?

IF (Object_id('abc1') IS NOT NULL)
DROP PROCEDURE abc1

go

CREATE PROC Abc1
AS
  SELECT [departmentid],
         [name],
         [groupname],
         [modifieddate]
  INTO   [AdventureWorks2019].[HumanResources].[#department]
  FROM   [AdventureWorks2019].[HumanResources].[department]
  SELECT *
  FROM   [AdventureWorks2019].[HumanResources].[#department];

go

IF (Object_id('abc2') IS NOT NULL)
DROP PROCEDURE abc2

go

CREATE PROC Abc2
AS
  SELECT [departmentid] ,
         [name] ,
         [groupname] ,
         [modifieddate]
  INTO   [AdventureWorks2019].[HumanResources].[#department2]
  FROM   [AdventureWorks2019].[HumanResources].[department]
  SELECT *
  FROM   [AdventureWorks2019].[HumanResources].[#department2];

go

EXEC Abc1;

EXEC Abc2;

Suppose I have SP abc1 and there is a GLOBAL temp table in that SP, there is another SP abc2 can I use the data for the temp table from SP “abc1” in abc2?

IF (Object_id('abc1') IS NOT NULL)
DROP PROCEDURE abc1

go

create proc abc1 asselect [DepartmentID] ,
  [Name] ,
  [GroupName] ,
  [ModifiedDate] INTO [AdventureWorks2019].[HumanResources].[##Department] FROM [AdventureWorks2019].[HumanResources].[Department]

SELECT *
  FROM   [AdventureWorks2019].[HumanResources].[##department];

GO

IF (object_id('abc2') IS NOT NULL)

DROP PROCEDURE abc2

go

create proc abc2

as

select [DepartmentID] ,

  [Name] ,
  [GroupName] ,
  [ModifiedDate] INTO [AdventureWorks2019].[HumanResources].[##Department2] FROM [AdventureWorks2019].[HumanResources].[Department]

SELECT *
  FROM   [AdventureWorks2019].[HumanResources].[##department2];

go

EXEC Abc1;

EXEC Abc2;

What is the difference between Table Scan and Index seek

What is a deadlock?

import Excel file using SQL server

string aggregate function

Stuff Function with for XML path

what is CTE

First day of the week, last day of the week

First day of the month, last day of the month

First day of the year, last day of year

All the Saturdays in 2022 from Jan to Dec, all the dates we need

What are the things to take care of while doing performance optimization?

What are the type of Table locks/ row-level locking in SQL servers?

For what purpose you have used triggers

What is a filtered index?

Difference between “and” and “where” joins

IF object_id('tempdb..##table1') IS NOT NULL

DROP TABLE ##table1

go

create TABLE
##mukesh(id int, name1 varchar(50), email varchar(50));

INSERT INTO ##mukesh
VALUES      (1,
             'Mukesh',
             'Mukesh.bpo'),
            (2,
             'Rakesh',
             'Rakesh.bpo'),
            (3,
             'Suresh',
             'Suresh.bpo');

IF object_id('tempdb..##table1') IS NOT NULL

DROP TABLE ##table1

go

create TABLE
##mukesh1(id int, name1 varchar(50), email varchar(50));

INSERT INTO ##mukesh1
VALUES      (1,
             'Mukesh',
             'Mukesh.bpo'),
            (4,
             'Rajat',
             'Rajat.bpo'),
            (5,
             'Mitesh',
             'Mitesh.bpo');

SELECT *
FROM   ##mukesh;

SELECT *
FROM   ##mukesh1;

SELECT *
FROM   ##mukesh a
       LEFT JOIN ##mukesh1 b
              ON a.id = b.id
WHERE  a.id IN ( 1, 2 );

SELECT *
FROM   ##mukesh a
       LEFT JOIN ##mukesh1 b
              ON a.id = b.id
                 AND a.id IN ( 1, 2 );

IF object_id('tempdb..##table1') IS NOT NULL

DROP TABLE ##table1

go

create TABLE
##table1 (company nvarchar(100) NOT NULL, field1 int NOT NULL);

INSERT INTO ##table1
            (company,
             field1)
VALUES      ('FooSoft',
             100),
            ('BarSoft',
             200);

IF object_id('tempdb..##table2') IS NOT NULL

DROP TABLE ##table2

go

create TABLE
##table2 (id int NOT NULL, name nvarchar(100) NOT NULL);

INSERT INTO ##table2
            (id,
             name)
VALUES      (2727,
             'FooSoft'),
            (2728,
             'BarSoft');

SELECT *
FROM   ##table1;

SELECT *
FROM   ##table2;

SELECT *
FROM   ##table1 cd
       LEFT JOIN ##table2
              ON cd.company = ##table2.name
WHERE  ##table2.id IN ( 2728 );

SELECT *
FROM   ##table1 cd
       LEFT JOIN ##table2
              ON cd.company = ##table2.name
                 AND ##table2.id IN ( 2728 );

TOP 200 SQL SERVER Interview Questions

VIEWS in SQL Server

With CHECK OPTION in a View.

Difference between a Materialized view and a NORMAL view

How to use a variable in a view?

Difference between Store Procedures & views Which one is faster

Difference between a VIEW and a simple query , which one is faster?

Can we create a view on the Temp Table?

CTE in SQL Server

CTE and types of CTEs

Print 1 to 100 in SQL without using a loop

Can we call a CTE within a CTE, what is the recursion level of a CTE

STORE PROCEDURES in SQL Server

Error Handling in Store Procedure

can we call a store proc from a user-defined function?

What is a Recursive Stored Procedure? What is the Maximum Level we can do recursion?

Exception Handling in Store Procedure?

Try and catch block in-store procedure

Can we use Store proc inside a Function and can I use a Function inside an SP?

DATABASE KEYS in SQL Server

What is normalization and why do we need normalization with a real-life example

Can we Create a PRIMARY Key as a NON-CLUSTERED Index?

What are PRIMARY KEY and FOREIGN KEY Constraints?

Can we delete the Primary key if there is a Primary key / foreign key Relation? Can I update something to delete the Primary key / foreign key Relation To delete the foreign key rows along with the primary key rows, you need to use ON DELETE CASCADE

Types of Keys in a Database and their Uses.

What is the difference between the primary key and the unique key how many nulls can be there in the unique key?

What are the Primary and Unique Key

Can I create a Primary Key on two Columns?

FUNCTIONS in SQL Server

Can we call a Store Proc in a UDF?

Can we Use DML Operations inside a UDF?

Can we use a Temp Table Inside a UDF?

Can we use Table variables Inside a UDF?

How many Input parameters can we have in a user-defined functions

Can we use transactions in functions?

How many types of User Defined Functions in SQL SERVER?

Is it possible to add an output parameter in the function

DETERMINISTIC vs NON-DETERMINISTIC Functions in SQL Server

Update a table that exists in 150 database

TRY CATCH Block in SQL Server

NOLOCK and READ PAST Hints IN SQL SERVER

IF EXISTS Function in SQL SERVER

What are STATISTICS and why does Query Optimizer need STATISTICS

What are LINKED Servers? What are the Security options available in LINKED SERVER?

What is the difference between UNION, UNION ALL, INTERSECT, EXCEPT, and MINUS Operators?

Triggers and types of Triggers in SQL Server.

We have a large table and lots of DML Operations are going on, We have an IDENTITY Column and it's getting exhausted, We need to change the data type from INT to BIG INT.

Triggers and types of Triggers in SQL Server.

What is the order of Query Execution in the SQL server?

what is the difference between UNION and UNION ALL?

UNION or UNION ALL Which one is good for performance

Apart from DISTINCT Operator other ways to get distinct records

How to get a count of records without using COUNT Functions

How to get Non-matching records from the Two/Three tables.

Suppose an employee leaves an organization we need to delete the traces of that employee from all 50 tables

Difference between OLTP and OLAP System

Temp Tables and CTEs, Which is better to use and where?

Index and types of indexes?

Rank, Dense Rank, and Row Number?

We need to delete duplicates from the table and keep the latest one which function to use?

GROUP BY Clause in SQL

HAVING Clause in SQL

DATA WAREHOUSE Concept:- STAR SCHEMA and SNOWFLAKES SCHEMA.

Difference between OLTP and OLAP

Count of rows without using the count function (Using row number and doing MAX row number)

The customer table has 10 records, customer ID 2 I, and then I want to again insert customer ID 2 with the insert command, How will you restore that customer ID 2.

The procedure has been working fine since yesterday and there is no change in the SP Code, now it's giving performance issues, how will you fix that?

What is the difference between a save Transaction and a Commit Transaction?

EXISTS and NOT-EXISTS Which one will you prefer?

What is pivot and unpivot and what are the alternatives of it?

What are triggers and when should we use the triggers, what are the different types of triggers

what are the joins, which one should we use, and which one we should avoid at any cost?

What is the difference between full join and cross join which one will be the faster

if you have a very large database, what steps do you take to increase the performance of the database?

ACID Properties in SQL server

ISOLATION levels in SQL servers where can be used Read committed and read uncommitted

Difference between functions and procedure

Can we use a try-catch block from UDF?

Remove duplicate records from a table, ways to remove duplicate records

Types of Triggers in SQL Server What are the magic tables in Triggers