Interview Questions and answers - SQL Server Day 3



Question:  What is the difference between Function and Stored Procedure in SQL Server?

Answer: Some the fundamental difference between stored procedure and function is described below.

The main difference between stored procedure and function is The function must return a value while stored procedure can or not this is optional. The function can have input parameters only, while a stored procedure can have both input/output parameters. The function can be called from stored procedures, but stored procedures can’t call from function. 

Function 
Stored Procedure
The function must return a value.
In stored procedure it is optional 
The function can have an input parameter only.
The stored procedure can have both inputs and output parameter s
The function can be called from the procedure.
The procedure can‘t be called from the function.
The function allows the only select statement.
Procedure allows DML statement(SELECT, INSERT, UPDATE, DELETE)
The function can use an SQL statement like WHERE/HAVING/SELECT.
The procedure can’t be used in SQL-statement like WHERE/HAVING/SELECT.
The function can return the table and can be treated as another row set. This can be used in joins with other tables.
The transaction can’t be implemented.
The transaction can be implemented in the stored procedure.

Question: What is a subquery in SQL server?

Answer: A SELECT statement query nested in another t-SQL statement is called a subquery. SELECT subquery always runs independently and returns the result set to the t-SQL statement. The subquery doesn't depend on a statement in which it is nested.

Question: What are the different types of joins in SQL Server?

Answer: Types of joins in SQL Server is given bellow.
1- Cross join
2- Inner join
3- Outer join
    a)Left Outer join
    b)Right Outer join
    c)Full Outer join
4- Self-join

Question: What is Cross Join in SQL Server?

Answer: Joins between tables without conditions produces the Cartesian product called a cross join. The size of the Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

example:


SELECT p.bID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BID;

Question: What is inner join in SQL Server?

Answer: Inner join displayed only the rows of tables which matched in both joined table on a particular column. This is the default type of join in the query.

Question: What is Outer join and it type in SQL Server?

Answer: Outer join includes related and rows and also not associated rows. there are three types of outer joins in SQL Server.
a) Left Outer Join: the Left Outer join display all the rows of the first table and all matched rows of the second table but not appears unmatched rows of the second table.
b) Right Outer join: Right Outer joined display all the row of the second table and matched rows of the first table, but not appears unmatched rows if the first table.
c) Full Outer Join: Full Outer join display all the rows of joined tables, whether they matched or not.

Question: What is Self join in SQL Server?


Answer: Join between a table, and its alias is called a self join. In this join table, join itself, but the same name may create confusion, so we used the alias of the table as the second table.


NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...