April 20, 2015 at 4:54 pm
Andrew..Peterson (4/20/2015)
...we cannot come to any unified agreement as to what "Best Practices" should be with regards to stored procedures.
I think part of this is two-fold. 1: Every situation is different, so you can't apply blanket statements: what works best in one situation is inappropriate in another. 2: As "we" (experienced SQL Server people) know, there are frequently many ways to do things. I just posted a question regarding doing something in a CTE that I was sure could be done using conventional joins, I just couldn't find how.
We can say, in general, that 'these' are Best Practices, but there will always be exceptions. And if people need to or want to use ORM and NoSQL methodologies, then we cope and see if we can subvert the dominant paradigm and convert them to better relational designs. :Whistling:
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 20, 2015 at 6:16 pm
IceDread (4/20/2015)
Jeff Moden (4/20/2015)
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. 😀
I'm not sure if you are serious or just provocative for fun.
Lets take your agrument a bit further, if you are the guy that turns of the db I'll recreate it else where and since I have backup redundancies it wont take much of an effort.
The db in most systems is next to useless without it's logical components and user interfaces. In today's world, in the companies I've seen in Sweden, only old school people who likes to focus only on one area (db) puts logic into the database. All thou to be fair, data processing and calculations could also be named logic, but you dont want the logical components for validation and busies rules, temporary campaigns etc in the database now do you? That would be clumsy compared to the simple alternatives.
The database is important, and I like working with them, but way too many people get too attached I'd say and thus does not look up to see the horizon and can not put together the big picture.
Do you sometimes forget that the db is not onlly meant to be an effective master piece but meant to be there to severe the interests of a company / organization?
Now how's that for provocative form you're point of view? 😉
To be clear, I absolutely agree that most databases would be useless without it's logical components and user interfaces. In most cases, the reverse is true as well. 😛
My whole poke at the crowd is based on the fact that I've had many people tell me that we should not use T-SQL to do ETL, not use T-SQL to do ETL file handling, not use T-SQL to import files that are supposedly "impossible" to import, not to use T-SQL to send critical people nicely formatted HTML reports on the status of every disk in the Enterprise, and even tell me to not use T-SQL to calculate simple durations for reporting. Their arguments are "SQL Server isn't the center of the universe" and "Just because you can do something in SQL, doesn't mean you should". My reply to the first was the quote that got so many worked up and the reply to the second is in my signature line below.
Even with the off-color wording, I found the article that a lot of folks are complaining about very refreshing especially since it appeared to be someone from the front-end world that get's it and a good portion of my time is fixing stuff where people don't get it but still manage to attempt to convince others about how right they are on the subject.
For me and in all cases, "It Depends". Again, that's why I sit in the middle of the Dev group. When I spy a performance or resource usage problem, they're eager to help. When they think they might be writing a performance problem or have some difficulty, whether it's embedded SQL, ORM-produced SQL, or a stored procedure, etc, we hammer it out before it becomes a problem or before any of us spend too much time chasing windmills. They all get it just like the fellow that wrote the off color article and I was tickled pink to see the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2015 at 7:44 pm
Interesting discussion.
I think Conery's article is excellent, never mind the phrasing look at what is being said.
It's a pity that some people seem to believe that using stored procedures for all access implies putting application logic that belongs in a higher layer in the data layer; that's such a crazy distortion of the facts that I think that anyone who believes it must be suffering from some combination of three delusions: (a) the function of stored procedures is to grab control of all the logic and not to ensure that the interface between the data layer and the software that uses it can not be manipulated in such a way as to prevent changes within the data layer that ought to be invisible to its users; (b) the mantra "no business logic in the database" means stored procedures can't be allowed to happen; and (c) data integrity is no concern of the data layer, it's the applications' business.
I started advocating that all access to data in a shared database should be through stored procedures a very long time ago - in fact I've done it ever since I realised (a long time ago, before there was any such thing as MS SQL Server) that we weren't going to see a fully declarative relational calculus based on the functional programing paradigm replace SQL at any time in the near future. Of course some databases aren't shared, they are the private data of some individual, and there it may not (depending on the individual concerned and what he wants of his data) be appropriate to apply the rule.
The "no business logic in the database" argument is regularly used to doom projects to failure by assigning data integrity maintenance to the front end and forbidding the use of constraints in the database "because they damage performance" (when in practise they usually improve it) and rejecting normalisation because (a) it wastes disc space (it usually saves it) and (b) it requires those nasty constraint things, so it's putting business logic into the database.
Some of the comments I've seen here suggest to me that plenty more catastrophes may be waiting to happen because the developers and dbas who made those comments could be ensuring that it's inevitable that data access will be the "cluster-fucked echo chamber of half-assed rocket engineering and cargo cultism based on decade-old cathedralized thinking and corporate naval -gazing" mentioned by Mr Conery.
(what naval-gazing as opposed to navel-gazing might be is beyond a landlubber like me, perhaps Jeff can tell us as submarines are somewhat naval?)
edit:correct the English
Tom
April 21, 2015 at 1:38 am
Wow. So much has been said here. Where to begin?
Language in the original article
I unplugged my headphones so that my wife could "read" the article right along with me. She said that he sounded just like a lot of the folks that I worked with every day. Agreed, but we were told never to use such phrases when we were on the phone with clients or vendors. This language discussion is a distraction from the points raised in the article. I agree that refraining from "street speech" is a good thing to do in articles and forum posts especially since this is an international audience. Yet I will overlook such in order to glean the meaning behind the phraseology much the same way that I would if I were meeting a person on the street who needed help.
Never and Always
These are words that I am beginning to despise as much as others despise words in the original article. If cursors are so bad why are they still allowed? Is there a move to deprecate them?
Only access data via a stored procedure? Best argument that I have heard for this point is security. With the security features built into SQL Server I don't see that a so strong of a point. Now that it means that there is a greater likelihood that your use of a procedure will use the cached plan of the last use of this procedure I get.
That you can do more advanced parameter checking in a procedure I agree with. I just don't enjoy the language as much as I do C#. I could write all my procs in the CLR. Performance would be great and I have all of these nifty functions right in the framework. Joyous, right?
Dataspacing and future scaling
These are a couple of my hot buttons. Did somebody mention 50 states? I worked at USDA and they have 54 state codes. I was alive in 1960 when we went from 48 to 50.
Are you storing IP addresses in 4 tiny ints? What happens when you have to use a V6 address?
In the U.S. we have three digit area codes for phone numbers. The old dialing rules stated that the middle digit was either a 1 or a 0. That went away a while back. There was a rule that an area code boundary could not cross a state line. That might go away soon. I am starting a new company and the phone providers tell me that I can pick my area code and the local dialing scope that I want to serve
You will see a UPC barcode on the products that you buy. They have twelve digits but you can strip off the first and last and use the remaining 10. I hope that you re not storing them a NULLable CHAR(10). As of January 2015 most systems are required to be able to handle EAN-13 codes.
We have been out of 9 digit Social Security Numbers for quite a while. Old ones are being reissued making the whole identity theft thing even harder to deal with.
You know your 9 digit ZIP Code (tm), right? You have one. These codes change with growth in areas. Don't assume that one at your house won't ever change.
What you assume to be hard and fast turns to quicksand right under your feet.
I have been bitten by things like this too. But I learned. Don't let it happen to you.
ATBCharles Kincaid
April 21, 2015 at 8:39 am
TomThomson (4/20/2015)
Interesting discussion.I think Conery's article is excellent, never mind the phrasing look at what is being said.
It's a pity that some people seem to believe that using stored procedures for all access implies putting application logic that belongs in a higher layer in the data layer; that's such a crazy distortion of the facts that I think that anyone who believes it must be suffering from some combination of three delusions: (a) the function of stored procedures is to grab control of all the logic and not to ensure that the interface between the data layer and the software that uses it can not be manipulated in such a way as to prevent changes within the data layer that ought to be invisible to its users; (b) the mantra "no business logic in the database" means stored procedures can't be allowed to happen; and (c) data integrity is no concern of the data layer, it's the applications' business.
I started advocating that all access to data in a shared database should be through stored procedures a very long time ago - in fact I've done it ever since I realised (a long time ago, before there was any such thing as MS SQL Server) that we weren't going to see a fully declarative relational calculus based on the functional programing paradigm replace SQL at any time in the near future. Of course some databases aren't shared, they are the private data of some individual, and there it may not (depending on the individual concerned and what he wants of his data) be appropriate to apply the rule.
The "no business logic in the database" argument is regularly used to doom projects to failure by assigning data integrity maintenance to the front end and forbidding the use of constraints in the database "because they damage performance" (when in practise they usually improve it) and rejecting normalisation because (a) it wastes disc space (it usually saves it) and (b) it requires those nasty constraint things, so it's putting business logic into the database.
Some of the comments I've seen here suggest to me that plenty more catastrophes may be waiting to happen because the developers and dbas who made those comments could be ensuring that it's inevitable that data access will be the "cluster-fucked echo chamber of half-assed rocket engineering and cargo cultism based on decade-old cathedralized thinking and corporate naval -gazing" mentioned by Mr Conery.
(what naval-gazing as opposed to navel-gazing might be is beyond a landlubber like me, perhaps Jeff can tell us as submarines are somewhat naval?)
edit:correct the English
+a billion.
Naval-gazing is just what you would expect whether you're in the Navy or not. The neat thing about doing it in the Navy is that there's usually enough time to pluck the observed t-shirt lint from it and save it to make a very personal pillow not likely to be useful to others. That's also a powerful metaphor for what "corporate naval-gazing" means. 😀 Really good corporate naval-gazers are also skilled at the meaningless skill of inserting a cherry in their naval, calling everyone's attention to it, and then doing some ridiculous move to shoot it across the room... lint included. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2015 at 8:55 am
Charles Kincaid (4/21/2015)
We have been out of 9 digit Social Security Numbers for quite a while. Old ones are being reissued making the whole identity theft thing even harder to deal with.
I know it's off topic but, unless you have some serious proof to the contrary, that's a myth. Please see Q20 from the official Social Security Website of http://www.ssa.gov/history/hfaq.html where it clearly states...
[font="Arial Black"]Q20: Are Social Security numbers reused after a person dies?[/font]
A: No. We do not reassign a Social Security number (SSN) after the number holder's death. Even though we have issued over 453 million SSNs so far, and we assign about 5 and one-half million new numbers a year, the current numbering system will provide us with enough new numbers for several generations into the future with no changes in the numbering system.
... and considering that it's a 9 digit number (1 less than a billion), I agree with their assessment that it will take several generations to exhaust the numbering system. At that point, they could add either a single digit or change the leading digit to alpha-numeric and go for another several centuries with no changes.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2015 at 9:10 am
I feel like I'm on a Fark.com comment thread and should be making a snarky comment about making popcorn.
Are we really still having this conversation in 2015 about business logic, ORMs, and the "proper" place to locate code and logic and rules? I guess we are, and probably for the same reasons.
And am I the only one on here who howled out loud at this gem from the linked article:
You build applications to generate data. That data is your value, that value brings in the money. There is nothing else and if you think safeguards for this data belong anywhere else other than cuddled up next to your data with a machine gun and flamethrower you’re out of your f***ing mind.
I'm sorry, but I love the imagery of RI and SPs cuddled up next to my data with vicious tools of protectiveness. Come to think of it, I think that might be my job description.
Lest anyone leap to contradict him, of course it's an exaggeration: a well architected database accessed via a terribly written application won't build a business. But that wasn't his point, and it's deliberate misunderstanding to suggest it was. The point is, these days since the days of the LEO computer, your data is your business, and if you allow business logic to become embedded in combobox dropdown generators, you deserve what you get IMHO.
Rich
April 21, 2015 at 9:46 am
The "no business logic in the database" argument is regularly used to doom projects to failure by assigning data integrity maintenance to the front end and forbidding the use of constraints in the database "because they damage performance" (when in practise they usually improve it) and rejecting normalisation because (a) it wastes disc space (it usually saves it) and (b) it requires those nasty constraint things, so it's putting business logic into the database.
There's some good stuff in the entire reply from above. However, to clear any misunderstanding, "no business logic in the database" does not preclude Referential Integrity constraints. I have heard either than the "application takes care of that" or "the ETL takes care of that." Inevitably I find that that tables without primary keys (which can mean without using the natural key when a suitable one is available) leads to duplicates and tables without the proper foreign key relationships lead to orphans. I have also heard the "because they damage performance," to which my reply is the first order of the day is to guarentee the integrity of the data. But all this is not what is meant by business rules.
If there is a stored procedure that loops through a data set to determine a particular output based on business rules, then more than likely this is work that should be done in the application layer. If there is a stored procedure that is applying tax rates and reworking totals, then more than likely this is work that should be done in the application layer. If the web services simply take information from the presentation layer and call a stored procedure with it, there's probably a design issue.
It's not because the stored procedure can't do it, although programming languages are more suited to looping and row by row alterations. The reason is because if the user base expands it is much easier to add more application servers and spread the computational load. The application layer can then format the data and apply the necessary inserts/updates/deletes to the database, usually though not necessary exclusively through stored procedures. The reply that a second database server could be set up seems to create an unnecessary complexity. Setting up a second app server is an easy operation.
It may be that a multiple application server setup is less common than I think, but having had one, I can appreciate the benefits of this arrangement, and one all the programmers with whom I work are in complete agreement.
//Edited for grammer
April 21, 2015 at 10:11 am
rmechaber (4/21/2015)
I feel like I'm on a Fark.com comment thread and should be making a snarky comment about making popcorn.Are we really still having this conversation in 2015 about business logic, ORMs, and the "proper" place to locate code and logic and rules? I guess we are, and probably for the same reasons.
And am I the only one on here who howled out loud at this gem from the linked article:
You build applications to generate data. That data is your value, that value brings in the money. There is nothing else and if you think safeguards for this data belong anywhere else other than cuddled up next to your data with a machine gun and flamethrower you’re out of your f***ing mind.
I'm sorry, but I love the imagery of RI and SPs cuddled up next to my data with vicious tools of protectiveness. Come to think of it, I think that might be my job description.
Lest anyone leap to contradict him, of course it's an exaggeration: a well architected database accessed via a terribly written application won't build a business. But that wasn't his point, and it's deliberate misunderstanding to suggest it was. The point is,
these dayssince the days of the LEO computer, your data is your business, and if you allow business logic to become embedded in combobox dropdown generators, you deserve what you get IMHO.Rich
Totally agree. Dibs on the flamethrower!
The more you are prepared, the less you need it.
April 21, 2015 at 1:05 pm
Thanks Jeff. I had read it somewhere but forgot the source. I need correcting from time to time.
Of course if they change to alpha on any of the places then that opens the dataspace range a lot.
ATBCharles Kincaid
April 21, 2015 at 8:18 pm
Andrew..Peterson (4/21/2015)
rmechaber (4/21/2015)
I feel like I'm on a Fark.com comment thread and should be making a snarky comment about making popcorn.Are we really still having this conversation in 2015 about business logic, ORMs, and the "proper" place to locate code and logic and rules? I guess we are, and probably for the same reasons.
And am I the only one on here who howled out loud at this gem from the linked article:
You build applications to generate data. That data is your value, that value brings in the money. There is nothing else and if you think safeguards for this data belong anywhere else other than cuddled up next to your data with a machine gun and flamethrower you’re out of your f***ing mind.
I'm sorry, but I love the imagery of RI and SPs cuddled up next to my data with vicious tools of protectiveness. Come to think of it, I think that might be my job description.
Lest anyone leap to contradict him, of course it's an exaggeration: a well architected database accessed via a terribly written application won't build a business. But that wasn't his point, and it's deliberate misunderstanding to suggest it was. The point is,
these dayssince the days of the LEO computer, your data is your business, and if you allow business logic to become embedded in combobox dropdown generators, you deserve what you get IMHO.Rich
Totally agree. Dibs on the flamethrower!
MMmmmmmm.... pork chops and flamethrowers... now THERE's a good combo! 😀 Working now to modify my high velocity pork chop launcher.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2015 at 2:47 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.
I don't in any way disagree with you choosing not to read it because you dislike the use of language, entirely your right, and up to you. I profoundly disagree with you that no-one else should have their attention drawn to it, just because you have a problem. That I actually find rather more offensive than the language in the article.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 22, 2015 at 6:59 am
I in no way stated "don't read this article" but instead it was a soft plea to SQL Server Central to not reference such articles. Doing so increases the "hits" on the site, and indicates acceptance of the language. By posting my comment (my first on this site), I've been notified of updates and have enjoyed the discussion. It is obvious that there are very intelligent people out there from whom I can learn, even new words and phrases ("ad hominem" -- thanks Jeff, I had to look that one up! :-)). I was mostly interested in how Steve as editor might respond (or not respond). This is my 2nd post (at the risk of being flamed further) -- others have expressed their opinions and others have been "off subject". I apologize to those who've thought I've been "passive aggressive" and "more offensive" as I never wanted to offend anyone, but wanted to express my personal opinion to SQL Server Central. If there is a better venue for doing so, please let me know (Steve?) -- otherwise, end of discussion.
April 22, 2015 at 9:07 am
bdenning (4/22/2015)
I in no way stated "don't read this article" but instead it was a soft plea to SQL Server Central to not reference such articles. Doing so increases the "hits" on the site, and indicates acceptance of the language. By posting my comment (my first on this site), I've been notified of updates and have enjoyed the discussion. It is obvious that there are very intelligent people out there from whom I can learn, even new words and phrases ("ad hominem" -- thanks Jeff, I had to look that one up! :-)). I was mostly interested in how Steve as editor might respond (or not respond). This is my 2nd post (at the risk of being flamed further) -- others have expressed their opinions and others have been "off subject". I apologize to those who've thought I've been "passive aggressive" and "more offensive" as I never wanted to offend anyone, but wanted to express my personal opinion to SQL Server Central. If there is a better venue for doing so, please let me know (Steve?) -- otherwise, end of discussion.
I have a similar personal preference that I don't see a need to have strong language in technical posts. Likewise, I don't care much for comedians who use lots of such in their acts. I'm not a prude, I just feel that it's telling me that this is not a person whom I would hire to conduct a training class in a professional setting. Strong language is for strong emotion, and when you're writing a tech article, even if it was about something upsetting that happened (recovering a system borked by ritual idiocy, for example), you should be in a reasonably calm state of mind before you start writing.
I think the NSFW tag, or whatever it was that Steve put on it, was fine. I use such language occasionally, though I try to moderate it and not use it in public and only use it around people who talk like that. Language is a tool for conveying ideas and information, words like that are nulls and only convey intensity as far as I'm concerned.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
April 22, 2015 at 11:06 am
There is another point worth considering: this blog has an international audience.
My English is only technical, and I have to say that I'm unable to solve crosswords, I have a lot of problems with the Woody Allen movies and it's very hard to me to understand the Frank Zappa's songs.
So, to be able to understand that paper, I had to read it two times. And still then I'm sure I have not captured all the subtleties.
Apart that I am an (exclusively) strong supporter of the stored procedures.
That is, given any business problem, I've never seen a situation where it was worthwhile not to use them.
Viewing 15 posts - 46 through 60 (of 77 total)
You must be logged in to reply to this topic. Login to reply