Stored Proc DB

Question

Stored Proc DB

Kris-155042

Hall of Fame

Points: 3460
More actions
December 5, 2017 at 8:22 pm

#341917

Hi All,
I was just wondering what the advantages are of separating Stored Procs into their own database. We currently have one database that has all the tables and views and another that has only the Stored Procs. Is there a benefit.
Thanks
Kris
Thanks,
Kris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ricky Valencia SSCommitted Points: 1907 More actions · Answer 1

The following are the benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:
·         They allow modular programming.
·         They allow faster execution.
·         They can reduce network traffic.
·         They can be used as a security mechanism.
You can create a stored procedure once, store it in the database, and call it any number of times in your program. Someone who specializes in database programming may create stored procedures; this allows the application developer to concentrate on the code instead of SQL. You can modify stored procedures independently of the program source code—the application doesn't have to be recompiled when/if the SQL is altered.

The following is a list of some of what you can do with stored procedures and reasons for using them.

· Encapsulation of Queries— so that you don’t have to worry
where they were saved, and you could execute them from anywhere on the
network.

· Parameterized Queries—These stored procedures accepted
one or two parameters and returned a subset of the information in the tables
in which the user was interested. This enabled the users to return only those
results that were important to them.

· Encapsulation of Data Modification Statements— Another great use of
stored procedures is to encapsulate data modification statements. When you
type data modification statements into a query window and execute them,
there is a possibility that you will mistype something and cause severe
problems in the database. If you encapsulate the data modification
statements into a stored procedure that has been adequately tested, you are
able to better control the statement and limit the amount of damage that can
be done in the statement.

· Maintainability of Application Logic— One very widespread use of stored
procedures is to use them as a container for application logic. This way, you
can maintain all your company's business rules and logic in a single location,
which makes them extremely easy to maintain. If a business rule changes, all
you have to do is change the code in the stored procedure, and all users
would have the new code.

· Standardization — If you roll all the data access, data modification, and
business logic statements into stored procedures, you are virtually
guaranteed that all access to your database will be standardized. That means
if a user accesses a particular table, you know exactly what he is doing and
how he is doing it.

· Ease of Troubleshooting— This point closely follows the previous point. If
you standardize all your database access through a common set of stored
procedures, troubleshooting is much easier. This ease is because you have
only one place to look to find the problems and, when the problem is fixed,
one place to roll the changes to.

· Security— One of the best, but least implemented, uses for stored
procedures is as a security measure. If you create a stored procedure that
accesses a table, you can revoke access to that table; the only way your
users can access that table is through the stored procedure you've created.
This is an extremely powerful method for locking down the server and
keeping users from accessing information they aren't supposed to.

· Automation of Administration Tasks — Like system stored procedures,
the core set of procedures installed with SQL Server, these procedures are
used to perform low-level system functions and to return information about
the server and the objects on the server.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

It's not what the benefits of Stored procs are but why you would create a completely separate them from the database that they are actually calling. ie DB1 has all the tables and views and DB2 only has the stored procs that call the data from DB1.

Thanks,

Kris

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Kris-155042 - Tuesday, December 5, 2017 9:32 PM
It's not what the benefits of Stored procs are but why you would create a completely separate them from the database that they are actually calling. ie DB1 has all the tables and views and DB2 only has the stored procs that call the data from DB1.

The only benefit I can think of and its also a con, is cross database ownership chaining, get it right it works a charm, get it wrong its a right PITA, proper security rights to the procs via roles is probably a better option to go down and potentially save yourself a lot of pain.

Apart from that it adds another database to maintain, restore, DR, HA etc etc, is it worth the additional administration effort? What is the business logic in doing this?

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

Hi Anthony,

I totally agree with you I just wanted make I wasn't missing something. I've just started a new job and this how it's set up. When I asked the business why, the answer is they don't know.

Thanks,

Kris