Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Critique - Common SQL Server Mistakes


Critique - Common SQL Server Mistakes

Author
Message
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10733 Visits: 12019
Question: why do you think multi-row triggers are a mistake? My view is that if you need triggers then single row triggers are usually a mistake, but multi-row triggers are fine.

Tom

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36206 Visits: 18751
The NULL mistake is that people often write

where a.col = null



instead of

where a.col is null



The multi-row trigger is the same. Developers assume triggers fire once per row, instead of once per transaction. I have examples of why this is an issue.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Koen Verbeeck
Koen Verbeeck
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: 16467 Visits: 13207
Steve Jones - SSC Editor (1/20/2011)
The NULL mistake is that people often write

where a.col = null



instead of

where a.col is null





Oh allright. Pfieuw, I was afraid I had to rewrite all my code :-D



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
GSquared
GSquared
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: 14383 Visits: 9729
Koen (da-zero) (1/20/2011)
I would go to a session like this one.
I'm always eager to learn from more experienced people who have already learned from mistakes, so that I can avoid them :-)

One question: what is wrong with IS NULL? Blush


Joe Celko personally dislikes it. I think it said something mean about his shoes one day.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6976 Visits: 8839
- why all your columns by default should be NOT NULL ! unless you really really really need to know no value has been provided (and the datatype default doesn't suit your datamodel.)

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10733 Visits: 12019
Steve Jones - SSC Editor (1/20/2011)
The NULL mistake is that people often write

where a.col = null



instead of

where a.col is null



The multi-row trigger is the same. Developers assume triggers fire once per row, instead of once per transaction. I have examples of why this is an issue.

So the common mistake is that some dim spark writes a single row trigger when a multiple row trigger is needed. That would make me list single row triggers as a common mistake, not multi-row triggers; do you really think it makes multi-row triggers a mistake?

Tom

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36206 Visits: 18751
Good point. I'll change the wording to say what the mistake is. Do you think

- writing single row triggers

or

- not coding triggers for multiple rows

is better?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
I think the big mistake that beginners make with triggers is using them at all. They see them as a sort of event driven solution, and don't really understand the implications.

Triggers are kind of a bad practice of first choice for beginners, while experienced developers avoid them whenever possible.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10733 Visits: 12019
Steve Jones - SSC Editor (1/21/2011)
Good point. I'll change the wording to say what the mistake is. Do you think

- writing single row triggers

or

- not coding triggers for multiple rows

is better?

I think "not coding triggers for multiple rows" is probably best.

Also, Michael Valentine Jones has a point about people using triggers when they are not the right thing to use.

Tom

ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6976 Visits: 8839
Michael Valentine Jones (1/21/2011)
I think the big mistake that beginners make with triggers is using them at all. They see them as a sort of event driven solution, and don't really understand the implications.

Triggers are kind of a bad practice of first choice for beginners, while experienced developers avoid them whenever possible.



Very good point !

other common caveats:
- sending mails form triggers
- forgetting triggers are in-transaction and complaining when rollbacks occur
- triggers doing xp_cmdshell stuff
- how about doing some clr stuff in triggers, please use a web service to add some data ... making it depending on stuff you cannot control Sick

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
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