Home
MS SQL
Interview FAQs

FAQs of level

Stored Procedures are logically grouped set of transect-SQL statement bundled up into a single unit that perform a specific task. They allow for variables and parameters as well as selection and looping constructs.
Stored Procedures may return multiple values, while functions returns only one value at a time.

Stored Procedures can have input, output parameters for it whereas functions can have only input parameters.

Stored Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

Functions can be called from Stored Procedure whereas Stored Procedures cannot be called from function.

Exception can be handled by try-catch block in a Stored Procedure whereas try-catch block cannot be used in a function.

Stored Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

Stored Procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

Reduced client/server traffic. If network bandwidth is a concern in your environment, you will be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you will find the development cycle takes less time.

Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

No. DDL is commit, you need not commit again.
Materialised View is used for performance improvement.
PreviousDisplaying 4 of 15Next
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*