Top 100+ popular SQL Interview Questions and Answers.

 

Ø   Different Version of SQL Server

ü  2012

ü  2014

ü  2016

ü  2017

ü  2019

Ø  New Feature In SQL Server 2017

ü  TRANSLATE

ü  CONCATE_WS

ü  TRIM

ü  STRING_AGG

ü  Support In Linux

Ø  New Feature In SQL Server 2019

ü  UTF-8 data encoding system

ü  Resumable Online Index Create

ü  Intelligent Query Processing

ü  Machine Learning on Linux

ü  Big Data clusters

ü  Always On availability groups (  

ü  5 replicas)

Ø  Different types of join

ü  Inner Join

ü  Left Outer Join

ü  Right Outer Join

ü  Full Outer Join

ü  Self-Join

Ø  Types Of Index In SQL Serve

ü  Clustered Index

ü  Non-Clustered Index

ü  Unique Non-Clustered Index

ü  Filtered Non- Clustered Index

ü  XML Index

ü  Full-Text Index

ü  Column Store Index

ü  Hash Index

Ø  Clustered Index

ü  Clustered index physically arrange the data based on the index key.

ü  Faster searching because of physical order of row.

ü  Insert, update operation become slow because arrange a new row in shortest order every time.

ü  There is only one Clustered Index on the table.

Ø  Non-Clustered Index

ü  There is one list which point to the physical rows.

ü  Searching operation slow compare to Clustered Index.

ü  Insert, Update operation faster because not travers to each rows physically.

ü  We can Include non-key column with Include Keyword.

ü  You can create a maximum of 999 non-clustered indexes on a table.

Ø  DML

ü  Data manipulation Language.

ü  Inert, Update, Delete.

Ø  DDL

ü  Data Definition Language.

ü  Create, Alter, Drop.

Ø  What is the trigger?

ü  Trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.

Ø  Types of trigger

ü  AFTER Triggers for DDL and DML Statement.

ü  INSTEAD OF Triggers for DDL and DML Statement.

Ø  Disadvantage of Trigger

ü  Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.

ü  It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.

Ø  What is Magic table

ü  Usually, inserted and deleted tables are called Magic Tables in the context of a trigger.

Ø  What is COALESEC function

ü  the COALESCE function returns the first non-null expression in the list.

Ø  What is Stored Procedures?

ü  Stored Procedure is a group of SQL Statements that accepts some input by parameter, and performs some task and may or may not be returns a value.

Ø  What is Function in SQL Server

ü  Function is a group of SQL Statements that accepts some input by parameter, and performs some task and must return a value.

Ø  Types of Functions in SQL Server

ü  System Defined Function: This function defined by SQL Server. There is two type of system defined function.

·         Scalar Function: This will operate on a single value and return single value. Like abs(),rand(),round(),upper(),lower(), ltrim(), convert()

·         Aggregate Function: This will operate on collection of values and return single value like max(),min(), avg(), count().

ü  User Defined Functions: Functions are created by the user. There are three types of User Defined Functions.

·         Scalar Function: This will return a single value as a result. We return any datatype value from a function.

·         Inline Table-Valued Function:  This will return the table variable as result. The Value of the table variable should be derived from a single SELECT statement (Can’t use join of two tables).

·         Multi-Statement Table-Valued Function: This will returns table value as result. Table must be explicitly declared and defined whose value can be derived from multiple SQL statement.

Ø  Difference between stored procedure and function

Function	Stored  Procedure
Not support DML.	Support DML Statement.
Must return a single value (Scalar or Multiple Values).	Can return Zero, Single or multiple values.
Can’t use Transaction.	Use Transaction.
We cannot call SP from Function.	We can Call Function from SP.
We cannot exception handling.	We can manage exception handling
The function has only the input parameter.  Support up to 1023 input parameters.	SP can have an input/output parameter. Support up to 2100 input parameters.
The function can be embedded in a SELECT statement.	Procedures cannot be utilized in a SELECT statement.

 

Ø  SET NOCOUNT ON

ü  This prevents the message from showing which contains the number of affected rows.

ü  Improve performance of Stored Procedure.

Ø  WITH(NOLOCK)

ü  Read UNCOMMITED Data (Dirty Read)

Ø  SET TRANSACTION ISOLATION LEVEL READ COMMITTED

ü  Read only Committed data in the STORED Procedure.

ü  Performance Overhead using this statement.

Ø  Difference between SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY.

ü  All functions are return values inserted into IDENTITY columns.

ü  SCOPE_IDENTITY: Return the last identity within the current session and current scope.

ü  @@IDENTITY: Return the last identity within the current session and any scope.

ü  IDENT_CURRENT: Return the last identity within any session and any scope.

Next Recommended Article:

1. When to use abstract class and interface in C#?
   
2. Async and Await in C#.
   
3. Optimized top 2 ways to find 2nd or Nth highest salary in SQL Server.
4. Different ways to remove or delete duplicate rows from a SQL Table.
5. Top 100+ popular ASP.Net MVC Interview Questions and Answers.
6. Top 100+ popular C# Interview Questions and Answers.
7. @Input, @Output decorator and EventEmitter class in Angular.
8. Dependency Injection and types of dependency injection.