Michael J. Swart

November 28, 2011

SQL Fingertips

They Might Be Giants (TMBG) released an album called Apollo 18. It has 38 tracks and it runs for 42 minutes. It’s an album that will always remind me of my friend and first University roommate (Hey, what’s up Chris!). So the average track is about a minute long and this is due to a set of 21 tracks collectively called “fingertips”. Most of these fingertips are five to fifteen seconds long. Some are a bit longer. These fingertips are probably not good enough to be put on an album on their own. But collectively, they make a really cool album (especially on shuffle mode).

The List

So when I found myself with a collection of tiny tips that might not qualify as a blog post on their own, I thought of that album and a microsecond later, we’ve got SQL Fingertips!

So here they are, in no particular order.

  • Stackoverflow tip: Use the bounty feature without being afraid of the hit to your reputation. A bounty offered on a decent question usually attracts enough attention to get your question up-votes. And unless it’s a crummy question, those up-votes will earn you back the reputation points you spent.
  • T-SQL tip: You can use the following script as a template to be able to run a single query on multiple databases and have it return a single result set:
    DECLARE @temp TABLE (
    	ServerName sysname,
    	DBName sysname,
    	ProductCount int
    );
     
    insert @temp (ServerName, DBName, ProductCount)
    exec master..sp_MSforeachdb N'use [?];
    	if exists (select 1 from sys.tables where name = ''Product'' and SCHEMA_NAME(schema_id) = ''Production'')
    		exec sp_executesql N''
    			select @@servername, db_name(), Count(1) as [count]
    			from Production.Product''';
     
    select * from @temp;
  • SQL Server Management Studio tip: GO can take a parameter. If you write GO 100, your batch will be executed 100 times. Many people know this but I always here a couple “wow”s each time I demonstrate it. And here’s a couple more notes about that batch separator.
    • Don’t forget to SET ROWCOUNT OFF.
    • The batch separator doesn’t have to be GO. It’s configurable! I used to set it to ENGAGE for a while, but the joke got old and I switched it back.
  • Blog tip: If you like following blogs or people, but maybe you’re falling out of love with your RSS reader. No worries. You can get any RSS feed at all sent straight to your inbox.  There are lots of RSS-to-inbox websites out there. The one I recommend is FeedMyInbox.com. So, for example, to get my own blog delivered to your inbox, start here.
  • Data → Eye → Brain, A Data Visualization tip: Data visualizations (charts, diagrams, etc..) help us gain knowledge and understand data that maybe we couldn’t understand otherwise (See Anscombe’s Quartet). And for large and complex datasets, the best visualizations are usually custom designed and interactive. There’s a lot of power in customized visualizations: See what David McCandless and Miriah Meyers have to say.
  • Twitter DBA Tip #1: I put a call on twitter asking people to tweet the one database tip they thought more people should know. The first one comes from @BrentO: “Don’t leave your database in full recovery mode if you’re not doing t-log backups.” He’s right. Full recovery mode usually implies a backup strategy that includes transaction log backups. (And full or differential backups are not enough either).
  • Twitter DBA Tip #2: This one’s from @wnylibrarian“If using SQLExpress & no SQLAgent, try using SQLCMD in a batchfile.” I’m glad Gary tweeted this one. I’ve come up against this problem in my own work. It turns out that when downgrading editions of SQL Server, often one of the first things that you miss is SQL Agent. If you follow Gary’s advice, it’s not a hurdle, it’s only an inconvenience.
  • Twitter DBA Tip #3: Next I heard from @DaveH0ward who wants to remind people that “You need to size your data /log files and set autogrowth amounts appropriately.” I think a lot of people don’t know that the default auto-growth settings for SQL Server databases are a bad idea. Tim Ford warns us of dire consequences for not following this tip.
  • Twitter DBA Tip #4: Finally I heard from @SQLSandwhiches (aka Adam Mikolaj). He shared a pet peeve “I’ve seen so many servers that don’t have logs to auto cycle. Don’t you love opening a log file with 300,000 entries?” Adam wrote about his experience earlier this year (including a solution of course).
  • MixedTape Fingertip Tip: Say you find yourself back in the nineties making a mixed tape and you find that you have a minute left to fill on one side of the tape. They Might Be Giants to the rescue! Pick several fingertips from their Apollo 18 album and optimize every second of that mixed tape! My personal favorite is the 45 second Minimum Wage off of their other album Flood.

So how many of these SQL Fingertips did you know? Have you got a small tip? Share it in the comments. Have you got a whole bunch of small tips? Write your own SQL Fingertip post. I’d be especially grateful to read a set of Business Intelligence fingertips.

13 Comments »

  1. One more tip. I heard a comedian once give this advice “Start a set with your second best joke and end with your best joke.” That doesn’t just apply to jokes. That applies to lists like the one above. Or arguments you want to use to make a point in an essay.
    I even see it in movies. Most action movies (And every James Bond movie) will start with an exciting action sequence that is only outdone by the climactic action sequence at the end.

    Comment by Michael J. Swart — November 28, 2011 @ 11:45 pm

  2. The surprising thing about Fingertips is that they often play it live. Heard it this summer in Toronto and it was quite amusing, they pulled it off pretty well.

    Here’s my tip – use ISO format (yyyymmdd) for dates whenever possible. Your regional settings become less relevant and you get used to it really quickly.

    Comment by Brian K — November 29, 2011 @ 8:43 am

  3. AAARGH!!!. Missed opportunity!! (I live in Waterloo, an hour from Toronto). That would have been so cool.
    I love that YYYYMMDD-date-literal tip. I saw it first in a demo by Itzik Ben Gan. I like it too because it sorts alphabetically and it’s easy to remember.
    Thanks Brian

    Comment by Michael J. Swart — November 29, 2011 @ 8:48 am

  4. Great post as usual, Michael :-)

    I’ve always enjoyed TMBG – Fingertips or full songs just the same. I probably have a handful of fingertips rattling around in my head… One I’d add here – “Look at the execution plan and IO statistics before saying ‘ship it!’ the application performance you save, may be your own”

    Comment by Mike Walsh — November 29, 2011 @ 1:59 pm

  5. Good job Mr. Walsh.
    I’d love to see the others rattling around in your head (maybe someday on StraightPathSQL?)

    Comment by Michael J. Swart — November 29, 2011 @ 2:37 pm

  6. Great post. Very informative. Some of these things seem obvious until you miss one!

    Comment by Joe Pearson — November 30, 2011 @ 5:33 pm

  7. Queuing up Flood on spotify now!

    Comment by Claire — December 1, 2011 @ 11:41 am

  8. @ Joe. I know. It almost reminds me of that saying about just how common common sense is.
    @ Claire, Great choice, I have to dig that album out again too.

    Comment by Michael J. Swart — December 1, 2011 @ 1:01 pm

  9. [...] SQL Fingertips - Sharing a wholse assortment of tips this week, it’s Michael J. Swart (Blog|Twitter). [...]

    Pingback by Something for the Weekend – SQL Server Links 02/12/11 — December 2, 2011 @ 6:02 am

  10. Very good and informative.

    Keep it up. Want to add a small thing.

    Sometime it happens we connect production database server and then due to some other task forget that we are working on production, start running queries, delete something unintionally and then …………….oh noooooooooooooo. one possible solution is to change the color of the SSMS status bar. While connecting to the server click options and change the color so that you can differentiate production server.

    Comment by Tahir — December 3, 2011 @ 10:04 am

  11. I’ve been there Tahir. And I use connection coloring now because of that reason.
    Luckily I learned that lesson early in my career. I accidentally deleted a table from a development database rather than my local database. It could have been a lot worse, but it did cause some headaches for some other developers I worked with.

    So another thing I’ve done is to use SSMS Tools Pack. That tool has a feature called “New Query Template”. It means that every time you open a new query window. You don’t get a blank page, instead you get:

    BEGIN TRAN
     
    ROLLBACK

    Comment by Michael J. Swart — December 5, 2011 @ 9:19 am

  12. Out of the loop for a few weeks but Minimum Wage was probably my favorite off Apollo 18 too. I recently repainted my home office/spare room while listening to Flood and a few other TMBG albums. Still holds up to me.

    Comment by Ansley — January 3, 2012 @ 10:46 pm

  13. [...] couple of months ago, you wrote a blog post about “SQL Fingertips” Any new, fresh-for-2012 fingertips you’d like to [...]

    Pingback by Michael J. Swart | Data Heads | Data Education SQL Training — March 19, 2012 @ 1:52 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress