Voice of the DBA

I’m late, but I wanted to get this out. I started the post, then got distracted with travel to Europe late last week.

In any case, this month the host for T-SQL Tuesday is John McCormack, with his invite to write about short code examples. It’s a good technical invite, and one that immediately got me thinking about things I do often.

If you haven’t ever tried a T-SQL Tuesday, set up a blog and put out a post on the topic, this week or whenever you read this.

Snippets

I work for Redgate Software, though I’d likely be using SQL Prompt no matter what. I was never thrilled with native intellisense, but SQL Prompt has been great. One of the things I like most about Prompt are the snippets, which let me insert code with a few keystrokes.

Here’s a quick example. If I type “” in SSMS, I see this:

Prompt detects this as a snippet first, then has other matches. I can hit Tab, and I’ll get this:

I get a quick “top 10” from a table. Often I use SELET * for a quick look at the shape of data.

However, I can add custom snippets, and one I use often is this:

WITH myTally(n)

AS

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)

)

SELECT *

FROM myTally

This is my “tt” snippet, so I can type “tt<Tab> and get this code for a tally table. This s handy in many string manipulation and problem solving situations.

If I need more than 100 rows, I can easily copy the cross join line, add a new alias, and I have 1000 rows with this:

WITH myTally(n)

AS

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)

)

SELECT *

FROM myTally

Short, but effective code.