First 14 SqlServer Interview Questions in the year - 2023 - USMTECHWORLD.

1.How to backup a single table with its data from database in sql server?

Answer:
1.RIGHT CLICK DATABASE 2.SELECT TASKS=>GENERATE SCRIPTS 3.CHOOSE TABLE OR OBJECTS 4.SET SCRIPTING OPTIONS AND CLICK ADVANCED 5.SELECT TYPES OF DATA TO SCRIPT AND CHOOSE SCHEMA AND DATA 6.CLICK NEXT AND FINISH

2.How to find duplicate rows in a Table?

Answer:
Requirement: I need to get duplicate records ending with kumar in persons table

            select count(*) from persons where lastname='kumar' group by lastname having count(lastname)>1

3.Which is the better way to store images in SQL Server Database?

Answer:
if your pictures or document are typically of less size, storing them in a database VARBINARY column is more efficient. if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee photo in the employee table - keep them in a separate table. So that the Employee table can stay lean and very efficient.

Two ways of storing images in Sql Server Database:-

- Store the location of the image in the database

- Converting the image into binary data and insert that binary data into database and similarly on retrieving the records again convert that binary data to image.

4.What are the different types of Joins in SQL?

Answer:

Understanding different types of SQL Joins in simple way

A Join will combine rows from one or more tables and return the resultset.

SELECT u.UserName,u.CountryName from users u inner join country c on u.countryid=c.countryid;

Types of SQL Joins

INNER JOIN:

It returns matching rows from both tables.

LEFT OUTER JOIN:

It returns all records from left side table and matching records from right side table.

RIGHT OUTER JOIN:

It returns all records from right side table and matching records from left side table.

FULL JOIN:

It returns all records from both tables when there is a match in either left side table or right side table.

5.What is Left Outer join in SQL?

Understanding Left Join in SQL Server with simple examples.

It returns all records from left side table and matching records from right side table.

Left Join Diagram

LEFT JOIN SYNTAX:

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN EXAMPLE 1:

Requirement: I need to display all records from Users and matching records from country

SELECT u.UserName,u.CountryName from users u left join country c on u.countryid=c.countryid;

LEFT JOIN EXAMPLE 2:

Requirement: I need to display all records from Customers and matching records from Orders

SELECT c.CustomerName,ord.OrderDate from Customers c LEFT JOIN Orders ord ON c.Customerid=ord.Customerid

LEFT JOIN EXAMPLE 3:

Requirement: I need to display all records from Students and matching records from Course

SELECT s.StudentName,c.CourseName from Students s INNER JOIN Course c ON s.Studentid = c.Studentid

6.What is Right Outer join in SQL?

Understanding Right Join in SQL Server with simple examples

It returns all records from right side table and matching records from left side table.

Right Join Diagram

RIGHT JOIN SYNTAX:

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN EXAMPLE 1:

Requirement: I need to display all records from Country and matching records from Users

SELECT u.UserName,u.CountryName from users u RIGHT JOIN country c on u.countryid=c.countryid;

RIGHT JOIN EXAMPLE 2:

Requirement: I need to display all records from Orders and matching records from Customers

SELECT c.CustomerName,ord.OrderDate from Customers c RIGHT JOIN Orders ord ON c.Customerid=ord.Customerid

RIGHT JOIN EXAMPLE 3:

Requirement: I need to display all records from Course and matching records from Students

SELECT s.StudentName,c.CourseName from Students s RIGHT JOIN Course c ON s.Studentid = c.Studentid

7.What is Inner join in SQL?

Understanding Inner Joins in SQL Server with more practical examples

It returns resultset only, if there is a match between both tables

Inner Join Diagram

INNER JOIN SYNTAX:

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

INNER JOIN EXAMPLE 1:

SELECT u.UserName,u.CountryName from users u inner join country c on u.countryid=c.countryid;

INNER JOIN EXAMPLE 2:

SELECT c.CustomerName,ord.OrderDate from Customers c INNER JOIN Orders ord ON c.Customerid=ord.Customerid

INNER JOIN EXAMPLE 3:

SELECT s.StudentName,c.CourseName from Students s INNER JOIN Course c ON s.Studentid = c.Studentid

8.What is Full Outer join in SQL?

Understanding Full Join in SQL Server with simple examples.

It returns all records from both tables, when there is a match in either left side table or right side table.

Full Join Diagram

FULL JOIN SYNTAX:

SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;

FULL JOIN EXAMPLE 1:

Requirement: I need to display all records from Country and also from Users

SELECT u.UserName,u.CountryName from users u FULL JOIN country c on u.countryid=c.countryid;

FULL JOIN EXAMPLE 2:

Requirement: I need to display all records from Orders and Customers

SELECT c.CustomerName,ord.OrderDate from Customers c FULL JOIN Orders ord ON c.Customerid=ord.Customerid

FULL JOIN EXAMPLE 3:

Requirement: I need to display all records from Course and Students

SELECT s.StudentName,c.CourseName from Students s FULL JOIN Course c ON s.Studentid = c.Studentid

9.What is Clusered Index in SQL?

A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column. Otherwise we can say in clustered index the rows are stored physically on the disk in the same order as the index

10.What is Non Clusered Index in SQL?

A non clustered index is stored seperately from the data table.For example,let us take our favourite book example,Here index of the book is maintained seperately and contents of the book is seperate. For example,if user needs to retrieve the email,location of the user named "Shiva".The database will first search username in the non clustered index table and fetch the actual record reference and use that to retrieve email and location of the user "shiva". Here database has to make two searches, first the non-clustered index and then in actual table. The non clustered index is slower for search operations.

11.What is Dynamic SQL?

It means you can pass sql statements as string and construct sql statements dynamically at runtime. For eg:-If input parameter for stored procedure is null and we need to construct sql statements based on the input parameter by checking whether it contains any value or not,so in this case we need to construct sql statements dynamically at runtime. Let us understand this by simple example

    declare @sql nvarchar(max)
    declare @username nvarchar(100)
    set @sql = 'select * from users where username=' + @username
    exec(@sql)

12.What is difference between Varchar and NVarchar?

The main difference in using nvarchar is that you can store unicode character.In future, if you want to translate your applications to other languages such as chinese,japanese,tamil,hindi etc then you can translate very easily using nvarchar. Unicode is an International character encoding standard that includes different languages, scripts and symbols. Each letter, digit or symbol has its own unique Unicode value. Unicode is an extension of ASCII that allows many more characters to be represented. Example:The unicode value of ! is U+0021.

13.What is Merge statement in sql?

The merge statement combines Insert,Update,Delete operations into one table. Syntax:
Merge targettable using Sourcetable on Merge condition when matched then updatestatement when not matched by target then insertstatement when not matched by source then deletestatement
Example of Merge Statement:
Create table script:

CREATE TABLE SOURCECUSTOMER(
	CustomerID INT,
	CustomerName varchar(50),
      Phone int)

CREATE TABLE TARGETCUSTOMER(
	CustomerID INT,
	CustomerName varchar(50),
      Phone int)

MERGE TargetCustomer as target
USING SourceCustomer as Source
on source.Customerid=target.customerid
when not matched by target then
insert (Customerid,customername,phone)
values(source.customerid,source.customername,source.phone)

MERGE TargetCustomer as target
USING SourceCustomer as source
on source.customerid=target.customerid
when not matched by target then
INSERT (customerid,customername,phone)
values(source.customerid,source.customername,source.phone)
--FOR UPDATES
WHEN MATCHED THEN UPDATE SET
	target.customername=source.customername,
      target.phone=source.phone;

MERGE TargetCustomer as target
USING SourceCustomer as source
on source.customerid=target.customerid
when not matched by target then
INSERT (customerid,customername,phone)
values(source.customerid,source.customername,source.phone)
--FOR UPDATES
WHEN MATCHED THEN UPDATE SET
	target.customername=source.customername,
      target.phone=source.phone;
--FOR DELETE
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

14.How will you check action in Merge Statement?

MERGE TargetCustomer as target
USING SourceCustomer as source
on source.customerid=target.customerid
when not matched by target then
INSERT (customerid,customername,phone)
values(source.customerid,source.customername,source.phone)
--FOR UPDATES
WHEN MATCHED THEN UPDATE SET
target.customername=source.customername,
target.phone=source.phone;
--FOR DELETE
WHEN NOT MATCHED BY SOURCE THEN
DELETE
output $action,DELETED.CustomerID as TargetCustomerid,
DELETED.CustomerName as TargetCustomername,
DELETEED.Phone as TargetPhone,
INSERTED.Customerid as SourceCustomerid,
INSERTED.CustomerName as SourceCustomername;