Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL

[admin]

Business Logic: To Store or not to Store that is the Question?

A lot of people in the database and programming professions are vehemently in favor or opposed to the use of stored procedures and the like in databases. They will argue that all access to the database should go thru stored procedures because it is more secure and shields applications from changing logic. The other side will vehemently argue that you should avoid this because not all databases support this and the way each supports it is different so your code is less portable.

I would start by saying those individuals who are vehemently on one side or the other should open their minds a little. One approach is not strictly the best solution for all business requirements. In the next couple of sections we'll outline what are the important questions to consider when choosing one approach over another. Often you will find that even in a single application a mixture of approaches works best.

Best Practices vs. Best approach to achieve the goal

Before dismissing one approach over another, it is important to map out what you are trying to achieve and then determine how using one approach aligns with your objectives.

You will often hear the term Best Practices used in Application Architecture. The danger of such a term is that it gives one a sense that there is no need to question a practice because it is always best. Just follow Best Practices outlined by an authority in the subject matter and things will magically fall into place. Rather than just focus on Best Practices, I would like to propose that one should think about what they are trying to achieve and why one approach is better than another to get there.

What to consider?

In this section, we will talk about considerations on a very elemental functional level rather than a more macro application level. We think it is important to consider each functional task of an application separately rather than thinking of an application as a whole. Example you may have one part of an application that needs to talk to a mainframe database or run scheduled tasks, and given that it doesn’t make sense to drive your whole application structure based on the need of this single functionality.

Does this function need to work in various kinds of databases?
Is this function used in multiple parts of an application or applications?
Does this function require many arguments and return one set of values in form of single table or scalar value?
Does this function require few arguments passed to the database, but require lots of data from the database to arrive at results?
Is the function data intensive or processor intensive? E.g. is it an encryption function or one to get results of a complex query - is it more SQL intensive or more procedural intensive
Are the parameters passed into the function consistently the same or can they vary? E.g. a complex search form will not always need to pass the same parameters, but a simple one will just have a single search field and always pass just that.
Does the function require page long batches of SQL statements or is it a one line SQL statement?
Are the fields of the result set always the same or do they need to vary. For example do you always find yourself joining the same set of tables varying slightly by the need for different fields or subsets of data?

Database Objects

I always find it amusing that when people talk about database logic they are very focused on stored procedures and its almost like nothing else exists. Makes me wonder if these people have ever worked with modern databases. Stored procedures are one of the oldest methods of encapsulating database logic, but they are not the only method available. Many relational databases nowadays have views, constraints, referential integrity with cascading update, delete, stored functions, triggers and the like. These are extremely powerful tools when used appropriately.

In the next couple of sections we’ll cover stored procedures and these other kinds of database objects and detail the strengths and weaknesses of each for encapsulating logic. We will give a rating of 0-5 for each feature 0 meaning the feature is non-existent, 5 meaning this object is one of the best suited objects for implementing this kind of task.

[admin]

Stored Procedures

Stored procedures are one of numerous mechanisms of encapsulating database logic in the database. They are similar to regular programming language procedures in that they take arguments, do something, and sometimes return results and sometimes even change the values of the arguments they take when arguments are declared as output parameters. You will find that they are very similar to stored functions in that they can return data; however stored procedures can not be used in queries. Since stored procedures have the mechanism of taking arguments declared as OUTPUT they can in theory return more than one output.

Feature	Rating
Works in various kinds of databases	3 (many  databases such as DB II, Oracle, SQL Server, MySQL 5, PostGreSQL, FireBird support them).  There are also a lot that don’t e.g. MySQL < 5.0, MS Access (although parameterized queries serve a similar role)
Can be called by multiple applications and interfaces	4 (generally they can be called, but the use of OUTPUT arguments  is not always usable)
Can take an undefined number of arguments	2 (note most databases allow to define optional arguments, but this can become very unwieldy to maintain if there are a lot  because you end up duplicating logic even within the stored procedure so is generally avoided)
Reusability within the database	3 (you can not reuse them in views, rarely in stored functions and other stored procedures unless the stored procedure using it does not require a return value or result query).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to change table directly	4  In general true for most DBMSs that support them.
Can return varying number of fields given different arguments.	3 –again in theory it can, but very hard to maintain since you would often be duplicating logic to say return one field in one situation and other set of fields in another situation or update a field when the field is passed in as an argument. Note that in many databases such as for example SQL Server and Oracle, one can return multiple result sets with a stored procedure, but the receiving end needs to be able to do a next result set call and know the sequence in which the result sets are being sent.
Long stretches of SQL easy to read and maintain	5 (one of the great strengths of stored procedures is that you can have long transactions of sql statements and conditional loops which can be all committed at once or rolled back as a unit. This also saves on network traffic.

Stored Functions
Stored Functions are very similar to stored procedures except in 3 major ways.

1- Unlike stored procedures, they can be used in views, stored procedures, and other stored functions.

2- In many databases they are prohibited from changing data or have ddl/dml limitations.  Note for databases such as
PostGreSQL this is not true since the line between a stored function and a stored procedure is very greyed

3- They generally can not take output arguments (placeholders) that are then passed back out with changed values.

Feature	Rating
Works in various kinds of databases	3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL).  There are also a lot that don’t e.g. MySQL < 5.0, MS Access
Can be called by multiple applications and interfaces	4 (generally they can be called, but the use of OUTPUT arguments  is not always usable)
Can take an undefined number of arguments	2 (note most databases allow to define optional arguments, but this can become very unwieldy to maintain if there are a lot  because you end up duplicating logic even within the stored function so is generally avoided)
Reusability within the database	5 (you can reuse them in views, in other stored functions and stored procedures).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to change table directly	3  Many databases do not allow changing of data in stored functions except temp table data, but those that do in general support this.
Can return varying number of fields given different arguments.	4 –For databases such as SQL Server, PostgreSQL, DB 2, Oracle that allow return tables and sets, you can selectively pick fields you want from within a query. So although the function always outputs the same number of fields, you can selectively use only some similar to what you can do with views. This is not true for scalar functions (MySQL 5.1- only supports scalar functions).
Long stretches of SQL easy to read	5 - yes - you can do fairly intensive multi-line processing which in the end returns one value or table to the user.

[admin]

Views
Views are one of the greatest things invented since sliced bread. The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

Feature	Rating
Works in various kinds of databases	4 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL, SQLite, MSAccess (calls it a query)).  There are also some that don’t e.g. MySQL < 5.0
Can be called by multiple applications and interfaces	5 (generally they can be called anywhere where you can call a table which is pretty much everywhere)
Can take an undefined number of arguments	5 (you can select subsets of columns and rows from a view just like you can from a table)
Reusability within the database	5 (you can reuse them in other views, in stored functions and stored procedures).
Can be used to change data in a table without giving rights to a user to change table directly.	3  In many databases Views are read-only and complex views are rarely updateable. Note that for example some databases such as Oracle,PostgreSQL, MS SQL Server , SQLite will allow you to update even a complex view by using an instead of trigger or rule against the view. MySQL 5, MS SQL Server and some others automatically make one table views updateable. MS Access has a fairly sophisticated update algorithm for queries in that it automatically makes one table and even multi-table queries updateable and allows deletes if you define primary keys appropriately. Also depending on the field a column comes from it will update that and also automatically create child records in child tables if you try to update a child column when there is no record.
Can return varying number of fields given different arguments.	4 – (you can select subsets of columns and rows from a view just like you can from a table). However you can't change the underlying structure e.g. return records from a different set of tables like you can with a stored procedure or function.
Long stretches of SQL easy to read.	3 (A view can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read) - in general though it is limited to only one select statement or unioned select statements.

Triggers And Rules

Triggers are objects generally tied to a table or view that run code based on certain events such as inserting data, before inserting data, updating/deleting data and before these events happen.

Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.

They are useful for making sure certain events always happen when data is inserted or updated - e.g. set complex default values of fields, inserting logging records into other tables.

Triggers are especially useful for one particular situation and that is for implementing instead of logic. For example as we said earlier, many views involving more than one table are not updateable. However in DBMS such as PostgreSQL, you can define a rule on a view that occurs when someone tries to update or insert into the view and will occur instead of the insert. The rule can be fairly complex and can layout how the tables should be updated in such a situation. MS SQL Server and SQLite let you do something similar with INSTEAD OF triggers. Note the term Rule is a little confusing in DBMS because they mean quite different things. In Microsoft SQL Server for example a Rule is an obsolete construct that was used to define constraints on tables. In PostgreSQL a Rule is very similar to a trigger except that it does not get triggered per row event and is defined without need of a handling function.

Feature	Rating
Works in various kinds of databases	2 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL,).  There are lots that don't e.g. MySQL < 5.0, MySQL 5 limited, MS Access
Can be called by multiple applications and interfaces	5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments	0 ( strictly for updating data and against a table or view )
Reusability within the database	0 - No
Can be used to change data in a table without giving rights to a user to change table directly.	4  In general yes for databases that support them
Can return varying number of fields given different arguments.	0 –Triggers are strictly for updating data
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read	5