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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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 and how to correct those mistakes. There are edge cases where these might not apply, but for the most part, these are mistakes that you do not don't want to repeat make 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. :-D

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Prasad Behere
Prasad Behere
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 94
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.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4595 Visits: 9529
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 ...


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
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: 36089 Visits: 18738
Good suggestions. I'll add some of these, as well as clean up the abstract a bit.

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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5950 Visits: 8304
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 at GMail
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: 36089 Visits: 18738
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.

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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5950 Visits: 8304
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 at GMail
Shawn Dube
Shawn Dube
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 216
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
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: 36089 Visits: 18738
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.

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
Shawn Dube
Shawn Dube
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 216
Sure... use as you see fit Wink
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