How to break up a large data insert script so it is runnable in SQL Server Management Studio

I needed to move a reasonably large amount of data from one table to another table on a different server. It was a one off activity so I didn’t want to bother with tools such as the Import and Export Wizard nor SSIS. I simply wanted to generate a script with the insert statements and run it in SSMS.

OK, when I tried that the first time, SSMS wasn’t too happy running the 160,000 line script:

Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not 
produce a query plan. This is a rare event and only expected for 
extremely complex queries or queries that reference a very large 
number of tables or partitions. Please simplify the query. If you 
believe you have received this message in error, contact Customer 
Support Services for more information.

The error was somehow anticipated when I saw the size of the script. I worked around the issue by keeping the insert statements in the same script but breaking up the “union all select” into separate inserts. Here is the full example for you to devour:

use tempdb

if object_id('dbo.student') is not null
 drop table dbo.student

create table dbo.student
(
 id int not null identity(1, 1)
 , name sysname not null
 , residential_address nvarchar(max) not null
)

if object_id('dbo.merge_output') is not null
 drop table dbo.merge_output

create table dbo.merge_output
(
 id int not null
 , name sysname not null
 , residential_address nvarchar(max) not null 
 , merge_action sysname not null
)
 
;with cte_source(name, residential_address)
as
(
 select 'Louie1', '1 King St'
 union all select 'Louie2', '2 King St'
 union all select 'Louie3', '3 King St'
 union all select 'Louie4', '4 King St'
 union all select 'Louie5', '5 King St'
 union all select 'Louie6', '6 King St'
 union all select 'Louie7', '7 King St'
 union all select 'Louie8', '8 King St'
 union all select 'Louie9', '9 King St'
 union all select 'Louie10', '10 King St'
 union all select 'Louie11', '11 King St'
 union all select 'Louie12', '12 King St'
 union all select 'Louie13', '13 King St'
 union all select 'Louie14', '14 King St'
 union all select 'Louie15', '15 King St'
 union all select 'Louie16', '16 King St'
 union all select 'Louie17', '17 King St'
 union all select 'Louie18', '18 King St'
 union all select 'Louie19', '19 King St'
 union all select 'Louie20', '20 King St'
)
merge Student t
using cte_source s on t.name = s.name
when not matched then
 insert (name, residential_address) 
 values (s.name, s.residential_address)
when matched then
 update 
 set residential_address = s.residential_address
output inserted.id
, inserted.name
, inserted.residential_address
, $action
into merge_output;

if object_id('dbo.insert_statement') is not null
 drop table dbo.insert_statement

create table dbo.insert_statement
(
 seq int not null identity(1, 1)
 , cmd nvarchar(max) not null 
)

insert dbo.insert_statement
select concat('union all select ''id:', id
, ', name:', name
, ', residential_address: ', residential_address
, ', event:', merge_action, '''')
from merge_output

update dbo.insert_statement
set cmd = replace(cmd, 'union all ', 'go' + char(13) + char(10) 
+ 'insert dbo.event (message)' + char(13) + char(10))
where seq % 5 = 1

print '
**** Run the command below to export data:
sqlcmd -S ' + @@servername + ' -d tempdb -M -y0 -Q "set nocount on;
select cmd from dbo.insert_statement order by seq" -o "events.sql"'

Let me walk you though the script.

use tempdb

if object_id('dbo.student') is not null
 drop table dbo.student

create table dbo.student
(
 id int not null identity(1, 1)
 , name sysname not null
 , residential_address nvarchar(max) not null
)

dbo.student is a table where we insert/update student details. As a result of the insert/update, we want to capture the actions as events and insert into an event table.

if object_id('dbo.merge_output') is not null
 drop table dbo.merge_output

create table dbo.merge_output
(
 id int not null
 , name sysname not null
 , residential_address nvarchar(max) not null 
 , merge_action sysname not null
)
 
;with cte_source(name, residential_address)
as
(
 select 'Louie1', '1 King St'
 union all select 'Louie2', '2 King St'
 union all select 'Louie3', '3 King St'
 union all select 'Louie4', '4 King St'
 union all select 'Louie5', '5 King St'
 union all select 'Louie6', '6 King St'
 union all select 'Louie7', '7 King St'
 union all select 'Louie8', '8 King St'
 union all select 'Louie9', '9 King St'
 union all select 'Louie10', '10 King St'
 union all select 'Louie11', '11 King St'
 union all select 'Louie12', '12 King St'
 union all select 'Louie13', '13 King St'
 union all select 'Louie14', '14 King St'
 union all select 'Louie15', '15 King St'
 union all select 'Louie16', '16 King St'
 union all select 'Louie17', '17 King St'
 union all select 'Louie18', '18 King St'
 union all select 'Louie19', '19 King St'
 union all select 'Louie20', '20 King St'
)
merge Student t
using cte_source s on t.name = s.name
when not matched then
 insert (name, residential_address) 
 values (s.name, s.residential_address)
when matched then
 update 
 set residential_address = s.residential_address
output inserted.id
, inserted.name
, inserted.residential_address
, $action
into merge_output;

We are using a MERGE statement to get the data into the dbo.student table. The changes are captured by the OUTPUT clause into the dbo.merge_output table.

if object_id('dbo.insert_statement') is not null
 drop table dbo.insert_statement

create table dbo.insert_statement
(
 seq int not null identity(1, 1)
 , cmd nvarchar(max) not null 
)

insert dbo.insert_statement
select concat('union all select ''id:', id
, ', name:', name
, ', residential_address: ', residential_address
, ', event:', merge_action, '''')
from merge_output

Now, we are taking data out of the dbo.merge_output table and inserting into a transient table called dbo.insert_statement. If you select everything from dbo.insert_statement now, you’ll see:

seq cmd
1 union all select 'id:1, name:Louie1, residential_address:1 King St, event:INSERT'
2 union all select 'id:2, name:Louie2, residential_address:2 King St, event:INSERT'
3 union all select 'id:3, name:Louie3, residential_address:3 King St, event:INSERT'
4 union all select 'id:4, name:Louie4, residential_address:4 King St, event:INSERT'
5 union all select 'id:5, name:Louie5, residential_address:5 King St, event:INSERT'
6 union all select 'id:6, name:Louie6, residential_address:6 King St, event:INSERT'
7 union all select 'id:7, name:Louie7, residential_address:7 King St, event:INSERT'
8 union all select 'id:8, name:Louie8, residential_address:8 King St, event:INSERT'
9 union all select 'id:9, name:Louie9, residential_address:9 King St, event:INSERT'
10 union all select 'id:10, name:Louie10, residential_address:10 King St, event:INSERT'
11 union all select 'id:11, name:Louie11, residential_address:11 King St, event:INSERT'
12 union all select 'id:12, name:Louie12, residential_address:12 King St, event:INSERT'
13 union all select 'id:13, name:Louie13, residential_address:13 King St, event:INSERT'
14 union all select 'id:14, name:Louie14, residential_address:14 King St, event:INSERT'
15 union all select 'id:15, name:Louie15, residential_address:15 King St, event:INSERT'
16 union all select 'id:16, name:Louie16, residential_address:16 King St, event:INSERT'
17 union all select 'id:17, name:Louie17, residential_address:17 King St, event:INSERT'
18 union all select 'id:18, name:Louie18, residential_address:18 King St, event:INSERT'
19 union all select 'id:19, name:Louie19, residential_address:19 King St, event:INSERT'
20 union all select 'id:20, name:Louie20, residential_address:20 King St, event:INSERT'

I have 20 rows in the example but the real data set I had to deal with as I mentioned earlier was 160,000 rows. Initially, I only added one “insert dbo.event (message)” as the first line with all the “union all select” following it. (Of course, the first union all was removed but I won’t show you how that’s done because the solution below covers this part)

update dbo.insert_statement
set cmd = replace(cmd, 'union all ', 'go' + char(13) + char(10) 
+ 'insert dbo.event (message)' + char(13) + char(10))
where seq % 5 = 1

This whole blog is about the statement above. In this UPDATE statement, we are trying to achieve three objectives:

  1. Add the “insert dbo.event (message)” header necessary for the insert statement.
  2. Remove “union all ” from the first line of the SELECT statements.
  3. Break up the script into chunks.

Once exported, the events.sql file looks like:

go
insert dbo.event (message)
select 'id:1, name:Louie1, residential_address:1 King St, event:INSERT'
union all select 'id:2, name:Louie2, residential_address:2 King St, event:INSERT'
union all select 'id:3, name:Louie3, residential_address:3 King St, event:INSERT'
union all select 'id:4, name:Louie4, residential_address:4 King St, event:INSERT'
union all select 'id:5, name:Louie5, residential_address:5 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:6, name:Louie6, residential_address:6 King St, event:INSERT'
union all select 'id:7, name:Louie7, residential_address:7 King St, event:INSERT'
union all select 'id:8, name:Louie8, residential_address:8 King St, event:INSERT'
union all select 'id:9, name:Louie9, residential_address:9 King St, event:INSERT'
union all select 'id:10, name:Louie10, residential_address:10 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:11, name:Louie11, residential_address:11 King St, event:INSERT'
union all select 'id:12, name:Louie12, residential_address:12 King St, event:INSERT'
union all select 'id:13, name:Louie13, residential_address:13 King St, event:INSERT'
union all select 'id:14, name:Louie14, residential_address:14 King St, event:INSERT'
union all select 'id:15, name:Louie15, residential_address:15 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:16, name:Louie16, residential_address:16 King St, event:INSERT'
union all select 'id:17, name:Louie17, residential_address:17 King St, event:INSERT'
union all select 'id:18, name:Louie18, residential_address:18 King St, event:INSERT'
union all select 'id:19, name:Louie19, residential_address:19 King St, event:INSERT'
union all select 'id:20, name:Louie20, residential_address:20 King St, event:INSERT'

Bonus Tip – Exporting string data out of a table without truncation

print '
**** Run the command below to export data:
sqlcmd -S ' + @@servername + ' -d tempdb -M -y0 -Q "set nocount on;
select cmd from dbo.insert_statement order by seq" -o "events.sql"'

If there is a string column longer than 256 characters, it can be fiddly trying to export it out of SSMS. Instead of messing around with query settings, I simply issued a sqlcmd command with the -y0 switch:

-y variable_length_type_display_width
If display_width is 0, the output is truncated at 1 MB.

It works most of the time but if your string is larger than 1MB, then you need a more sophisticated solution.

My job is done for the day.

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I had the pleasure to read about the new index rebuild option WAIT_AT_LOW_PRIORITY. It’s a great addition to the DBA arsenal and you can read all about it here: Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

I have written about how index operations can interrupt cursor operations. Now naturally I wondered whether the new index option makes any difference to this scenario. Let’s work through an example.

1. Create table and index

create table temp
(
    id int not null identity(1, 1) primary key
    , data varchar(10) not null default('test')
)
go

insert temp default values
go 50

create index idx_temp on temp (data)
go

2. Start a cursor loop

declare cur cursor
for
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
begin
    print @id
    waitfor delay '00:00:01'
    
    fetch cur into @id
end

close cur
deallocate cur

3. Rebuild index with the new option

alter index idx_temp 
on temp 
rebuild with 
(
    online = on 
    (
        wait_at_low_priority
        (
             max_duration = 1 minutes
             , abort_after_wait = self
        )
    )
)

We still get the error:

1
2
3
4
5
6
Msg 16943, Level 16, State 4, Line 18
Could not complete cursor operation because the table schema changed 
after the cursor was declared.

I guess the lesson for today is that no matter how seemingly harmless/efficient the index operation is, you still need to test it thoroughly before applying to prod.

Powershell’s Write-Host sometimes is better than Write-Output

Despite what’s being said about the danger of Write-Host, I have found a scenario where it actually is the desired cmdlet to use over Write-Output.

Consider the code snippet below:

function Get-Array
{
    "I am not supposed to be in the array."

    $a = @("I am the only item in the array.")
    return $a
}

$array = Get-Array

cls

"`$array.count: [$($array.count)]"
$array

When I first encountered this piece of code (the simplified version shown above), I was quite frustrated with the output:

$array.count: [2]
I am not supposed to be in the array.
I am the only item in the array.

Why the hell was my print statement showing up in the array? Well, after hours of debugging and googling now I know it has everything to do with the behaviour of Write-Output:

The Write-Output cmdlet sends the specified object down the pipeline to the next command. If the command is the last command in the pipeline, the object is displayed in the console.
Write-Output sends objects down the primary pipeline, also known as the “output stream” or the “success pipeline.” To send error objects down the error pipeline, use Write-Error.
This cmdlet is typically used in scripts to display strings and other objects on the console. However, because the default behavior is to display the objects at the end of a pipeline, it is generally not necessary to use the cmdlet. For instance, Get-Process | Write-Output is equivalent to Get-Process.

The Get-Array function was explicitly returning an array object, but the assignment later on was also passing on whatever was in the primary pipeline. Gotcha!

A simple solution to this gotcha is by replacing the implicit Write-Output to Write-Host:

function Get-Array
{
    Write-Host "I am not supposed to be in the array."

    $a = @("I am the only item in the array.")
    return $a
}

$array = Get-Array

cls

"`$array.count: [$($array.count)]"
$array

As expected, you’d get the output below:

$array.count: [1]
I am the only item in the array.

This works because Write-Host behaves quite differently to Write-Output:

Write-Host sends the objects to the host. It does not return any objects. However, the host might display the objects that Write-Host sends to it.

Understanding the differences between the seemingly simple commands can save loads of wasted time in debugging, especially in a scripting world.

How to create a new local git branch that tracks a remote branch

I’ve had to look up this on StackOverflow twice in the last week. I thought it’d be a good idea to jot down some notes so I can remember what to do next time.

1. Add the remote repo
git remote add louie [email protected]:louiebao/dbarsenal.git

2. Get all the branches for the new remote repo
git fetch louie

3. Create a new branch locally that tracks the remote branch
git checkout -b demo louie/demo

4. Check
git branch -v -v

Handy SSMS Query Shortcuts

In SQL Server Management Studio, you are able to define custom query shortcuts to help with common tasks. I have been using them for quite some years now and realized I haven’t written about them yet. So here we go.

The query shortcuts can be found under Tools -> Options... -> Environment -> Keyboard -> Query Shortcuts and the default setting doesn’t look very exciting.

ssms_keyboard_query_shortcuts

Before I go any further, let me explain how query shortcuts work. The “shortcut” is obviously the key combo you press which does two things. It (1) prepends the “statement” to the highlighted text in the editor and (2) executes it. Although SSMS calls the statement Stored Procedure but it is not limited to only procs, it works with any valid T-SQL statement. In other words:

shortcut = statement [highlighted text]

Let me show you with an example, let’s say you have a query that looks like the one below and you would like to run sp_help sys.columns to see the table details:

select *
from sys.objects a
join sys.columns b on a.object_id = b.object_id

Without a shortcut, you’d need to go to a new line and type the command. Now with the help of the shortcut, you can simply select the text sys.columns and press Alt+F1 to achieve the same result. How cool is that? I hear you want more. Ok, I will reveal all the query shortcuts I find particular useful.

sp_helptext – How many times have you looked at a view or proc and wondered about the implementation underneath. Well, no need to search for that object in Object Explorer anymore, simply setup a shortcut. My personal preference for this shortcut is the Ctrl+F1 slot.

select * from sys.objects where name like – This is quite handy when you want to quickly check to see if an object exists in the database. I normally set this to Ctrl+5

select – Don’t let the simplicity of this shortcut fool you. Not having to type those six characters can save a lot of time when you want to run a system function embedded in a query, such as suser_sname(), newid(), getutcdate() etc. I normally set this to Ctrl+6

select top 10 * from – The reason for having a separate top 10 query shortcut from the select all is that it provides a quick and easy way of retrieving the column names with some sample data. I normally set this to Ctrl+9

select * from – I use Ctrl+0 for this.

The shortcuts listed above are the common ones I use almost everywhere whereas the ones below are less common but still handy to have around:

select concat(””, newid(), ””) as [newid()] – If you have a need to generate GUIDs all the time, then surely it deserves a shortcut slot.

set statistics io, time on – This one is for folks looking at performance.

select * from dbo.dbversion order by id desc – This is useful if you have a version table in the database. Setting up a shortcut can make it easier for checking versions.

Watch out for square brackets when using Select-String to search for SQL objects

Powershell’s Select-String cmdlet is powerful, well, sometimes too powershell you’d want to dumb it down a notch. That’s what happened to me today.

Here is the scenario. I have the name of a column in the form of [schema_name].[table_name].[column_name] and I want to see if [schema_name].[table_name] part of the name is found amongst a list of tables. I have set up two test scenarios below for demonstration purpose:

Table List
[sys].[objects]
[sys].[columns]
[sys].[types]

Column
1. For [sys].[objects].[type_desc], Select-String should return something as the table name [sys].[objects] forms part of the column name.
2. For [sys].[tables].[type_desc], Select-String should return nothing as no tables from the list matches [sys].[tables].

Expressed in powershell:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array

In this instance, -Pattern takes an array and for each one of the array items it is matched against -InputObject. In other words, we are trying to find a -Pattern that is a subset of -InputObject.

Running the above script yields the output below:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...
[sys].[tables].[type_desc]

Notice how both Select-String returned something? That’s wrong as we don’t expect the second Select-String to return anything. After trying to debug with a few different scenarios, it dawned on me that Select-String uses regular expression matching by default. It was one of those “OF COURSE!” moments in hindsight. Most of the time it is ok if you are just trying to do some simple string matching but when working with SQL (square bracket) quoted names, it can wreak havoc.

The solution is to turn regular expression matching off and to do that you just need to specify the flag -SimpleMatch:

$table_array = "[sys].[columns]", "[sys].[types]", "[sys].[objects]"
$sys_objects_name = "[sys].[objects].[type_desc]"
$sys_tables_name = "[sys].[tables].[type_desc]"

"`nLooking for a table that is part of $sys_objects_name..."
Select-String -InputObject $sys_objects_name -Pattern $table_array -SimpleMatch

"`nLooking for a table that is part of $sys_tables_name..."
Select-String -InputObject $sys_tables_name -Pattern $table_array -SimpleMatch

Running the script again now yields the correct output:

Looking for a table that is part of [sys].[objects].[type_desc]...
[sys].[objects].[type_desc]

Looking for a table that is part of [sys].[tables].[type_desc]...

–oo00oo–
For more info, please refer to the links below:
Select-String

 

Use Powershell’s Select-String to find SQL update statements

I had a SQL script and I wanted to check if there are any update statements in the file. It was a seemingly easy task but after messing around with Powershell’s Select-String, I learnt a few gotchas that are worthy of a write-up.

Let’s start with something simple:

SQL:

update dbo.table set column = 1

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update"

Output:

script.sql:1:update dbo.table set column = 1

Now, let’s say I want to exclude print statements and comments that have a random “update” in them.

SQL:

-- start to update dbo.table...
update dbo.table set column = 1
print 'update complete.'

Without changing the powershell, I will get all three lines returned. It is reasonable to assume that a legit update statement would always come with a set clause, so maybe I can include that in the search pattern.

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update.*set.*="

Output:

script.sql:2:update dbo.table set column = 1

This looks good so far. However, it is also reasonable to assume that most update statements would have the set clause on the next line, let’s see how that’s handled.

SQL:

-- start to update dbo.table...
update dbo.table 
set column = 1
print 'update complete.'

Oops. Nothing returned this time. I learnt this in hindsight that Get-ChildItem is feeding into Select-String one line at a time so it will never find the update.*set pattern. In order to get around this problem, I just need to feed the entire script into Select-String as a string.

Powershell:

[string](Get-Content script.sql) | Select-String -Pattern "update.*set.*="

Output:

-- start to update dbo.table... update dbo.table  set column = 1 print 'update complete.'

It worked a treat!

–oo00oo–
For more info, please refer to the links below:
Select-String

How to setup GIT for Windows using Command Prompt and Posh-Git

On my local environment, I am using:

  • Git for Windows
  • Command Prompt (comes with Windows so no additional install required)
  • Posh-Git (for nice visual prompt)

I have had the above setup for quite some time now. After helping out a colleague with the setup today I have decided to put the instructions in a post so I can quickly reference it later.

Install Git

Everything starts from here. You can download Git for Windows from here if you haven’t already got it installed. Make sure you ticked “Use Git from the Windows Command Prompt”.

git_setup

Setup Command Prompt shortcut

The steps below will provide you with a MUCH more friendly command prompt.

1. Pin Command Prompt to the taskbar.

2. Right click on Command Prompt (the one in the taskbar) and click on Properties. Set the properties to:

Target: %windir%\system32\cmd.exe /K powershell
Start in: C:\Dev (or wherever you keep your git repos)

This will run the command powershell and start command prompt in your working folder.

3. Click on the Options tab. Tick QuikEdit mode in Options. This makes copy and paste operations a lot easier.

Install PowerGUI (optional)

You can choose any IDE to work with powershell scripts but this is my personal preference. PowerGUI is a free IDE for powershell scripts that can be downloaded from here.

Install Posh-Git

1. Download the Posh-Git zip file from here. You can install Posh-Git using other methods like git clone or PsGet but my experience says manipulation of a zip file is the easiest way for beginners to understand and follow.

2. Unzip (using 7-Zip) to C:\Dev\posh-git-master. If you don’t have 7-Zip and relied on the Windows “Extract All…”, you might encounter issues with powershell’s execution policy. Instead of messing around with the execution policy, just go download and install 7-Zip from here. It will save you lots of headaches.

It doesn’t matter where you extract the files, I leave it in C:\Dev because that’s where I keep things.

3. In a command prompt, go into the posh-git-master folder and run .\install.ps1. You may or may not need to set the powershell’s execution policy but RemoteSigned at the LocalMachine level should be sufficient.

4. Inspect your newly created profile. If you don’t know where your profile is located. Just run $profile in the command prompt. You profile should contain two lines that read:

# Load posh-git example profile
. 'C:\Dev\posh-git-master\profile.example.ps1'

5. By now you should get the nice prompt that’s integrated with git. command_prompt_posh-git

6. Bonus step. I have talked about including the current date time in the command prompt in a previous post. After installing posh-git, the handy timestamp in the comand prompt would be lost. In order to get that back, I had to edit the file C:\Dev\posh-git-master\profile.example.ps1 by adding Write-Host($(Get-Date)) above the default Write-Host line:

...
Write-Host("")
Write-Host($(Get-Date))
Write-Host($pwd.ProviderPath) -nonewline
...

Setup SSH Key

1. Generate a new SSH key. Again, seemingly simple step but expect unpleasant surprises if your environment is not configured properly. Make sure:

  • C:\Program Files (x86)\Git\bin is in your PATH.
  • HOME = C:\Users\<user name> is added to the system environment variables.
  • C:\Users\<user name>\.ssh folder is manually created if it doesn’t exist already.

Once all the above checks passed, run the command below in the command prompt:

C:\Users\<user name>\.ssh> ssh-keygen -t rsa -C "<email address>"

2. Press <Enter> to accept the default location and file name.

3. Press <Enter> and <Enter> again when prompted to enter a passphrase.

4. Open the public key file (C:\Users\<user name>\.ssh\id_rsa.pub) and copy the content.

5. Add the public key to GitHub / BitBucket.

–oo00oo–
For more info, please refer to the links below:
Better Git with PowerShell

Code snippets in SQL Server 2012 for dummies

Normally I don’t write about a topic if resources are widely available on the web. Now knowing how useful code snippets are, I was a bit surprised at the lack of good instructions on the topic. In the past, I had access to Red Gate at work so I was blessed with the power of SQL Prompt snippets. Gone are the days. I eventually dived in and learnt to use the Code Snippets Manager that comes with SQL Server Management Studio (2012). It involved some messy XML but at the end, it wasn’t too hard at all.

In this post, I am going to walk through one of my snippets and try to explain what everything means to the best of my knowledge:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets>
 <CodeSnippet Format="1.0.0">
 <Header>
 <Title>sp_msforeachdb</Title>
 <Shortcut></Shortcut>
 <Description></Description>
 <Author>Louie Bao</Author>
 <SnippetTypes>
 <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
 <Code Language="SQL"><![CDATA[
set nocount on
set transaction isolation level read uncommitted

if object_id('tempdb.dbo.#results') is not null
 drop table #results

create table #results 
(

)

insert #results
exec sp_msforeachdb 'use [?]
if db_name() like ''''
begin

end'

select * from #results
]]>
 </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

The first thing you’d notice with the xml above compared to one of the examples found on Books Online is that that I have removed quite a few things. This is what I found the easiest way of creating code snippets.

<Title>

This is the name you will see in the list of snippets. Make sure it is named properly so you can find it later.

<Shortcut>

Initially I thought it was the keyboard shortcut to invoke the snippet like how it is done in Red Gate’s SQL Prompt snippets. Turned out it isn’t all that useful as you still need to go through the Ctrl + K + X sequence to get to it. That’s why I left it blank.

<Description>

Optional. Common sense applies here.

<Author>

Again optional, nothing special, just put your name here.

<SnippetTypes>

As far as I know, there are two snippet types: Expansion and SurroundsWith. SSMS uses this value to determine which context menu will be used to display the code snippet.

Expansion        = Edit -> IntelliSense -> Insert Snippets… Ctrl + K, Ctrl + X
SurroundsWith = Edit -> IntelliSense -> Surround With… Ctrl + K, Ctrl + S

I choose to create all my snippets as the Expansion type. It is just so much easier to understand and work with as I don’t need to worry about things like the $selected$ and $end$ tokens. One of the key reasons I decided to go with Expansion is that I just can’t seem to find a list of all these predefined tokens in order to take advantage of the SurroundsWith type. Furthermore, using SurroundsWith means I need to learn about all the silly tags like

<Declarations>
<Literal>
<ID></ID>
<ToolTip></ToolTip>
<Default></Default>
</Literal>
</Declarations>

and it is just simply unneccessary.

<Code Language=”SQL”>

Self explanatory. Since this is a Visual Studio technology I assume you can have other languages like C#. For my purpose, a snippet is a snippet, SQL will do just fine.

<![CDATA[ code snippet ]]>

You don’t have to be an XML expert to figure this out. The actual code snippet lives inside this tag.

Demo

As I have shown above, that’s all the XML you’ll need to know to create a code snippet file. Next I will show you how it all ties together.

1. Save the code snippet file as sp_msforeachdb.snippet in the %userprofile%\Documents\SQL Server Management Studio\Code Snippets\SQL\My Code Snippets\ folder which should already exist. Once done, the file is immediately visible in SSMS (Tools -> Code Snippets Manager…, look inside My Code Snippets) and any modifications done to the file thereafter will be reflected immediately in SSMS. How cool is that! By the way, notice how I haven’t done anything fancy to Add… or Import…, all I did to make the snippet available was by saving a file.

2. Invoke the code snippet with the Ctrl + K + X key sequence.

code_snippet_quick_access

The screenshot above shows the code snippet I have saved earlier. Some of you may notice at a glance that there are something missing from the list, yeah, all the default Microsoft code snippets! That’s because for faster access, I have removed (hidden) all the default ones that came with SQL Server 2012 by renaming…\Tools\Binn\ManagementStudio\SQL\Snippets\1033 to 1033 (Hidden).

3. Complete the query by filling the gaps.

set nocount on
set transaction isolation level read uncommitted

if object_id('tempdb.dbo.#results') is not null
	drop table #results

create table #results 
(
    name sysname
)

insert #results
exec sp_msforeachdb 'use [?]
if db_name() like ''louie''
begin
    select name from sys.objects where type = ''u''
end'

select * from #results

–oo00oo–
For more info, please refer to the links below:
Investigating Code Snippet Technology

What is query_hash from sys.dm_exec_query_stats?

According to Books Online, it is a:

Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

My first question after reading the above was how similar is similar? There is only one way to find out and what started out as a simple experiment turned into a game of its own. The aim of the game is to change a proc in as many steps as possible and still generate the same query_hash. My record was 10 steps and stopped at that.  Here is an example of what I did:

StepChangequery_hashProc
1Create a new proc0x47F48A4C2B564BB8CREATE proc louie as select * from sys.objects
2Add a parameter0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as select * from sys.objects
3Set nocount on0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects
4Return 10x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects return 1
5Print0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' return 1
6Raiserror0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' raiserror('Error', 16, 1) return 1
7Try...Catch0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select * from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
8Return all columns0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
9Set transaction isolation level read uncommitted0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
10Manipulate variable0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

I think by now you get the picture. My observation was that as long as the query was not touched, the query_hash stayed the same. Now let’s see what changes would cause a different query_hash to be generated. By the way, below is the query I used to retrieve the query_hash:

select a.sql_handle, a.query_hash, c.text
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) c
join sys.objects b on c.objectid = b.object_id
where b.name = 'louie'
Changequery_hashProc
With (nolock)0xC32DED2DE8B7FB38CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects with (nolock) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
Add a where clause to return the same resultset0xE659977264A05561CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects where name = isnull(nullif(@name, 'louie'), name) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

The list is of course not exhaustive and it is pretty safe to assume that if a query is modified (with the potential to return a different resultset), its query_hash would change as well.