SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are there such things as SQL heresies?


Are there such things as SQL heresies?

Author
Message
Phil Factor
Phil Factor
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 3161
Comments posted to this topic are about the item Are there such things as SQL heresies?


Best wishes,

Phil Factor
Simple Talk
jetboy2k
jetboy2k
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 75
Great article - In my experience, the subset of "heresies" that should be discussed under the "it depends" clause is almost always substantially larger than the actual heresies. And as you point out, once you start discussing the impact of versions, that subset grows even larger.

There are definitely some hard and fast "don't ever do this/make sure you always do that" rules, but, again, my experience has been that those are the minority. At the same time, as you also noted, sometimes the "it depends" clause is definitely somewhat esoteric, or beyond the skills/experience/needs of the majority of users, in which case it often is easier to say, "treat this as a hard and fast rule." As with almost everything that reaches a certain level of complexity, things almost never fit neatly into a specific category (heck, notice how many times I had to put qualifiers on my statements here Smile)
ben.gijsen
ben.gijsen
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
Wise words Phil. Things would be a lot easier (and musch quieter) if we would only talk about things we actually know about in stead of just "parrotting" words of others (who sometimes don't know what they're talking about but just repeat what they heard from "experts").
However I learn a lot from those comments, even if it's just how NOT to do it :-). Keep an open mind.

Ben Gijsen,

" Life is like a Ferrari, it goes too fast,
But that's okay, you can't afford it anyway "
- Garfield the Cat-
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)

Group: General Forum Members
Points: 870364 Visits: 47424
Really cool article, Phil. Are there heresies in SQL Server or, more specifically for most of what I do, in T-SQL? There sure are and some of them are, indeed, version dependent. I also find that what you said about some article somewhere or some old leftover caution from the past which, IMHO, frequently fall into the categories of "not adequately tested" or "not adequately understood" or "Old Wives' Tale based on previous fact" that's no longer appropriate, remaining or becoming a supposed "Best Practice" is spot on and totally annoying.

Some great examples are of such things are (all of which I use at one time or another)...
The proper use of xp_CmdShell, OpenRowset, and the Trustworthy database setting.
The use of the "Quirky Update".
The ability to overlay a variable in the loop known as a SELECT.
The use of SELECT/INTO.
The proper use of EAVs and NVPs.
Dynamic SQL
The occasional WHILE loop as a non-RBAR control method to support "Divide'n'Conquer".
And virtually everything that some nonqual dismisses with the statement "Just because you can do something in SQL Server, doesn't mean you should". Wink

Then there are those things that have, in the minds of many, become "Best Practices" and they go out of their way to advertise such a thing. Two great and seriously annoying examples of that is all the people that write about using Recursive CTE's to provide an incremental count and people that still think that XML string splitters are the way to go (there is a way but that's not what most people are doing)... and they get seriously hostile if you attempt to prove to them that the opposite is true and that their supposed "Best Practice" is a "Worst Practice".

Heh... on that same roll, I "love" it when people say it's a "Best Practice" to base your Clustered Index on the most common queries for the table or to generally avoid the use of IDENTITY as the Clustered Index or that you should never have a Non Clustered Index that duplicates the keys of the Clustered Index or that Temp Tables should never be used or that Dynamic SQL must never be used, etc, etc. Newbies see such rubbish coming from supposedly "experienced" people and take it to the bank not knowing what the actual possibilities are because they've not yet learned that no one knows everything about SQL Server and that "It Depends" is some of the best advice that you'll ever get.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
call.copse
call.copse
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14116 Visits: 2410
Nice post Phil (and follow on Jeff).

The corollary could be - are there any actual best practices? In general there's a bunch of stuff you can generally do that will mean you won't go too far wrong. I can't say there's many things you can say - you should invariably do this...
jonathan.crawford
jonathan.crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4664 Visits: 1369
call.copse - Monday, September 24, 2018 4:54 AM
Nice post Phil (and follow on Jeff).

The corollary could be - are there any actual best practices? In general there's a bunch of stuff you can generally do that will mean you won't go too far wrong. I can't say there's many things you can say - you should invariably do this...


Here's one: Set SQL Coding standards with justification for each one and follow them. Wrong/right/otherwise, be consistent.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
ebattermann
ebattermann
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 273
So much truth in those two words: "It depends." Smile I repeat these words often when discussing SQL-related topics with my coworkers.
kiwood
kiwood
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 119
Good luck stomping them out. Far too many people refuse to let go of their outdated information. I recall a particular instance in 1996 when someone was instructing new support people that the only time a drive could be safely set to boot was when originally formatted. Of course showing her documentation from years earlier when that changed was of no use.And when it was demonstrated that one could do it, well I just got lucky. Over and over.

I would love to know more about only declaring variables near the top. I had assumed that was held over from the days of compiler requirements. Some still say that should be the cast in C# even though many others insist a variable should be declared as close to first use as possible.
RonKyle
RonKyle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26901 Visits: 4461
I would still caution that too many "it depends" becomes "I'll do it like I want without understanding why the best practice is the best practice." Just an observation that I've made all too often in real life. Maybe better to phrase "normally this way but we need an exception here because..." I occasionally use NOLOCK under very select circumstances. But I don't proceed from there to say "it depends." I would say NOLOCK is generally a bad idea because..... However, in this case it's okay because ….. That's not the same as "it depends."



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)SSC Guru (870K reputation)

Group: General Forum Members
Points: 870364 Visits: 47424
RonKyle - Monday, September 24, 2018 8:02 AM
I would still caution that too many "it depends" becomes "I'll do it like I want without understanding why the best practice is the best practice." Just an observation that I've made all too often in real life. Maybe better to phrase "normally this way but we need an exception here because..." I occasionally use NOLOCK under very select circumstances. But I don't proceed from there to say "it depends." I would say NOLOCK is generally a bad idea because..... However, in this case it's okay because ….. That's not the same as "it depends."


That's also how new and terrible "Best Practices" are born. And, yes, I agree.... "It Depends" is correct but can be abused like anything else. Eventually, people need to get something done and that means that someone eventually has to make a decision. As we all know, it's far too often that decision is made by people that aren't actually qualified to make such a decision.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search