Interview Questions On Sql Server -Part 2

1. What is the difference between the Union and Union All?

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.

Read more about difference between Union and Union All

2. what is the stored procedures?

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

Read more about Stored Procedures

3.What are the benefits of using stored procedures?

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.

5) Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

Read more about Stored Procedures

4. What are the difference between Stored procedures and Functions?

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?

Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-

Select column1, column2, column3, aggregate_function(expression )

From TableName Where (condition)

Group By Column1, column2, column3

Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-

Select column1, column2, column3, aggregate_function(expression )

From TableName Where (condition)

Group By Column1, column2, column3

having aggregate_function(expression ) operator value

Read more about Group By ………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.

2) We can’t use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.

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

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.

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.

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Read more about the difference between Query, Subquery, Nested Subquery and Correlated Subquery

10.Suppose we have a table whose structure is given below

Create table addcounter( counterid int identity(1,1))

Now how can we insert values in the table

By using the following Insert Statement

Insert into addcounter defalut values

Related Articles

Interview questions on Sql Server – Part 3

Interview Questions On Sql server –Part1