SQL Server Inportant Inteview Questions

 

Difference between Stored Procedure and Function in SQL Server

    1-The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.

    2- Functions can have only input parameters for it whereas Procedures can have input or output parameters.

    3-Functions can be called from Procedure whereas Procedures cannot be called from a Function.

    4-The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

    5-Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

    6-Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

    7-Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

   8- Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

    9-An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

    10-We can use Transactions in Procedure whereas we can't use Transactions in Function.

 

 

Difference between CTE and Temp Table and Table Variable

CTE:-

It is a temporary result set and typically it may be a result of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.

Temporary Tables:-

Temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database.

Local Temp Table:-

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. The local temporary table name is stared with a single hash ("#") signThe scope of Local temp table exists to the current session of the current user means to the current query window. If you will close the current query window or open a new query window and will try to find above-created temp table, it will give you the error.

Global Temp Table:-

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. The global temporary table name is stared with double hash ("##") sign.

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable:-This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

NOTE:----

Temp Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, an index like normal tables.

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. You cannot create an index on CTE.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of a batch. This is also created in the tempdb database but not the memory.