3 ms·
Nice write-up. > This code has to be maintained, versioned, etc. Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Micr
by zamalek 12y ago
Nice write-up.
> This code has to be maintained, versioned, etc.
Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is handled on by CI and the migration (as well as the creation) scripts are generated for us - based on the schema differences (that it determines for us) between our last release and the current release.
So far as SQL Server goes, maintenance and versioning woes are an outright myth.
More pros:
4. A policy of using SPROCs only is a good way to significantly reduce the risk of SQL-related vulnerabilities. If developers are required to justify why they are sending raw queries to SQL they will have a hard time introducing e.g. an injection vulnerability.
5. A lot of the overhead of query execution (parsing, resolving objects, etc.) is done when you CREATE or ALTER the procedure, this can results in a performance benefit (especially if the application is chatty).
More cons:
5. Query plans for SPROCs are aggressively cached, in extremely rare circumstances this can play havoc with performance (direct contradiction of my own pro 5).
6. Triggers+family are invisible logic. They can cause confusion during debugging.
- spacemanmatt 12y ago> 6. Triggers+family are invisible logic. They can cause confusion during debugging. I'm not sure I can agree. This is the appropriate venue to validate/copy/mutate data. An heavily constrained table should be documented as a matter of API instructions, because (as you mentioned) they can cause confusion, but let's not throw the baby out with the bathwater, eh?
- zamalek 12y agoYeah it's totally a debatable one, hence the "can." I should have listed it as a caveat or something.