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.

Posted on

Find the Scalar Function Call: What it Means If It’s Hiding in Probe Residual

User defined functions are fairly simple to create in SQL Server, but figuring out exactly how they are being used can take a little time – even in a simple execution plan. This is because the functions can be tucked away into lots of different operators in the plan, even join operators.

The good news: once you learn what to look for, it’s not terribly complicated. There are some fancy terms involved, but the definitions are pretty simple.

Here’s a quick look at a function call hidden in a hash match operator, which is being used to implement an inner join.

The estimated query execution plan

This query uses the Microsoft WideWorldImporters sample database. The query calls the built in scalar user defined function, Website.CalculateCustomerPrice:

SELECT
    il.InvoiceLineID
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
    ON i.InvoiceID=il.InvoiceID
WHERE 
    [Website].[CalculateCustomerPrice] ( i.CustomerID, il.StockItemID, i. InvoiceDate ) 
        <>
     il.UnitPrice;
GO

Here’s the query execution plan:

There’s not a lot of places for the function call to hide in this particular plan, right? No Compute Scalars to look into at all.

If we look at the properties of the Hash Match join, we can find the function call:

How does the hash join work?

The description at the top of this tool tip is quite helpful when it comes to remembering how hash matches work: SQL Server is building a table in memory from the Sales.Invoices table. It’s then “probing” into it with items from the Sales.InvoicesLines table.

The “Hash Keys Probe” part of the tool tip tells us that it is looking for matches based on the InvoiceID column (our join column).

What about the ‘Probe Residual’?

After we find matches based on the customer id, we have more work “left over” — that’s the “residual” bit.

For every row that matches, SQL Server is plugging values into the Website.CalculateCustomerPrice() function and comparing the result to the Unit price column, just like we asked for in the where clause.

In other words, this is happening for every row in Sales.InvoiceLines that has a matching row in Sales.Invoices.

Which is every single invoice & invoice line, as it turns out.

Bonus

If you’re ever looking for an example of a query where collecting an actual execution plan dramatically slows down execution (observe effect), this is a good one. On instances I’ve tested, this query takes 14-18 seconds to run when no plan is collected, and about a minute longer when you enable actual plan collection.

Takeaways

When you’re looking at execution plans, it can be very tricky to spot user defined functions in the plan:

  • You need to not only check into Compute Scalar operators if they are around, but also look carefully at the properties of other operators, including joins
  • If you spot the function call as part of a “probe residual” on a hash join, it is being applied to every row that meets the initial join criteria
Posted on

ORDER BY, OFFSET, and Fetch in TSQL

My most recent Quizletter featured a quiz on ORDER BY in TSQL, complete with questions on the OFFSET and FETCH clauses which we got in SQL Server 2012.

One great thing about teaching TSQL School is that it reminds me of these cool options that can be easy to forget.

Want to take the quiz before you read the answers?

Take the quiz here first, if you’d like.  If you get 100%, you probably don’t need to read this post 😉


Overall Quiz Stats

Folks did pretty well on this quiz, but lots of people did fall into one mean little trap that I left in one question. (Sorry! I can be tricksy.) Here are the stats at the time of writing this post:

Entries: 436 Average Score (Out of 4): 2.64 Average Percentage: 66%

Now for the fun stuff, let’s talk about answers!


Question 1

1) How will the results of this query be ordered (and why)?
SELECT PersonID
FROM Application.People;

Correct: 76%
Answer: No ordering is guaranteed in this case

This is very important to know: although the answers may come back ordered by PersonID in this case, you cannot count on that always being the case, and it could change at any point. Ordering of results is never guaranteed unless you explicitly use an ORDER BY in your query.

Question 2

2) What will this ORDER BY do?*
SELECT PersonID,
FullName
FROM Application.People
ORDER BY 1 ASC;

Correct: 90%
Answer: Order the results by the first column listed, PersonID

Numbers used like this in the ORDER BY clause represent the column position. I was surprised that so many people knew the answer to this one, to be honest. It is a little bit troubling, because Microsoft calls out using numbers as positional representations in order by as an anti-pattern that should be avoided.

But, I confess, I personally do use this myself when I’m writing quick and dirty code (and sometimes forget to fix it, too).

Question 3

3) What will this OFFSET / FETCH clause do?*
SELECT FullName
FROM Application.People
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Correct: 26%
Answer: This query will throw a syntax error

Why? You can only use OFFSET with an ORDER BY. (This ain’t no TOP operator which doesn’t require ORDER BY, this is a part of ORDER BY.)

I asked this question to try to get a sense of how many people are currently using the OFFSET clause already in code and would pick out the goof based on familiarity. Seems like not many! That’s OK, but it’s good to know how this works for those times when it may come in handy.

Question 4

4) What will this OFFSET clause do?*
SELECT FullName
FROM Application.People
ORDER BY PersonID
OFFSET 10 ROWS;

Correct: 72%
Answer: Return all the rows except for the 10 with the lowest PersonIDs

You are allowed to specify OFFSET without a FETCH NEXT. In this case, we are ordering by PersonID. We didn’t specify ascending or descending, so the default ascending sort order is used for the order. We are OFFSET-ing the first 10 rows ASC, so that’s the lowest 10 PersonIDs. We’ll get all the other rows, because we haven’t limited how many will be fetched.

And now, for a terrible joke

Unlike cursors, order by has finally made fetch happen. (Sorry.)

Nice work, folks!

Posted on

New Courses: Extended Events, Parallelism, Indexes, and More

It’s been a fun, busy spring and early summer. I have loads of new courses to share, and great stuff is coming up soon!

A big thank you to all the SQLWorkbooks members who attended live events, or posted your comments and questions! Your questions are terrific, and you’ve made these events so much fun.

SQLChallenge Courses

SQL Symposium and SQL Learning League members get all SQLChallenges.

SQLChallenge: Create an Extended Events Trace (55 minutes)

Follow the challenge script to reproduce a problem with Query Store in your test instance of SQL Server.

Then, your challenge is to configure a trace that will capture the problem — and also learn more about when the trace will capture an event, and when it won’t.

Challenge difficulty: This 200 level challenge will be pretty quick for folks who have configured Extended Events traces before. If you haven’t done that yet, it may take a little more time — but it’s worth it! These traces can be extremely useful.

Design the Best Index For One Year Wonders – SQLChallenge (50 minutes)

Can you reduce the logical reads as much as possible for the sample query using a disk based rowstore index?

Challenge difficulty: This challenge comes with three levels of difficulty. Even the 200 level, creating a nonclustered index without using advanced features, is interesting in this case due to the nature of the query.

Write a Query to Find “Revived” Names SQLChallenge (45 minutes)

I love writing a good query! This challenge is all about TSQL

Challenge difficulty: This is a 300 level TSQL challenge. If you’ve written queries to solve this kind of problem before, you may be able to blast through it- so there’s an “extra credit” option for you to strut your stuff in that case. If this is your first time thinking through approaches to the problem, it may take longer — but this is a pattern that will very likely prove useful in your query writing future.


SQL Seminar Recordings

SQL Symposium members get to attend all seminars live, plus watch recordings anytime.

How To Decipher CXPACKET Waits and Control Parallelism (4 hours)

Learn how to configure SQL Server’s degree of parallelism, how to dictate which queries go parallel, how to force parallelism, and how to avoid performance-killing anti-patterns.

You’ll also get the scoop on interpreting CXPACKET waits and the new CXCONSUMER waits introduced in recent versions of SQL Server.

Learn Indexing By Solving Problems – SQL Seminar – June 2018 (7 hours 23 minutes)

Learn to design and tune indexes by taking this set of eight indexing SQLChallenges!

This course builds your skills at architecting disk based rowstore indexes in SQL Server, and teaches you how indexes work at the same time. The challenges in this course teach you about clustered and nonclustered indexes, filtered indexes, indexed views, and more.


Upcoming Live SQL Seminars

Join the SQL Symposium to get all online seminars, live and recorded. Or purchase seminars individually.

Conquer Blocking and Isolation Levels

Are your users getting wrong data back? Learn how to get correct data and minimize blocking in this eight hour seminar.

  • July 11-12, 2018

Query Tuning Jam Session

Sharpen your query tuning skills in this four hour course of live SQLChallenges and solutions.

  • July 30, 2018

How to Decode Memory Pressure

Learn to identify and monitor both external and internal memory pressure against SQL Server in this four hour seminar.

  • August 14, 2018

Learn Indexing by Solving Problems – September Edition

Learn to architect disk-based rowstore indexes in eight hours of indexing challenges and solutions.

  • Watch the previously recorded seminar right away here
  • September 24-25, 2018

T-SQL School: SELECT Seminars

Learn Transact-SQL by writing queries each week: Twelve one-hour sessions. Additional signups for this experimental new course are only available via the SQL Symposium.

  • Watch the recorded sessions so far here
  • 1PM – 2PM PDT, each Saturday through Aug 11, 2018

 


Considering Joining?

Coupon code summersql will get you a big discount on the SQL Symposium or the SQL Learning League through June 28th — and it’s a recurring discount too!

As always, you can control the length of your subscription anytime. If you just want one year, you can cancel immediately and still get access for the rest of the year.

Posted on

AVG() in TSQL: Watch Your Data Types

This week’s Quizletter featured a quiz on using the AVG() function in SQL Server.

I was inspired to write this quiz because I’ve been teaching TSQL School each week. When we covered aggregate functions, I remembered just how tricksy and non-average that AVG() can be.

Want to take the quiz before you read the answers?

Head over here and take the quiz first.

Or read this post and then take the quiz, if you prefer. I’m not gonna call it cheating, because it’s learning either way.


Question 1: AVG of 1 and 1

In question 1, we average a value on two rows: one and one.

How did folks do on this question?

  • Correct: 91% – These folks guessed the answer would be: 1
  • Incorrect: 9% – These folks almost all guessed the answer would be: 1.0

This seems like a subtle difference, but the key to the answer is understanding what data type SQL Server will implicitly convert these numbers to. And data types matter a lot when it comes to the results you can get out of AVG. In this case, 1 will be implicitly converted to an INT data type.

Question 2: AVG of 1 and 2

Here’s where the wacky fun begins: if I average two ints, one and two, I get back… one!

  • 46% – YAY TEAM! Great work! This is bizarro, so good on you folks.
  • 54% – I feel your pain. Most of these folks guessed 1.5, a few guessed 1.500000

What does this mean? Well, let’s look at an example with negative numbers to help get a bigger picture:

When we round integers -1 and -2, we get -1.

How to describe this behavior

There are two different ways you can think about this behavior. Pick the one that is easier for you to remember.

Description 1: SQL Server rounds integers “toward zero”. (When the average was positive, we got the ‘floor’. When the average was negative, we got the ‘ceiling’.)

Description 2: It’s truncating the decimal bits.

Extra credit: Guess which one of these descriptions came from me, and which one came from my computer scientist partner.

Question 3: Mixing in a CAST()

Aha! Now we’re getting fancy. We have a CAST function outside of the AVG function.

The keys to this answer are that CAST will be run after the AVG function completes, and the AVG function is still returning 1, because inside the AVG function, it sees the INT data type.

  • Correct: 36% – These cunning quizzers worked out that AVG would truncate the decimal bits and return 1, and that this would then be cast to 1.0
  • Incorrect: 64% – Most folks guessed 1.5

Question 4: Move that CAST() inside the AVG()

Here we finally have an example where I am changing the data type inside the AVG, and we are casting to a numeric type with precision of three, scale of one. (One number past the decimally bit!)

And, yeah, look at that, we get back a result with a scale of SIX.

In fact, if you to the trouble to see what type we end up with here (like by selecting the results into a temp table and inspecting the data types), this is numeric(38,6). 38 is the highest precision you can get with numeric.

  • Correct: 37% – Clever rabbits!
  • Incorrect: 63% – Most of these folks guessed 1.5, and I do not blame you at all

What’s up with this? Well, there’s a bit of info inside the documentation on AVG, but it’s about the decimal data type. Numeric and decimal are twins (but you want to use one or the other consistently, because of things like foreign keys). In the section on return types, it explains that the return type for decimal category (p, s) will be:

decimal(38, s) divided by decimal(10, 0)

Plugging that formula into the numbers we’re working with in this question:

Yep, it checks out.

Takeaways

Weird things like this are why some folks say not to do calculations in the SQL language.

For times when you do need to do calculations in TSQL, remember to think about your data types– both inside your functions, and outside.