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.
Empty results are often dissatisfying. Especially when stored procedures return multiple result sets, it can be easy to get lost in which result is empty.
Of course, there’s many a good argument to be made against actual production stored procedures returning multiple results — usually these should be multiple procedures — I see it happen quite a lot.
Rather selfishly, I do this for my stored procedures, for all the reasons in the first sentence. Especially when debugging stored procedures, you’ll want to know where things potentially went wrong.
In this post, I’m going to walk through a couple different ways that I use to do this. One when you’re storing intermediate results in a temporary object, and one when you’re just using a single query.
This is fairly obvious and straightforward. You do the insert, check the row count, and return a message if it’s zero:
DECLARE
@t table
(
database_id integer,
database_name sysname
);
INSERT
@t
(
database_id,
database_name
)
SELECT
d.database_id,
d.name
FROM sys.databases AS d
WHERE d.database_id > 32767;
IF @@ROWCOUNT > 0
BEGIN
SELECT
t.*
FROM @t AS t;
END;
ELSE
BEGIN
SELECT
msg = 'table @t is empty!'
END;
Nothing new under the sun in this one at all.
Let’s say you don’t want or need a temporary object. Your query is good enough, smart enough, and gosh darn it etc.
This is a particularly tricky one, because there’s no way to check the row count from within the query. In this case, you can use a common table expression in a rather handy way.
WITH
d AS
(
SELECT
d.database_id,
d.name
FROM sys.databases AS d
WHERE d.database_id > 32766
)
SELECT
d.*
FROM d
UNION ALL
SELECT
0,
'table @t is empty!'
WHERE NOT EXISTS
(
SELECT
1/0
FROM d AS d2
);
Of course, the usual caveats about common table expressions bear repeating here: The query within the common table expression will run twice:
I don’t recommend this approach for long running queries within common table expressions, since this is essentially double your displeasure, double your misery.
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.
Thanks for watching!
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.
This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.
All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.
Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.
Here’s what I’ll be presenting:
Session Abstract:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Get your tickets here for this event!
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 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 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.
Darling Data was born too late for MySpace, Friendster, Facebook, and Make Out Club (MOC).
While I patiently wait for 4chan to make business accounts available, I figured I’d try out something new.
So, you can find bite (byte?) sized chunks of my videos on TikTok here: Darling Data
I’ve also hired my wife (clearly a nepo baby) as a social media intern to slice, dice, edit, caption, #hashtag, and add complimentary gifs to the clips.
If I’m not careful, she’ll be better at SQL Server than me by the time this goes to international media outlets. I would have hired her to write this press release, but she went to bed.
Since I’m clearly not good at these things, please do the TikTok version of liking and subscribing over there, fellow kids.
This post was brought to you by the Senior Executive Management Team at Darling Data, a subsidiary of Beer Gut Magazine, Inc.
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 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.
This May, I’ll be presenting my full day training session The Foundations Of SQL Server Performance Tuning.
All attendees will get free access for life to my SQL Server performance tuning training. That’s about 25 hours of great content.
Get your tickets here for this event, taking place Friday, May 10th 2024 at the Microsoft Offices in Burlington.
Here’s what I’ll be presenting:
Session Abstract:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Get your tickets here for this event!
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 was quite publicly mystified by the coupling of Azure Data Studio into the SQL Server Management Studio installer. They’re different tools for different people.
This isn’t a tirade against Azure Data Studio, nor is it a victory lap since apparently the feedback item I posted was part of the decision to remove it.
This is purely informational, since the announcement that it’s not in there anymore is nearly as quiet as the announcement that it was being included back in SQL Server Management Studio 18.7, and only slightly louder than the availability of a command line switch to skip installing it.
Back when I initially complained about the inability to skip installing Azure Data Studio, there was a lot of talk about how SQL Server Management Studio would increasingly rely on it for new functionality. I suppose that was either untrue, or the roadmap changed significantly.
Quite cynically, I thought it was a cheap way to increase the install base of a new product, but who knows? I assume Microsoft has better telemetry about usage than binaries just existing. Again, who knows?
A further miffance was that you could download and install Azure Data Studio independently, but not SQL Server Management Studio.
If you read through Erin Stellato’s post about the SQL Server Management Studio roadmap, where version 20 was fully released:
For SSMS 20 we have removed Azure Data Studio from the installation. In addition to being a highly voted item (Make ADS An Optional Install Alongside SSMS · Community (azure.com)), this aligns with the future extension work for SSMS 21 and we decided to make the change in the current release.
So, hooray! Dreams do come true, etc. When I was but a young boy, I dreamed that someday my desires would be aligned with future extension work for SSMS.
I’d also like to say here that being the public face of SQL Server Management Studio makes Erin Stellato about the bravest soul I know. It’s a tool that millions of people rely on, and nearly everyone actively complains about. Part of my RSS feed is not only posts from Microsoft about data platform related stuff, but also the comment feed. She has saintly patience in her responses to the comments on these things. Comments on the internet maintain their position and title as the worst things on earth. Erin deserves infinite credit for doing the job that she does as well as she does it.
If you’re keen on moving to SQL Server Management Studio 20, you can download it here.
If you’re still stuck using older versions because of a new bug or an old feature, you can skip installing Azure Data Studio by running the installer via the command prompt:
SSMS-Setup-ENU.exe /Passive DoNotInstallAzureDataStudio=1
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.