Michael J. Swart

I’ve given SQL Server Denali a spin and I have to say that I really like it. I want to demonstrate how I use some of the new features to make my life easier. How much easier?

The Scenario

Using the Adventureworks database, I want to perform the following:

SELECT top 30 ModifiedDate, rowguid, EmailAddress 
into myNewTable
from Person.EmailAddress

And I want to distribute a script to other databases so I can’t depend on the Person.EmailAddress table. So the two things I want to do in my script are:

1. script the creation of the table and
2. load the data.

SQL Server Denali actually makes this easy and I don’t even need to use SQL Server’s existing scripting features. Here’s how:

Using dm_exec_describe_first_resultset

First I use the new dynamic management function dm_exec_describe_first_resultset. Aaron Bertrand explains this feature well at his post SQL Server v.Next (Denali) : Metadata enhancements. You can use it to describe the columns of a query. This is perfect for what I need here and I base my script on the results of this query (modified a bit from Aaron’s script):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT top 30 ModifiedDate, rowguid, EmailAddress 
from Person.EmailAddress
 ';
 
SELECT 
    CASE column_ordinal 
        WHEN 1 THEN '' ELSE ',' END 
        + name + ' ' + system_type_name + CASE is_nullable 
        WHEN 0 THEN ' not null' ELSE '' END
FROM 
    sys.dm_exec_describe_first_result_set
    (
        @sql, NULL, 0
    ) AS f
ORDER BY
    column_ordinal;

But it’s really inconvenient to remember that syntax isn’t it? That’s why I like to use Code Snippets!

Code Snippets

Code snippets are something new in Denali. You may be familiar with SQL Snippets as offered by Mladen Prajdić using his SSMS Tools Pack. Having used both, I would say that Mladen’s SQL Snippets are much much easier to manage than Denali’s Code Snippets (at least as of CTP1). Denali does have code snippets that surround selected text though. I demonstrate with this snippet:

DescribeResultSet.snippet is a code snippet you can download and import into SSMS. It’s an alternative to remembering the syntax above. Here’s the way it works, it’s pretty simple. Select the query you’re curious about so that it’s highlighted. Then use the snippet to incorporate your query into a metadata query as shown in the code sample above.

Like I said, Snippets are pretty powerful, but SQL Server’s Code Snippets have still got a few quirks that will hopefully be ironed out by RTM:

• For example according to the docs, the surrounds with shortcut key combo is supposed to be Ctrl+K Ctrl+S, but with CTP1 it’s not hooked up yet.
• Writing snippets is a pain in the Denali, If you plan to write many snippets, stick with Mladen Prajdić’s SSMS Tools Pack

Okay, that’s great, now what about getting the data into the table?

Block Editing

Well that requires an Insert statement. That’s easy enough to write, but the VALUES clause is harder to write. It can be pretty finicky creating the literals. But SQL Server Denali runs on Visual Studio 2010! And that means we can use all the nifty tricks that VS2010 offers. One of those is enhanced Box Selection features. You select a rectangular box of text by hold down the alt key while dragging a mouse over a selection. Once you do that and start typing, the things you type will be inserted on each line of text.

This makes formatting blocks of data easy as pie. Thanks Denali!

Seeing The Whole Thing In Action

Great! That’s how to script data into a new table. And it’s so much simpler using Denali.

If you care to see it in action, I’ve got a screen capture showing what I mean. It’s got no audio (yet) or annotations.

Cheers!