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 Saturday, January 22, 2011 7:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 35,371, Visits: 31,914
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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1052014
Posted Monday, January 24, 2011 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 2:25 AM
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.
Post #1052239
Posted Monday, January 24, 2011 7:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 1,708, Visits: 4,854
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 ...
Post #1052352
Posted Monday, January 24, 2011 7:48 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #1052366
Posted Monday, January 24, 2011 8:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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
Post #1052390
Posted Monday, January 24, 2011 8:35 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #1052423
Posted Monday, January 24, 2011 9:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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
Post #1052473
Posted Monday, January 24, 2011 11:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:06 AM
Points: 18, 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
Post #1052597
Posted Monday, January 24, 2011 11:35 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #1052600
Posted Monday, January 24, 2011 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:06 AM
Points: 18, Visits: 216
Sure... use as you see fit ;)
Post #1052646
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse