It's all set based if used correctly.Īs for performance, there is NO significant execution performance difference between a SP and any other static/parameterized text query sent over the wire at least when we are talking about MS SQL and most others.
If you are using SQL like code you are writing awful SQL. They aren't even on the same realms of thinking. I don't know I would attribute an OO paradigm like SOLID to a relational database. However I've since moved to a more code-first approach to SQL with dotnet EF. And stored procedures have served me well in the past. Overall, it's right tool for the right job.
And lastly, if you upgrade your database in anyway, that can warrant a complete rewrite of the stored proc. They can also be complicated to write, and that can affect all sorts of things, so at the very least you need to be aware of SQL querying shortfalls when it comes to optimizing performance. I don't know if there are any tools out there to help, but it's something you really should keep in mind. I've got 10 years of experience in MSSQL, and I've never found a way I'm comfortable calling 'the best way'. Testing and debugging them is a nightmare. So they're pretty good, but I don't want to leave you with any disillusions. If you only allow data manipulation through regimented stored procedures, it can be quite hard to bypass any checks in the stored proc itself. So you don't have to repeat complicated queries in your code, just call the stored procedure. Stored procedures are compiled and executed by SQL in an executable form, which means it is also cached, which results in lower memory usage for each run. As for things like performance, they're about as performant as they can get when SQL is concerned. As long as they're properly version controlled you shouldn't run into any production issues with them. I think stored procedures are the way to go.