Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Critique - Common SQL Server Mistakes Expand / Collapse
Author
Message
Posted Thursday, January 20, 2011 6:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1051273
Posted Thursday, January 20, 2011 9:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:42 AM
Points: 31,284, Visits: 15,747
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
Post #1051308
Posted Friday, January 21, 2011 12:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,545, Visits: 11,359
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




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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1051361
Posted Friday, January 21, 2011 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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?


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
Post #1051528
Posted Friday, January 21, 2011 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 6,743, Visits: 8,517
- 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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1051618
Posted Friday, January 21, 2011 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1051735
Posted Friday, January 21, 2011 11:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:42 AM
Points: 31,284, Visits: 15,747
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
Post #1051745
Posted Friday, January 21, 2011 12:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, November 23, 2014 12:50 AM
Points: 3,109, Visits: 11,515
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.




Post #1051779
Posted Friday, January 21, 2011 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1051799
Posted Saturday, January 22, 2011 4:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 6,743, Visits: 8,517
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


Johan


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1051942
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse