Posted on

SQL Operations Studio: Keyboard Shortcuts, Actual Plans, & More

Last week I posted a quiz on SQL Operations Studio, a free, multi-platform tool from Microsoft.

This tool is under active development and the features are improving by the day — which makes it a great time to start trying out the tool and see what you like: because you can suggest changes!

Personally, I find that one of the things like I like to learn first with a tool is how to get around more easily using keyboard shortcuts. As soon as I set up keyboard shortcuts and color themes, I feel more at home in a client.

So, let’s make ourselves at home!


Here’s the answers to the quiz, and an overview of how folks did

Question 1: How can you change the keyboard shortcuts in SQL Ops Studio?

Correct answers:

  • Preferences -> Keyboard Shortcuts
  • The SSMS Keymap extension

Correct: 48 (30%) / Incorrect: 113 (70%)

Most of the folks who got that one incorrect didn’t know about the SSMS Keymap Extension. It’s definitely worth checking out, because makes things much easier for some shortcuts that are trickier to map, like enabling actual execution plans.

Opening up extensions in SQL Ops Studio
Opening up extensions in SQL Ops Studio

Question 2: To toggle a BLOCK comment, the built-in shortcut is…

Answer: Shift+Alt+A

  • Correct: 43 (27%)
  • Incorrect: 118 (73%)

I think a lot of folks who use SSMS regularly and don’t use VSCode may not know what I meant by the question, because SSMS doesn’t have this functionality (or if it does, I’ve never figured out the shortcut!)

Here is one version of togging a block comment in action around existing text.

Toggling a block comment in SQL Operations Studio
Toggling a block comment in SQL Operations Studio

It can also be very nice to insert a block comment and then type in the middle of it right after inserting the brackets!

If it’s possible to fall in love with a keyboard shortcut, I am in love with this one.

Question 3: To add a LINE comment, the built-in shortcut is…

Answer:  Ctrl+K Ctrl+C (windows) / ⌘K ⌘C (mac)
Mnemonic: Control Kansas City (nonsensical, but works for me! Thanks Merrill!)

  • Correct: 83 (52%)
  • Incorrect: 78 (48%)

Some things are the same as in Management Studio! At least for Windows folks.

For folks, like me, who use a mac, the default shortcut for this uses the Command Key. It is re-mappable if you don’t feel like retraining yourself when you switch between clients.

Question 4: To see a searchable list of your keyboard shortcuts…

Answer: Ctrl+K Ctrl+S (windows) / ⌘K ⌘S (mac)
Mnemonic: Control Keyboard Shortcuts

  • Correct: 48 (30%)
  • Incorrect: 113 (70%)

Especially when you’re first getting started, you’ll be asking a lot: is there a shortcut for x? You may want to map a shortcut where there isn’t one, or change a mapping. That makes this a useful screen to toggle quickly.

Question 5: Can you get ‘actual’ execution plans in SQL Ops Studio?

Answers:

  • Yes, via the command palette
  • Yes, via a custom keyboard shortcut

This was news to most people!

  • Correct: 18 (11%)
  • Incorrect: 143 (89%)

There’s currently more than one way to get an actual execution plan.

The awesome easy way is to install the SSMS Keymap Extension (pictured above). It maps the “Run Current Query with Actual Plans” command (which doesn’t have an option in the GUI as of today) to the CTRL+m shortcut.

If you don’t feel like using the extension, you can open the Command Palette (Ctrl+Shift+P (windows) / ⌘+Shift+P (mac)) and use the option “Run Current Query with Actual Plans”

Using the command palette to run a query with actual plans
Using the command palette to run a query with actual plans

Notes:

  • Highlight the query you want to run first, and then use the shortcut (if you’re using the extension) or the command palette command. It runs the query.
  • Getting estimated and actual plans for multiple queries is still a bit tricky (I filed this issue).

BONUS: Does SQL Operations Studio have a dark theme?

Answer: Yes, several

  • Correct: 78 (48%)
  • Incorrect: 83 (52%)

I know some of you folks out there love dark themes, so rejoice: you can take your pick. You can also customize them! In my last post, I wrote about customizing my highlight color and included links to lots of shortcut references.

Haven’t tried it yet? Give it a go!

While I still switch into SSMS for many tasks, I personally am finding that SQL Ops Studio is a super nice place to write code – so I’m using it as much as I can. Here’s that download link again.

Posted on

Changing Your Highlight Color in SQL Operations Studio

I’ve been enjoying working with SQL Operations Studio lately – a new, free, cross-platform editor from Microsoft.  The tool is under active development and it’s a great time to try it out and suggest changes for how you’d like to see the product work. (Here’s a suggestion I made this week, for example.)

tldr; use workbench.colorCustomizations

To change the highlight color quickly to bright yellow, open your user settings file, add code like you see in one of the samples below, and save it.

Note that a bright yellow highlight color will not work well with themes that put some font in white – configuring editor.selectionForeground currently only works with high contrast themes (issue).

Because of that current limitation, I like a bright yellow highlight specifically with the “Light SQL Operations Studio” theme:

/* Yellow highlight, works well with Light SQL Operations Studio: */
    "workbench.colorCustomizations": {
        "editor.selectionBackground": "#fffb00",
        "editor.inactiveSelectionBackground": "#fffb007e", 
        "editor.selectionHighlightBackground": "#fffb007e"
    },

The change will take place as soon as you save the settings file.

For the “Dark SQL Operations Studio” theme, which I really like, I find that a purple highlight renders very well:

/* Purple highlight, works well with Dark SQL Operations Studio: */
"workbench.colorCustomizations": {
    "editor.selectionBackground": "#ae00ff86",
    "editor.inactiveSelectionBackground": "#ae00ff41", 
    "editor.selectionHighlightBackground": "#ae00ff41"
}

Highlighting code is important to me

One of my major uses of SQL Ops studio will be demonstrating code in webcasts and videos, so it’s important to me to be able to set a high-contrast highlight for lines of code.

SQL Ops Studio is based on VSCode, which is very flexible, so I suspected there was a way to do this already. Also, there is a lot of documentation out there on VSCode already, so I searched on “VSCode change highlight color” to help find my way to the solution.

But I figured that lots of folks starting out with SQL Ops Studio may not know that, and that this post might be a good introduction to how to change things like this – as well as how to find things by searching for “vscode”!

Changing the highlight and testing themes

Some handy shortcuts shown in this animation:

  • Opening preferences to edit: ctrl+, (windows) / cmd+, (mac)
  • Toggle block comment: shift+option+a
  • Saving file: ctrl+s (windows) / cmd+s (mac)
  • Viewing/changing color themes: ctrl+k ctrl+t (windows) / cmd+k cmd+t (mac)

Shortcut references: windows, mac

Here’s what it looks like to change the highlight to bright yellow, and to preview it in different themes:

Note that you don’t truly see how the highlight will be implemented until you fully select a theme (preview mode is slightly dimmed), and that the highlight color persists across different themes (which I think is awesome).

This screencap was taken from a mac, using Camtasia screencap to capture the keystrokes.

 

Posted on

CROSS APPLY in T-SQL: Why to APPLY

Why use APPLY?

Michelle asked a great question on the quiz page:

In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join.

Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:

  1. APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
  2. Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
  3. And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.

But it can do even more…

It can help you (un) pivot data. Check out Kenneth Fisher’s example here.

Brad Schulz’s post here has code samples for those, plus more things like showing how it can be useful for shredding XML: http://bradsruminations.blogspot.com/2011/04/t-sql-tuesday-017-it-slices-it-dices-it.html

Brad gets a bit jokey at the end of the post, but there’s a lot of valid uses along the way.

Want to take the quiz on APPLY?

Take the quiz here, then head back here to see how you compare to other folks who took the quiz– reported below.


Quiz Results/Answers/Explanations: CROSS APPLY

The questions in this quiz are based on two tables, created and populated with the following commands:

CREATE TABLE dbo.t1 (t1c1 int IDENTITY PRIMARY KEY); 
CREATE TABLE dbo.t2 (t2c1 int IDENTITY PRIMARY KEY, t1c1 int NOT NULL DEFAULT 'foo'); 
GO

INSERT dbo.t1 DEFAULT VALUES
GO 5
INSERT dbo.t2 (t1c1) SELECT TOP (2) t1c1 FROM dbo.t1;
GO

This produces two tables which look like this:

Question 1: If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

SELECT COUNT(*) AS ct 
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
     FROM dbo.t2 
     WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: The results of COUNT(*) based on an inner join between the two tables

  • Correct: 106 (42%)
  • Incorrect: 146 (58%)

I know, it’s weird, there’s a 1/0 in there which seems like it would produce a divide by zero error! But that bit of code never gets evaluated in this case, and the query runs successfully.

Sometimes a query using APPLY can be “transformed” into a join operation, and this is one of those cases. You can read more about this in Paul White’s article here.

Most of the time, if you want a join, you should use a join. However, it’s definitely useful to know that APPLY can be used this way for when you are reading and editing code written by others.

Question 2: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
CROSS APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: yes

  • Correct: 140 (56%)
  • Incorrect: 112 (44%)

In this case, the second query will be rewritten to use an inner join, just like the first query. One method you can use to verify this is to look at the execution plan for both queries (and you can obviously run them and compare the results).

Both queries return a count of 2.

Here are the estimated plans for both queries:

Question 3: Will these queries return the same result?

SELECT COUNT(*) AS ct
FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

SELECT COUNT(*) AS ct
FROM dbo.t1
OUTER APPLY (SELECT 1 / 0 AS x 
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: no

  • Correct: 198 (79%)
  • Incorrect: 54 (21%)

The first query returns the count of 2. The second query returns a count of 5.

This is because the first query is implemented as an inner join, and the second query is implemented as an outer join.

Question 4: Will these queries return the same result?

SELECT t1c1 * 2 AS d
FROM dbo.t2;

SELECT x.d
FROM dbo.t2
CROSS APPLY (SELECT t1c1 * 2 AS d) AS x;

Answer: yes

  • Correct: 161 (64%)
  • Incorrect: 91 (36%)

This is an example of using a CROSS APPLY to compute a calculation.

It’s pretty trivial in this case, but I find this can be very useful in the case of complex queries where you refer to the result of a calculation more than once — doing this can allow you to only do the computation in once place and then refer to it many times. Not only can this make the query more readable sometimes, it can limit the errors if you need to change the calculations (because you’re changing it in fewer places.)

 

Posted on

A New Look and Feel for the SQLWorkbooks Quiz Archive

Ch-ch-ch-ch-changes

I’ve revamped the section of the site where I archive the quizzes I make for the Weekly Quizletter. The biggest changes are:

  • I got rid of the old menu, which just didn’t scale to the amount of quizzes we have now
  • I broke the quizzes into topics. Each topic has its own page, listing the quizzes under that topic
  • I made a deeply meaningful image header for each quiz topic page

Check out the revamped quizzes section here – and take a quiz or two, why not?

I plan to do a little more fine tuning once I see if the new setup works for folks or not.

If you have feedback, I’d love to hear it.

Posted on

Dear SQL DBA: Why Don’t You Teach Service Broker?

To Broker, or Not to Broker? That is the Question

I’ve gotten a few questions about whether I recommend learning Service Broker, and recently I was asked if I’m planning to make any courses on Service Broker. In this 23 minute episode, I talk about why I personally haven’t become an expert with Broker, the factors that I believe play into Service Broker adoption, and other resources online for learning Service Broker in SQL Server.

Join in the fun

  1. Attend the Dear SQL DBA podcast — live! Register here to get an invitation.
  2. Got a question for Dear SQL DBA? Ask away.
  3. Subscribe:  If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app: http://dearsqldba.libsyn.com/rss#

Posted on

The BREAK Keyword in Transact SQL

BREAK is a useful piece of control-of-flow language in SQL Server, but it only works in specific situations.

Want to take the quiz first?

Take the quiz on BREAK here, then head back here to see how you compare to other folks who took the quiz.


Break Questions, Answers, and Explanations

1) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

BREAK;

PRINT 2;

Answer:

Msg 135, Level 15, State 1, Line 6
Cannot use a BREAK statement outside the scope of a WHILE statement.

How’d people do?

  • Correct: 237 (77%)
  • Incorrect: 70 (23%)

I know, it seems like this should work! But, in fact, BREAK only breaks you out of a WHILE loop.

For other purposes, you may want to use RETURN.

2) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
    BREAK;
    PRINT 2

Answer: 2

This one was a little trickier…

  • Correct: 202 (66%)
  • Incorrect: 105 (34%)

In this case BREAK will cause us to exit that WHILE loop (so we won’t be stuck in it forever). BREAK doesn’t cause the execution of the whole batch to terminate, though, so we go on to the next statement, and PRINT 2 is executed.

3) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
IF 1 = 1
    PRINT 2
    BREAK;

Answer:

Msg 135, Level 15, State 1, Line 6
Cannot use a BREAK statement outside the scope of a WHILE statement.

This one foiled even more folks:

  • Correct: 135 (44%)
  • Incorrect: 172 (56%)

The key to this one is that we haven’t defined a statement block. That means that the WHILE statement applies to the next statement only. The IF statement also applies to the next statement only. The BREAK is left out in the cold, and it will throw an error if it is not part of a WHILE loop.

But it’s a tricky question, because it might seem like SQL Server would get to the PRINT and be stuck in an infinite loop, never getting to the point that it throws the error. That doesn’t happen – as soon as we execute, the parse process sees the mistake and we get the error right away.

4) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
BEGIN
    BREAK;
    PRINT 2;
END

Answer: Commands completed successfully.

  • Correct: 239 (78%)
  • Incorrect: 68 (22%)

In this case, the BREAK and the PRINT are within a defined BEGIN / END statement block together, inside a WHILE loop. When we hit the BREAK it exits the loop, never getting to the PRINT statement.

 

Posted on

Using RETURN in Transact SQL: Quiz Results

RETURN is a “control of flow” language keyword in Transact-SQL. It’s very useful for managing when code does — and does NOT — get executed in procedural code, and it can also be used to communicate the status from procedural code.

But not everyone knows how to use RETURN, and learning about it can be awkward because of some quirks of SQL Server Management Studio. It is well worth taking the time to get used to it, though!

Want to test your knowledge?

Take this quiz on RETURN, then head back here to see how you compare to other folks who took the quiz.

Note: I’m going to implement a change soon so that only the four most recent quizzes are open to everyone. I am still going to post questions and answers in follow up posts, but the fun interactive bit won’t be as open (after a month for each quiz).

So now is a good time to go through my quiz archive if you haven’t already!

Quiz results and Discussion of RETURN

I changed the wording on question #4 on July 4 after Nick posted a message about the wording — and he was totally right, there was more than one defensible correct answer.

Changing the wording did not significantly change the average score, it turns out. The average percentage dropped from 65% to 64% once that question was clarified. (That’s not a bad percentage at all — I think a lot of folks aren’t used to using RETURN, and hopefully the quiz made them curious on how it can be useful.)

RETURN
GO

PRINT 2;
GO
  • Correct: 292 (78%)
  • Incorrect: 84 (22%)

Answer: 2

The key to this question is that RETURN controls flow within a given batch, and the default batch separator is the word “GO”.

If you want to stop the execution of a script you’re running in SSMS, the RETURN keyword won’t stop everything if you have more than one batch.

RETURN

PRINT 2;
GO
  • Correct: 249 (66%)
  • Incorrect: 127 (34%)

Answer: Commands completed successfully.

In this question, RETURN is shown inside a single batch. It will stop the flow of execution before the PRINT statement, and SSMS will simply report, “Commands completed successfully.”

IF 1 = 1
BEGIN
    RETURN;
END

PRINT 2;
  • Correct: 256 (68%)
  • Incorrect: 120 (32%)

Answer: Commands completed successfully.

This time we have everything in one batch statement again, but we have the RETURN inside of a BEGIN / END block.

The gist of this question is: does RETURN cause it to exit only the BEGIN/END block, or that entire block of code?

The answer is that it causes it to exit from the entire block of code, it will never reach the PRINT statement.

CREATE OR ALTER PROC dbo.test
AS
    RETURN 1;
GO

EXEC dbo.test;
GO
  • Correct: 29 (58%)
  • Incorrect: 21 (42%)

Answer: Commands completed successfully.

The wording in this post is the “corrected” wording. The original wording was less clear, so I’ve reported the correct/incorrect ratios for July 5 – 9, after the wording was changed.

This one may seem silly, but I think it’s very important to know that SSMS will not report anything in the Messages tab in the case. It doesn’t mean that RETURN didn’t work, it simply means that the SSMS interface doesn’t show it!

If you want to capture the value that RETURN sends back and do something with it, you need to assign it to something, like this:

CREATE OR ALTER PROC dbo.test
AS
    RETURN 1;
GO

DECLARE @RESULT TINYINT
EXEC @RESULT = dbo.test;

PRINT @RESULT
GO

Used in this way, RETURN can be incredibly useful when it comes to returning status from procedural code.