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.

In this post and video at SQLPerformance.com, I discuss what join elimination is and how it works in SQL Server.  Join elimination is one of the many techniques that the SQL Server query optimizer uses to create efficient query plans. Specifically, join elimination occurs when SQL Server can establish equality by using query logic or trusted database constraints to eliminate unnecessary joins... Read more or watch the video

Watch this week's video on YouTube

I love continuing my education in SQL Server.

Recently I was fortunate enough to take Brent Ozar's 3-day live online Master Index Tuning training class.

Since I had never taken an online class before, I thought it'd be fun to write a mini-review of what I thought about the class as well as what lesson from the course had the biggest impact on me.

As excited as I was to take the class, I was a little concerned about a couple of aspects about it. My main concerns before taking the online class were:

• Would I have difficulty focusing in an online class?  YouTube, Facebook, and other distractions are only a few clicks away.
• Will it feel like a 1-way dialogue since I wouldn't be there in person to ask questions, participate in discussion, and interact with other student?

My first concern was immediately invalidated when we started working on our labs. During the 3 days, Brent does the traditional training of explaining problems, techniques, and solutions (in his own humorous way of course) BUT THEN instead of moving on to the next topic he gives the students time to work through problems on their own in labs he set up.

This was huge. Every student gets an 8 core, 64TB Azure VM running simulated workloads that provide a great real-world representation of an under performing SQL Server.

Instead of taking notes for 3 days and hoping that I would remember to try them out when I returned to work, the labs allowed me to apply those new concepts immediately. I think working through these problems in a real-world scenario is not only practical, but probably the best way of retaining the information you learn. I'm not sure how much work it was for Brent to set up these labs (guess: a ton), but they really payoff as a student.

As for my concern about the course not being interactive, that turned out to not be a problem either. During the duration of the class (as well as for a couple hours before and after the class each day) there was a Slack chatroom where Brent and the students could interact.

The Slack chat had two huge advantages:

1. Questions were typed out and written down, making it easy to know who asked what and what question Brent was responding to.  It also made it easy to copy and paste the chat for my notes.  It was also nice to have the ability to type a question overnight and get an answer the next morning.
2. We were asked to post all of our lab queries into the chat when we think we had found a solution to the performance problems. This was AWESOME because not only did I get 1-on-1 code review and advice from Brent, I got to see what everyone else was submitting as an answer.
   Each lab scenario could usually be solved a few different ways, so this meant other students were submitting completely different solutions than what I came up with.  Seeing other people's approach to problem solving was really cool. Even better, I could try out their solutions in my own lab by copying and pasting the queries from Slack into my lab VM, seeing how those different solutions compared to my own.

So while I had some concerns before the class started, it turns out they weren't problems at all and the class was great.

My Favorite Lesson: Prioritizing Performance Solutions

The Master Index Tuning training covered everything needed to practically troubleshoot and solve index performance issues.  While Brent taught many techniques, from everyday solutions to advanced once-in-a-while techniques for certain edge cases, my biggest takeaway was how Brent taught us to prioritize the low-hanging-index-tuning-fruit first.

I'm a query tuner by nature.  Before taking this training, my go-to solution for fixing a performance problem was to start refactoring a query or correcting the indexes for that one particular query.

Instead, Brent recommended that we focus on the actions with the largest performance returns first: at a database or instance level, clean up duplicate (or near duplicate) indexes, drop unused indexes, add missing indexes, etc...

Many individual query performance issues can be fixed by changing indexes alone, without ever needing to touch the queries themselves.  Fixing poorly created indexes on your server has the additional advantage that it can improve performance of MULTIPLE queries running on your server simultaneously.

The takeaway here is that unless your server is already in tip-top shape, you'll probably get more bang for your buck fixing duplicate, inadequate, and missing index issues than you will by tuning individual queries.  This may sound obvious, but from my "every problem is a query tuning problem" standpoint this was a nice reminder that sometimes looking at the bigger picture will produce larger returns than focusing only on the details.

Final Verdict: Is It Worth It?

Yes.  Especially if you are the kind of person who learns best by doing and wants real-world index tuning advice.  By the end of the class I felt confident enough to immediately go back to work and start applying what I had learned from Brent and practiced in the labs.

Watch this week's video on YouTube

Occasionally you may need to create a lookup table of values for a single query.  Building a permanent table of these values is a good option if you need to reuse the values frequently, but for one-off, ad-hoc queries you may want to hard-code the lookup table of values directly in your query.

We can hard-code these values using several different techniques.  Below are the techniques I've seen used before, going in order from my least to most favorite.

Table Variables

DECLARE @T TABLE 
(
    ColorName varchar(20), 
    HexCode char(7)
);

INSERT INTO @T VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970');

SELECT * FROM @T;

Table variables get a bad rap in SQL Server because they don't provide accurate row estimates to the query optimizer.  This can cause SQL Server to come up with some really terrible execution plans that will kill your query's performance.

However, if your use case is a single small lookup table of less than 100 records, table variables might actually be a viable option.  They are quick and easy to setup and can be added to the top of your query.

With that said, I don't think I've ever used a table variable in this type of scenario (or any scenario really).  I know some people love using them and I think that's fine as long as you are keeping track of your query performance.  For me though, there are so many better options available...

Temporary Tables

CREATE TABLE #T 
(
    ColorName varchar(20), 
    HexCode char(7)
);

INSERT INTO #T VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970');

SELECT * FROM #T;

Temp tables are the answer to many of the table variable's shortcomings.

Temp tables can perform well with larger amounts of data because they can be indexed and can have statistics generated on them.  Both of these features typically help SQL Server generate better execution plans.

There is some overhead in coding a temp table though: just like a table variable, a temp table needs to be created and inserted into before being able to use it in your query.  While normally not a huge deal, this is not something I want to have to do in those instances where I want to define some lookup values quickly...

SELECT with UNION ALL

SELECT
    *
FROM
    (
        SELECT 'FireBrick','#B22222'     UNION ALL
        SELECT 'HotPink','#FF69B4'   UNION ALL
        SELECT 'Tomato','#FF6347'    UNION ALL
        SELECT 'PapayaWhip','#FFEFD5'    UNION ALL
        SELECT 'RebeccaPurple','#663399' UNION ALL
        SELECT 'LawnGreen','#7CFC00'     UNION ALL
        SELECT 'MidnightBlue','#191970'
    ) T(ColorName,HexCode);

The next option is hard-coding values in SELECT statements and then joining them together with UNION ALLs.

This is probably the most common technique I see, and for good reason: the syntax is straight forward and doesn't require any special setup; perfect for the one-time use ad-hoc scenario.

Its format also makes it easy to use the ALT + highlight shortcut to quickly create a derived table lookup from the results of another query or values copied from elsewhere.

I do like this method a lot, but there is one method that I like slightly more...

The VALUES() Constructor

SELECT
    *
FROM
    (VALUES 
        ('FireBrick','#B22222'),
        ('HotPink','#FF69B4'),
        ('Tomato','#FF6347'),
        ('PapayaWhip','#FFEFD5'),
        ('RebeccaPurple','#663399'),
        ('LawnGreen','#7CFC00'),
        ('MidnightBlue','#191970')
        ) T(ColorName,HexCode);

You've probably use the VALUES constructor in an INSERT INTO statement, but did you know you can also use it in a FROM clause?

This syntax is similar to our SELECT + UNION ALL technique above, except we are swapping in single quotes and parentheses for SELECTs and UNION ALLs.  I find this slightly easier to write, if only because it requires typing out fewer characters and feels more programmatic.

One Last Tip: CTE

WITH HtmlColors AS (
        SELECT 'FireBrick' AS ColorName,'#B22222' AS HexCode     UNION ALL
        SELECT 'HotPink','#FF69B4'   UNION ALL
        SELECT 'Tomato','#FF6347'    UNION ALL
        SELECT 'PapayaWhip','#FFEFD5'    UNION ALL
        SELECT 'RebeccaPurple','#663399' UNION ALL
        SELECT 'LawnGreen','#7CFC00'     UNION ALL
        SELECT 'MidnightBlue','#191970'
)

SELECT * FROM HtmlColors

This isn't really an additional technique, but something related that I use often and feels appropriate to mention.

If using either the SELECT + UNION ALL or VALUES techniques, you can put those lookup queries into a common table expression for easier referencing.

This doesn't give any performance advantage, but it does help keep your code clean by putting your lookup logic right at the top of your file.  This becomes particularly useful when using those hard-coded lookup values as parameters, allowing all changes to your query to be made right at the top of your file during subsequent runs.

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.