1). What is Stored Procedure?
Ans: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
2). uses of stored procedure?
Ans: Stored procedures are often used for data validation and as access control mechanism. Logic applied in applications can be centralized and stored in applications. Complex procedures and functionalities which require huge amount of data processing and logic implementation access their data by procedures. Data is stored in these procedures and accessed by procedures.
3). types of Stored procedure in SQL Server?
System Defined Stored Procedure
Extended Procedure
User Defined Stored Procedure
CLR Stored Procedure
4). How do we recompile a stored procedure at run time?
Ans: Add the WITH RECOMPILE hint when creating or executing the stored procedure
5). How to Optimize Stored Procedure Optimization?
Ans:
Include SET NOCOUNT ON statement.
Use schema name with object name.
Do not use the prefix "sp_" in the stored procedure name.
Use IF EXISTS (SELECT 1) instead of (SELECT *).
Use the sp_executesql stored procedure instead of the EXECUTE statement.
Try to avoid using SQL Server cursors whenever possible.
Keep the Transaction as short as possible.
Use TRY-Catch for error handling.
6). Can we use multiple select statements in a Stored Procedure SQL Server?
Ans: Yes, we can use multiple select statements in a SP.
7). Can we create Stored Procedure without "Begin" and "End" refer the below image and try to answers?
Ans: Yes,
8). What are the disadvantages of using a Stored Procedures?
Ans:
Writing and maintaining stored procedures requires more specialized skills.
There are no debuggers available for stored procedures
Stored procedure language may differ from one database system to another.
Poor exception handling
Tightly coupled to the database system
Not possible to use objects
Sometimes it is hard to understand the logic written in dynamic SQL
9). How do we recompile a stored procedure at run time?
Ans: By adding the WITH RECOMPILE hint when creating or executing the stored procedure.
10). When would you use stored procedure or functions ?
Ans: Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
0 comments:
Post a Comment