January 21, 2011 at 11:31 am
Steve Jones - SSC Editor (1/20/2011)
The NULL mistake is that people often write
where a.col = nullinstead of
where a.col is nullThe 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
January 21, 2011 at 11:55 am
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?
January 21, 2011 at 12:57 pm
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.
January 21, 2011 at 1:25 pm
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
January 22, 2011 at 4:55 am
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
Learn to play, play to learn !
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[/url]
- 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
January 22, 2011 at 7:08 pm
Steve Jones - SSC Editor (1/20/2011)
This is a presentation that I have been working on and evolving at a few user groups. Is this abstract ok?Abstract: This presentation looks at a number of common mistakes that people make in SQL Server. There are edge cases where these might not apply, but for the most part, these are mistakes that you do not want to repeat as you are building applications on the SQL Server platform.
The basic agenda:
* SELECT *
* Shrinking databases
* GUID as a Clustered Key
* Functions in the WHERE Clause
* IS NULL
* Multi-row triggers
* Indexing all Columns
* Cursors
* SA Account Usage
Short and sweet... that's nice. Knowing the speaker, I'd go. Not knowing the speaker, I'd select it as something that should be included in a conference (if it were my vote) and then I'd have to do like I do with everyone else... find out what else is in the time slot and make a decision from there.
If it were me, I'd write the abstract as follows (BOLD shows the changes)...
This presentation looks at a number of common mistakes that people make in SQL Server[font="Arial Black"] and how to correct those mistakes[/font]. There are edge cases where these might not apply, but for the most part, these are mistakes that you [font="Arial Black"]do not don't [/font]want to [font="Arial Black"]repeat make [/font]as you are building applications on the SQL Server platform.
As a side bar, I still don't understand why people refuse to use contractions in their descriptions, articles, and elsewhere.
Of course, all of that is an "opinion" and we know how those are. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2011 at 3:11 am
You also can add about
1>Data, Log and tempDB file placement on disk for better performance
2>Usage of bare SQL statements.
3>How to select columns and order of the columns in index for better performance.
4>How to avoid bottle necks of performance like cursors, GROUP BYs and many other
5>How to avoid performance issues in execution plan like Key Lookups, Table scan, index scans.
January 24, 2011 at 7:23 am
There are a lot of mistakes developers make regarding dates and data types; an entire presentation could cover this topic alone.
For example:
- Containing "date" values (ex: 3/10/09, 09/10/03, 3-10) in a VarChar column, which in my opinion is the absolute worst T-SQL anti-pattern possible.
- Using SmallDateTime only to later discover that it only supports a max date of June 6, 2079.
- Doing something like this "... where enrolled_date <= '2011-01-31'" and then assuming the result will include enrollments entered on Jan 31, 2011.
- Inconsistent coding of GetDate() and GetUTCDate() in a datetime column and invalid assumptions by the SQL developer about what local time is contained in the datetime column.
- Etc, ad nauseum ...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 24, 2011 at 7:48 am
Good suggestions. I'll add some of these, as well as clean up the abstract a bit.
January 24, 2011 at 8:16 am
Steve, I don't think a talk abstract can be reviewed unless the level of the topic is also included, along with the target audience (Administrators, TSQL Developers, etc).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2011 at 8:35 am
Kevin, very true. I think this one is mostly a 100 level, sometimes going to a 200 level of stuff. It probably needs to be separated out to Dev and Admin items as it grows.
January 24, 2011 at 9:21 am
Steve Jones - SSC Editor (1/24/2011)
Kevin, very true. I think this one is mostly a 100 level, sometimes going to a 200 level of stuff. It probably needs to be separated out to Dev and Admin items as it grows.
You already have a hodge-podge of dev/admin topics. Probably best to already pick one area or the other for this talk (likely developing 2 separate talks) now since DEV/ADMIN are completely different tracks and interest completely different audiences, at least at SQL Saturday type events. User groups are a bit of a different matter (although you could easily give a DEV-centric session at .NET user groups as I have done a fair bit).
Has anyone mentioned table variables yet? Bad stuff for developers there and easy win perf wise as well as easy to demo too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 24, 2011 at 11:31 am
Steve,
I just took a shot at rewording a bit. I hope it's helpful even if it's just by taking bits an pieces. If not, feel free to disregard:
Abstract:
Best practices for building applications on the SQL Server platform.
We will delve into a number of common anti-patterns used when developing SQL and explore (maybe demonstrate) their adverse impact on your SQL Server.
Come along and take your skillset to the next level.
Shawn
January 24, 2011 at 11:35 am
Shawn,
I like it all but "anti-patterns". I've seen the term, but it's not one I use, and I suspect the people at a beginning level might not know.
I do like the conciseness, and I should aim for something along those lines.
January 24, 2011 at 12:59 pm
Sure... use as you see fit 😉
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply