Watch this week's video on YouTube

I write a lot of dynamic SQL and frequently encounter variables that contain many characters:

DECLARE @LongValue NVARCHAR(MAX) = CAST('' AS NVARCHAR(MAX)) + 
N'SELECT
    ''A'' AS AShortValue,
    '''+REPLICATE(N'A',4000)+''' as ALongValue
ORDER BY 1';

This variable is 4059 characters long, and when I execute it it runs great.

SELECT LEN(@LongValue); -- 4059 characters
EXEC(@LongValue);

A homage to one of my favorite Uncyclopedia entries.

If my programmatically built query had an error in it, the first thing I'd want to do when debugging it would be to see the the text of the entire @LongValue variable.

I could do this by just saying SELECT @LongValue, and while recent versions of SSMS will display the whole value for me, it completely loses my formatting which stinks (and is especially bad if there are any comments prefixed with --  in the query):

Need a ultra HD wide display to fit this all on one screen.

I can say PRINT @LongValue, which will keep the formatting, but it will get trimmed at 4,000 characters (notice the missing ORDER BY):

Some Better Ways

Erik Darling posts one solution to this problem in his T-SQL Tuesday #104 entry (as well as some other problems/solutions for lengthy SQL variables). Specifically he links to a SQL string printing script that will loop through the lengthy variable and print everything while maintaining formatting:

Not perfectly formatted, but good enough.

And while I like using that stored procedure on my primary server, I'm too lazy to install it every where I need it.

Instead, I have a couple of go-to solutions that work on all SQL Server instances 2008 forward.

Solution 1: CAST to XML

SELECT CAST(@LongValue AS XML) AS LongValue

Casting the long variable to XML allows SSMS to generate a clickable, single-row result that preserves formatting:

IntelliSense complains but I'm OK with it

The only downside to this approach is that certain charaters, like "<" and ">", can't be converted to XML:

Solution 2: FOR XML PATH

A slight variation on solution 1, we can get similar results using FOR XML PATH:

SET @LongValue = '<' + @LongValue -- Let's add in an invalid character
SELECT @LongValue FOR XML PATH('')

FOR XML PATH is one of the most abused SQL Server functions.

In this solution, the "<" is escaped to "<", which isn't perfect but at least my variable can be displayed with formatting intact.  A quick find and replace for any escaped characters and I'm good to go.

Good Enough

These techniques aren't perfect, but for purposes of debugging dynamically generated code they are good enough.

Maybe one day SSMS will print longer strings or include a syntax formatter and I won't care nearly as much.

And if not, I'll happily continue to abuse FOR XML to do things other than generate XML documents.

Watch this week's video on YouTube

While the text of this post contains good information on SSMS object filters, I highly recommend watching this week's video on YouTube - I stretched my creativity with filming this week while I was on vacation.

This week I'm sharing a trick I learned at Jess Pomfret's Ohio North SQL Server User Group presentation on data compression.  Her presentation on data compression was awesome (check it out if you get the chance), but I was shocked that I have been working with SSMS for so many years and have never known about the following trick I watched her perform in her demos.

An "Organized" Nightmare

I'm guessing you've probably worked in a database that has hundreds or thousands of database objects:

I often come across this in applications where for one reason or another someone decides that there is no need to separate apps into different databases; why bother creating different databases when you can have lots of different schemas to organize your objects instead!? (*cough* vendor applications *cough*).

The problem with these enormous lists of tables, procedures, functions, etc... is that it can get pretty tiring to scroll through them to find what you need.

For years I wore down my mouse's scroll wheel, scrolling between thousands of objects across multiple server instances.  As it turns out, SSMS has a much better feature for handling this problem.

Filtering Objects in SSMS

You can apply filters to most objects in SSMS by right clicking and choosing "Filter Settings":

You can filter on attributes such as name, schema, create date, etc...:

For example, if I want to see only tables that are in the Travel schema, all I have to do is create a filter:

SSMS will even tell me that my list of objects is filtered so I don't go crazy later on wondering where all of my other tables went.

Limitations

There are a few limitations with using SSMS object filters though.

For example, the different filter attributes work together as if they had "AND" operators between them, so you can do something like filter on tables in the Travel schema that contain the letter "a":

However, there is no way to write multiple conditions with OR logic (eg. you can't filter on the schemas "Travel" OR "Lodging").

And while SSMS will indicate that your objects are filtered, it won't persist that filter after restarting SSMS.

Even with those drawbacks, I've used this filtering feature at least once per week since learning about it; it saves a lot of time and I can't believe I went so long without knowing about it.

Watch this week's video on YouTube

When I started working with T-SQL, I thought the GO command was optional, kind of like semicolons.  It appeared in plenty of SSMS generated scripts, but it seemed like I never had to add it to any queries of my own:

SELECT 1 as Col1 INTO #Test1;
GO

-- ...or....

SELECT 1 as Col1 INTO #Test2;

-- both seem to work equally well!

Turns out that GO isn't T-SQL at all, but a command that allows apps (like SSMS) to send batches of queries to SQL Server.  It also turns out it has some more functionality than I originally thought.

So when should you use GO?

Using GO When Required

Just like semicolons not being entirely optional (ever try to run a CTE after a statement without a semicolon?), GO isn't completely optional either.

Some operations require that GO appear immediately after them:

DROP PROCEDURE IF EXISTS dbo.BatchTest;
CREATE PROCEDURE dbo.BatchTest
AS
BEGIN
    SELECT 1 as Col1
END;

The above script will fail with the error message "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."

There are multiple commands ("CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW") that require being the first statement of a batch, so using GO is required if you are going to try running other statements as part of your script.

So how do you run that CREATE PROCEDURE statement after first checking and dropping that procedure?  Just add GO so that CREATE PROCEDURE is the first statement of the batch:

DROP PROCEDURE IF EXISTS dbo.BatchTest;
GO

CREATE PROCEDURE dbo.BatchTest
AS
BEGIN
    SELECT 1 as Col1
END;

Executing Commands Multiple Times

So the previous example was one where SQL Server required me to type two extra characters to run certain commands.  Boo.  What about something actually useful I can do with GO?

Sometimes you may want to run a statement more than once.  You can do that by being trigger happy with the F5 key or your mouse button, but you don't want to do that 10,000 times, do you?

That's where GO shines.  Simply add an integer after GO and SQL Server will execute that batch of statements however many times you specified.  For example, the following code will insert 10,000 rows into a table:

INSERT INTO dbo.TestData VALUES (1,2,3)
GO 10000

This is really useful when creating test data or simulating workloads.

There are times when you are writing a query while referencing another piece of information: the results of another query, a variable value, a webpage, etc...

If using two monitors you can have a query window on each as a reference, or you can resize two query window tabs so they both fit on the screen.  But did you know there's a better option?

Watch this week's video on YouTube

Vertical Tab Groups

Vertical tab groups are great because they stay contained within the parent SSMS window.  This is nice for when I'm working on a small laptop screen or don't want to receive whiplash from moving my head back and forth between two monitors.

In order to create a vertical tab group, you need to have at least two query tabs open.  Right-click on the tab you want to be on the right-side of of the screen and choose "New Vertical Tab Group"

You can easily change the size of each open tab group by dragging the divider between them:

If you are working with really wide data, you can also try a horizontal tab group instead:

My favorite feature of tab groups is that I can move my entire SSMS window from one monitor to another (or minimize it) and all of my tabs follow suit.  This comes in real handy if working on multiple different projects at the same time.

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I'm used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

Pinpoint error finding

SQL Server Management Studio does a decent job too - **except when it doesn't. ** Different versions of SSMS provide error line numbers differently.

Watch this week's video on YouTube

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query.  This means if you have a window full of T-SQL batch statements, your error message will be relative to the last "GO" in the editor before the syntax error:

In SSMS 2014, batch start locations aren't considered:

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

While all of these error messages point me in the right direction, I'm too lazy to try and remember how each version of SSMS refers to the error location.  This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it's one of those small things I never thought of trying until I saw someone else do it.  Little did I know what I was missing out on - especially in those giant hundreds/thousands of line long scripts.  but now that I know about it it's the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)?  You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.