Why Use Stored Procedures?
Why Use Stored Procedures?
By using stored procedures, we can reduce the time a process can take, as stored procedures are compiled.
Another gain from using stored procedures is that they are much simpler to maintain, as compared to raw T-SQL code. Since the stored procedure is held centrally within the database, any required bug fix, upgrade, or modification can be completed
The code in a stored procedure is executed in a single batch of work. This means that it is not necessary to include a GO command while writing code for procedures. SQL Server will take any of those statements and implicitly execute them, as a batch.
Perhaps, the greatest reason to create a stored procedure rather than use inline T-SQL, is security. As we will discuss in
Chapter 8
, we have several security options at our fingertips while using a stored procedure. Along with other security measures, it helps to ensure that we can lock down our database from
| Note |
Stored procedures are rarely used for creating other database objects with the exception of temporary tables, which are special tables created to complete a task within a stored procedure. There is more on temporary tables in Chapter 4. |
Replication
Stored procedures can also be used to work with databases involved in replication. By creating a specialized custom stored procedure, which is placed on each publishing database, it is possible to resolve any row UPDATE conflicts that may occur. Stored procedures can aid in replication processing, and also ensure that a replicated database is optimized. We won't be teaching you about replication in this book, but we bring up the subject to ensure that you are aware of potential problems that can arise with stored procedures in a replication scenario.
Consider a scenario where either a stored procedure or T-SQL updates or deletes a large number of rows. We can't really put a deterministic figure on this, as there are a large number of factors, such as connection between the databases, network traffic, network
For example, the publisher places each update into the log reader for publication to each subscriber of the database. The Distribution Agent takes one row at a time, distributes them to the subscriber, and updates the subscriber database. If there is more than one subscriber, or if there is a delay due to connection speed or network traffic, the subscriber can lag behind, if there is no break in processing at the publisher's side.
In a real life scenario, at one installation, we had a very fast publisher and subscriber link (between London and New York). Traffic on one table was relatively light, but due to stock market conditions an update had to take place. The developer in question didn't realise that they had to update 100,000 rows, so off they went, and
How does replication, and problems like this fit into this book? In the above example, if the developer had placed the updates in a stored procedure and made it a procedure execution article , then SQL Server would have replicated the execution of the stored procedure and not the updates that took place.
| Important |
You have to be 100% sure that the data in the publisher and the subscriber databases are consistent. If not, you can have a failure on one of the databases, but a success on another, thus
|
It's preferable to have the stored procedure set up as a serializable procedure execution article . By setting the isolation level to SERIALIZABLE within the stored procedure, we can make a stored procedure into this form. We'll see more information on isolation levels in Chapter 3.
If we have an isolation level that can allow dirty reads, then we can insert data into a table before a previous modification has been committed.
