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.
