Archivio di categoria: Stored Procedures

Stored Procedures and Triggers allow to implement business logic into the database. While this has many drawbacks, it can also be very useful for many reasons:

• Validity rules and basic behaviour can be considered as part of the data definition, just like the column names and their types. Having the whole definition in the same place makes the definition cleaner and more maintainable.
• Processing big amounts of data to produce a small result is often better done in a stored procedure. This avoids data round trip, which consumes resources in both the client and the server, as well as the network itself.
• Grouping sequences of queries in a procedure allows to reduce client-server communication and data round trip. Typically it also allows to write less code.
• During a transaction, locks are typically held. If this is done in a stored procedure, locks are held for a shorter time.
• If several applications read and write the same data, they should implement the same logic, possibly in different languages. This increases the development costs and the amount of bugs. Rules can be implemented once in the database instead.
• Porting applications from one language to another is much more frequent than migrating from one database type to another. Stored procedures have a longer life than typical applications.