SQL Server Consulting, Education, and Training
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If the Gods of post scheduling cooperate, this should publish in October. That means you’ve got a little more than a month to buy tickets and book travel to PASS.
And of course, if you’re going to go, I’d love it you came to my precon: The Professional Performance Tuning Blueprint.
Searching the internet for every problem isn’t cutting it.
You need to be more proactive and efficient when it comes to finding and solving database performance fires. I work with consulting customers around the world to put out SQL Server performance fires.
In this day of learning, I will teach you how to find and fix your worst SQL Server problems using the same modern tools and techniques which I use every week.
You’ll learn tons of new and effective approaches to common performance problems, how to figure out what’s going on in your query plans, and how indexes really work to make your queries faster.
Together, we’ll tackle query rewrites, batch mode, how to design indexes, and how to gather all the information you need to analyze performance.
This day of learning will teach you cutting edge techniques which you can’t find in training by folks who don’t spend time in the real world tuning performance.
Performance tuning mysteries can easily leave you stumbling through your work week, unsure if you’re focusing on the right things.
You’ll walk out of this class confident in your abilities to fix performance issues once and for all.
If you want to put out SQL Server performance fires, this is the precon you need to attend. Anyone can have a plan, it takes a professional to have a blueprint.
All attendees will get free access to my performance tuning training library, which also covers the material covered in the precon.
In-person attendees will also get a cool t-shirt. Arguably the coolest t-shirt ever made for a SQL Server conference.
The PASS Data Community Summit is taking place in Seattle November 15-18, 2022 and online.
You can register here, to attend online or in-person. I’ll be there in all my fleshy goodness, and I hope to see you there too!
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I’m still looking into this a bit, but I ran into this issue when helping set up a process to migrate data to a table because the original table was running out of integer identity values.
The process looks something like:
All well and good! Except… You can end up with a really weird execution plan for the backfill process, and some pretty gnarly memory grants for wide tables.
Demonstrating with the Votes table:
CREATE TABLE
dbo.Votes_bigint
(
Id bigint IDENTITY NOT NULL,
PostId int NOT NULL,
UserId int NULL,
BountyAmount int NULL,
VoteTypeId int NOT NULL,
CreationDate datetime NOT NULL,
CONSTRAINT PK_Votes_bigint_Id
PRIMARY KEY CLUSTERED (Id ASC)
)
GO
INSERT
dbo.Votes_bigint
(
Id,
PostId,
UserId,
BountyAmount,
VoteTypeId,
CreationDate
)
SELECT TOP (100000)
v.Id,
v.PostId,
v.UserId,
v.BountyAmount,
v.VoteTypeId,
v.CreationDate
FROM dbo.Votes AS v
ORDER BY v.Id;
The query plan has a Sort in it, which is weird because… Both tables are primary key/clustered index on the same column. Why re-sort that data?
I’m showing both serial and parallel plans:
The Sort is always on this expression:
Which in the XML looks like this:
[StackOverflow2013].[dbo].[Votes_bigint].[Id] = [Expr1003]
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
I use sp_HumanEvents to troubleshoot all sorts of SQL Server issues.
Most frequently, I used it to troubleshoot stored procedure performance like this:
EXEC sp_HumanEvents
@event_type = 'query',
@query_duration_ms = 5000,
@session_id = N'60',
@keep_alive = 1,
@debug = 1;
The session it creates will have this definition:
CREATE EVENT SESSION keeper_HumanEvents_query
ON SERVER
ADD EVENT sqlserver.module_end
(SET collect_statement = 1
ACTION (sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed)
WHERE (
sqlserver.is_system = 0
AND duration >= 5000000
AND sqlserver.session_id = 60
)),
ADD EVENT sqlserver.rpc_completed
(SET collect_statement = 1
ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed)
WHERE (
sqlserver.is_system = 0
AND duration >= 5000000
AND sqlserver.session_id = 60
)),
ADD EVENT sqlserver.sp_statement_completed
(SET collect_object_name = 1, collect_statement = 1
ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed)
WHERE (
sqlserver.is_system = 0
AND duration >= 5000000
AND sqlserver.session_id = 60
)),
ADD EVENT sqlserver.sql_statement_completed
(SET collect_statement = 1
ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed)
WHERE (
sqlserver.is_system = 0
AND duration >= 5000000
AND sqlserver.session_id = 60
)),
ADD EVENT sqlserver.query_post_execution_showplan
(
ACTION(sqlserver.database_name, sqlserver.sql_text, sqlserver.plan_handle, sqlserver.query_hash_signed, sqlserver.query_plan_hash_signed)
WHERE (
sqlserver.is_system = 0
AND duration >= 5000000
AND sqlserver.session_id = 60
))
I collect:
Not all of them are relevant to stored procedure calls, but in larger contexts where I have no idea where long running queries might be coming from, it’s useful to get all these.
Where I find things getting somewhat annoying is when things start showing up in there that meet the duration filter, but don’t really give me anything further to go on.
To simulate what I mean, I’m going to use this stored procedure:
CREATE OR ALTER PROCEDURE
dbo.Eventually
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
WAITFOR DELAY '00:00:01.000';
SELECT TOP (1) * FROM dbo.Badges AS b;
WAITFOR DELAY '00:00:01.000';
SELECT TOP (1) * FROM dbo.Comments AS c;
WAITFOR DELAY '00:00:01.000';
SELECT TOP (1) * FROM dbo.Posts AS p;
WAITFOR DELAY '00:00:01.000';
SELECT TOP (1) * FROM dbo.Users AS u;
WAITFOR DELAY '00:00:01.000';
SELECT TOP (1) * FROM dbo.Votes AS v;
WAITFOR DELAY '00:00:01.000';
END;
GO
EXEC dbo.Eventually;
There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.
If I watch the event output, eventually, I’ll see this:
Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.
To troubleshoot further, I have to set the duration bar even lower, and then figure out what I can meaningfully tune.
I see this behavior quite a bit with queries that loop/cursor over tons of data. They might take a whole bunch of rows and do a lot of really fast queries over them, but the time adds up.
There’s not really a good solution for this, either. The closer you look, by reducing the duration filter to lower and lower values, the more you return, the more overhead you cause, and the longer you have to wait for things to finish.
Sure, you can wring someone’s neck about not “thinking in sets”, but that doesn’t help when the processing takes place by passing looped values to stored procedures, unless you have the freedom to make significant app changes by using table valued parameters or something instead.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Far and away, some of the trickiest situations I run into when helping clients is rewriting scalar functions that have WHILE loops in them.
This sort of procedural code is often difficult, but not impossible, to replace with set-based logic.
Sure, lots of IF/THEN/ELSE stuff can be tough too, though that’s often easier to manage with CASE expressions in stacked CTEs or derived tables.
I ran across a really interesting function recently that I had to rewrite that had a couple WHILE loops in it, and I’ve simplified the example here to show my approach to fixing it.
The original intent of the function was to do some string manipulation and return a cleaned version of it.
There were several loops that looked for “illegal” characters, add in formatting characters (like dashes), etc.
The problem the function caused wasn’t it running for a long time (we’ll talk more about that tomorrow), it was that the function was called in really critical code paths that Function Repercussions© were messing with:
These are not the kinds of functions that are Froid Friendly© either. If they were, I could largely leave them alone. Maybe.
Depends on bugs.
The bad way of doing this is like so. If you write functions like this, feel bad. Let it burn a little.
Ten years ago, I’d understand. These days, there’s a billion blog posts about why this is bad.
CREATE OR ALTER FUNCTION
dbo.CountLetters_Bad
(
@String varchar(20)
)
RETURNS bigint
AS
BEGIN
DECLARE
@CountLetters bigint = 0,
@Counter int = 0;
WHILE
LEN(@String) >= @Counter
BEGIN
IF PATINDEX
(
'%[^0-9]%',
SUBSTRING
(
@String,
LEN(@String) - @Counter,
1
)
) > 0
BEGIN
SET @CountLetters += 1;
SET @Counter += 1;
END;
ELSE
BEGIN
SET @Counter += 1;
END;
END;
RETURN @CountLetters;
END;
GO
SELECT
CountLetters =
dbo.CountLetters_Bad('1A1A1A1A1A');
This is a better way to write this specific function. It doesn’t come with all the baggage that the other function has.
But the thing is, if you just test them with the example calls at the end, you wouldn’t nearly be able to tell the difference.
CREATE OR ALTER FUNCTION
dbo.CountLetters
(
@String AS varchar(20)
)
RETURNS table
AS
RETURN
WITH
t AS
(
SELECT TOP(LEN(@String))
*,
s =
SUBSTRING
(
@String,
n.Number +1,
1
)
FROM dbo.Numbers AS n
)
SELECT
NumLetters =
COUNT_BIG(*)
FROM t
WHERE PATINDEX('%[^0-9]%', t.s) > 0;
GO
SELECT
cl.*
FROM dbo.CountLetters('1A1A1A1A1A') AS cl;
This is a problem I run into a lot: developers don’t really test SQL code in ways that are realistic to how it’ll be used.
But this is hardly the methodology you should be using, because:
In tomorrow’s post, I’m gonna show you an example of how to better test code that calls functions, and what to look for.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Recently while working with a client to fix some deadlock issues, they asked about the client options stored in the XML.
I could remember looking at them at one point to see if there was a decoder ring for them, but never found anything definitive.
There’s a similar type of bittish thing in dm_exec_plan_attributes. It gets decoded in sp_BlitzCache like so:
SetOptions = SUBSTRING(
CASE WHEN (CAST(pa.value AS INT) & 1 = 1) THEN ', ANSI_PADDING' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 8 = 8) THEN ', CONCAT_NULL_YIELDS_NULL' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 16 = 16) THEN ', ANSI_WARNINGS' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 32 = 32) THEN ', ANSI_NULLS' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 64 = 64) THEN ', QUOTED_IDENTIFIER' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 4096 = 4096) THEN ', ARITH_ABORT' ELSE '' END +
CASE WHEN (CAST(pa.value AS INT) & 8192 = 8191) THEN ', NUMERIC_ROUNDABORT' ELSE '' END
, 2, 200000)
Based on that, I was hopeful that I could use a combination of SSMS and session settings to figure out where bits flip in client options.
I don’t think I got all of them, but this simple demo shows off the ones that were accessible. Some caveats here:
Here’s what I came up with, using my own experimenting, and also some test data from deadlock XML files I had sitting around.
CREATE TABLE
#temptable
(
clientoption1 bigint,
clientoption2 bigint
);
INSERT INTO
#temptable
(
clientoption1,
clientoption2
)
VALUES
(536870944, 128056),
(671088672, 128056),
(671088672, 128058),
(673185824, 128056),
(673316896, 128056);
SELECT
q.clientoption1,
q.clientoption2,
clientoption1 =
SUBSTRING
(
CASE WHEN q.clientoption1 & 0x20 = 0x20 THEN ', QUOTED IDENTIFIER ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x40 = 0x40 THEN ', ARITHABORT' ELSE '' END +
CASE WHEN q.clientoption1 & 0x800 = 0x800 THEN ', USER SET ARITHABORT' ELSE '' END +
CASE WHEN q.clientoption1 & 0x8000 = 0x8000 THEN ', NUMERIC ROUNDABORT ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x10000 = 0x10000 THEN ', USER SET NUMERIC ROUNDABORT ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x20000 = 0x20000 THEN ', SET XACT ABORT ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x80000 = 0x80000 THEN ', NOCOUNT OFF' ELSE '' END +
CASE WHEN q.clientoption1 & 0x200000 = 0x200000 THEN ', NOCOUNT ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x8000000 = 8000000 THEN ', USER SET QUOTED IDENTIFIER' ELSE '' END +
CASE WHEN q.clientoption1 & 0x20000000 = 0x20000000 THEN ', ANSI NULL DEFAULT ON' ELSE '' END +
CASE WHEN q.clientoption1 & 0x40000000 = 0x40000000 THEN ', ANSI NULL DEFAULT OFF' ELSE '' END,
3,
8000
),
clientoption2 =
SUBSTRING
(
CASE WHEN q.clientoption2 & 2 = 2 THEN ', IMPLICIT TRANSACTION' ELSE '' END +
CASE WHEN q.clientoption2 & 8 = 8 THEN ', ANSI WARNINGS' ELSE '' END +
CASE WHEN q.clientoption2 & 0x10 = 0x10 THEN ', ANSI PADDING' ELSE '' END +
CASE WHEN q.clientoption2 & 0x20 = 0x20 THEN ', ANSI NULLS' ELSE '' END +
CASE WHEN q.clientoption2 & 0x1000 = 0x1000 THEN ', USER CONCAT NULL YIELDS NULL' ELSE '' END +
CASE WHEN q.clientoption2 & 0x2000 = 0x2000 THEN ', CONCAT NULL YIELDS NULL' ELSE '' END +
CASE WHEN q.clientoption2 & 0x4000 = 0x4000 THEN ', USER ANSI NULLS' ELSE '' END +
CASE WHEN q.clientoption2 & 0x8000 = 0x8000 THEN ', USER ANSI WARNINGS' ELSE '' END,
3,
8000
)
FROM #temptable AS q;
I may work on getting these into some deadlock analysis tooling after I have a little more validation that the results are correct.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If the Gods of post scheduling cooperate, this should publish on October 3rd. That means you’ve got a little more than a month to buy tickets and book travel to PASS.
And of course, if you’re going to go, I’d love it you came to my precon: The Professional Performance Tuning Blueprint.
Searching the internet for every problem isn’t cutting it.
You need to be more proactive and efficient when it comes to finding and solving database performance fires. I work with consulting customers around the world to put out SQL Server performance fires.
In this day of learning, I will teach you how to find and fix your worst SQL Server problems using the same modern tools and techniques which I use every week.
You’ll learn tons of new and effective approaches to common performance problems, how to figure out what’s going on in your query plans, and how indexes really work to make your queries faster.
Together, we’ll tackle query rewrites, batch mode, how to design indexes, and how to gather all the information you need to analyze performance.
This day of learning will teach you cutting edge techniques which you can’t find in training by folks who don’t spend time in the real world tuning performance.
Performance tuning mysteries can easily leave you stumbling through your work week, unsure if you’re focusing on the right things.
You’ll walk out of this class confident in your abilities to fix performance issues once and for all.
If you want to put out SQL Server performance fires, this is the precon you need to attend. Anyone can have a plan, it takes a professional to have a blueprint.
All attendees will get free access to my performance tuning training library, which also covers the material covered in the precon.
In-person attendees will also get a cool t-shirt. Arguably the coolest t-shirt ever made for a SQL Server conference.
The PASS Data Community Summit is taking place in Seattle November 15-18, 2022 and online.
You can register here, to attend online or in-person. I’ll be there in all my fleshy goodness, and I hope to see you there too!
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Defining things scares people. Pause for a moment to think about how many scripts have been written where some roustabout has a mental breakdown when someone refers to them as a boyfriend or girlfriend.
Table definitions have a similar effect on developers. In today’s post, I’m going to use temp tables as an example, but the same thing can happen with regular tables, too.
The issue isn’t with NULL values themselves, of course. The table definition we’re going to use will allow NULLs, but no NULLs will be present in the data.
The issue is with how you query NULLable columns, even when no NULLs are present.
Let’s take a look!
Let’s create a temporary table that allows for NULLs, and fill it with all non-NULL values.
CREATE TABLE
#comment_sil_vous_plait
(
UserId int NULL
);
INSERT
#comment_sil_vous_plait WITH(TABLOCK)
(
UserId
)
SELECT
c.UserId
FROM dbo.Comments AS c
WHERE c.UserId IS NOT NULL;
Unfortunately, this is insufficient for SQL Server’s optimizer down the line when we query the table.
But we need one more table to round things out.
This temporary table will give SQL Server’s optimizer all the confidence, temerity, and tenacity that it needs.
CREATE TABLE
#post_sil_vous_plait
(
OwnerUserId int NOT NULL
);
INSERT
#post_sil_vous_plait WITH(TABLOCK)
(
OwnerUserId
)
SELECT
p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId IS NOT NULL;
Just three tiny letters. N-O-T.
That’s all it takes.
If you’ve been hanging around SQL Server for long enough, you’re probably aware of what happens when you use NOT IN and encounter NULL values in your tables.
It says “nope” and gives you an empty result (or a NULL result!) because you can’t match values to NULLs that way.
SELECT
c = COUNT_BIG(*)
FROM #post_sil_vous_plait AS psvp
WHERE psvp.OwnerUserId NOT IN
(
SELECT
csvp.UserId
FROM #comment_sil_vous_plait AS csvp
);
SELECT
c = COUNT_BIG(*)
FROM #post_sil_vous_plait AS psvp
WHERE NOT EXISTS
(
SELECT
1/0
FROM #comment_sil_vous_plait AS csvp
WHERE csvp.UserId = psvp.OwnerUserId
);
But since we have no NULLs, well, we don’t have to worry about that.
But we do have to worry about all the stuff SQL Server has to do to see if any NULLs come up.
For the NOT IN query, which runs about 4.5 seconds, there are two separate scans of the #comments table.
Most of this query plan is expected. There’s a scan of #comments, a scan of #posts, and a hash join to bring them together.
But down below, there’s an additional branch with a row count spool, and a predicate applied to the scan looking for NULL values. The spool doesn’t return data, it’s just there to look for a NULL value and bail the query out if it finds one.
For the NOT EXISTS query, which finishes in 2.5 seconds, we have all the expected parts of the above query plan, but without the spool.
You could partially solve performance issues in both queries by sticking a clustered index on both tables.
If you’re into that sort of thing (I am).
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
In yesterday’s post, I showed you a function rewrite from Scalar to Inline Table Valued, and gave you a couple example calls.
Now, if this is all you’re doing with a function, there’s absolutely no need to rewrite them.
SELECT
cl.*
FROM dbo.CountLetters('1A1A1A1A1A') AS cl;
SELECT
CountLetters =
dbo.CountLetters_Bad('1A1A1A1A1A');
If you’re doing something like this, and maybe assigning it to a variable or using it to guide some branching logic, don’t you sweat it for one single solitary second.
You may want to make sure whatever code inside the function runs well, but changing the type of function here isn’t going to improve things.
More realistically, though, you’re going to be calling functions as part of a larger query.
Let’s say you’re doing something a bit like this:
SELECT
u.DisplayName,
TotalScore =
SUM(p.Score * 1.),
Thing =
dbo.CountLetters_Bad(u.DisplayName)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE p.Id = v.PostId
)
AND u.Reputation >= 100000
GROUP BY
u.DisplayName
ORDER BY
TotalScore DESC;
It’s a far different scenario than just assigning the output of a Scalar UDF to a variable or using it to guide some branching logic.
A few minor syntax changes to the function and to how the query calls it can make a big difference.
SELECT
u.DisplayName,
TotalScore =
SUM(p.Score * 1.),
Thing =
(SELECT * FROM dbo.CountLetters(u.DisplayName))
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE p.Id = v.PostId
)
AND u.Reputation >= 100000
GROUP BY
u.DisplayName
ORDER BY
TotalScore DESC;
Since this is a table valued function, you have to ask for results from it like you’d ask for results from a table.
Whether you like competitive sports, good vs. evil, Great British Bake Off, or watching election results, you can appreciate the magnificence of a Flawless Victory©.
As long as someone wins who you’re rooting for. But here, no one’s rooting for scalar functions. They’re just unlovable.
I’m going to show you the very end of these plans to see the timing differences.
The Scalar UDF plan takes ~23 seconds, and the inline TVF plan takes 7.5 seconds.
And this is why testing certain linguistic elements in SQL needs to be done realistically. Just testing a single value would never reveal performance issues.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
So, the rules require that I use this picture:
Hopefully those requirements are met. There may be a more romantic way of following the rules, but I’m not very good at either one.
No one has ever accused me of sending flowers.
If you write the good kind of dynamic SQL, that is:
You’ll probably have run into some silly-ish errors in the practice. Namely, that sp_executesql expects your SQL string and your Parameter string to be NVARCHAR(…).
DECLARE
@sql varchar(MAX) = 'SELECT x = 1;'
EXEC sys.sp_executesql
@sql;
Msg 214, Level 16, State 2, Procedure sys.sp_executesql, Line 1 [Batch Start Line 0]
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.
The thing is, if you write complex enough branching dynamic SQL with many paths, you have to:
IF @1 = 1 BEGIN SET @sql += N'...' END; IF @2 = 2 BEGIN SET @sql += N'...' END; IF @3 = 3 BEGIN SET @sql += N'...' END; IF @4 = 4 BEGIN SET @sql += N'...' END; IF @5 = 5 BEGIN SET @sql += N'...' END;
And that’s just… tough. If you miss one, your string could go all to the shape of pears. Curiously, the last time I wrote for a T-SQL Tuesday, it was also about dynamic SQL.
So what’s up this time?
If you know that no part of your string is going to contain unicode characters that need to be preserved, it is easier to do something like this:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT x = 1;';
IF @1 = 1 BEGIN SET @sql += '...' END;
IF @2 = 2 BEGIN SET @sql += '...' END;
IF @3 = 3 BEGIN SET @sql += '...' END;
IF @4 = 4 BEGIN SET @sql += '...' END;
IF @5 = 5 BEGIN SET @sql += '...' END;
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
No worrying about missing an N string prefix, and then set the nvarchar parameter to the value of the varchar string at the end, before executing it.
This can save a lot of time, typing, and debugging.
Where you have to be careful is when you may have Unicode characters in identifiers:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT p = ''アルコール'';'
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
This will select five question marks. That’s not good. We lost our Unicodeness.
But you are safe when you have them in parameters, as long as you declare them correctly as nvarchar:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT p = @p;',
@p nvarchar(10) = N'アルコール',
@params nvarchar(MAX) = N'@p nvarchar(10)';
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql,
@params,
@p;
GO
This will select アルコール and we’ll all drink happily ever after.
One side piece of advice that I would happily give Young Erik, and all of you, is not to rely on data type inheritance to preserve MAX-ness.
As you concatenate strings together, it’s usually a smart idea to keep those strings pumped up:
DECLARE
@nsql nvarchar(MAX) = N'',
@vsql varchar(MAX) = 'SELECT x = 1;';
IF @1 = 1 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @2 = 2 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @3 = 3 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @4 = 4 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
IF @5 = 5 BEGIN SET @sql += CONVERT(varchar(max), '...') END;
SET @nsql = @vsql;
EXEC sys.sp_executesql
@nsql;
GO
And of course, if you need to print out longer strings, I’d recommend Helper_LongPrint, or using the XML processing instruction function to XML-ify things.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.