T-SQL Bad Habits to Kick : NESQL 3/8
March 13th, 20128
T-SQL Bad Habits to Kick : NESQL 3/8
March 13th, 20128
 
 
By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

8 Responses

  1. AaronBertrand says:

    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 this example.

  2. Shawn says:

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

  3. Aaron Bertrand says:

    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.

  4. Jerry says:

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

  5. Jerry says:

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

  6. Tracy McKibben says:

    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.  

  7. Scott McFadden says:

    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.

  8. Janos says:

    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.