I hate that you can do this Here’s the thing: I’m mostly writing this because I didn’t know you could do it. But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it. I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on…
Short winded Quite often people will inherit and rely on views written back in the dark ages, before people were aware of the deleterious effects that scalar valued functions can have on performance. Of course, for some people, it’s still the dark ages. Sorry, you people. Questions and answers So what are the side effects…
You’re confident writing queries to get the data you want, and you’ve been doing it for a couple of years. You’ve taken our free T-SQL Level Up class, mastered tally tables and windowing functions, but what else is there? In this class with T-SQL maestro Erik Darling, you’ll learn tricks to take your queries to…
I like blogging Really, I do. But sometimes I like a change of pace. Instead of blogging about this question, I posted it over on dba.stackexchange.com. Also, I thought it was an interesting question, and apparently many other people agreed. Not just because I wanted some more points, but because blog comments are notoriously sucky…
In every training class we do, I learn stuff from students. Here’s last week’s surprise. Create a temp table, and put in a value with trailing spaces. Then query for it using the = and <> operators, using different numbers of trailing spaces: CREATE TABLE #Wines (Winery VARCHAR(50)); INSERT INTO #Wines VALUES (‘Cliff Lede ‘);…
While working on a demo I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don’t have a monitoring tool or application profiling to figure out who ran what and when, most of the time you’ll have no idea what went…
It keeps me up at night Out of the box, sp_BlitzCache will give you enough stuff to work on in your server’s plan cache to last a month of leap days. This is great for most people, but if you use a ton of dynamic SQL in stored procedures, it can make things difficult. What’s…
I’d been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn’t get into at the time is that it can give you incorrect results. When I see people using SQL_VARIANT, it’s often in dynamic SQL, when they don’t…
You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…
Way back in 2016 I wrote about what to do if sp_BlitzFirst warns about high compiles. During GroupBy, Adam Machanic gave a great talk on new features in SQL Server 2016. It reminded me of a blog post I wanted to write about one common culprit of high compiles: Multi-Statement Table Valued Functions. Leaving aside…
I dunno about you, but I got a big stocking full of coal. Next year, I’m gonna be better, and I plan on asking Santa for a whole bunch of Connect requests. For T-SQL Tuesday, I asked you to name your favorite SQL Server bugs & enhancement requests, and here’s what you want in your…
For this month’s T-SQL Tuesday, we’re talking about interesting Connect requests to fix or change SQL Server behavior. In our Senior DBA Class, one of the exercises involves figuring out changes to a table after an Always On Availability Group failover. In every class, several students always say the same thing: “I really wish I…
I know this is going to come as a stunning shock to you, dear reader, so you may want to be sitting down. SQL Server isn’t perfect. It’s okay. Deep, calming breaths. We’re going to get through this together, and here’s how: Go to Connect.Microsoft.com, the place where we file bug reports and enhancement requests…
I’ve never been much at math But I’ve always liked reading about it. It’s super interesting, and it always blows my mind. Like, guaranteed. Once in a while I’ll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes…
We were once asked in class what TRY/CATCH doesn’t handle besides object existence errors. It’s well documented in Books Online (BOL). If you’re like me, then tl;dr. Are we even calling it Books Online these days? I still say “bookmark lookup” instead of “key lookup”. I suppose I’ll be saying Books Online for quite some…
As if there weren’t enough reasons In my last blog post I talked about different things that cause plans, or zones of plans, to execute serially. One of the items I covered was computed columns that reference scalar functions. We know that they’ll make queries go serial, but what about other SQL stuff? Oh no my…
In theory, all of your T-SQL statements are supposed to end with a semicolon, like this: SELECT * FROM dbo.Posts; SELECT * FROM dbo.Comments; GO Why? Well, SQL Server 2005’s Books Online says: Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon is not required, using it is considered a…
My First Post Here… Well, my first technical post, was about how the default index creation method is OFFLINE. If you want that sweet, sweet Enterpri$e Edition ONLINE goodness, you need to specify it. It’s been a while since that one; almost six months to the day. So here’s another one! But Window Functions Are…
Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…
Over the years, I’ve done all kinds of awful things with T-SQL and made countless mistakes. Some were harmless; others were borderline catastrophic (exciting times!). I was curious what kind of horrible mistakes other people make, so I threw the question out to Twitter. Every answer I got was unique, which was both telling (so…
