Stored Procedures Interview Questions

Stored Procedures Interview Questions

1. What do you understand by a stored procedure?

The group of SQL statements that are or have been stored in the server database is known as a stored procedure. Input parameters are accepted in the stored procedures so that several clients can use that single procedure over the network using that single input data. It holds an advantage that whenever the procedure gets updated; all the clients automatically get the updated version. Stored procedures help in reducing the network traffic and also improve the performance of the data. The integrity of data can be ensured while making use of stored procedures.

2. Mention the uses of stored procedures.

Stored procedures are used for various different things. Some of the uses of stored procedures are as follows:

1.Stored procedures are often used as access control mechanism and for data validation.

2.The logic applied in this application is centralized and is stored in the application.

3.Procedures are used to process the huge amount of data for complex procedures and functionalities and for logic implementation access of their data.

4.These procedures are used to store data in them and can be accessed by procedures.

5.They support modular programming.

6.Faster execution takes place.

7.It can also be used as a security mechanism.

8.They are used for reducing network traffic.

3. What are the types of stored procedures in an SQL server?

1.User-defined stored procedures– these are the modules or routines which encapsulate code for reuse. There are 2 types of user-defined stored procedures:

2.Transact-SQL: It is a collection of transaction SQL statements that takes and returns user-specified parameters.

3.CLR: It is a reference to the Microsoft .NET framework.

4.Extended stored procedures– with the help of these procedures, you can create your own external routine in a programming language.

5.System stored procedures– these are used to perform various administrative activities in the SQL server.

4. What are the advantages of using a stored procedure?

The following are the advantages of using a stored procedure: –

1.It reduces the network usage between the client and the server and immediate processing is performed on the database server. It hence reduces unnecessary data transfer.

2.Security is improved and the user access to the stored procedure is managed by the administrator.

3.Development cost reduces and reliability increases.

4.It improves the performance of the database.

5.These are used to encapsulate the logic and hence the code can be changed without affecting the clients.

6.The access to other database objects becomes more secure.

7.SQL injection attacks can be avoided using this.

5. What are the disadvantages of using the stored procedures?

1.Everything which has advantages has disadvantages too. The disadvantages of using stored procedures are listed below:

2.Specialized skills are required for writing and maintaining the code of a stored procedure.

3.Debuggers are not available for a stored procedure.

4.The language in which a stored procedure is written may differ from one database to the other.

5.Exception handling in a stored procedure is not up to the mark.

6.These are tightly coupled to the database.

7.You may not be able to use objects.

8.Sometimes the logic may not be understood by the programmers.

6. How will you optimize a stored procedure optimization?

The various tips and tricks for optimising a stored procedure optimization are listed below:

1.“SET NOCOUNT ON” statement should be included.

2.A schema with its object name should be used.

3.The prefix name “sp_” should not be used in a stored procedure name.

4.Instead of using (SELECT*), use IF EXISTS (SELECT 1).

5.SQL server cursors should be avoided whenever possible.

6.The transaction should be kept as short as possible.

7.In case of error handling, make use of the try-catch block.

7. What are the differences between stored procedure and view in SQL server?

The differences between stored procedure and view in SQL server are as follows:

1.A stored procedure accepts parameters. Whereas, views do not accept parameters.

2.In any large query, a stored procedure cannot be used as a building block. Whereas, a view can be used as a building block.

3.A stored procedure can contain several statements. Whereas, a view can contain only one single select query.

4.Using stored procedures, one or more tables can be modified. Whereas, using view no table can be modified.

5.A view can be used within a stored procedure. Whereas, a stored procedure cannot be used inside a view.

8. List some major differences between triggers and stored procedures?

Following are some differences between stored procedures and triggers:

1.Event and actions needs to be identified at the time of creating a trigger. Whereas, at the time of creating a stored procedure, it is not important.

2.Trigger can be run automatically when any event occurs. Whereas, stored procedures have to be run manually.

3.A stored procedure can be called within a trigger. Whereas, a trigger cannot be called in a stored procedure.

4.Triggers execute implicitly. Whereas, stored procedures execute explicitly.

5.A trigger cannot be called from front end. Whereas, a stored procedure can be.

9. What do you understand by recursive stored procedures?

Repetitive tasks are being performed by the recursive stored procedures. By default this function is disabled but can be activated using a particular command. The command is as follows-

Max_sp_recursion_depth;

After using this, the system variable should be renamed to a non-zero variable.

10. When would you use the stored procedures or functions?

As we all know, functions are the computed values and they cannot perform any permanent environmental changes to the SQL server. Neither insertion in the statement is allowed nor can it be updated. If a function returns a scalar value or can be joined upon if it returns a scalar set, then it can be used inline in the SQL statements. These are used when the logic applied in the application is centralized and the data is stored in the application. These stored procedures are used when we need to validate the data and the complex procedures.

11. How do we recompile a stored procedure at run time?

By adding the WITH RECOMPILE hint when creating or executing the stored procedure.

12. Why do we use functions instead of stored procedure in SQL?

If you want perform some calculation base on some column value, then you can use function instead of stored proc because you can not call a procedure in a select statement but you can call function in a select statement.