April 18, 2015 at 11:09 am
Comments posted to this topic are about the item Embrace Stored Procedures
April 18, 2015 at 12:19 pm
I read Rob's article when it first came out. What a refreshing change it was from the dogma that I've been reading about for the previous several years before that.
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 12:29 am
Jeff Moden (4/18/2015)
I read Rob's article when it first came out. What a refreshing change it was from the dogma that I've been reading about for the previous several years before that.
Likewise
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
"So what the CEOs, Sales and Project managers email are not in your database then the world will keep spinning!" The old argument between Exchange Admins and DBAs
April 20, 2015 at 1:05 am
Just because a system would use an ORM does not mean that the ORM itself can not use stored procedures.
Jeff Moden (4/18/2015)
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
Turn of the systems that makes use of the db and what value does the db then add? They live in symbiosis.
April 20, 2015 at 2:01 am
IceDread (4/20/2015)
Just because a system would use an ORM does not mean that the ORM itself can not use stored procedures.Jeff Moden (4/18/2015)
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
Turn of the systems that makes use of the db and what value does the db then add? They live in symbiosis.
+1
Also, re:
'Those manipulations might make more sense in the database as a stored procedure rather than trying to ensure every application implements (and updates) the logic in its own code.'
Even if a stored procedure is not employed, multiple applications can still make effective use of a middle layer library that implements the data access. This can then be modified to use a proc if it becomes apparent that this is the best way forward.
I guess this covers it to be fair, and is the most important part at the end of the day. 'Think about whether each call, page, form, whatever makes sense as having the programming in the front end or the back end.'
April 20, 2015 at 5:09 am
Really old discussion: the fact that we are still arguing about that is not a good sign !
I was in Microsoft, it was 10 years ago, and we were in a task force aimed to clearly define the meaning (and the boundary) of therms like "user services", "business services", "data services".
Definition of "user services" was fairly easy. The boundary among"business services" and "data services" not.
To be more precise: stored procedures are "business services" or "data services" ?
The classification difficulty led one of us to state: "I don't understand stored procedures". My answer was (mimicking P.A.M. Dirac): "this is not a question, this is a comment".
When Microsoft went out with the CLR stored procedures, a lot these people stated the the T-SQL stored procedures were dead.
I let you decide who was right .
April 20, 2015 at 7:19 am
So the database is the center of the universe just because turning it off brings the system down? You could say the same thing about a router, a switch, a network cable, a DNS server, a domain controller, your ISP or many other links in the very complex chain that is an enterprise system.
I used to be a big believer in using sprocs as an api for all applications. I still think that is a valid approach for some applications and i have written an application that way recently. But on the whole, sprocs represent units of functionality that are difficult to plug into a unit test framework in a programmer's IDE. Anything that raises the barrier to implementing unit-integrating-load-simulation testing is something that should be avoided until you really prove that you need to use it.
Don't be dogmatic, be smart enough to use the right approach for the problem.
April 20, 2015 at 7:52 am
A router can be replaced.
A corrupt, inaccessible database (unless you have a valid backup) not.
April 20, 2015 at 8:16 am
I started to read this article and didn't get past the first paragraph. Why technical people resort to street language to express themselves is beyond me. I expect that an intelligent person should be able to intelligently articulate their thoughts without the use of such language. I am an avid subscriber to SQL Server Central and have learned a lot from the posts, and would prefer that SQL Server Central NOT reference articles with such language. We are data professionals, and the use of such language is NOT professional. That's my personal opinion, and I suspect that there are others out there that agree with me. I will continue to bypass articles with such language.
April 20, 2015 at 8:28 am
bdenning (4/20/2015)
I started to read this article and didn't get past the first paragraph. Why technical people resort to street language to express themselves is beyond me. I expect that an intelligent person should be able to intelligently articulate their thoughts without the use of such language. I am an avid subscriber to SQL Server Central and have learned a lot from the posts, and would prefer that SQL Server Central NOT reference articles with such language. We are data professionals, and the use of such language is NOT professional. That's my personal opinion, and I suspect that there are others out there that agree with me. I will continue to bypass articles with such language.
Simply put, I agree with you.
April 20, 2015 at 8:32 am
A lot of this is over my head. So I'll just say what we do and why, as far as I know. We use stored procedures everywhere for nearly everything. If you have to do something more than once, call it from more than one place, we put it into a stored procedure. That way if we have to make any sort of change to that code, we don't have to do it in multiple places. We just fix it once and it is fixed everywhere. In those cases where we may want unique behavior, that may require a new stored procedure. That's what we do. Right or wrong it works for us. That's what's important for us, that it works.
April 20, 2015 at 8:37 am
I agree with the previous comment regarding language. We should be able to express ourselves more professional. I'm not above swearing, but I don't swear at others or in writing. Wouldn't cross my mind.
As for the topic, in my universe, it isn't the developers who don't want the business logic in stored procedures. It is I who don't want business logic in the stored procedures, a DBA who used to be a programmer. Applications should be scalable, and the more business logic that is in the stored procedures, the less scalable the application(s) become. The database is a chokepoint. If the logic is in the middle tier, where it should be, and the number of users grows, I can add more app servers. These servers will do the work and send the results to the database. I'm all for using stored procedures for the data retrieval, updates, and deletes. And I will never count on code to maintain my data integrity. That's what referential integrity constraints are for. But the number crunching belongs somewhere else. I have seen first-hand the improvements in performance that comes from moving business logic into the middle tier, and in my view it is irresponsible to advocate this view. This issue was settled a long time ago.
Edited for word style change.
April 20, 2015 at 8:56 am
IceDread (4/20/2015)
Just because a system would use an ORM does not mean that the ORM itself can not use stored procedures.Jeff Moden (4/18/2015)
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
Turn of the systems that makes use of the db and what value does the db then add? They live in symbiosis.
It depends. I don't need for "the website" to be active to continue processing batch data or data from other sources. For example, just because a website for folks to see their bank account data is down doesn't mean that they can't use their credit/debit cards against it.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 8:57 am
Yet Another DBA (4/20/2015)
Jeff Moden (4/18/2015)
I read Rob's article when it first came out. What a refreshing change it was from the dogma that I've been reading about for the previous several years before that.Likewise
To coin a phrase, "If you don't think the database server is the center of the world, turn it off and see what happens." 😛
"So what the CEOs, Sales and Project managers email are not in your database then the world will keep spinning!" The old argument between Exchange Admins and DBAs
Heh... if the database server is down, they'll certainly have a whole lot to talk about. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 9:03 am
I believe the vast majority of SQL Server DBAs and developers understand the virtues of leveraging stored procedures for practically all database access. It's the application developers you have to sell the idea to. I've found it useful to frame the debate within the context of encapsulation, because the vast majority of application developers totally get encapsulation. The developer passes A and B input parameters to the method, web service, stored procedure, or whatever, and they then get resultset C back. They don't really need knowledge of, or create functional dependencies on, the implementation details of the underlying data model or SQL.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 77 total)
You must be logged in to reply to this topic. Login to reply