Last Thursday I presented my "Bad Habits to Kick" presentation at the New England SQL User Group.
Attached is the deck.
File Attachment: NESQL_AB_BadHabits.pptx.zip
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.
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.
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.
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.
That was supposed tob be habit "#11", not "#1".
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.
Hey Aaron, can you expand on one of the bullets in #3? Specifically: what's the problem with using VARCHAR for proper names?
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.