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


Critique - Common SQL Server Mistakes


Critique - Common SQL Server Mistakes

Author
Message
Tom Thomson
Tom Thomson
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: 26577 Visits: 12506
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 Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151812 Visits: 19455
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
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65296 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GSquared
GSquared
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60471 Visits: 9730
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
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31185 Visits: 8986
- 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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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
Tom Thomson
Tom Thomson
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: 26577 Visits: 12506
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 Guru
SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)SSC Guru (151K reputation)

Group: Administrators
Points: 151812 Visits: 19455
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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15225 Visits: 11848
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.
Tom Thomson
Tom Thomson
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: 26577 Visits: 12506
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
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31185 Visits: 8986
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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