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?
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:
- script the creation of the table and
- 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:
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 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?
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.