create temp table sql Archives | Page 16 of 139

Learn T-SQL With Erik: Scalar UDFs

Posted on October 22, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Scalar UDFs

Video Summary

In this video, I delve into scalar UDFs in T-SQL, discussing their potential pitfalls and how they can impact query performance. I explore the differences between non-inlinable scalar UDFs and those that are inlined, using practical examples to illustrate the hidden work these functions can do when not inlined. By walking through an example with a non-inlinable scalar UDF, I demonstrate how it executes once per row, leading to inefficiencies that can be mitigated by adding parameters or rewriting the function as an inline table-valued function. The video also includes links for those interested in my training courses and upcoming events where you can interact directly with me.

Full Transcript

I’ve been listening to a lot of Tom Petty lately. I don’t know why. Anyway, Erik Darling here with Darling Data, joined today by Bats Maru, who has some thoughts and feelings on what we’re going to talk about. We are going to, of course, be doing some more T-SQL learning. These are all little tidbits and morsels from my larger course called Learn T-SQL. I think with Erik might just be Learn T-SQL. I forget how much I involve myself in the course name. But that’s okay. There are more important things like content, accuracy, you know, polarity, things, other things that I care deeply about. But before we go talk about Scalar UDFs, if you would like to buy any of my training, including the full Learn T-SQL with me course, all these links are available down in the video description. You can find all sorts of useful ways to interact with me, both by giving me money for things and by getting things for free from me, like asking office hours questions or liking, subscribing and telling a friend. So look down and you’ll find links for all the useful stuff in the world.

If you would like to have some useful interactions with me in person, the fine folks at Redgate are yanking me from the comforts of home and dragging me to various business class hotels in Dallas, Texas, September 15th and 16th. Utrecht, Netherlands, October 1st and 2nd and Seattle, Washington, November 17th to 21st, where I will be attending various past events and talking about SQL Server stuff, including, but not limited to, two days of T-SQL pre-cons with me, hosted by myself and Kendra Little. So you should come to those because you won’t be disappointed like you would be if you went to any other pre-con.

It’s the tremendous disappointment, not just fear, but the knowledge of missing, the como, the knowledge of missing out that you would feel by going to a different pre-con would just ruin your life. You would be a complete dark downward spiral for you from that day on. You would be able to just mark it on the calendar.

Life, graph, down. If you were making a tapestry of your life, it would just be black thread from that moment onward. Frayed at the edges.

All right. Anyway, let’s talk about T-SQL. Let’s talk about something. Well, we are in dark mode here, aren’t we? Coincidence?

I don’t know. But let’s talk about scalar UDFs. Now, scalar UDFs, of course, if you have spent any amount of time in your life dealing with T-SQL and perhaps Googling, or let’s just say Googling. There’s no one using Bing.

Let’s not lie to ourselves. You will undoubtedly have come across many articles, maybe even some articles by yours truly, about the perils of scalar user-defined functions in T-SQL code. And you may have even read something added to SQL Server 2019 called scalar UDF inlining.

Of course, scalar UDF inlining has many restrictions and limitations put on it that make it so it is ineligible for certain forms of functions. If you would like to explore the full list of that, Microsoft has just about, I think, just about all of it documented. There may be some stuff that we don’t know about, we don’t see.

But, you know, look up scalar UDF inlining. You’ll see the Microsoft Learn or Docs or whatever they’re calling it these days page. And you’ll be able to see the full list of restrictions and limitations.

So what we’re going to do is, since my copy of the Stack Overflow database is currently in compatibility level 160, I am currently using SQL Server 2022. I need to play a slight trick on SQL Server in order for this function to not be eligible for scalar UDF inlining. The trick that I am playing on SQL Server is incorporating one of these non-deterministic date functions in the function body.

So sysdate time, get date, any of those things would have the same effect on disallowing inlining for this code. And what I want to show you first is the estimated execution plan for this. So when we run the estimated execution plan, when we collect the estimated execution plan, we get two things back.

We get back the query plan for the query. And then we also get back an additional query plan that shows us what the scalar UDF would have done. We will not see that with the actual execution plan for this query.

Because SQL Server does two things with non-inlineable scalar UDFs. The thing that immediately affects this is that the scalar UDF is not run once per query, since it is not inlined. It is run once per row that the query has to process.

If we look at this query plan, we’ll see an index scan, we’ll see a sort, and then we will see a compute scalar. The compute scalar is where the scalar UDF is… Rather, the compute scalar is the operator responsible for executing our scalar UDF.

You can see that right here. These are the properties of the compute scalar. And we will see the name of the UDF in there.

So that is where the UDF was executed once per row that the query projected out. All right. So we do not see the actual execution plan for the scalar UDF because it executes once per row.

And if we executed that UDF for a thousand rows, we would not want to get back 1,000 execution plans, would we? No. No siree, Bob.

The second thing that scalar UDFs will do to us is prevent a… Rather, non-inlinable scalar UDFs will do to us is prevent the calling query from using a parallel execution plan. Not that one is warranted here, but it is restricted from using one even if one were warranted.

So those are the two big downsides of non-inlinable scalar UDFs. To prove out that this UDF does, in fact, execute once per row, that query returned six rows. And we have six executions of the scalar UDF in the sys.dm exec function stats view, which only collects data about scalar UDFs.

Ah, I didn’t want that. One way that we can work around that situation with the UDF not being inlinable is rather than have sysdate time in the body of the UDF, we can just add a third parameter to the function. And the third parameter will get used here in place of the sysdate time function.

And if we do that and we pass in sysdate time from outside of the function call, then it will be perfectly inlinable. So if we run this query and look at the execution plan, this will look a little bit different. Rather than seeing the compute scalar with the scalar UDF in it, now we have some extra stuff in here.

And this is where the function was inlined into the query. All right, these constant scans and the nested loops join and stuff. This is where the function, this is where the body of the function ran, but it was inlined into the execution plan.

So now we have this little bit of additional information. If we get an estimated execution plan for this, we no longer have that second plan down here, right? We can only prove a negative so much, but there’s no second execution plan that shows us what the body of the scalar UDF would have done.

And if we go and interrogate the sys.dmexec function stats dmb, it will be empty for the inlinable UDF. All right, so that is one sort of peril of the UDF is when they are not inlinable, they hide some work for you. Of course, the work that they hide from you is only obvious if we look at the use of a function where user tables are touched.

Right, so here you can see in the body of this function, we touch the post table and we touch the comments table. But if we get, and we’ll only, rather, let’s start here. We’ll only see that if we get the estimated execution plan, right?

So like the query plan up here with the UDF in it just shows us clustered index c compute scalar select. The estimated execution plan for the function, right? This is our non-inlinable scalar UDF here.

This shows us two index scans of posts and comments, some parallelism, some stream aggregates, some compute scalars and so forth. And when we execute this query, we get the actual execution plan. We will once again not have a, we will not have the benefit of the execution plan for the UDF.

We will have absolutely no information that it went out and touched the post table and the comments table and scanned the clustered index. That will not be visible to us. All we’ll see is that we scanned the clustered index of the users table, which took zero seconds.

And then we spent 1.2 seconds in the compute scalar. And of course, the 1.2 seconds that we spent in the compute scalar was executing our user defined function once per row that came out of the original query, which for our results was just once, but that one execution took 1.2 seconds.

So think about that if we returned 10 rows, we’re looking at 10 something sec, maybe 12 seconds at that point. If we executed this 20 times, well, what’s 1.2 times 20? It’s a higher number, isn’t it?

So when scalar UDFs are a particularly nasty piece of work, if you are, you know, given the number of limitations and restrictions, we will, we will have work to do rewriting these as inline table valued functions for many years to come. So a really smart fellow named Sam Arch wrote a paper a little while back about scalar UDF inlining and how, I think it’s called like, scalar UDF inlining first start with outlining or something.

And it was actually implemented in DuckDB. And what it did was it took cases where scalar UDF inlining broke like queries completely and fixed them. So it was a very, very cool paper.

So it’s Sam Arch, A-R-C-H. If you have the wherewithal to read a white paper about query optimization, I highly suggest checking that one out. Very, very cool thing.

Anyway, scalar UDFs, you know, you can pretty much tell when someone has used one because they just return a data type like integer or date or date time or varchar max or something. And when you see these in your queries, you better hope that they’re simple and that SQL Server can rewrite them. Otherwise, you’re going to have to hire a young and handsome consultant with reasonable rates to come and rewrite them for you.

Isn’t that a shame? Isn’t that something there? All right.

Anyway, thank you for watching. Hope you enjoyed yourselves. Hope you learned something. And I will see you in tomorrow’s video. I believe we’re going to be talking about multi-statement table-valued functions. And we’re going to learn a little bit about how the T-SQL that goes into those interacts with the queries and query plans that you see presented to you.

When you go trying to tune queries and whatnot. Anyway, thank you for watching.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

A Little About ROW_NUMBER Filtering Performance in SQL Server

Posted on October 21, 2025January 23, 2026 by Erik Darling

A Little About ROW_NUMBER Filtering Performance in SQL Server

Video Summary

In this video, I dive deep into an intriguing SQL Server performance question that came up during yesterday’s office hours. Specifically, we explore why a `ROW_NUMBER() = 1` query performs differently from a `ROW_NUMBER() <= 1` query by analyzing their execution plans in detail. You'll see how the choice of operators and execution modes can significantly impact performance, with one query finishing in just over five seconds compared to another that completes in less than two seconds. I walk you through the nuances of windowing functions, batch mode vs. row mode operations, and how SQL Server optimizes these queries. Whether you're a seasoned DBA or just starting out, this video offers valuable insights into optimizing your T-SQL code for better performance.

Full Transcript

Erik Darling here with Darling Data and I’m very excited. Very excited today. Hugely excited. Probably the most excited I’ve ever been. I’m going to be answering in longer form a question from yesterday’s office hours about why row number equals one. It has a different performance than row number is less than or equal to one. And of course, we will be looking at query plans. We’ll be looking at performance and we will be answering this question in as much detail as I can muster off the top of my head. So good stuff there, right? All right. I was going to say something else funny here, now I forgot it. See, this is the problem with the top of my head or things just fly off it. It’s like frying an egg. Little bubbles. Anyway, if this sort of stuff is interesting to you and you think, gosh, this Erik Darling guy just might know a thing or two about SQL Server, maybe he could help me with SQL Server. Maybe he could do it. Maybe he can be the one to finally change my life. Well, there are all sorts of ways for me to accomplish that. And if you look down in the video description, you will find all of these ways for me to change your life. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training.

You can hire me for consulting by my training. You can hire me for consulting by my training. You can hire me for consulting by my training, oversee this monthly consultation, and then for free, you can ask me office hours questions. And also for free, you can like and subscribe and tell a friend about the magnificence and glory of this channel and its content. And of course, the young and handsome consultant behind the majority of the content therein. If you would like to see a young and handsome consultant live and in person, well, golly and gosh. Won’t I just be a good friend? Here’s an awesome consultant. everywhere you want to be. Dallas, September 15th and 16th. Utrecht, the Netherlands, October 1st and 2nd. And Seattle, the Washington, November 17th through the 21st. These are all past events put on by the magnificent, lovely, also glorious, also magnificent and glorious folks at Redgate who were kind enough to say, hey Eric, we like you a little bit. Why don’t you come talk about these things? And I said, yes, yes, please. Yeah, I’d love to go places, talk about things because you know, there’s only so much time one can spend trapped in a PowerPoint or in an SSMS tab. It starts to jar the brain a little bit. So with that out of the way, let’s talk about this any aggregate thing. Let’s make sure that I go to the right spawn of SSMS. Now, it took me a little, it took me a little while to get this demo to the place where there was a nice noticeable difference in performance between these two queries. And part of it was having a slightly imperfect index key to support the windowing function that we’re going to be using. So that was the first thing. The second thing was getting this index to create in a reasonable amount of time by filtering out all these zeros, because there are a lot of zeros in the comments table.

And then of course, writing the query in a way where there would be a noticeable difference in performance between a query where the row number function is filtered to equals one, and a query where the row number function is filtered to less than or equal to one. All right, so we’re going to, well, we’re not going to create this. We’re not going to go do all this stuff over again, because that’s just silly. We are going to make sure that we have actual execution plans enabled, which we do. We can tell that via the lovely purple highlighting there. That is a nice touch. I do, I do like purple. If Erin Stellato had as much to do with picking the color purple for that highlight as I think she did, my kudos go out to Erin Stellato for her choice in purples, because that is a great purple. There’s a nice, there’s like a grimace purple. And that is, that is the perfect purple for me. That is purple rain to me. So let’s run these queries. Now what these, now just to get rid of the query results, because I hate waiting for query results. And the discard query results thing is a bit too heavy handed for me, because then I have to remember to turn it on and back off and on and back off. It’s all too much. We are going to be dumping the results of these queries into different, slightly different temp tables, because we don’t want to overcrowd a single temp table.

And let’s do this and let’s look at the performance difference. We’re going to execute these and we’re going to wait, oh, around about seven seconds total, I think, if I, if I remember things correctly. There we go. All right, look at all those rows affected. See, sometimes having, having no count off is nice, right? Make sure that the same number of rows got changed. Look at that beautiful symmetry. Wow, we’re so good at this. All right. So that was indeed about seven seconds.

And here is the first query plan. Now you’ll, if you’re familiar at all with SQL Server execution plans, you will immediately recognize that for a query with a windowing function in it, we are missing some potential operators. Usually when there is a windowing function involved, you will see some combination of segment and sequence project. You may even see a window spool for some of them. And here we do not have those. Here all we have is a stream aggregate. What this means is that SQL Server transformed the row numbering into a grouping operation. So we did not actually generate a row number. We just grouped the whole thing. Now there are some rules that SQL Server follows in order for this to happen. You must filter the, I think it’s, you must filter the windowing function to an equality predicate. I think that’s one of them, but more, but other more important stuff is like you can’t be presenting the window function column and the results, right? So if I had the column X dot N in here, that’s what the windowing function is, um, is alias to, uh, then we would not see this transformation get applied. But, uh, you, you can tell from this that we just did a stream aggregate. Now this all took around, around about five and a half seconds, right? 5.356 seconds, which is close enough to five and a half for me. Part of why this query, uh, took the five and a half seconds is that this query happened entirely in row mode, right? So we processed quite a lot of rows all in row mode. We did not use batch mode for this. Part of the other signs that we use batch mode for this, rather that we did not use batch mode for this, are that we have, uh, some parallel exchanges in the plan. Often these are, uh, omitted to some degree when you have a, uh, when you have a query plan that uses batch mode, usually fully omitted. If you have a query plan that is just about fully batch mode, sometimes only partially omitted. If you have a query plan that is a mix of row mode and batch mode. Another good sign is that we have a stream aggregate operator. The stream aggregate operator, of course, does not support batch mode, right? Uh, only the hash, uh, only the hash, uh, aggregate and, uh, join support batch mode. Nested loops and merge joins also do not support batch mode. Now, if we come down here and look at the second query, the first, well, let’s, let’s, let’s actually make this a little bit easier on the eyeballs. What do you say? I say that’s a great idea. Uh, this query finished in 1.6 seconds.

Hmm. It’s a meaningful improvement, isn’t it? I would say so. From about five and a half to about one and a half. That’s, that’s pretty good, right? We saved about four seconds there from, my math is correct. And, uh, if you look at the various operators in here, you might notice that, uh, SQL Server, uh, used, you might, you might have some visual indicators that SQL Server used batch mode. And, uh, uh, the visual indicators are, well, the main, the prime visual indicator is that we have a window aggregate function, right? So the window aggregate is a batch mode operator that SQL Server uses when you have a windowing function and batch mode is chosen as the execution engine for the query.

So if we look at this, uh, the details of this operator, we will see that this executed in batch mode. We will see that this sort executed in batch mode. We will see that the window aggregate, of course, executed in batch mode and even the filter operator filtered in, uh, yeah, even the filter operator filtered in batch mode. So, uh, if you, so to answer the question from yesterday’s office hours, where, uh, why is performance different? Well, this, these are the types of things that you would want to look for in your query plans in order to ascertain why performance is different for you locally.

Like most things, the answer is in the execution plan. So, uh, you should look at them and maybe take, take note of some of the details, take note of some of the operator times. Uh, I realize that there is nothing in the query plan that’s going to tell you that your, uh, windowing function was transformed to an any aggregate, uh, by SQL Server’s query optimizer. But, uh, this is just a piece of knowledge that you will have to stick on the shelf in your brain where you store your SQL Server knowledge and come back to when you see this sort of thing happening. Anyway, I hope that answers your question in a little bit more detail. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where I reckon, I do reckon, I do hereby reckon, uh, we will talk more about learning T-SQL. So, um, hope to see you there.

Anyway, thanks for watching.

Going Further

SQL Server Performance Office Hours Episode 33

Posted on October 20, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 33

Questions:

* What are your favorite sandwich condiments?

* First off big fan. Also, Why does lt=1 on a query with row_number window function perform so different than = 1. I find its a trick that almost forces sql server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join “table”. I tried in (1,null) and gt0 and lt2 without the same results. Thanks!

* If you could change something about the world, not related to Perf Tuning SQL Server, what would it be?

* What do we need to be aware of when enabling snapshot isolation in a large production DB? Should we expect any downtime? Thanks!

* I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. Can I hear you say more about what DPA does wrong? I haven’t used it in years.

To ask your questions, head over here.

Video Summary

In this video, I dive into some fascinating questions from the community during our office hours session. We tackled topics ranging from sandwich condiments to SQL Server performance tuning and database management best practices. Specifically, we explored why less than or equal to one in a query with row number window function behaves differently compared to equals one, delved into enabling snapshot isolation in large production databases without expecting downtime, and discussed the shortcomings of DPA as a monitoring tool compared to SQL Sentry. If you have any more questions or topics you’d like me to cover, feel free to ask during our next office hours session!

Full Transcript

Erik Darling here with Darling Data, and boy oh boy do we have an exciting, exciting day for you planned. More exciting than you could imagine because we are going to, we’re going to have an office hours. We’re going to answer some questions, five of them, this many, and we’re going to, we’re going to see what we got in the old mailbag today. Before we go and answer questions, if you would like to give me money in some meaningful way, there are all sorts of helpful ways to do this. I’ll do that down in the video description below. You can hire me for consulting, buy some training, become a contributing member of the channel. Of course you can, you know, do some stuff for free too, like ask me office hours questions. That doesn’t cost anything. That is a, that is a free service. And if you enjoy any or all of what I do here, you can of course like, subscribe, and the most important thing is tell a friend. That is how communities work. That is how communities get built, isn’t it? You spread the good word. All right. Uh, I will be leaving the house a little bit over the next couple few months. Uh, well, I guess that’s, well, it is September, isn’t it? I mean, oh boy, I gotta, I gotta start working on some stuff. Uh, we have Dallas, September 15th and 16th. Uh, Utrecht, Netherlands, October 1st and 2nd, and past, and, uh, Seattle, November 17th to the 21st. These are all, uh, past events put on by the nice folks at Redgate.

Uh, and, uh, I look forward to hopefully seeing you at, uh, any or all of them. Uh, well, we’re, we’re, we’re on the subject of any’s and all’s. Uh, but with that out of the way, let’s do our office hours dance here. You know, let’s, uh, zoom on in and get things properly situated so that, uh, everyone can focus on these things correctly. All right. Uh, what are your favorite sandwich condiments? Well, this brings up an interesting question is what you define as a condiment. Uh, of course, you know, you have your traditional spreadable condiments like mustard, mayonnaise, and, uh, stuff like that. Uh, of which I, I only, I really just only have a preference towards mustard. Um, of course, my favorite sandwich would be a roast beef and Swiss with, with mustard. That’s about as good as it’s going to get. Uh, then you have your spreadables. You have your relishes and your, your, your hots and other things like that. They all have their place. Uh, well-made muffaletta sandwich is probably one of, one of the better things in life. Um, you know, my, my personal favorite sandwich is, of course, the peanut butter and onion on a, on a nice, nice, uh, potato bread. That’s, that’s about as good as things get for me.

But, you know, uh, I think, I think mustard would probably take the top, top spot there. Mustard is probably number one for me. Uh, good, solid deli mustard. We’re, we’re good to go wherever we go. Wherever we go, there we are. All right. That’s, oh, well, that was, that was a line, not a square. Thank you, Zoomit, for betraying me once again. Uh, first off, big fan. Well, that blows. Ha ha ha. Uh, why does less than or equal to one on a query with row number window function perform so different than equals one?

I find it’s a trick that almost forces SQL Server to compute all row number data before doing something else like pushing down the predicate of a join if the window query is used as a join table. I tried in one null and greater than zero and less than two without the same results. Thanks. Well, uh, so I’m not sure, uh, why, uh, other attempts at this, uh, did not work, uh, in the same way. But, uh, I, I, I, so two things here. One, uh, you, you have earned, I think you have earned yourself a full, uh, video explanation. So I’ll, uh, I’ll crack one of those out tomorrow.

But, um, really what the, the thing is, is that, um, when you use, uh, less than or equal to one, uh, you prevent SQL servers optimizer from, uh, trans using an any aggregate transformation, uh, in the query plan, uh, where you, if you use, so there, um, Paul White has, so I did a video about this, um, some time ago and, uh, I forget the title of it, of course, because, you know, there are a lot there, there are somewhere over 600 of these things. Uh, so it’s hard to remember the names of them all, you know, I love my children, but there are too many.

Uh, but, uh, Paul White also has a good post on any aggregate transformations, uh, where he talks about, uh, ways that SQL Server can take a windowing function and under certain circumstances, uh, can transform, uh, the windowing function into, uh, an aggregation rather than, uh, doing the full, uh, like segment sequence projector window aggregate, uh, typical query plan for a windowing function. So that’s probably what’s happening, but I will, I will do, uh, a demonstrative video about this tomorrow. So you have that to look forward to.

I will postpone all my other important work and do that because I care deeply about you. And, uh, you know, as, as much as I do want people to buy my training, I cannot, uh, I cannot bear to just tell you to buy it and hope that you stumble upon the correct thing. Uh, if you could change something about the world, not related to perf tuning SQL Server, what would it be?

Oh, I mean, there’s some obvious stuff isn’t there. You get rid of cancer, you know, you get rid of egg salad, you know, you Thanos snap government employees. Uh, I don’t know.

Some other, I don’t know. I think, I think, I think that would, that would just about clear everything up for me. Yeah.

Um, yeah, that about does it. I don’t know. Protein should be free. Socialized protein, I guess. Or alcohol or cigarettes.

I don’t know. One of those things. Whatever makes you happy. You get to pick one thing in life and that one thing is free for you. Everything else you have to pay extra for. All right.

Uh, what do we need to be aware of when enabling snapshot isolation in a large production database? Should we expect any downtime? No, you shouldn’t expect any downtime. Uh, enabling snapshot isolation is of course different from enabling read committed snapshot isolation in that you do not require, uh, an exclusive, exclusive access to the database.

Uh, when you enable read committed snapshot isolation, uh, it is most beneficial to use the with rollback immediate, uh, addition to the command in order to do that. But with regular snapshot isolation, uh, you do not require any such thing. Uh, there could of course be some hiccups along the way.

Um, you know, you, you might see increased CPU if you have a busy system and you start generating a lot of row versions, uh, right off the bat. Uh, if you have more of a slow moving system, you probably wouldn’t see much of that. Um, if you’ve enabled accelerated database recovery, you may see some, uh, growth in your user database.

Uh, also in the transaction logs, since the, uh, persistent version store is fully logged. Um, so you might see some transaction log growth in there. Uh, if your error, if your, uh, transactions suffer a lot of errors or, uh, aborts, or, um, um, if you have, uh, certain types of transactions that, uh, do not qualify for, uh, accelerated database recovery to work on them, uh, then you might see, uh, some additional, uh, transaction log growth in there.

Uh, but no, I wouldn’t expect any, anything like downtime. You might just see some increased usage and utilization and other stuff like that. But, uh, usually those things settle out over time.

Uh, if you leave it, leave it, if you leave things, uh, to their own devices for a bit. But of course we are working with databases and there is no such thing as a risk-free change. All of these changes that you make to your database, uh, involve trade-offs.

It’s up to you to make sure that the trade-offs that you are willing to make are fair and square for your workload. So, uh, always test in a development environment first and then beat the tar out of your development environment. Really smack it around.

All right. Last but not least here. Uh, I enjoyed hearing you beg a viewer to switch from DPA to SQL Sentry. First off, I don’t beg.

I, I instruct, I command. Uh, no, I’m kidding. Uh, I, I, I do really wish that people would, you know, step away from DPA, but, uh, that’s a personal thing. Uh, can I hear you say more about what DPA does wrong?

I haven’t used it in years. Well, you’ve had some happy years then, haven’t you? Um, so, you know, all monitoring tools probably collect roundabout the same set of information. Some, you know, more than others.

I do think SQL Sentry collects, uh, a bit more since it is very specific to SQL Server and DPA is more of a generalized database monitoring tool. Of course, you can use it on other platforms, uh, which may appeal to some people, but does not particularly appeal to me. Uh, and, uh, I think my biggest gripe with DPA is in the web interface.

It is very difficult to navigate around and isolate actual problems. Uh, you find yourself, like, you know, just twiddling around through snapshots and everything’s in a different place. And, like, it’s, it’s just very, I can, I don’t know, like, the way it displays blocking and deadlocks, it, it just doesn’t, it doesn’t work for me.

It doesn’t, like, it just doesn’t mentally jive with the way that I, the way that I prefer things. Uh, and, you know, not to say that SQL Sentry is a perfect monitoring tool either. Um, you know, certain aspects of the way that it, uh, displays, uh, blocking and deadlocks are very, you know, sort of unfortunate Vizio weirdness kind of thing.

It’s like, I don’t, like, there’s too many arrows to click through to see stuff. And, uh, you know, having the, the deadlock graph constantly at the bottom is not terribly helpful for me. Um, you know, I, so, like, the reason why, uh, I originally wrote SP Blitzlock and the reason why I wrote SP Human Events Block Viewer is because a lot of the information about blocking and deadlocking, uh, really does, um, make more sense when it’s sort of laid out in front of you rather than you having to click through a million different things.

And, like, the visual representations of things are just, like, I don’t know. They, they, they, it doesn’t, it doesn’t work well, uh, for me, for, for those things. For things like query plans and other stuff like that, it works very well.

Uh, DPA uses, uh, some, uh, strange interpretation of Justin Peeling’s, uh, HTML Plan Viewer, which, um, is, uh, woeful. I’m not saying Justin’s thing is woeful. I’m saying DPA’s weird, uh, implementation of it is quite woeful.

Um, things are mislabeled and, uh, things are not shown, uh, in the tool that can be very useful, uh, when looking at execution plans. Often you have to, you know, download whatever, uh, execution plan DPA has saved off and open it in another tool of your choosing to, uh, view the query plan with, uh, is, with more details. Uh, and of course, you know, neither of these tools capture, uh, what I would say is an actual execution plan.

SQL Sentries, Trace will capture some execution details, uh, about long running queries. So, like, sometimes you’ll be looking through, like, the sort of Plan Explorer-esque, um, uh, query plan, like, stuff when you open up a query plan in there. And, like, some of the steps in there will have, like, uh, CPU and duration and stuff assigned to them, which is helpful, but you’re still not getting, like, the actual execution plan with runtime metrics in there.

Um, you just have some additional information about, like, at the trace level, what, like, how long that query ran for and CPU and stuff. So, there are helpful things in there, but it’s still not as good as getting an actual execution plan. Um, you know, uh, I think, I don’t know, uh, yeah, just, it’s really mostly the way that, uh, DPA presents the data and the steps that you have to take to dig in further, uh, with DPA in order to, um, in order to find, uh, problematic things.

Uh, you know, be able to come to some, some reasonable conclusion about, um, what, what went wrong and what you can do to fix it. Uh, also, um, if, if there’s one thing that I, I really hate about what DPA does, uh, it’s the sort of canned advice that’s in the tool. Uh, whoever wrote that, I don’t think, knows anything about SQL Server.

It doesn’t seem like they have ever actually worked with SQL Server. Uh, it, it seems like they maybe got most of their information from very old blog posts and just sort of, uh, copied and pasted some, uh, inherited old wisdom, uh, into, into the tool, which, uh, doesn’t really make much sense these days. So, that’s, that’s about it there.

Uh, so, I don’t know. Uh, I, I am, I am quite anti-DPA as a monitoring tool. Um, you know, I, I still haven’t found anything that, uh, does as well as SQL Sentry with the ease that SQL Sentry does. Uh, and again, I do think that, uh, uh, whoever is in charge of SQL Sentry at SolarWinds, uh, should be sent to some sort of federal prison for, uh, their, their neglect and malfeasance.

Uh, and, uh, keeping that tool up to date with more modern SQL Server, uh, information that is available. But, uh, that’s just me. Anyway, uh, that’s about it for this Office Hours.

Thank you for watching. I do hope you enjoyed yourselves. I hope you learned something, and I will see you tomorrow, where I will do a full, uh, uh, full, uh, example of why you might see some of this stuff get weird. So, with that out of the way, I’m gonna go do something else.

Uh, mainly, uh, record that second video while it’s on my mind, so I don’t forget. And I will see you next time! Alright, thanks for watchin’, bye!

Going Further

Learn T-SQL With Erik: Bad Trigger Habits

Posted on October 10, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Bad Trigger Habits

Video Summary

In this video, I dive into the world of triggers in SQL Server, highlighting common pitfalls and best practices to avoid them. Starting with some practical advice, like setting your database compatibility level to 2022, I emphasize the importance of understanding how triggers can behave unexpectedly when dealing with bulk operations or external data modifications. The video then delves into specific examples, such as a trigger that incorrectly assumes single-row updates and demonstrates the issues that arise from using cursors in triggers, leading to inconsistent results. Throughout the discussion, I encourage viewers to consider more efficient methods for handling batch operations, like joining directly with the `inserted` or `deleted` tables, to ensure reliable and consistent outcomes. If you’re interested in diving deeper into T-SQL and mastering these concepts, be sure to check out my full Learn T-SQL course before the price increases!

Full Transcript

What am I doing? Recording? Really? More? Alright. Erik Darling here, Darling Data. Today we are going to do some T-SQL learning about triggers because it’s important to know about triggers. Of course, all of this content is small little itty bitty morsels from my full Learn T-SQL course. If you would like to purchase this course at the pre-sale price before the price doubles, after the advanced material is finished in tech review, you can do that down in a link in the video description. You can also do all sorts of other things. You can hire me for consulting, ask me office hours questions, contribute some money to the channel, four bucks or something. And if you don’t want to do any money stuff, you can do some nice open source free things, some FOS things with this channel, like like, subscribe and tell a friend. If you want to see me in person, or maybe, maybe not me, maybe you look at the lineup for any of these events and say, look at all these other great people. Maybe I’ll go harass them in the bathroom about what I should set MacStop to. You can catch me and all these other wonderful people who can tell you about MacStop in the bathroom at these various events. Pass on Tour in Dallas and Utrecht coming up very soon. Dallas, September 15th and 16th. Utrecht, October 1st and 2nd. Then we have Pass Data Community Summit taking place in Seattle from November 17th to 21st, where I have two, not one, but two days of T-SQL pre-cons with Kendra Little, where we will be dancing with the T-SQL stars or something. Anyway, let’s go talk about triggers. Now, the reason I bring this up, and I end up spending an inordinate amount of time dealing with client code that involves triggers in some way, shape or form, because people do all sorts of terrible, awful, no good, very bad things when they write them. And strange things happen, right? So we’re going to look at some of the terrible, awful, no good, very bad things that can happen. We can’t go fully into that, obviously, because sometimes, you know, I hit like, you know, right click modify on a trigger to like script it out. And the next thing I know, there’s like a 4000 line business application in a trigger. And I’m like, Oh, we have handled every use case under the sun here, haven’t we?

Good job. So let’s start simple. Let’s start with some things that you can do very early on to not deal with trigger problems in some sort of terrible way. Okay, I need to put my database back into 2022 compatibility mode, don’t don’t I stack overflow 29 set compatibility level equals 160. There we go. I guess I was working on something else in the meantime. Let’s go try this again. There we go. Beautiful. We have 255 rows in our table. Don’t tell anyone that happened. Everything else that I do is much more professional. And now we are going to look at a trigger with a very bad bug in it. This is something that I see when people make funny assumptions. Like I’m only ever going to like do something to one row at a time could be insert could be update could be delete. And they later find out someone maybe worked around the application and did their own insert or update or delete. And their trigger malfunctioned.

malfunctioned. So the main thing here is that this trigger makes that makes the bad assumption that we will only ever be able to update one row at a time, let’s say that we all of our updates usually in the application anyway, generally work off a primary key. But of course, what do we have? What do we have? And whenever we have a database in an application, we have people who use the database outside of the application. or people who want to special access to do their own weird little things in there. And what happens when we let users use a database? Hell breaks loose, right? Never let users in a database the first rule of database administration, make it so awful and painful for them to even get access to the database that they give up, right? Easiest way to have a quiet database. No users. It’s a good way to not have a job or money, but great way to have a nice quiet weekend. So our trigger does the bad thing and says we’re only going to get one ID from this inserted table. Let’s just update the one thing that we get it from that, right? So our update is based on whatever ID we pull out of inserted.

Now, if we update all of the rows in our table, we will of course, you know, update 255 rows, right? We say 255 rows affected. So good for us. We did that. But only one row actually gets hit by the trigger, right? If we look at the results of this, all of the data that we loaded in from all this down is still looking at from the year 0001, but only that first row got updated to the end of time, right? So that was what we did here. We said you all changed to that, but in the trigger we say you changed to that. And guess what? One row got affected.

So obviously that’s not good. That is not how we want things to go when we run our update. Another wacky weirdy thing that I see people do in triggers is say, you know, I’d like a loop to run in my trigger. I would like to take each one row at a time out of the inserted or deleted virtual tables and I would like to do something with that one row.

So why they do that? Lord knows, but I’ve seen it many times. So I’m going to use a cursor in this case. It could be a while loop. It could be any other type of loop you want to write. I’m just using a cursor because I like writing cursors. Deal with it. And the cursor has nothing to do… Well, I mean, the cursor doesn’t have anything directly to do with like a performance problem.

And actually the cursor is so fast that I have to put a small wait for delay in at the end of the trigger to just to simulate a very small weight. So we can see what happens when we process things in a trigger based… Sorry, in a cursor based trigger. Right. And the reason why this is a big deal for us when we’re looking at this example is because if we were to update all of these rows at the same time, we would expect all of these rows to get sort of like, let’s say that we were having, we had like a last modified column or something.

We might expect the last modified column to have like a standard time for everything, which it’s not going to have if we do this. Right. So if we have a trigger go through, sorry, a cursor go through and do things in a loop, we’re not going to get the time that we want from all this. So if I go and I update all of the rows in the table and this will take a second because that cursor has that two millisecond delay.

So we have to wait for a few milliseconds every time the cursor runs and we go look at the result of this. We’ll see that there is a little bit of a gap between all of these right now. So if that cursor were doing anything more intense or advanced and there were a, we like if I, or if I cranked up the wait for on that, I don’t have the wait for cranked up on this.

Cause I don’t want to spend a lot of time waiting for it to finish and for us to get something out of it. But you can see that all of these numbers ended up different. Right. So we, we like, like we, we ended up with a very sort of inconsistent update output from this.

And this is probably not what we want. So what you’re much, much better off doing whenever you are dealing with triggers is really to just join to the either inserted or deleted tables. Right. So we can join, but you know, okay. We can use exists too, right? Cause all we need to know if a row is there or not, we don’t need to know if every row matching is there or not. Right.

We just need to, is it there? Yeah, cool. Got it. We don’t need a one to many relationship for this to work. So if we do this and we just base our update off of some sort of correlation to the inserted table, we will end up with a much more reliable final sort of date on that. So if I update this now and we look at the results here, we will get back a consistent number across all of these, which is probably much more what you would expect.

And if you are doing any sort of auditing on a last modified thing, you would probably want to find, you would probably want to know if things were all like all done sort of like in a batch. Right. Because that would be important to know. Cause that might, if something is done in a batch, that might mean that someone went outside of your application and did something naughty or nauseous or something that they maybe shouldn’t have been doing, like changing lottery numbers or I don’t know, bills or money things, you know, just all sorts of unsavory stuff thing.

And then thinking, oh yeah, of course I placed a bet on someone to do something. And they did that thing when no one expected them to do that thing. Crazy.

All right. Anyway, that’s about it for triggers. Of course, there’s more in the course. So this, this sort of, this sort of thing interests you. It would behoove you to purchase the full course and get the full spate of knowledge from it.

Wouldn’t it? Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Uh, where we will, I think, I think we start talking about, is it functions?

Maybe it’s functions. If it’s not, then I’m sorry for lying to you. Should have, I should, I should, I should edit this video later. Maybe like an unsavory batch update.

Anyway, goodbye.

Going Further

Learn T-SQL With Erik: Indexed View Ups and Downs

Posted on October 9, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Indexed View Ups and Downs

Video Summary

In this video, I delve into the intricacies of indexed views in SQL Server, exploring both their advantages and limitations. I discuss how indexed views can significantly improve query performance when used correctly but highlight that they come with a set of constraints, such as restrictions on certain functions and clauses. I also cover practical examples and best practices for creating and maintaining indexed views, including the importance of schema binding, using count big in grouping elements, and understanding the implications of indexed view maintenance. Additionally, I provide insights into when SQL Server matches or fails to match queries with indexed views, emphasizing the role of hints like NOEXPAND and the necessity of statistics on these views for accurate query optimization.

Full Transcript

…things work better when you turn the microphone on. I’m Erik Darling with Darling Data, nothing if not professional recorders. Today we’re going to talk about more T-SQL learning, where we’re going to talk about indexed view ups and downs.

This is, of course, bite-sized portions, little morsel-sized portions of my full T-SQL course, which, if you go down into the video description, there is a link to buy at the pre-sale price. Well, the advanced material is polished up and tech-reviewed.

So, you can buy it for half price now or full price later. I can tell you which one I prefer, but… If we pit what I prefer versus what you prefer, you would probably end up just buying it now.

You can also do all sorts of other stuff with the links. You can hire me for consulting, become a contributing with money member of this channel, not with content. You can ask me office hours questions for free, right?

Ask me privately, I answer publicly, we all have a nice time. And if you don’t want to do any of that stuff with me, you can do the usual like, subscribe, tell a friend, yadda yadda yadda. Of course, I will be leaving the house a little bit, way more often than I usually do.

I’m going to Dallas, September 15th and 16th. I’m going to Utrecht, Netherlands, October 1st and 2nd. And of course, I’m going to Seattle, November 17th to 21st.

These all have something in common. And they are all pass related events. The two in Dallas and Netherlands are of course, Utrecht rather, are pass on tour events. Those are little mini events.

And of course, pass data community summit is the big one. At the big one in Seattle, I have two days of T-SQL pre-cons with Kendra Little, where we will light up the stage in your lives and your minds with T-SQL goodies. I lost my train of thought on that one.

Anyway, let’s talk about indexed views here a little bit. And let’s figure out the right SSMS spawn to open and let’s get into things. So teaching beginners about indexed views is a little bit difficult, mostly because once you’ve talked, once you’ve talked through all the limitations and once you’ve talked through all the sort of, you know, weird stuff between standard edition and enterprise edition, you’re left with people who are just completely blank eyed and slack jawed.

And it’s like, what is the point of any of this doesn’t make my life easy. How long has this feature been on this V1? Happened, right?

You got fabric. Oh, God. Regret. Anyway, so there’s some stuff that you can’t do in indexed views that has work arounds, right? Like you can’t use the average function directly, but you can do a sum divided by a count big.

All right. You can’t do a sum on a nullable column, but you can use is null on the nullable column to replace nulls with zeros. So you can get around that.

And you can’t do distinct, but you can group by, you know, any columns that you need to and have that get matched. So that’s all cool. Right? Great.

Here’s the stuff you can’t do. And to me, this is like a few of these are like table stakes, right? Like min and max. Like, how dare you? It’s like 99% of why I’d want a filtered or why I’d want an index view is min and max. Right? Aggregates.

How do we live? We can’t use window functions. So that includes anything with an over clause. We can’t use table expressions. That means common derived, anything like that.

We can’t use sub queries. This includes exists and not exists. Why you cannot use exists and not exists. I don’t know.

That’s a bit strange to me. You can do all the, I mean, you can do all the inner joins you want, but exists and not exists. Don’t work. Order by. I guess that makes sense. That would be a little weird.

Top and offset fetch. I also probably get that. No outer joins. That means left, right or full. You cannot use cross apply or outer apply. That is befuddling in ways, but okay.

No inline or multi-statement functions. I guess that makes sense. Inline actually probably doesn’t make a lot of sense, but whatever. No pivot, unpivot.

Having union, union all or accept or intercept. You know, I guess, I guess some of that, I mean, I’m not going to say it makes sense, but I will say, I could live without this stuff. Right.

The other stuff, some of this other stuff is like, we got fabric. Okay. Okay. So this list usually kills off most of the bright ideas people have. Especially once you take out min, max and any form of outer join, the use and utility of index views in SQL Server gets thrown directly out the window.

So then once you throw in the fact that all of your client options need to exactly match this magical incantation of things in order to match automatically with index views. The blank eyed and slack jawed developers. obrigadosellers.

Bears forget the functions… карт you to always view of that. Yeah.

Blackysle. Study does increase! Hey, well, you know what, you know what, I mean the limit it is essentially and send them to your exact object or just like this, my gosh that DM lid wascend to text. Let the survey just tell us that, you know. Even if I would remember this, you know what, I’d say to all ask us about these identifiers, but where are my mirasins?

Maybe you’d have to tell us our time I’m going to do that. So let’s create an index view that works, right? We’re going to create our alter this view. We got fabric.

Great. So now we have an index view. So stuff that you need to have in an index view, if you are going to, well, if you’re going to have an index view, it must be created with schema binding. That’s a pretty easy rule to follow.

And if you are going to have any sort of grouping elements in your index view, then you must have a count big in there. There are good reasons for this that the wording for always escapes me when I have to talk about it, but I’ll have dreams about it sometimes.

Now, because we have an index view properly, or rather we have a view set up with all of the required bibs and bobs necessary, we have done the incantations.

In order to index it, we are now free to do that. We can create a unique clustered index, and we can create a nonclustered index on our indexed view. And this all happens pretty quickly.

Now, a lot of people, when you’re talking about indexed views, will get, you know, worked up about indexed view maintenance. Ideal world, under, you know, most circumstances, you will be lucky enough that your indexed view maintenance will not be any more expensive than the maintenance of maybe an additional nonclustered index on the table.

This does change a little bit if you get any bright ideas about joining tables together in your indexed views, because then you start to get into stranger locking scenarios. But anyway, move on a little bit.

In some cases, a SQL Server can match some expression that you write to an indexed view, right? Let’s make sure actual execution plans are enabled here.

And if we run this, we will see that we do indeed do a seek into our indexed view, right? Look at us go. We have indexed view matched.

We did a good job there. We are using our post scores index view. That’s this thing. And we have hit the nonclustered index on total score in that index view. So good job, us.

100%. If we were to run this query and have it not work, one way that I can show you what it would look like, because, like, getting indexed views to not match is, like, I would have to, like, do something obviously stupid.

But, like, sometimes you might find yourself writing a query that you would expect to match the indexed view, and the query plan will end up looking like you just touched the base table anyway, which would be the case here.

Now, this happened because I used the option expand views thing. Little weird that that’s a thing, but okay, you know, we expanded our indexed view and got a worse query plan, right?

We had to do that whole calculation where we have that calculation basically stored in the indexed view. So, like, why would we want to expand that here? I don’t know, but we have the option.

I can show you that sometimes SQL Server will not adhere to our indexed view requests. And one thing that is important, and this is something that you will almost definitely need in standard edition, is the no expand hint.

So, just tell SQL Server you are not under any circumstances allowed to expand the indexed view, and we will indeed get usage of the indexed view here. So, we, you know, for a query that maybe SQL Server will be like, no, no, no, no, no, this would be too expensive.

We can, I’m going to expand this view and do the other thing. You can override SQL Server’s, you can override SQL Server’s thought process on that and say, no, thank you, no expand.

Please no. So, the no expand hint will work there. Now, but one thing that is important, whether you are on standard edition or enterprise edition, is that unless you use the no expand hint, SQL Server will not create or use statistics on your indexed views.

If you might get bad estimates, and you might even see missing statistics warnings if you don’t use it.

So, you know, at least maybe like, you know, when you start querying your index views, you might want to throw something on there with a no expand hint. At least get statistics created on that.

And then, you know, don’t forget to update your statistics because that’s usually a good thing, right? Usually. Stuff we can get away from. Stuff we can get away with sometimes.

Sometimes. But, anyway. We got fabric. Cool. So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we have some equally interesting T-SQL stuff to talk about.

All right. Cool. Thank you and goodbye. Bye.

Going Further

Learn T-SQL With Erik: Views and TOP 100 PERCENT

Posted on October 8, 2025September 21, 2025 by Erik Darling

Learn T-SQL With Erik: Views and TOP 100 PERCENT

Going Further

SQL Server Performance Office Hours Episode 32

Posted on October 7, 2025January 23, 2026 by Erik Darling

SQL Server Performance Office Hours Episode 32

Questions:

I’ve just installed SQL Sentry. Any advice?
Order By NEWID() will impact on Query Execution on large data table?
Are you aware of any issues related to restoring a query store database with forced plans to a different server with a different database name that the forced plans will no longer work? The plans have the database name in them but not sure if that’s a problem.
When you modify a stored procedure that contains a query with a forced plan it get a new query_id in query store even if the query hasn’t changed. Are there any tricks for applying the already forced plan to the new query id?
Will you do a jig or a goofy dance. Feel free to have fun with your green screen with this one

To ask your questions, head over here.

Video Summary

In this video, I dive into answering five user-submitted questions on various SQL Server topics, including troubleshooting font issues in SQL Sentry, the pitfalls of using `ORDER BY NEWID()` for large datasets, and the complexities of restoring query store databases to different servers. I also share some insights on modifying stored procedures with forced plans and even reveal a little secret about my constant, albeit invisible, dance moves. If you have any questions or want more details on these topics, be sure to check out the links in the video description for interactive ways to engage with me and other community members. Stay tuned for tomorrow’s session where we’ll continue our journey into T-SQL and SQL Server performance tuning!

Full Transcript

Erik Darling here with Darling Data. And you wouldn’t believe it’s that time. Can you believe it’s just another Monday? And we have to do office hours, don’t we? This is where I answer five user submitted questions at a time, usually. Every once in a while I’m off by one. But let’s get on to that. Before I answer questions, we’re not doing anything. You are not contributing anything to this except questions, which hopefully you’ll do. And you can find the link to do that down in the video description. There are all sorts of helpful links down there for you to interact with me in all sorts of exciting ways. So I hope you will choose to peruse those links and excite me in some way. If you want to get excited in person, we can do that together. That’s it. That is definitely a wee thing. I will be on tour with the nice folks at Redgate. We have two mini Pass On Tour events coming up. One in Dallas, September 15th and 16th. And one in Utrecht. That is, in fact, the Netherlands, I’m told. October 1st and 2nd. And of course, all this is leading up to Pass Data Community Summit taking place in Seattle November 17th to 21st, where I will have two wonderful, mellifluous days of pre-cons with the lovely and talented Kendra Little. All about T-SQL and performance tuning a little bit, because it’s hard to talk about T-SQL without some performance stuff sneaking in there, isn’t it?

Anyway, let’s go get them. Let’s go do these here questions. Boy, I have a lot of stuff in the taskbar today. A lot of work going on. A lot of stuff flying around. A lot of moving pieces here. Hopefully, you can keep track of them all. All right. First question. Let’s see what we got here. I don’t understand why the font is a little wonky on that. Not my fault. I’ve just installed SQL Sentry. Any advice? Well, we should open it. Make sure it’s running. Make sure it’s collecting data. These are all good things to do. Assuming that you’re talking about the monitoring tool and not just PlanExplore. If you’ve just installed PlanExplore, I have bad news for you.

Use SSMS instead. But if you’re using SQL Sentry, that is one of my favorite monitoring tools out there. I would suggest maybe taking a look at the alerts section and making sure that the alerts that are set up and being sent out are ones that are of use and value to you. It does default to sending out a lot of alerts and not all of them are particularly useful or meaningful in every environment.

The other thing that I would suggest you do is when you go into the top SQL tab, there’s a little settings button somewhere. And there’s a little settings tab along with top SQL, procedure stats, query stats, stuff like that. And by default, SQL Sentry only shows you the top 15 queries.

It will show you a max of 5,000 queries. So I suggest changing the 15 in there to 5,000. Alongside that, way up at the top of SQL Sentry, there’s a little button that sort of looks like a Greek E, right?

Whatever that little epsilon thing maybe. I don’t exactly know what it’s called. I’m not Greek.

And it’s all to me. So you should push that button because that button will group the queries in the top queries and procedure stats and query stats tabs together by text pattern. So if you have the same query over and over again doing stuff, you can see sort of top-level grouped results for CPU duration, memory, things like that.

So that’s my advice. That’s where I spend most of my time in there. Don’t neglect the blocking and deadlocking tabs.

Those are also very useful. Aside from that, I would just say enjoy yourself because you have the best monitoring tool on the market despite SolarWinds’ best efforts to have it do nothing new or interesting. So I actually think that they should be arrested for how they’ve neglected SQL Sentry.

As a monitoring tool, DPA is, of course, a tire fire that no one should ever be forced to use. All right. Next question.

Order by new ID will impact on query execution on large data table. Yes. Don’t do that unless you hate yourself or someone else. Ordering by new ID is a great way to have a performance problem.

Demo is really well, but don’t do it. It’s avoid at all costs. If your question is about getting random data out, ask that question instead and we can talk about that. If you just really are not sure about what new ID is going to do with an order by, good luck.

All right. Here’s a question, I believe. Yeah, there’s a question, Mark.

Look at that. Are you aware of any issues related to restoring a query store database with forced plans to a different server with a different database name that the forced plans will no longer work? That sounds like a statement.

It sounds like something that you have found. It sounds like you have found an issue. And you are asking me if I’m aware of an issue. And it sounds like it’s an issue to me. I would think that that I would guess like this is without me going and, you know, looking at the looking at that and actually going through with all of the steps that you’ve outlined there.

I would guess that the plan forcing would have some difficulty because the database name stored in the query plan XML might throw things off. And you would probably end up getting new query IDs for the queries that had forced plans once the new database name comes in with the plan XML. So I would guess that that’s not going to work, but you know, probably not great.

So here’s another question. When you modify a store procedure that contains a query with a forced plan, it get a new query ID in query store, even if the query hasn’t changed. Are there any tricks for applying the already forced plan to the new query ID?

Well, no, you have to get that plan for that new query ID if you want to force it for that query ID. Otherwise, otherwise, query store throws an error saying that there’s no plan ID for that query. I would be interested because you’re saying modify.

And I think that if you alter or create or alter, actually, you know, I think if you alter a store procedure, it keeps the same object ID. I know that dropping and recreating will assign a new object ID. So I’m not sure that that would be it.

But it’s an interesting question. And I think that’s something that I would need to dig in on a little bit. But I don’t have a quick answer for that because I’ve just never looked at it.

So there we go. All right. Last question here. Will you do a jig or goofy dance?

Feel free to have fun with your green screen on this one. Well, I am dancing currently. So what you don’t know about me is that I am constantly dancing. You can’t see from here down.

But my legs are just in a constant flurry of dance. So maybe that’s just the level of skill that I have at the form of dance that I practice. Is that you can’t even tell I’m doing it because nothing up here moves.

So now you’ve learned something new about me. Anyway, that’s five questions, I think. Well, that’s five questions and some homework for me, I think.

All right. So I’ll have to go look at that. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will do some more T-SQL learning or something along those lines. And after that, who knows?

We’ll figure it out when we get there, won’t we? All right. Thank you for watching.

Going Further

Learn T-SQL With Erik: Fun with RAISERROR!

Posted on October 6, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Fun with RAISERROR!

Video Summary

In this video, I delve into some of the often-overlooked aspects of the `RAISERROR` function in SQL Server. While many have moved on to using `THROW`, I still find significant value and utility in `RAISERROR`. I explore its various substitution parameters, such as `%d` for signed integers, `%x` for unsigned hexadecimal, and `%i64d` for bigints, pointing out the absence of native support for bits or temporal data types. This can be a bit cumbersome but also opens up creative possibilities for error handling in dynamic SQL scenarios.

I also cover how `RAISERROR` can be used effectively within loops to print messages without waiting for buffer fills, and demonstrate how adjusting severity levels can change message appearance. Additionally, I show how you can dynamically assign values to parameters like severity and state based on your code logic, providing a more flexible error handling mechanism. The video concludes with practical examples of using `RAISERROR` in dynamic SQL contexts, highlighting its benefits over traditional error functions.

Full Transcript

Erik Darling here with Darling Data. And today we’re going to be talking about some of the somewhat overlooked things about raise error. Because I still find value and use and utility in raise error. You know, some folks have graduated to throw or semicolon throw, as I often see in the world for no particular good reason. So you just terminate your previous T-SQL statement, you won’t have a problem. So there’s that. Anyway, we’re going to do that. In the meantime, if you feel so bold and you look a little bit further down in the video description, right? My face is up here, but I don’t care if you look down here for a minute. You’ll see all sorts of useful and helpful links for ways where you can interact with me further. You can hire me for consulting. I do that as a full-time job. It’s nice. You can buy my training, right? Including the Learn T-SQL with Erik course that this is teaser material from. It’s down there. You can become a subscribed member of the channel. You can give me a small amount of money every month to keep producing this wonderful content. You can ask me office hours questions for those episodes where I answer five user submitted questions. I get a bit of a backlog now, but you know, times are busy. So, we’re rolling these videos out as we can. And of course, if you enjoy this content and you feel that it would be a remarkable, it would be a remarkable improvement in someone’s life if they were to view this content on a regular basis, I would suggest that you not only like and subscribe, but you tell that person about this channel. Perhaps send them a link and tell them, encourage them to like and subscribe. So, with that out of the way, we need to discuss my travel plans.

For the remainder of the year, where I will be in Dallas, September 15th through 16th and Utrecht October 1st and 2nd for a couple of mini pass on tour events that Redgate is putting on. And of course, this all leads up to the greater, larger, past data community summit taking place in Seattle, Washington from November 17th to 21st, where I will be doing two days of T-SQL pre-cons with Kendra Little. So, we do have many things to look forward to there. But with that out of the way, let’s talk about some of the fun stuff with raise error.

Now, the first rule of raise error is to make sure that you understand how to use it. Now, if you look at things that you can use as substitution parameters or strings with raise error, or substitution wildcards or something with raise error, you’ll notice that you have %d or %i for signed integers.

You have %x or that should be % uppercase x. Let’s fix that. Let me don’t say zoomed in on that for too long. Got lost in the copy paste or something. We have %o for an unsigned octal. I’m not sure what that is.

I’m not a math person. I don’t know what an octal is. You have %s for string. You have %u for unsigned integer. Well, apparently the… I don’t know.

I don’t know why you wouldn’t sign an integer. And then you have %i64d for big ints. There are a couple notable things that are missing here.

Like say, bits. Right? Or say dates or times or date times. If you want to use bits, or you want to use some temporal thing in here, you have to store that as a different data type before you are allowed to use it with raise error.

So if you have a bit, you often have to store it as a tiny int. And if you have a date, you often have to convert it to a string in order to present it as a substitution in raise error.

So there are no native replacements for bits and strings with it, which is quite annoying. I find that quite obnoxious about raise error, because now I have to declare all these additional parameters and hang on to them, and it’s just annoying.

Why we couldn’t get bits and strings for raise error? I don’t know. It’s just the way it is. So there are some neat things with raise error. Or if you just want a normal printed message, you can use log if you want to send it to the error log.

I’ve never found a lot of use for this. Maybe people who do more dedicated SQL development work, who plow through the error log for various things, find this a useful addition to it.

I’ve never really used it all that much. Most of the code that I write these days is my store procedures, where I don’t need to send anything to the error log.

There wouldn’t be any use in that. And most of the client code that I work on, there’s no sense in polluting their error logs with anything that would happen in there. But if I were writing some sort of maintenance procedure, this might be useful.

But notice if we use 10 for a severity here. I am an error gets printed in, well, because I’m using dark mode. So this text is some form of white.

If I were using light mode, this text would be some form of black. So local factors will apply. But the most important thing is that this doesn’t look much like an error, does it? It just looks like a regular printed string.

That’s why raise error can be very, very useful when you’re working with things like loops. So if you are writing a loop of any variety or any sort of task where you want to print a message when things happen, this with no wait is very, very useful because you do not have to wait for these messages to buffer anywhere before SSMS starts sending them out.

If you just use print, you do have to wait for certain buffers to fill before the print messages come out. If you want your text to come out red, you can use severity 11 or higher. And now notice we get some additional stuff here along with the text being red.

This would be red regardless of dark mode or light mode or anything in between. System default, if you will. One thing that you do want to avoid though is not using an aggressively high error message.

So here I, the last one is severity 11. Here I’m going to use severity 20. And severity 20 is just about as high as you can get. But when I run this, well, we get a whole lot of stuff back.

I once worked with a client who was doing, was using raise error for stuff. And people like the one developer was putting a level 20 in there. And everyone thought that the database was corrupt and that there was some problem.

Like every time this, like this, the store procedure had like hit an error, it was like, it would spit all this stuff back. And people would be like, we got corruption run check DB there, run check DB. Nothing came back.

And it’s very confused until we looked at the code and I said, ah, well, guess what? We’re all safe. We all live another day. So what, what, getting to sort of what I was talking about first with a sort of wildcard replacement is that you can put whatever you want in here, as long as it’s not a bit or a date or a time or a date time, those you have to store differently.

But you can, you can substitute things in either as a string or as an integer. These are the replacements that I’ve chosen here with percent S and percent I. Well, I guess there’s already a little line underneath it.

So we’ll give this one a hat. But what’s kind of nice in here is that if we run this, we will see the replacements all worked very nicely. Now, one thing that people do miss sometimes is that you can substitute anything in raise error with a parameter or variable, right?

So in a lot of these, we were using just a literal value for severity and state. But you can, you can have, you can assign this dynamically based on whatever your code does. And you might, you know, assign some things a severity of 10.

If you don’t care about it being read and printing an error and maybe like halting execution, you, if you just want to like, you can assign things any way you want. So like, if you look at this, I’ve declared some variables in here in order to do a little bit of replacement.

So I’ve got error message. I’m just going to highlight this part. So you can add a little bit more, uh, severity, state, error number, error procedure, and error line. Now in real life, like if you were writing like real legit error handling, I don’t know, you would probably just use the various built-in error functions to assign these in your try catch block.

And that would be totally fine. You could also assign your own things to them. If you want to a little bit more control over what gets spit out to people, right? Like you might like, instead of like a full crazy error message, you just might say, retry your procedure or something, right?

You put whatever you want in there. It’s nice. It’s a good time. So what you can do is have any one of these, uh, replace things. So we have severity and state this time is, uh, well, obviously local variables, because I just declared them up there, but then we have, uh, all the replacement stuff that I would be using down here.

Right? So, uh, you’ll see number procedure line message, all the stuff that you would put in like a normal, good verbose error message to help you figure things out. And if we run all this, we will get all that back.

Uh, I’m going to put that on a new line just so we have things a little bit more friendly for zooming in and we have our number, right? Good for us.

Right. And we see, well, we used 11 and one up here, so that doesn’t really do anything, but we, uh, we did mess with our line number a little bit. Uh, and then we have, uh, the number that we wanted to replace, uh, the procedure name, right?

So like, if you write a lot of dynamic SQL and, uh, maybe, you know, you want to raise an error and you like, you know, procedure name is cool and all, but you know, you could say like dynamic SQL block 10 from whatever procedure, and you would be able to locate the dynamic SQL block that you, that you, uh, you like name or like label dynamic SQL block 10, like a comment in the code or something.

And, you know, I’m grant, you know, I write some long procedures, but that’s, that’s getting out of control. And, you know, we can put in whatever message we want here. So there’s all sorts of fun stuff that you can do with raise error to make things, uh, better suited to your environment, more descriptive and a little bit more dynamic than what you might get with sort of like just normal assignment to the various built-in error functions that SQL Server has.

Um, I like using this stuff, particularly, particularly with dynamic SQL, uh, because I can, uh, maybe like in the error message, I can do a preview of the dynamic SQL, or I can put the error procedure as, you know, whatever block that I like labeled in the procedure with a comment, something like that.

So there’s all sorts of neat things you can do to help you get exactly to the code that’s having a problem that maybe wouldn’t be so obvious if you were just using like, um, like the normal results of error functions.

So, uh, easy Friday video, nothing too crazy in here, but I hope you enjoyed yourselves. I hope you learned something and I will see you over in the next video, which will be on Monday, which will be office hours, reliable office hours, where I will answer five user submitted questions because I will, I will count carefully.

I will endeavor to count more carefully in the future. Anyway, thank you for watching.

Going Further

Learn T-SQL With Erik: How to Pick a Temporary Object

Posted on October 3, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: How to Pick a Temporary Object

Video Summary

In this video, I delve into the nuances of temporary object comparison within SQL Server, focusing on table variables and temporary tables. I explore their respective strengths and weaknesses to help you choose the right one for your specific use case. Starting off, I highlight the downsides of table variables, such as preventing parallel query plans when modified and lacking column-level statistical information. In contrast, temporary tables offer more flexibility with full parallelism support and better handling of statistics. The video then dives into practical examples, comparing execution plans between a table variable and a temporary table to illustrate how cardinality estimates differ significantly. By the end, you’ll understand why temporary tables might be preferable for complex queries that rely on accurate cardinality estimates, making this content invaluable for anyone looking to optimize their SQL Server performance.

Full Transcript

All right, Erik Darling here with Darling Data and continuing on the theme of learning T-SQL. I suppose it’s worth saying that all of this content is teaser content, of course, for my Learn T-SQL with Erik course. Today we’re going to be talking about temporary object comparison, not how to like compare data from one to another, because that’s dull. Intersect, accept, whatever. But more how to choose the right one. one for you and for each particular use case that you may have for a temporary object. So, before we move on to that, if you are saying, wow, a course where I can learn T-SQL with Erik. Well, yeah, there’s a description, in the video description, there’s a link to do that. The course is still currently at the presale price of $250. But as soon as the advanced content is all done, which probably has like maybe like two more months left on it, and that like fully recorded and produced, like a quarter of the way written, you will have to pay $500 for it. So I would strongly suggest buying it now. You can also do other things down there, like hire me for consulting. You can contribute money to the channel to support my work to bring you SQL Server content. You can ask me questions for my Office Hours episodes that I really enjoy answering. And of course, if you enjoy this content, but not in a monetarily productive way, you can always like, subscribe, and of course, tell a friend, because maybe your friend is secretly rich, wants to hire me for anything. Cool. I’ll be leaving the house a few times between now and November. I will be in Dallas September 15th and 16th. I will be in Utrecht October 1st and 2nd. And I will be in Seattle November 17th to 21st. What do all of these things have in common?

Well, they are all past events put on by the nice folks at Redgate, who were kind enough to decide to have me show up at all of them and talk at you about SQL Server performance tuning. So with that out of the way, let’s compare temporary objects here. Let’s make sure that I go to the right SSMS spawn because, well, I need multiple of them. So let’s talk first about table variables. They have some upsides and some downsides. Downsides would include preventing parallel query plans when you modify them. At base, you need to put data into a table variable via an insert. And of course, that will force whatever query populates the table variable to run fully single threaded. There is a non-parallel plan reason for that.

There is no column level statistical information. There are no histograms available for table variables. This is true even if you index them. You can get some benefit if you create a unique index on them for things, but you still don’t know what values are in there that are unique. You often get poor cardinality estimates with them when you start joining on them to other tables or filtering on them locally with a where clause.

There is no recompilation threshold with some exceptions. There are some trace flags out there and there are some newer SQL Server features out there where you may find a recompile does happen. Most notably, if you put a recompile hint on the query. There we go.

You cannot select into a table variable. Table variables use the collation of the local database that your procedure is executing in. They are only available in the current scope and batch, so you cannot declare a table variable out here and then reference it as easily as a temp table, either in dynamic SQL or in another store procedure.

Modifications to table variables are not automatically rolled back on error or explicit rollback. They can be used in some functions like a multi statement table valued function or a T-SQL scale R UDF. And of course, you can create indexes on them when they are declared.

Temporary tables have a bit of a different profile. There’s no inherent parallelism restrictions. You can insert, update, and delete. And as long as SQL Server thinks a parallel plan would be a good idea, you can get yourself a parallel plan.

They do allow for statistical information and histograms. They can sometimes suffer poor cardinality estimates, though, because statistics can be cached when they are used in store procedures. They do have a low compilation threshold, so you don’t often run into the cached temporary table statistics problem, but never say never.

You can select into them. Temp tables use the collation of tempDB. So if you might ever look through my store procedures that create temp tables, you might see a lot of things where I need to collate to the local database.

So like collate database default, and that is to prevent string comparison issues if the collation of tempDB does not agree with the collation of the local user database that the store procedure is running in. They are visible to child batches and scope, so they’re very easy to reference in Dynamic SQL or in other store procedures that you might call. Modifications are rolled back on error or explicit rollback.

They cannot be used in some functions, unfortunately. And you can create indexes with the table. When you say create table, you can make the index definitions inline, or you can add them on later, depending on which you find to be most beneficial. There are a lot of insert patterns where you want to insert into a heap temp table to get a fully parallel insert, and then you can create an index later on that.

And you also get the benefit of a full scan statistics histogram when you create the index later, rather than the sampled histogram that you would get if you loaded the data with the index already there, and then maybe use the column in a where clause or a join clause or group by or something. There are some similarities though, and these are non-negotiable. They both live in tempDB and take up space there.

Table variables are not in memory unless you are explicitly using the in-memory SQL Server feature and creating in-memory table variables, which do not look like the declaration of a normal table variable. They both log equally to tempDB’s transaction log. They are both session scoped, meaning that once the session that created or declared the temporary object is gone, they are gone.

Global temp tables have slightly different rules, but we’re not talking about those. Simultaneous sessions can use temporary objects with the same name without existence errors, but that, of course, does not apply to global temporary tables. Now, that’s a lot of talking, isn’t it?

We’re already seven minutes, and still, all we did was talk. But now we can get down to some of the nittier, grittier stuff with them. So what I’m going to do is create a table, or rather declare a table variable called user scores. And I want you to note that I have some check constraints on here, and I have a couple indexes on here.

Now, the point of these indexes is to aid later queries. So what I’m going to do is I’m going to run all of this stuff, and I’m going to talk about these two hints in a second. But let’s run all of these, and let’s wait a moment, and we’ll get the actual execution plans for those when this is all finished.

So because I am using SQL Server 2022, and my compat level is 150 or higher, keep in mind this is a feature that started in SQL Server 2019 with compat level 150 up, assuming you’re on Enterprise Edition, of course.

You have paid the Microsoft friendship tax, and they have decided to try to help the performance of your workload. Because of that, I have an intelligent query processing feature called table variable deferred compilation at play here, which is going to get me table level cardinality for like a full scan of the table or something, a full join of the table.

But you will see in a moment that this does not help us with cardinality when we have a where clause. So if I were to apply either one of these hints to either disable that feature or to use the optimizer compatibility level 140, the table cardinality guesstimate for table variables would disappear.

But this is all finished running, and I’ve got a few queries down here. I’ve got one query saying where owner user ID equals 22656. I’ve got one query here asking for where score is greater than zero.

And I’ve got one query down here asking for both. So this should be interesting because, like we went over, when I created this table, I had a check constraint for score being greater than zero.

And I have an index on owner user ID and an index on score. When I inserted data into the table variable, both of these things were written in a way to get us the same owner user ID for every entry in here, right?

Every row in this is going to be 22656. And of course, we have score greater than zero in order to not run into any check constraint errors. But the cardinality estimates that I get from this, I mean, the totals are all correct, but, you know, not much else.

Starting with the top query that does the insert, you will see that there is a clustered index scan here, a clustered index insert here, and then there’s an assert over here, which the check constraint uses to make sure that all of the scores are greater than zero.

If you hover over the clustered index insert, you’ll see the two additional indexes listed that were inserted into for owner user ID and score. Anyway, moving on.

If this query ran for 4.295 seconds, and you’ll notice that there are no parallelism indicators at all for this plan. This is because if we right click and hit properties, we will have this non-parallel plan reason over here, like I described earlier, where SQL Server will not be able to generate a parallel execution plan when we go and, what do you call it, run the query, do the insert.

There we go. Anyway. All right.

Moving on. Now, because I have the table variable deferred compilation intelligent query processing feature enabled here, and it happens for us, what SQL Server is able to do is when we just get a count of the entire table, where one equals select one will always mean true, but when we do that, SQL Server is able to guess that 27,029 rows will come out of here.

Great. But this is where things do start to fall apart. Why?

Well, because all SQL Server knows is that there are 27,029 rows in the table variable. It still has no description of what values ended up in there. So when I change the query a little bit, and I say where owner user ID equals 22656, SQL Server, well, we still return 27,029 rows, but SQL Server guesses that 164 of them will meet that where clause.

There’s no histogram. If there were a histogram, we would see that. Now, excuse me.

If we look at other places, we’re going to see where I say, hey, show me where score is greater than zero. Well, we know that every row in there is greater than zero because of that check constraint, and we have an index on score, but SQL Server says, well, I think you’ll get about 8,100 rows back.

And look at the check, neither the check constraint nor the index were of any benefit to cardinality estimation here. And going down even further, if we say where owner user ID equals 22656 and score is greater than zero, SQL Server says, I think you’ll get about 90.

But we still get back all 27,029. So it is difficult to prove a negative when looking at things like this. But if we look at the properties over here, there’s a thing missing from over here.

There’s usually, if you look at a query where statistics were in use, you will see an optimizer stats usage entry in the properties of the root operator in the query plan. But we do not have one of those here.

Again, it is difficult to prove a negative, but we can go on and use a temp table and prove a positive, can’t we? We can do something very scientific. So what I’m going to do is I’m going to create a temp table.

And I’m not even going to festoon it with all of the things that we had on the other one. I’m not going to do that. I’m just going to say we have two columns in this temp table.

They are both integers and they are both not null. There’s no check constraints. There’s no indexes. So that’s what we have going for us. So let’s drop this table if it exists, even though it doesn’t.

And let’s do that exact same insert. Now, if you look at the execution plan, you’ll note that we do get a partial parallel plan here. SQL Server does not use a fully parallel insert.

Why? I don’t know. Didn’t meet the cost. I think it was maybe the number of rows because SQL Server estimated 213 rows. And, of course, we got 27,000.

But, you know, I think there’s a cardinality estimation thing at play with if SQL Server decides that a parallel insert is going to happen or not. But anyway, we do not have, clearly do not have a non-parallel plan reason for the insert into the temporary table. And you’ll note that it does run in about 700 milliseconds.

I think the other one was, was it 3.4 or 4.3 seconds? I forget. But it was much longer than this, right? It was a long time. Now, we’ve got this table and we’ve got it populated.

So that’s our good first step. Now, let’s run the same queries, but now against the temp table that we had against the table variable before. We’re going to get back all our 27,029 results, which is a good first step for us here.

But if we look at these, of course, you know, table cardinality, very easy to guess. So this top one, you know, it gets a good, good grade here, right? 27,029 to 27,029.

Not much difference just yet. But now, boy, howdy. Look at, we get accurate cardinality for both of these. When we, when we said where owner user ID equals 22656, SQL Server looked at, generated statistics on the, on the, on the temp table, just on its own. Remember, there’s no indexes on this thing.

SQL Server just generated statistics and said, hey, that’s 27,029. All right. I know. I got this one. I’m your friend.

And when we say where score is greater than zero, SQL Server said, hmm, got your back, homie. 27,029 of 27,029. So now not only do we have accurate table cardinality, we have accurate column level cardinality when we start filtering on our table variable.

This becomes very important if we were to start joining, sorry, our temporary table. This becomes very important if we were to start joining a temporary object off to other larger tables where perhaps, you know, different, having statistics on columns that we’re joining to very large tables would be beneficial in SQL Server generating an optimal execution plan, don’t you think? And if we combine these two predicates where owner user ID equals 22656 and score is greater than zero, SQL Server will once again, remember last time it said like 90 or something?

Now we get the full number of rows that would actually come out of that temp table. So not to say that table variables do not have any uses, but for me as a performance tuner, generally I am very interested in not only materialized results, but getting either better or more accurate cardinality estimates when I start doing other things with those materialized results. And this is where I mostly want to sway people to use temp tables instead of table variables, but I still have to spend a lot of time going over things like this.

So perhaps this channel just does not have enough reach. Perhaps not enough of you have told a friend about this channel and perhaps you should because there are a lot of people out there who still need to know these things. Anyway, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where we are going to talk about some of the fun and joys and quirks of Razor. It’s of course a much smaller portion of an error handling module, but you only get teasers because you didn’t pay for it.

If you buy the full course, you get the full content. All right. Anyway, goodbye.

Going Further

Learn T-SQL With Erik: Just Use QUOTENAME!

Posted on October 2, 2025January 23, 2026 by Erik Darling

Learn T-SQL With Erik: Just Use QUOTENAME!

Video Summary

In this video, I dive into the exciting world of Dynamic SQL and why using `QUOTENAME` is far superior to simply adding square brackets in your code. I explore how `QUOTENAME` can protect you from potential SQL injection attacks and handle strange characters or names that might appear as column or object names. Additionally, I share some amusing bonus content at the end, including a clever workaround for dealing with very long dynamic SQL strings that are too big to print in one go. Whether you’re looking to enhance your T-SQL skills or just want a laugh, there’s something for everyone!

Full Transcript

It’s me, Erik Darling with Darling Data. And today’s video, we are going to go over some of the kind of fun things with quote name when you’re using Dynamic SQL and of course why it is far superior to the old, I’m just going to stick some square brackets in my code because that doesn’t give you the same level of protection. And then there’s going to be some rather amusing bonus content at the end, which I do hope you’ll enjoy because without your enjoyment, I die. No pressure. Anyway, if you look down in the video description, there are many important links in there. And they’re very clearly labeled places where you can hire me for consulting, places where you can buy my training and of course at a nice discount because you watch my videos and you know, I think I think, I think you’re a nice person and I think you deserve a break in life, especially if your employer is not willing to pay for training. It is difficult to afford the very, very high priced, often not worth it training out there on the internet. You can also become a subscribed channel member where you actually give me a small monthly donation in exchange for all of the wonderful content that I produce. You can ask me questions for my Office Hours episodes where as long as I’m going to be able to get a little bit more information, I’m going to be able to get a little bit more information.

As long as I count correctly, I answer five user questions. And of course, if you enjoy this content, I would encourage you to like subscribe and of course, tell a friend. The more friends you bring, the more people I have to talk to and gosh, then I might bring joy to more people and I might live longer, which is hopefully good for all of us. Speaking of things that are good for all of us, I’m getting out of the house. Actually, I’m getting out of the country a couple of times. Once, once that, oh, I mean, is Texas another country yet? I don’t know. Hard to keep track. So much going on in the world. But the nice folks at Redgate are dragging me around to Dallas, September 15th through 16th. Utrecht, October 1st and 2nd. And of course, past data community summit in Seattle from November, from the 17th to the 21st, where I will be hog tying and spit roasting two days of T-SQL pre-cons with Miss Kendra Little. So I do hope you attend those. Can’t wait to see you there. Again, no pressure. Might die if you don’t come. Wouldn’t be good for either of us. And then, I don’t know, whatever. So with that out of the way, let’s talk about some of the joys of quote name here. Now, I am in such a busy state that I need multiple SSMS spawns open. So please don’t judge me.

No, I’m not, I’m not here to bring pain to anyone. Anyway, important reasons why you need to use quote name. Well, well, it is somewhat unlikely that you will encounter someone naming a column, the clown emoji out in the world. You never know. You never know what kind of rough scallions you’re going to see out there. So what you need to do is protect yourself. Cause there are other things that people might do, like put spaces or dots or dashes or put weird things in, in, in, as column names that, that really cause issues. If they are not properly quoted in a way where SQL Server can identify the full text as an object name.

So let’s, let’s create this table, which, which, which happens successfully, even though we’re not using quote name here, but we do have to use it while we’re constructing dynamic SQL. That is if we’re smart. Now, one thing that I like to do when I’m trying to write very safe dynamic SQL is maybe not depend on user input, uh, very much for things. Um, you know, I, I very much believe in, uh, maybe taking the user input, but using it to value, like, uh, especially when it’s, um, you know, when you’re using dynamic SQL and you need to take, uh, like sort of dynamic input for things like database name, schema name, table name, column name, all of that information is in your, your server’s metadata.

Uh, more, probably more specifically, well, I guess from server level down to database level. And so I’d like to take that input and not use that maybe directly in the dynamic SQL. I like to generate my own inputs based on that metadata that’s in the, in the server where I, where there’s no chance of SQL injection of anything malicious happening.

And I can sort of weed any of that out. So whenever someone, uh, will pass in a list of columns or a database name or a table name or a schema name, I always validate that stuff against server. Metadata.

And I, I always get, uh, like the, the stuff, like the parameters and variables that I’ll use in dynamic SQL from the metadata directly, because it’s much, much harder for someone to tamper with that maliciously. Of course, with, with the remote DAC, you can do many fun things. So like one thing that I’ll do.

And let’s like, you know, if, if, if let’s like, let’s pretend that column names was, uh, like a parameter, right. Uh, to a store procedure on someone could put in a CSV list of column names. I would even use, uh, go even further and validate those column names when the metadata for whatever table we’re looking at.

So there are all sorts of things that you can do to protect yourself from a SQL injection. Using quote name is a very good, not only good for helping you avoid SQL injection, but also for helping you deal with straight objects with strange characters in them. So let’s, uh, run this, right.

Let’s get this, let’s get a list of column names and let’s look at what dynamic SQL this generates because this is going to fail mightily. If we look at what happened in here, SQL Server tried to run this select query. And this select query is not going to run with any of these things as column names without square brackets.

And it’s certainly not going to be able to select anything from a table name with the space in it. If you’re like, if you keep in mind, there’s an absolutely no like underscore or anything here. It’s just a bare ass table name.

So we want to use quote name to fix these things in order to encapsulate them correct correctly. So if we rerun this and now instead of, uh, just taking the name from, uh, sys.columns, we’re going to say quote name, uh, c.name from sys.columns. And down here, we’re going to wrap these various things in quote name as well.

Now, if this were like a big old complicated store procedure, there’s a pretty good chance that I would, um, have maybe like an inner variable that I would set to being like quote name, database name. But of course, uh, after like verifying it and like sys.databases or something. So just here for sort of brevity, I’m putting quote name around these, but like in real, like big code, I probably don’t want to like have quote name everywhere.

Cause I may forget at some point. So it’s much easier to set it in one place and then just have the object name you care about quoted. But now, of course, when we run all this and we get down to the print and the execute, this works just fine, right?

But there’s no data in the table, but we at least run the select successfully and we get back our column names here. And the dynamic SQL that gets printed out would, uh, completely, uh, uh, avoid any of the terrible errors that we saw before, because now we have square brackets around everything. Now, the nice thing about quote name is that, um, it will produce a valid output for anything that is a legal valid object name, uh, where you have to be careful as if things get longer than that.

But we’ll talk about that in a moment. But the reason why, um, square brackets do not work the way that quote, like the, as well as quote name works is because if you were to do something like this, right? Like, uh, you could like, if you were just to put your own square brackets and can concatenate those into a string at some point in the dynamic SQL, they, they are not, they, they, they do act as an object identifier, but they do not quote out additional square brackets.

So quote name would, would fix this, right? Right. Quote name would actually protect you would like double quote these things. So you would get valid results back, but, or rather it would, it would escape those things.

So you would get valid results back just adding in square brackets does not do that. So if we run this whole thing, we will see that, uh, we get valid results, but we actually executed SP who right here, right? We got all this dumb information back that no one ever understands and is very confusing, but over in the messages tab, this is what we ran, right?

We executed SP who, and then we dropped a table, right? And then when we try to select from that table at the end, we say invalid object name, right? And the reason for that is because just adding in square brackets will not escape any additional square brackets in here.

So this does not have the same power. If we were to do this and use quote name instead, right? So like, rather than say like square bracket plus this string plus square bracket, if we just say quote name, that same string, well, all of a sudden SQL Server is very, very happy to, uh, say, well, I’ve, I’ve never heard of the store procedure SP who dropped table.

All right. That’s not a thing. So like notice that when, uh, when I made this string, there was no double square bracket here, but when I use quote name, look what happens here.

We get double square brackets here. So quote name does some extra stuff that just rolling your own square brackets doesn’t do. Now you could totally, you know, do something on your own and be like, well, I’m just, if there’s a square bracket, I’m going to replace it with double square brackets to just use quote.

Right. And unless there’s some strange reason why you can’t use quote name, you’re just allergic to the letter Q. Maybe I don’t know.

I don’t know. Use quote name. Very, very simple. But, uh, just, you know, there are some cases where quote name will return a null if you, uh, make the string too long. So please do try to avoid that.

Um, if you, if you do have a situation where you need to, uh, square bracket something that is longer than quote name will return a string for, uh, where if it would just return a null, you have my blessing to use square brackets and replace square brackets within the string with double square brackets. Okay. Okay.

You can do that. But for most cases, you know, database schema, object table view, store procedure, anything like that, any, like, you know, system, like, uh, system identified object, uh, a mix of sys name and, uh, and quote name is totally fine. Um, there, you can use different things for quote name.

Uh, so like you are not just stuck with square brackets. Uh, you can use, uh, single quotes. You can use parentheses.

You can use curly brackets. You can use double quotes. You can use those funny little ticks, but you, you cannot use tildes. I’ve learned. Uh, sorry, Tilda Swinton. I do apologize. But if we run this, you will see that the word Eric has been, uh, bracketed, single quoted, parenthesized, curly bracketed, double quoted, single ticked, but, uh, we cannot tilde Eric.

Tilda and Eric were not meant to be. I will not be one of her husband’s sad to say. Uh, but one thing that is, uh, useful to talk about is that if you, if you like, so when I was talking earlier about how I will often, uh, in my store procedures, I will have a, uh, like an inner variable that I’ll set to be like quote name.

Um, something like, so like, let’s just say it’s a, uh, someone, I like, I let someone pass in a database name. I’ll hit sys.databases. Right.

And I’ll, I’ll get the database name from there where the database name parameter they pass in equals like the database name and sys.databases. And I’ll put quote name around that in that internal local variable. Where that can mess things up is if you have to do anything with it later to validate other metadata.

So now you have a database name with square brackets around it, but that your, your database name probably isn’t stored like that in sys.databases. Your schema names aren’t going to be stored like that in sys.schemas. Your table names aren’t going to be stored like that in sys.tables and so on and so on and so on.

So if you need to locate other data with, um, something that you have already quote names, uh, it’s, it’s really helpful to use parse name. So parse name is another built in function that will actually remove those square brackets. So you see up here, I have, uh, declared this local variable DBO.

And if I run this, you’ll see that, uh, just adding quote name again, we’ll double. Well, actually, well, you can actually see there’s three there, right? So we actually add in another square bracket quote name tries to do us a solid, but when we use parse name, we remove the square bracket.

So sometimes if you, in any of my procedures, you see like where some object name equals parse name, some local variable. It’s because that local variable has a quote name quoted identifier in it. And it’s messing up me looking up other metadata.

But of course, if we pair, uh, parse name and quote name, we will go back to just having single brackets. So there are all sorts of neat things you can do. And, um, last but not least, as I promised, I’m going to show you some, some, a couple of amusing things.

Now, one thing that, uh, I run into a lot and that I run into people sort of, uh, having issues with a lot is when, if you’re writing dynamic SQL, or, uh, you have your, whatever string you’re building is very, very long. So, uh, you might not be able to print it successfully in one go. It’s not a good time.

It’s not a good time. Because now you’re looking at like having to chunk print statements with like sub string or something, maybe write like a while loop to produce like a, like continuously printing thing until you run out of characters. That’s not a lot of fun.

Now I think generally for me, it’s a lot easier to, uh, produce like an XML cookie column where I can just click on it and get the full thing somewhere else. That’s, that makes my life a lot easier. So, uh, let’s pretend that I have, this is a very long string.

One way of achieving that is to use, uh, this processing instruction alias. And I just have an underscore in here to avoid like having any, like, like, like I could put anything between these, uh, these parentheses. I could put like query text something, but I just have an underscore in here to sort of eliminate lots of strange XML stuff.

But if I select processing and processing dash instruction with parentheses, and I, again, I just have the underscore in here for brevity. Uh, and I say for XML path, well, I get back a clicky column. All right.

And if I click this, I get the, that’s where the underscore comes in. But now I have this thing that I can, I could copy and paste out and do whatever I want with, you know, however it is. But what’s kind of funny is, uh, you see that it produced a column with this name, which is XML and then sort of a funny looking pseudo gooey looking thing. And what’s really funny is if you, uh, maybe skip the processing instruction thing.

And you just, uh, alias a column with that same name, you will get an XML clicky column. And this one doesn’t have any of the sort of festive XML bracketing on it. So maybe, maybe that’s more to your liking.

And what’s, what’s also very funny is, uh, I don’t know if you’ve ever used like set statistics XML on, but if you use set statistics XML on, it produces like an XML clickable for a query plan. And if you, the alias of set statistics XML on is Microsoft SQL Server, 2005 XML show plan. And if you alias a column with that name, you also get an XML clicky that you can click on and open, have open in a brand new tab for you.

So that’s my amusing long print, long printing tricks. All right. Perhaps I’ve gone too far.

Thank you for watching. Hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video, uh, where we will talk about, let’s see, what is it? Ah, choosing temporary objects.

Well, we’ve got some work ahead of us, don’t we? Still, I still have to talk people out of table variables and they still make all the same excuses. So I’m going to ride that train till it dies.

I guess I guess this is coming up on the 25th anniversary of people being wrong about table variables. So happy anniversary table variables. Happy anniversary.

All right. Goodbye. Bye. All right.