Disadvantage with having code in Database
I suddenly realized one big disadvantage about having code in the database (stored procedures etc) - Restoring
Restoring a database disrupts the versioning. Code and data are not versioned together. Database structure and code are usually tied together.
Say i have a procedure i want to modify.
- I edit the SP,
- update it in the database
- run it.
I find an error, correct it and want to run it against the original data. What do i do?
Restore is usually the best option if my test-data has a alot of relations. Maybe i even want to run the sproc against a copy of the production database when fixing a bug.
After each restore i need to update the sprocs to the new version. It can get quite tedious after a while and one risk is that you miss to update something if there are many procedures. One solution to this is to always have the code on disk and version controlled and script the updates into the database.
My opinion is rather clear in this case. Don’t have too much program-logic in the database.
