Does SQL Server stored procedure is still necessary if you know EFCore and Dapper ?

  1. I would say that no matter what, knowing how SPs work is worth learning. Also, they are definitely not "legacy", except for basic "data in - data out" projects.

  2. Or like put an API over the database and use that instead of calling the same database directly from multiple places.

  3. You can get very far by just having fundamentals of SQL and then use EF Core. Personally I've only worked with nosql databases these passed 3 years ¯_(ツ)_/¯

  4. Definitely this. And someone within your group should be competent with SQL. That doesn't necessarily have to be you, but it should be someone.

  5. I’ve been a developer for 22 years. Sql and stored procedures is the only thing that hasn’t really changed. Yes- I use ef and dapper- but I still have to write sql stores procs for the odd report or more complex logic.

  6. This is a really bizarre question for a "backend developer" to ask. Yes, a backend developer should learn about fundamental backend technology like SQL.

  7. When I need to access the DB from the app, I usually first write the SQL code in SSMS in order to test it and tweak it. From there its very easy and tempting to turn it into a sproc since its already 98% there. But I resist that temptation because we also use EF and thats where we want our DB access code.

  8. Sprocs lives outside of the app and can be changed at database level, which can affect the app. I take inline SQL with Dapper over a SP any day.

  9. Our team has very strong SQL skills but as we transitioned into using EF, I made it a mandate to not use those skills, i.e., don't use sprocs. The main reason for doing this was to make sure we don't use those skills as a crutch from learning EF.

  10. Opinions differ but I believe you nearly never need stored proc.s and they are wildly overused to pre-optimize. When you do need them though, there's no substitute.

  11. "It works dont thouch" is the general rule for all stored procedures. Then you are stuck writing applications around the database because no one can change it or even want to risk trying. Resulting in business rules being split in the App and in the Db containing wierd work arounds.

  12. My group takes the opposite approach of what most of the responses I've seen here are. We write stored procedures for anything other than basic read-write operations. There have been many, many times over the years that having the more complex data operations encapsulated in a stored procedure have prevented us from having to build new versions of software and go through a release cycle.

  13. I start with ef and move to sps as needed, some times it’s obvious tho, in those cases you got with store procedures from the start

  14. Just like the vast majority of these comments, it all depends on the scenario you are dealing with, the amount of data, and the level or dynamism/maintenance you can have.

  15. I actually prefer all my SQL to live in stored procedures. It means that all the SQL is within the database server. The stored procedures create an API layer for the database (a contract for parameters and responses).

  16. Dynamic queries have been cached on the server for MS SQL since 2005 if you repeatedly call the same query, so there is negligible performance difference in the very vast majority of cases.

  17. They are a pain to build and maintain. In an ideal world, they can act as the API at the data layer. I never end up having the time to do this.

  18. Yes you need stored procedures. EF and Dapper are good for simple small straightforward database operations. Lets say you have to fetch data from 4-5 table/views with 2-3 inner joins and 1-2 left/right joins Can you think of how difficult/complex it would be to write it using EF and Linq. And the second point is the offcourse the performance of the queries. there is small point of SProcs are pre compiled piece of sql statements. You can write some database related complex logic in sproc (if you want to do it in ercore or linq then you will need to pull out the data from the database and store the data in the server memory then operate on it, so its less efficient and heavy memory usage)

  19. I havent used a SP in the last 5 years. They were however common in legacy systems so its likely you will run into one from time to time.

  20. Every dev should know stored procedures/packages, functions, triggers, views, basic SQL, etc... It will definitely be useful in the future.

  21. Stored procs are useful to know. At my current company (and also my personal preference) they are currently out of favor because they encourage having logic live in both SQL and the codebase (which is bad), and for our deployment pattern if the signature of the stored proc changes - parameters or response - then getting it deployed in sync with app code is 3x as difficult as just updating code.

Leave a Reply

Your email address will not be published. Required fields are marked *

Author: admin