February 3, 2017 at 7:48 am
Hi. Recently I have attended a couple of interviews. I have faced a similar question about stored procedures. What are the disadvantages of stored procedures in SQL? I was unable to answer. Can someone please enlighten me.
TIA
February 3, 2017 at 7:57 am
I guess if I'd been in that position, I'd have listed a few advantages, before adding that I couldn't really think of any disadvantages. Then I'd have turned it round by asking them whether they use stored procedures, and if not, why not?
John
February 3, 2017 at 7:59 am
susmitha117 - Friday, February 3, 2017 7:48 AMHi. Recently I have attended a couple of interviews. I have faced a similar question about stored procedures. What are the disadvantages of stored procedures in SQL? I was unable to answer. Can someone please enlighten me.TIA
He he, the only appropriate answer is "it depends", tells me more about the ignorance of the person asking the question than anything else, personally I would just cut the interview short if asked such a question as it portrays utter ignorance on the subject.
😎
February 3, 2017 at 8:01 am
i had to google it myself, as my answer would have been to identify the advantages i know of instead...the things i thought of was compiled/faster code,ability to tune, code centralization and reuse, security abstraction....
this link has a few items that it points out as disadvantages:
http://www.seguetech.com/advantages-and-drawbacks-of-using-stored-procedures-for-processing-data/
the ones that stick out for me is portability to other database systems and location of business rules. the other items don't seem all that relevant to me....
Lowell
February 3, 2017 at 8:09 am
Hmm.... well in some cases they're not the right tool for the job, I wouldn't call that a disadvantage so much as a use the tool for the job though.
In that regard, they can't be used as parts of queries in the same way views or TVF's can and some systems just don't interface well when trying to use a SP as a data source. But once again those aren't disadvantages so much as pick the right tool.
February 3, 2017 at 8:27 am
susmitha117 - Friday, February 3, 2017 7:48 AMHi. Recently I have attended a couple of interviews. I have faced a similar question about stored procedures. What are the disadvantages of stored procedures in SQL? I was unable to answer. Can someone please enlighten me.TIA
Do they ask you that question before or after they ask you how you research a problem? Try this... it's an essential tool.
http://bfy.tw/9sBl
All kidding aside, these are the types of things that you need to research after someone asks you. Research can certainly include asking a question on a forum but that doesn't show much initiative on your part and THAT will show up as a fault in your interviews, especially on such a common and easy question to answer. Invest in yourself. Spend some time doing some research. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2017 at 8:46 am
From a performance, security, usability, and maintainability perspective; stored procedures clearly have an advantage over embedded or ad-hoc SQL. My approach to answering this questions would be to think of a scenario where a stored procedure would perhaps have a usability disadvantage from the perspective of a developer. T
he first that comes to mind would be the classic use case where a SELECT statement needs to return a resultset based on multiple search criteria. Embedded SQL allows the application developer to build up a WHERE clause with a complex set of predicates dynamically. Still, it is possible to implement this dynamic SQL functionality within a stored procedure too.
Looking at the issue purely from the perspective of a developer, embedded SQL allows them to code against the database without DBA intervention; meaning that they don't have to request that a stored procedure be coded or at least deployed by the DBA. However, that's not really a limitation of stored procedures but rather a limitation of the process. There are many ways to have a process in place by which developers can code their own stored procedures and then deploy them to DEV or QA using tools like TeamCity, Octopus, or DbUp.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2017 at 9:37 am
Lowell - Friday, February 3, 2017 8:01 AMi had to google it myself, as my answer would have been to identify the advantages i know of instead...the things i thought of was compiled/faster code,ability to tune, code centralization and reuse, security abstraction....this link has a few items that it points out as disadvantages:
http://www.seguetech.com/advantages-and-drawbacks-of-using-stored-procedures-for-processing-data/
the ones that stick out for me is portability to other database systems and location of business rules. the other items don't seem all that relevant to me....
Read through the blog post, portability and location of business rules for me is not really a disadvantage. It really becomes, as others have said, a decision regarding the correct tool for the job. If the database can be accessed by means other than a single application then you may want your business rules in the database so that you have them in one place should (when) they require changes. So it will almost always depend of the business requirements.
February 3, 2017 at 12:57 pm
Eric M Russell - Friday, February 3, 2017 8:46 AMLooking at the issue purely from the perspective of a developer, embedded SQL allows them to code against the database without DBA intervention; meaning that they don't have to request that a stored procedure be coded or at least deployed by the DBA. However, that's not really a limitation of stored procedures but rather a limitation of the process. There are many ways to have a process in place by which developers can code their own stored procedures and then deploy them to DEV or QA using tools like TeamCity, Octopus, or DbUp.
Yes, that's until they reach production(esque) environments where the account shouldn't have access to run any DML code directly and should be handled through stored procedures.
February 3, 2017 at 1:07 pm
People mention portability of code and correct placement of business rules as disadvantages of Stored Procedures.
I've seen far more times systems changing their front end code language than their database systems (not counting upgrades).
What I've seen published as disadvantages are more likely ignorance from people writing them.
February 3, 2017 at 3:50 pm
yeah the items in the article barely merit a nod in acknowledgment , as far as considering them disadvantages.
i couldn't call any of them show stoppers, or identify what relevant alternatives there are.
It was just the first article i found with disadvantages.
Lowell
February 6, 2017 at 9:46 pm
That's an interesting interview question.
I would recommend that you consider advantages and disadvantages from different points of view; an advantage for A is often a disadvantage for B, and it will differ from organization to organization how that plays out overall.
Stored procedures can be changed by a DBA without involving a developer, instantly, at runtime. In what circumstances is that an advantage? A disadvantage.
That question is hopefully intended to evaluate your critical thinking skills; parroting what you read on a forum isn't going to get you very far, so consider it carefully, and if you can and you find it appropriate, run some experiments.
February 8, 2017 at 4:36 am
If not checked staff could include presentation layer logic in the final result set where as that logic would be better placed in the hands of the report tool. This limits the reuse of the procedure.
February 8, 2017 at 7:37 am
When participating in an interview, I'll often ask the candidate open ended questions similar to this. It's a lot more telling than a simple question about "What is a clustered index" how to use a specific T-SQL function. I'm not expecting a specific answer; I'm just wanting to listen to the candidate talk through the problem presented, so I can then ask followup questions and judge the depth of their experience as well as their conversational skills. Having to argue in favor of stored procedures versus embedded SQL is actually an issue the candidate would face on the job, so it's a complicated but entirely fair question.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 8, 2017 at 8:30 am
I'd throw in my vote for not loading up stored procs with business rules. Full disclosure: I have personally loaded up SPs with business logic many, many times and always with open eyes. It's just that, IMO, this counts as technical debt since in many organizations the data layer has the least transparency and it's generally preferable to have the implementation of business rules transparent to the business. When possible, having the business own the business rule implementation using a BRE (and exposing this to client apps via a service) provides the maximum transparency, but not all organizations are in a position to handle it this way (mine is just starting to refactor around this model).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply