SQL Server Archives | Page 125 of 164

Common Table Expression Myths In SQL Server: Materialization

Posted on July 13, 2020May 16, 2022 by Erik Darling

I Got Five On It

I wish I had a dollar for every wrong thing I’ve heard about CTEs in my life. I’d buy a really nice cigar and light it with fire made by the gods themselves.

Or, you know, something like that.

One common thing is around some persistence of the queries contained inside of them in some form, whether in memory or in tempdb or something else.

I honestly don’t know where these things begin. They’re not even close to reality.

Getting It Right

Let’s take this query as an example:

SELECT u.Id, 
       u.Reputation
FROM dbo.Users AS u
WHERE u.Reputation * 2 = 22;

If you’ve been tuning queries for longer than a day, you can probably spot the issue here.

Applying expressions to columns in the where clause (or joins) messes up some things. Unfortunately, you can also run into the exact same issues doing this:

WITH cte AS 
(
SELECT u.Id, 
       u.Reputation, 
       (u.Reputation * 2) AS ReputationDoubler
FROM dbo.Users AS u
)
SELECT c.Id,
       c.Reputation
FROM cte AS c
WHERE c.ReputationDoubler = 22;

To be explicit: both of these queries have the same problem.

Erik D Is President

Starting with this index:

CREATE INDEX toodles ON dbo.Users(Reputation);

Both queries have the same execution plan characteristics:

I understand why you think a mature database product might be able to deal with this better:

Locate values in the index with a value of 11
Divide the literal value by 2 instead

But SQL Server doesn’t have anything like that, and neither do CTEs. Both indexes get scanned in entirety to retrieve qualifying rows, with the unseekable expression applied as a residual predicate:

SQL Server Query Plan Tool Tip — Day Planner

Gopherville

To be clear, and hopefully to persuade you to write clear predicates, this is the end result that we’re after:

SELECT u.Id, 
       u.Reputation
FROM dbo.Users AS u
WHERE u.Reputation = 11;

While this is of course intuitive when writing simple queries, the point of this post is to show that expressions in CTEs don’t offer any advantage.

This goes for any flavor of derivation, too. Whether it’s wrapping columns in built in or user defined functions, combining columns, combining columns with values, etc.

Thanks for reading!

Going Further

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 performance problems quickly.

Are You An ISV With SQL Server Performance Problems? I Want To Help.

Posted on July 10, 2020May 16, 2022 by Erik Darling

Sound Familiar?

You’re an ISV, and your customers are complaining about application performance. You have competent developers, but they’re overwhelmed by long lists of new features and spend a lot of time putting out fires. Underneath it all is a SQL Server that everyone is scared of.

They probably don’t have the luxury of sitting and learning performance tuning theory, and figuring out how to apply it to the database they’re working in. That kind of training can be time-consuming, and if the right topics don’t get covered they might not learn the right things.

You need someone to show up with the knowledge in hand to assess your databases, indexes, and queries, and guide the team to better practices and better performance.

The Pain Is Plain

Week in and week out, I work with nice people in tough situations, and my advice helps them learn exactly what they’re doing wrong with SQL Server so they can solve their problems. That model works for most people who only need a little boost.

For those with a little more trouble and a little less expertise, one of my most popular packages is a week of query tuning. I jump right in and work on your queries on a development server, and hand over all the changes I make along with documentation and training.

But there’s another class of client out there, too. You need more dedicated help, training, and face time.

It’s nice to have all your performance issues analyzed with detailed advice on how to solve them, or have your worst queries magically tuned for you, but your developers still don’t have the confidence or skill set to do all that on their own.

Videos, Classes, and Conferences

These are all great ways to learn, but may not cover exactly what you’re going through.

Your developers have specific questions about their code, indexes, and other local factors that lead to them seeking out education.

You just don’t get the kind of ultra-personalized answers and training that you need in those settings. And there are some obvious drawbacks:

Videos: Time to sit, watch, and concentrate on them, then come back and fix things
Classes: Hours or days away from work, work stuff coming up during them, and they still need to come back and fix things
Conferences: I remember those, too. It’s like all the above, except with travel and a week long hangover that everyone calls “nerd flu” ?

If that’s all you need, check out mine. If you need something more, here’s what I have to offer.

Performance Leader As A Service

What I do is bring the best all of those things together in your workplace.

We review your environments, work with your databases, and fix your your problems together.

Developers learn by watching and doing right along with me, and nothing is hidden away. If performance problems come up during the work day, we look at them together.

Think of it like having that performance lead you always needed around, without all the mess of having to hire one full time.

What you get from our time together is more than just consulting or a remote DBA. You get coaching that gets results, and your developers get the skills and confidence to keep SQL Server performing well on their own.

Want to find out more? Drop me a line here.

Going Further

Helpers Views And Functions I Use In Presentations About SQL Server

Posted on July 9, 2020May 14, 2022 by Erik Darling

Leakin’

There are some helper views and functions that I use in a few presentations, and I figured it was time to stick them on The GitHub so I don’t have to package them up and remember to keep changes in sync, etc.

It’s a nightmare. There are enough of those.

What am I dropping on you?

Grab’em here and play along.

WhatsUpIndexes

This view gives you a quick look at index sizes and row counts. It’s not exhaustive, and it’s not meant to be.

If you want exhaustive, use sp_BlitzIndex.

Since it’s a view, all you have to do is select from it.

SELECT *
FROM dbo.WhatsUpIndexes AS wui;

I know, I’m terrible for writing SELECT *, but I really do want all the columns here.

If there were columns I didn’t want, I wouldn’t have put them in the view.

You know?

Because I’m kinda forgetful, I like having the view name in the results so I know what I’m looking at, and it’s obvious to people watching where results came from without looking at the T-SQL up on the screen. The rest of the info is pretty self-explanatory. It measures indexes by size and rows.

WhatsUpMemory

These two views are designed to complement each other a bit, because often what I’m showing with them is how big indexes are compared to what’s been read into memory. Sometimes it’s the whole index, sometimes it’s part of the index. But it’s nice to be able to see right next to each other.

As far as I know, there’s nothing out there that analyzes what’s in memory, and most of the time this isn’t something I’d want to run in production.

SELECT *
FROM dbo.WhatsUpMemory AS wum;

There are two reasons for that:

The sys.dm_os_buffer_descriptors view is really slow
It gets slower with more memory/stuff in memory

I don’t think there’s another memory view that gives me what I want back, so I’m sort of stuck with this one here.

You can see how the two help each other, and you can also probably see why it’s easy to get the results confused. If it weren’t for the buffer cache pages column here, it might look just like index info. Heh.

WhatsUpLocks

This is an inline table valued function, and it takes one parameter for a SPID.

It can be NULL if you want, but usually I want to focus in on what one things is doing.

SELECT *
FROM dbo.WhatsUpLocks(@@SPID) AS wul;

This doesn’t give you nearly as much other detail as sp_WhoIsActive, but it’s good for just looking at locks taken. Note that a lot of the time, you might need to use a transaction to preserve the locks so you can see the full extent of the damage. If you’re looking at another session while it takes locks, it’ll either have to run for a bit, or you’ll have to be really fast with F5.

I use the Votes_Beater table to have a copy of the Votes table with a bunch of indexes on it that I don’t need to go and create live. They’re always there and ready for abusive demos. I like the Votes table because it’s big and narrow, with sensible data types for things (read: no MAX types).

It makes things a lot simpler.

Fine Print

I make no guarantees about which versions these’ll run on, and quite frankly I’m not interested in them being backwards compatible. They run on the versions that I do my demos on (2017 and 2019).

If they happen to work on older versions, great. If not, I’m fine with you making local changes, but won’t accept pull requests for it, and I’ll close issues about it immediately. It’s not like you can have dynamic SQL in these things, anyway.

For any questions about what they return, make sure you read the docs for the views they touch. It’s not hard to look at the queries, I promise ?

Grab’em here!

Thanks for reading!

Going Further

More Dates For Online Training: July 10th and 24th

Posted on July 8, 2020May 16, 2022 by Erik Darling

Missing Persons

If you missed out on my online class, I’m offering two more dates for it in July.

You can catch all the goodness of Premium Performance Tuning on July 10th and 24th.

In this class, I’ll teach you about my strategy for tuning horrible queries, and show you practical examples for dealing with a variety of performance issues. It’s a full day of learning with no fluff.

I’ll be covering topics that you’ll face day to day, like dealing with parameter sniffing, functions, temporary objects, complex queries, and more. You’ll learn how to get to the bottom of query performance issues like a pro by getting the right information and learning how to interpret it.

Enrolling in my full day course also gets you access to my training videos, which is a $1000 value with over 24 hours of content.

Showing Up

On the day of the training, you’ll be able to watch the video stream on my site here.

To interact with me and other students, go to SQLslack.com to get an invite, then go to https://SQLcommunity.Slack.com, or download the Slack app to join. We’ll be in the #erikdarling-tuning channel. That’s the only way to ask questions right now. Unfortunately with video streaming, there’s no built-in chat. You have to bring your own.

If you wanna follow along, you’ll need to download the StackOverflow 2013 database, along with the latest SSMS, and Developer Edition of SQL Server: 2017 | 2019. While you’re being a good DBA, don’t forget to make sure you’re up to date on patching.

Get your tickets here!

See you in class!

Going Further

Local Variables Also Cause Performance Problems In The ORDER BY Clause In SQL Server

Posted on July 7, 2020May 16, 2022 by Erik Darling

Local Disasters

I see this kind of pattern a lot in paging queries where people are doing everything in their power to avoid writing dynamic SQL for some reason.

It’s almost as if an entire internet worth of SQL Server knowledge and advice doesn’t exist when they’re writing these queries.

Quite something. Quite something indeed.

First, let’s get what doesn’t work out of the way.

DECLARE @order_by INT = 3

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score
              WHEN @order_by = 2 THEN p.CreationDate 
              WHEN @order_by = 3 THEN p.Id 
              ELSE NULL 
         END;
GO

You can’t write this as a single case expression with mismatched data types.

It’ll work for the first two options, but not the third. We’ll get this error, even with a recompile hint:

Msg 8115, Level 16, State 2, Line 46
Arithmetic overflow error converting expression to data type datetime.

What Works But Still Stinks

Is when you break the options out into separate case expressions, like so:

DECLARE @order_by INT = 1

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END;
GO

This will work no matter which option we choose, but something rather disappointing happens when we choose option three.

Here’s the query plan. Before you read below, take a second to try to guess what it is.

What Stinks Even Though It Works

My issue with this plan is that we end up with a sort operator, even though we’re ordering by Id, which is the primary key and clustered index key, and we use that very same index. We technically have the data in order, but the index scan has False for the Ordered attribute, and the Sort operator shows a series of expressions.

The Sort of course goes away if we add a recompile hint, and the Scan now has True for the Ordered attribute.

DECLARE @order_by INT = 3

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END
OPTION(RECOMPILE);
GO

You Shouldn’t Do This

Unless you’re fine with recompile hints, which I don’t blame you if you are.

SQL Server seems to get a whole lot more right when you use one, anyway.

My point though, is that adding uncertainty like this to your queries is more often than not harmful in the long term. Though this post is about local variables, the same thing would happen with parameters, for example:

DECLARE @order_by INT = 3
DECLARE @sql NVARCHAR(MAX) = N'
SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END;
';

EXEC sys.sp_executesql @sql, N'@order_by INT', 1;
EXEC sys.sp_executesql @sql, N'@order_by INT', 3;
GO

The way to address it would be something like this:

DECLARE @order_by INT = 3
DECLARE @sql NVARCHAR(MAX) = N'
SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY ';

SET @sql += 
CASE WHEN @order_by = 1 THEN N'p.Score'
     WHEN @order_by = 2 THEN N'p.CreationDate'
     WHEN @order_by = 3 THEN N'p.Id' 
     ELSE N'' 
END;

EXEC sys.sp_executesql @sql
GO

Thanks for reading!

Going Further

Local Variables Cause Performance Problems With TOP In SQL Server

Posted on July 6, 2020May 16, 2022 by Erik Darling

Number One

In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s the single most important blog post ever written, even outside of SQL Server. It might even be more important than SQL Server. Time will tell.

While live streaming recently about paging queries, I thought that it might make an interesting post to see what happens when you use variables in places other than the where clause.

After several seconds of thinking about it, I decided that TOP would be a good enough place to muck around.

Unvariables

Let’s say you’ve got these two queries.

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id;
GO 

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(RECOMPILE);
GO

Without a RECOMPILE hint, you get a 100 row estimate for the local variable in a TOP.

Which of course might could lead to some problems if you were selecting way more than 100 rows via TOP.

Not Unexpectedly

You can manipulate what the optimizer thinks it’ll get with optimizer for hints:

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(OPTIMIZE FOR(@pagesize = 1));
GO

And of course, when used as actual parameters, can be sniffed.

DECLARE @pagesize INT = 10000;
DECLARE @sql NVARCHAR(1000) = 
N'
  SELECT TOP (@pagesize) p.Id
  FROM dbo.Posts AS p
  ORDER BY p.Id;
'

EXEC sys.sp_executesql @sql, N'@pagesize INT', 1;
EXEC sys.sp_executesql @sql, N'@pagesize INT', 10000;
GO

Got More?

In tomorrow’s post, I’ll look at how local variables can be weird in ORDER BY. If you’ve got other ideas, feel free to leave them here.

There’s not much more to say about WHERE or JOIN, I’m looking for more creative applications ?

Thanks for reading!

Going Further

More Dates For Online Training: July 10th and 24th

Posted on July 3, 2020May 16, 2022 by Erik Darling

Missing Persons

If you missed out on my online class, I’m offering two more dates for it in July.

You can catch all the goodness of Premium Performance Tuning on July 10th and 24th.

Enrolling in my full day course also gets you access to my training videos, which is a $1000 value with over 24 hours of content.

Showing Up

On the day of the training, you’ll be able to watch the video stream on my site here.

Get your tickets here!

See you in class!

Going Further

In SQL Server, Lookups Are Just Nested Loops Joins With No Choices

Posted on July 2, 2020May 16, 2022 by Erik Darling

Such Optimize

At this point in your life, you’ve probably seen, and perhaps even struggled with how to fix a key lookup that was causing you some grief.

This post isn’t going to go terribly deep into anything, but I do want to make a few things about them more clear, because I don’t usually see them mentioned anywhere.

Lookups are joins between two indexes on the same table
Lookups can only be done via nested loops joins
Lookups can’t be moved around in the execution plan

I don’t want you to think that every lookup is bad and needs to be fixed, but I do want you to understand some of the limitations around optimizing them.

The Join

When you see a lookup in an execution plan, it’s natural to focus on just what the lookup is doing.

But there’s something else lurking in here, too.

That nested loops join is what’s bringing the data from a nonclustered index to the data in a clustered index (or heap, but but whatever).

For every row that comes out of the index seek on the nonclustered index, we go back to the clustered index to find whatever data is missing from it in the clustered index. It could be columns in the select list, where clause, or both.

Much like index union or index intersection, but much more common. For a table with a clustered index, the join condition will be on the clustered index key column(s), because in SQL Server, nonclustered indexes inherit clustered index key columns. For heaps, it’ll be on the row identifier (RID).

You can most often see that by looking at the tool tip for the Lookup, under Seek Predicates.

The Loop

At this point, SQL Server’s optimizer can’t use merge or hash joins to implement a lookup.

It can only use nested loops joins.

That’s a pretty big part of why they can be so tricky in plans with parameter sniffing issues. At some point, the number of loops you can end up doing is far more work than just scanning as clustered index all in one shot.

There’s also no “adaptive join” component to them, where SQL Server can bail on a loop join after so many executions and use a scan instead. Maybe someday, but for now this isn’t anything that intelligent query processing touches.

They can look especially off in Star Join plans sometimes, where it’s difficult to figure out why the optimizer went with the lookup for many more rows than what people often call the “tipping point” between lookups and clustered index scans.

The Glue

Another pesky issue with lookups is that the optimizer doesn’t currently support moving the join between the two indexes around at all.

You can get this behavior on your own by rewriting the lookup as a self join (which is all a lookup really is anyway — a self join that the optimizer chose for you).

For instance, here are two query plans. The first one is where the optimizer chose a lookup plan. The second is one where I wrote the query to self join the Users table to itself.

The thing to understand here is that when there’s a lookup in a query plan, it is inseparably coupled.

When you write queries as self joins, the optimizer has many more choices available to it as far as join order, join type, and all the other usual steps that it can take during optimization. A simplified example of doing that (not related to the query plans above), would look like this:

CREATE INDEX joan_jett
ON dbo.Posts
(
    PostTypeId, Score
);

                                 /* Not In The Index */
SELECT p.Id, p.PostTypeId, p.Score, p.CreationDate
FROM dbo.Posts AS p
WHERE p.PostTypeId = 7
AND   p.Score = 0
AND   p.OwnerUserId = -1;
       /* Not In The Index*/



                                    /* From p2 */
SELECT p.Id, p.PostTypeId, p.Score, p2.CreationDate
FROM dbo.Posts AS p
JOIN dbo.Posts AS p2 --Self join
    ON p2.Id = p.Id
WHERE p.PostTypeId = 7
AND   p.Score = 0
AND   p2.OwnerUserId = -1;
      /* From p2 */

The index is only on PostTypeId and Score, which means the CreationDate and OwnerUserId columns need to come from somewhere.

Probably more interesting is the second query. The Posts table is joined to itself on the Id column, which is the primary key and clustered index (for style points, I suppose), and the columns not present in the nonclustered index are selected from the “p2” alias of the Posts table.

AND BASICALLY

Sometimes I take these thing for granted, because I learned them a long time ago. Or at least what seems like a long time ago.

But they’re things I end up talking with clients about frequently, and sometimes even though they’re not optimizer oddities they’re good posts to write.

Hopefully they’re also good posts for reading, too.

Thanks for doing that.

Going Further

LOB Data And Weird Locks In SQL Server

Posted on July 1, 2020May 16, 2022 by Erik Darling

Working On It

I’ve been re-working a lot of the demos in a presentation that I’m working on called Index Internals That Matter For Performance, because after a first walk through them, I had a lot of realizations about not only what I was presenting, but the way it was presented, and the order I was presenting it in.

This isn’t abnormal, and it’s hardly my finest moment in video history. But I wanted to stream it because a lot of people are out there who might be thinking about getting into blogging or presenting, and I wanted to show that it’s a process, not something you just walk out and nail like a half-court-no-look-over-the-shoulder-nothing-but-net-shot.

Anyway, I came across a weird thing, and had to make some changes to my helper script WhatsUpLocks to get some more information.

And not get blocked.

Read Committed is a trash isolation level. Don’t @ me.

The Not Weirdness

This is the simplest I could work things out to. I don’t have anything very practical to say about it right now.

Here’s an update:

BEGIN TRAN

UPDATE p
    SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.Id = 999;

ROLLBACK

If we run it up to the rollback, it finishes pretty quickly. We are, after all, just updating a single row that we locate via the primary key.

My original idea for the demo was to show some of the odder things you can run into with blocking, so I wrote this query to return a bunch of rows, but get blocked at the very end.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900;

Which is exactly what happens. We get to Id 997 and crap out.

Now if we check on those sessions with WhatsUpLocks, we can see what happened.

SELECT *
FROM dbo.WhatsUpLocks(58) AS wul; --Writer SPID

SELECT *
FROM dbo.WhatsUpLocks(57) AS wul; --Reader SPID

Why is this not weird? Well, comparatively, we take a normal number of overall locks and get blocked in a fairly predictable spot. We get blocked waiting on one of the keys that we need to keep going.

The Weirdness

To backtrack a little bit, part of what I wanted to show was that using order by can sometimes result in “more” blocking. I don’t mean more locks; what I mean is that when we need to order by Score, but we don’t have Score indexed in a useful way, the query will get hung up without showing any rows whatsoever.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;

Originally this was a SELECT * query, but I want to show you that it’s specific to the Body column because it’s an NVARCHAR(MAX).

Here’s what comes back from looking at the locks now:

LOOK HOW MANY LOCKS WE TAKE ON PAGES. That’s bananas.

Watch my video on readers blocking writers for a little background on why this could be troublesome.

If I change my query to not have the Body column in the select list, the locks go back to normal.

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;

Of course, sticking Body in the WHERE clause results in an uptick in shared locks taken:

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
AND p.Body LIKE N'_%'
ORDER BY p.Score;

But Of Course…

This kind of thing is maybe not the most likely thing you’ll see happening IRL, because you probably have other indexes that queries can use to access data in different ways. For instance, if I have this index on the Posts table, the first query will still get blocked, but all of the other queries will finish instantly.

CREATE INDEX surprise ON dbo.Posts(Score, Id);

Is this another reason to avoid MAX datatypes? Maybe probably.

Don’t forget that they also prevent predicate pushdown, and they can really mess with memory grants.

But hey, I’m sure you have more than enough indexes to fix everything.

Thanks for reading!

Going Further

Experiment With Table Variable Deferred Compilation Without SQL Server 2019

Posted on June 30, 2020May 16, 2022 by Erik Darling

I Have A Secret To Tell You

If you haven’t heard by now, Table Variables have some ✌features✌ that can cause performance issues pretty generally in SQL Server.

One row estimates unless you recompile (or that darn trace flag)
No column-level statistics (even with indexes)
Modifications to them can’t go parallel (without sorcery)

But in SQL Server 2019, Microsoft fixed one of those things, kind of, with Table Variable Deferred Compilation.

Rather than just give you that one row estimate, it’ll wait until you’ve loaded data in, and then it will use table cardinality for things like joins to the table variable. Just be careful when you use them in stored procedures.

That can be a lot more helpful than what you currently get, but the guesses aren’t quite as helpful when you start using a where clause, because there still aren’t column-level statistics. You get the unknown guess for those.

How Can You Test It Out Before SQL Server 2019?

You can use #temp tables.

That’s right, regular old #temp tables.

They’ll give you nearly the same results as Table Variable Deferred Compilation in most cases, and you don’t need trace flags, hints, or or SQL Server 2019.

Heck, you might even fall in love with’em and live happily ever after.

The Fine Print

I know, some of you are out there getting all antsy-in-the-pantsy about all the SQL Jeopardy differences between temp tables and table variables.

I also realize that this may seem overly snarky, but hear me out:

Sure, there are some valid reasons to use table variables at times. But to most people, the choice about which one to use is either a coin flip or a copy/paste of what they saw someone else do in other parts of the code.

In other words, there’s not a lot of thought, and probably no adequate testing behind the choice. Sort of like me with tattoos.

Engine enhancements like this that benefit people who can’t change the code (or who refuse to change the code) are pretty good indicators of just how irresponsible developers have been with certain ✌features✌. I say this because I see it week after week after week. The numbers in Azure had to have been profound enough to get this worked on, finally.

I can’t imagine how many Microsoft support tickets have been RCA’d as someone jamming many-many rows in a table variable, with the only reasonable solution being to use a temp table instead.

I wish I could say that people learned the differences a lot faster when they experienced some pain, but time keeps proving that’s not the case. And really, it’s hardly ever the software developers who feel it with these choices: it’s the end users.

Thanks for reading!