March 13, 2012 | SQL Server

T-SQL Bad Habits to Kick : NESQL 3/8

Last Thursday I presented my "Bad Habits to Kick" presentation at the New England SQL User Group.

Attached is the deck.

File Attachment:

8 comments on this post

    • Janos - March 13, 2012, 10:59 PM

      This is a pretty nice collection. I'm using "select *" sometimes, because it is easy to get some sample data with TOP operator from a table 😉
      Item #17 may be the most important one. I'm using different colours for my connections in SSMS along with explicit transactions.

    • Scott McFadden - March 14, 2012, 5:29 AM

      Great slide deck Aaron!  Must admit that I have been guilty a time or two of SELECT *.  I learned my lesson once we added varchar(MAX) columns to our tables and each table contains hundreds of thousands of rows.  This made for some painful re-factoring sessions.

    • Tracy McKibben - March 15, 2012, 4:43 AM

      Great minds must think alike!  I cover about half of these in a presentation that I've given for internal devs, and a trimmed down version coming soon to SQL Saturday #118.  

    • Jerry - March 19, 2012, 6:02 PM

      Hi Aaron.  Good slides, but I have a couple questions on #1, about default cursors.
      1. You have both STATIC and READ_ONLY in your suggestion, but the DECLARE CURSOR entry in BOL states STATIC cursors are READ_ONLY by default.  I wasn't sure if you had experienced otherwise, or just liked spelling ito ut to be clear.
      2. You mention in the first bullet that we want to avoid heavy tempdb usage, but doesn't the STATIC keyword cause a copy of the data to be created in tempdb to satisfy the entire cursor operation?  Seems like that would increase tempdb uage.

    • Jerry - March 19, 2012, 6:04 PM

      That was supposed tob be habit "#11", not "#1".

    • Aaron Bertrand - March 19, 2012, 11:04 PM

      Jerry, READ_ONLY is to be explicit about intent. And while STATIC does use tempdb, it has less impact than, say, KEYSET, and with the typical cursor we're talking about (not amillion rows, and no 2GB XML tuples), it really should be inconsequential. It is also predictable (e.g. dynamic could spill to tempdb). Hugo has done some good performance comparisons in the past, and these are the combined options that have been superior for me overall. I could probably do another roundup.

    • Shawn - April 16, 2012, 11:36 PM

      Hey Aaron, can you expand on one of the bullets in #3?  Specifically: what's the problem with using VARCHAR for proper names?

    • AaronBertrand - April 16, 2012, 11:45 PM

      Shawn, if you have people entering data into your web forms, and their name contains characters which require Unicode representation, you're going to lose their data (and possibly offend them) when you show their name back to them. Consider the following:

Comments are closed.