Press "Enter" to skip to content

Category: T-SQL

Randomization With NEWID()

Published 2018-06-18 by Kevin Feasel

Michael J. Swart tests whether ORDER BY NEWID() produces a biased result:

One of his articles, Visualizing Algorithms has some thoughts on shuffling at https://bost.ocks.org/mike/algorithms/#shuffling.

He says that sorting using a random comparator is a rotten way to shuffle things. Not only is it inefficient, but the resulting shuffle is really really biased. He goes on to visualize that bias (again, I really encourage you to go see his stuff).

Ordering by random reminded me of the common technique in SQL Server of ORDER BY newid(). So I wondered whether an obvious bias was present there. So I shuffled 100 items thousands of times and recreated the visualization of bias in a heat map (just like Mike did).

I’ve used this pattern to good effect, but definitely pay attention to Michael’s warning at the end.

Comments closed

Using RAISERROR Instead Of PRINT

Published 2018-06-07 by Kevin Feasel

Randolph West recommends using RAISERROR WITH NOWAIT rather than PRINT for printing messages:

Read that last line again. It’s saying that the PRINT command needs to fill some sort of memory buffer before you see any output. Sometimes you’re lucky, and sometimes you’re not. Erland Sommarskog says the same thing in the series Error and Transaction Handling in SQL Server (Part 1: Jumpstart Error Handling | Part 2: Commands and Mechanisms | Part 3: Implementation).

If you want immediate output from your long-running process, use RAISERROR instead of PRINT, and make use of the WITH NOWAIT parameter.

Naturally I can’t leave you hanging with a 150-word blog post, most of which is quoting Erik, so I decided to do some digging. The question I posed myself was “How big is the PRINT buffer?”

I always use error level 10, as that’s the minimum level guaranteed to print to the console immediately and it does not write to error logs or stop operations.

Comments closed

There’s Only One Way To Order

Published 2018-05-31 by Kevin Feasel

Matthew McGiffen notes that there is only one way to order, and that is to use the ORDER BY clause:

Everyone, at the beginning of their SQL career, get’s told that it is important to include an ORDER BY if they want the results ordered. Otherwise the order in which they are returned is not guaranteed.

But then you run queries a lot of times that don’t need a specific order – and you see that they (at least seem to) come out in the same order every time. You could (almost) be forgiven for thinking you can rely on that.

There was even a question on a Microsoft SQL certification exam a few years ago that asked what the default order was for records returned by a simple SELECT – the answer it was looking for was that it would be according to the order of the clustered index. So you can rely on that – right?

Wrong. The question was a bad question, and the answer was incorrect. Let’s look at this in action.

Order is never guaranteed to be stable unless you specify a unique ordering using ORDER BY.

Comments closed

Tic-Tac-Toe In T-SQL

Published 2018-05-29 by Kevin Feasel

Riley Major implements Tic-Tac-Toe in T-SQL:

It turns out there’s a concept called bitmasking which can work a lot like this cardboard cut-out process. (Props to Dylan Beattie for his quick visual demonstration at NDC Minnesota which drove this point home.) First, you represent your game state with a bunch of bits (“OXOOOXXXX” yields “0100011110” for our example above, remembering that we’re padding that last 0 just to make the powers 1-based instead of 0-based) and then you represent your winning state with a bunch of bits (“0000001110” for our example winning state here). Now you use the magic of “bitwise math” to compare the two.

For our use, we want to find out whether our mask exposes the winning three bits. We want to block everything else out. With bits, to check if both items are true, you use “AND” (0 and 0 is 0; 0 and 1 is 0; 1 and 1 is 1). If we apply that “AND” concept to each bit in our game, it will squash out any values which don’t match. If what we have left matches the mask (fills in all of the space we can see through), then we have a match and a win.

The twist in all of this is that the end result doesn’t quite work as expected, but it was interesting watching the process.  That said, there’s a good reason why we don’t use T-SQL as a primary language for development…

Comments closed

Finding Procedure Parameters Which Don’t Match Column Names

Published 2018-05-29 by Kevin Feasel

Shane O’Neill has a process to update procedures to make input parameter names match output column names:

I was asked to standardise stored procedures we use for common support cases before we hand them over to IT Helpdesk.

One of the comments that came back from the Helpdesk while testing was that the parameter names that they had to put values in for didn’t match what they saw in the application.

Luckily for me (or unluckily) the application was a third party developed one and they didn’t bother renaming the columns. So if the column is called create_date in the database then the application is going to show create_date.

However, if I created a parameter called DateCreated or even CreateDate, then they don’t want it.

Shane has a Powershell script which uses the Find-DbaStoredProcedure method in dbatools; click through to see it in action.

Comments closed

Bitwise Logic To Make Values Negative

Published 2018-05-25 by Kevin Feasel

David Fowler is working with arcane magic:

The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT.

If you want to apply a bitwise NOT to a value, simply use the ~

Give it a go, try converting 10 to a negative,

SELECT ~ 10 + 1

The result, -10.

One of my rules of thumb (given how many I have, I need more thumbs) is that if you’re using bitwise logic in SQL Server, something has likely gone wrong somewhere along the way.  But read David’s explanation, which is quite clear.

Comments closed

Executing Dynamic SQL: EXEC vs sp_executesql

Published 2018-05-24 by Kevin Feasel

Tara Kizer gives a scenario where using sp_executesql can result in lower CPU utilization than EXEC:

For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues.

For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated plan cache with lots of single-use execution plans.

Let’s compare the performance difference between “EXEC (@sql)” and “EXEC sp_executesql @sql” for a frequently executed query.

For application code, I tend to lean on sp_executesql very heavily to create parameterized queries.

Comments closed

Deferred Name Resolution In SQL Server

Published 2018-05-23 by Kevin Feasel

Kendra Little explains the concept of deferred name resolution in SQL Server:

In this case, I’m creating a temporary stored procedure (out of laziness, it means I don’t have to clean up a quick demo) –

CREATE OR ALTER PROCEDURE #test
AS
IF 1=0
    EXECUTE dbdoesnotexist.dbo.someproc;
GO

The database dbdoesnotexist does NOT exist, but I’m still allowed to create the procedure.

When I do so, I get an informational message:

The module ‘#test’ depends on the missing object ‘dbdoesnotexist.dbo.someproc’. The module will still be created; however, it cannot run successfully until the object exists.

This can be useful in some cases where you’ll be querying a table or procedure that may not exist all the time, but which will exist when a certain code block is run.

But, as Kendra points out, deferred name resolution doesn’t work everywhere, so it’s important to know the rules around when it will or will not work.

Comments closed

T-SQL Join Delete

Published 2018-05-18 by Kevin Feasel

Steve Stedman walks us through a bit of T-SQL proprietary syntax:

1
2
3
DELETE t2
FROM [dbo].[Table1] t1
INNER JOIN [dbo].[Table2] t2 on t1.favColor = t2.id;

Names have been changed to protect the innocent.

In the above delete statement which table will have rows deleted from it?

A: Table1

B: Table2

C: Both Table1 and Table2

D: Neither Table1 and Table2

Got it in one.  I like having this syntax available to me when I need it, even though it’s not ANSI standard.

Comments closed