Interview Questions On Sql Server -Part 2

1) Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.

2) The second difference between Union and Union All can be given on the basic of their uses. Union All can be used to insert multiple rows in the table in a single query whereas Union can not be used to insert multiple rows in the table.

3)Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.

Stored Procedure:- Stored Procedure In Sql server can be defined as the set of logically group of sql statement which are grouped to perform a specific task.There are many benefits of  using a stored procedure. The main benefit of  using a stored procedure is that it increases the performance of the database

1) One of the main benefit of using the Stored procedure is that it  reduces the amount of information sent to the database server.It can become more important benefit when the bandwidth of the network is less.Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don’t returns the expected results, if the sql query is not  used between Transaction statement and rollback statement is not used.

2) Compilation step is required only once when the stored procedure is created.Then after it  does not required recompilation before executing unless it is modified and reutilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.

3) It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.

4) Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.

Read more about Stored Procedures

1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.

2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.

3) Functions can be called from within the Stored Procedure but a stored procedure can not be called from within a function.

4) A stored procedure can return multiple parameters but a function can return only one value.

5) We can use join in the outcome from the functions but we can’t use joins in the outcome from stored procedures.

6) Transaction management is not possible in functions but it is possible in Stored procedures.

7)Print function can not be called within the function but it can be called within the stored procedure.

Read more about difference between stored procedures and Functions

5.What is the Group By Clause ………. Having Clause?

6. What are the difference between Having and Where Clause?
1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.

3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).

Read more on difference between Having and Where Clause

7.How we can find out the duplicate values in a table?
We can find out the duplicate values in a table with the help of the Having clause in conjunction with the Group By Clause.

Read more about how to find out the Duplicate values in a table 

8.What are the Views?
Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server.

Read more about Views  

9. What are the difference between Query, Subquery, Nested Subquery and the Corelated Subquery?
Query: – Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.