All About SQL Server Stored Procedures: ANSI Settings

All About SQL Server Stored Procedures: ANSI Settings

---

Video Summary

In this video, I dive into the nuances of ANSI settings within SQL Server stored procedures, highlighting why these settings are crucial for maintaining database integrity and performance. I discuss how setting up these options correctly can prevent errors when working with index views, computed columns, and filtered indexes, ensuring that your queries match the correct expressions in the optimizer’s plan cache. Additionally, I explore other important settings like implicit transactions, row count suppression, and exact abort, providing practical advice on when to use them and why they are beneficial for analysis procedures.

Full Transcript

Erik Darling here with Darling Neda. I was talking to my friend Batts about what we should do next. Aside from Batts’ fantastic idea to take some office hours style questions, Batts also said, you know what, Erik, you spend a lot of time fixing store procedures that have all sorts of problems, all sorts of problems. Not just performance problems, structural problems, integrity problems, just the things that people do within store procedures are completely unhinged. I find developers untrustworthy to the point where I sometimes just wish that they had used an ORM for that. It’s that bad. It’s that bad. People are not kind to their databases. It’s amazing. Amazing some of the things you see when you get into the world of consulting. If you are the type of DBA or developer who has only ever had full-time employee positions and you’ve worked at relatively respectable organizations, you might see some of the things that I talk about and think, that’s the most unrealistic preposterous thing that I have ever heard that I have ever heard. No one would actually do that. Lo and behold, every time I think it can’t get worse, it gets worse. So, in this video, we’re going to cover the very beginning of the store procedure, the ANSI settings. ANSI settings, of course, have a couple interesting outcomes in SQL Server. A lot of the ANSI settings to me are funny because a lot of them, even though they will still have these impacts, don’t actually change anything with the queries. Some of them, like a Rith abort or numeric round abort, don’t even actually do anything really. They just end up with different plan cache entries and you get errors if you do stuff with index view, computed columns, filtered indexes. You try to make modifications to a table and you have the wrong ANSI settings. You get errors.

If you have the wrong ANSI settings and you query a table that does the index view, computed column, filtered index thing, you don’t get the matching for it. I guess you get different plan cache entries for different ANSI settings, which makes reproducing performance problems a lot harder. But some of them just don’t even do anything. Some of them only do stuff if you have the ANSI warning settings set to something specific.

So, a lot of this stuff is just stupid and there for backwards compatibility, but this is the kind of stuff that builds up when your code base is as old, wretchedly old, decrepit as SQL Server is. Not that I don’t love it for all its intricacies, but anyway, this is where we’re at. But now Bats is saying it’s time to make some money.

That’s not what Batsmuro actually sounds like. So, if you like this content, if you like this channel, if perhaps you just hit mute and stare at me while I wander around on the screen a little bit, and you want to support my work here to bring you this extra high quality SQL Server content, you can sign up for a membership. Something that needs to be said is that the video description contains helpful links for everything that I talk about.

Whether it’s signing up for a membership, asking me questions during office hours, of course, the liking, commenting, and subscribing are all separate button functions in the YouTube interface, which you are free to click. Just make sure you only click the subscribe button once, because if you hit the subscribe button more than once, I think you unsubscribe. We don’t want.

We want more subscribers. It recently passed the 6,000 subscriber mark. Also fairly recently passed the 50 member mark for the channel. So, thank you to all, I think it’s 53 or so people, for your continued support of four plus dollars a month to my antics. If you would like to pay me more money because you need more help with SQL Server, you can do that.

I am a consultant. That is how I make the majority of my income. I do not make it from YouTube, thankfully.

Otherwise, we would be in quite a destitute situation here at Darling Data. But if you need any of this stuff done to your SQL Server, health checks, performance analysis, hands-on tuning, performance emergencies, or developer training in order to prevent further SQL Server performance emergencies, I am the best consultant in the world outside of New Zealand for all of these matters.

Do not get suckered into paying anyone else to do this who will make things worse, probably. If you want some very high quality, very low cost SQL Server training content, boy do I have a lot of streaming stuff. You can get 75% off of it.

That brings the total cost down to just about 150 US dollars. And again, helpfully assembled links are in the video description. I suggest if you haven’t yet clicked on that, you click on it now.

If you’ve already clicked on it, send it to a friend. You might have friends who need this help too. And by gosh, the more of us who get better at this, the better off we all are. Upcoming events.

Saturday, May 10th, 2025. SQL Saturday, New York City is back in action. It’ll be taking place at the Microsoft offices in Times Square. I think there’s a Dave and Buster’s nearby, so if you get bored, you can just duck out.

Or you can go make friends at Port Authority, bus station, whatever. Whatever floats your boat. There’s vibrant communities in both places.

Also recently announced, I haven’t added the information here yet, but I will. There is a performance tuning pre-con on May the 9th by Andreas Volter, I think. I think that’s how you pronounce it.

I’m just going to say it like that because it’s pleasing to me to say it like that. But tickets are on sale for that as well. I’ll get the necessary background information added in here for the next video. So I messed up and didn’t do it for this video.

So sorry about that, but the information is now out there in the world. But with that out of the way, let’s talk about store procedure stuff. Now, this is the proposed list of topics that I’m going to be covering during these videos.

I’m not going to read them all because, by God, we have things to talk about. But this is the stuff that I’m going to be covering. Most of it is already written.

Some of it’s going to need some additional tinkering and tweaking. But what the hell? I don’t… It’s not like I have to do… It’s not like I do this live. Because there are a lot of times when I’m recording stuff and something happens.

And I don’t know, I have to stop and curse at things for a while or, I don’t know, weird noises sometimes. Sometimes there are ghosts. But this is all the stuff that I’m going to be talking about in there.

Some of these topics individually I have talked about in other videos. But it’s sometimes helpful to have them as part of a larger series of things. So that people can have everything all kind of grouped together in a helpful thematic playlist.

If you’re going to make a playlist, it should have a theme to it, right? You don’t want random jumble of stuff in there. Not a good way for a playlist to be handled.

So we can get rid of this thing now. And let’s talk a little bit about ANSI set options. Now, like I said before, a lot of these things don’t really do much of anything, right? But there’s a kind of specific way that you want to set up your ANSI set options for a store procedure to make sure that all of the correct options are in place for when that procedure runs.

ORM connection strings often are not exactly the way they should be to be compatible with index views, computed columns, and filtered indexes. I’ve mentioned that a few times.

But there are a few settings that are stored with the procedure definition. And then there are a bunch that are not. So when I’m setting ANSI settings for a procedure, I need to make sure that all these things are set this way so that when I want to make sure that I don’t get errors, when I am modifying tables that have indexed views, computed columns, and filtered indexes, I don’t get errors when I try to modify those.

And that when I query tables that have any of those things, the optimizer is able to match the expressions in my queries to those different facilities.

There are a few other things that I like to set up in SOAR procedures as well. Setting implicit transactions to OFF is a little bit of a weird one, but comes in handy if you are calling store procedures from the JDBC driver or the PyODBC driver, the Python driver, connecting to SQL Server, because both of those use implicit transactions by default.

And you often don’t want that to happen. Now, granted, a lot of the store procedures that I write from scratch are my analysis type store procedures where I go in and I run stuff. So I don’t often have to worry about this, but I do try to set them up to be good examples.

Within my store procedures, purely for me, because there are times when I need to performance troubleshoot or debug or work with my store procedures in different ways, I usually put these in there.

You may not care about either of these for your situation. That’s totally fine. They’re worth talking about because I tend to use them. And then within a store procedure, I usually set both of these things to ON and ONE GO because I don’t really want to get row counts from every query that runs.

And I want to make sure that if one of my queries hits some sort of issue and fails that all of the queries stop happening. Now, exact abort ON is actually something that I don’t use in my analysis procedures because it’s not critical if they run through to the end, if there’s an error or not.

There are often transient errors that might happen at one point or another. And I don’t want to not get a result, like any result set back from them because I hit an error during one point in processing. So for me, this is a silly example.

In my human events block viewer procedure, sometimes a query will fail trying to look up agent job information or something like that. But the rest of the results are fine.

And the agent job information isn’t like super critical to me getting like, like to me figuring other stuff out. So I’ll just like, like, I don’t care if that fails.

I still want to get all the rest of the stuff back. So exact abort, you know, if you’re writing important production quality code, you most likely going to want that one on. But if you’re just writing some, like, you know, if it’s just a single select query or something, you probably don’t need that.

So before we go any further, let’s talk about the three settings that are stored, like persisted with the procedure in SQL Server. So since I already have all this stuff as I want it, when you look at the sys.sql modules, dynamic management view in SQL Server, if you look at what’s stored in there, you’ll see ANSI nulls quoted identifier is recompiled.

Now, this is only when you have a store procedure level recompile up here. If you just have option recompile on a query, like down here, like it’s not going to say that the store procedure is made with recompile.

That’s only when you create it with the recompile at the like crater alter with recompile, just adding an option recompile. Even if there’s one query in there, we’ll not have the store procedure marked that way.

Cause you could, you know, take that out something, coat it out. I don’t know. I mean, granted you could coat this out too, but then it would just disappear from down here.

So these settings are stored with the procedure definition. So these, these ones you tend to worry about a bit less, but then within the store procedure.

So like if you like, you know, just say set these for the connection and then create or alter your procedure, like those get stored in the sys.sql modules thing. The SQL Server is like, this is created with these settings.

Then there are the runtime settings that we care about for making sure that we get index views, filtered indexes and computed columns used correctly. And so that we don’t hit errors.

If we try to modify tables that are attached to any of those things. So anti-padding, anti-warnings, erythaboard, concatenal yields, and all of those all have to be on and numeric, numeric rounderboard has to be set to off.

And then, you know, like I said, JDBC and Python drivers, PyODBC, I guess, both use implicit transactions by default. So if you’re using either of those drivers and you’re calling store procedures, you probably want to set this off in the store procedure.

If you’re writing, you know, queries that, well, honestly, these are just for me and my analysis procedures.

There’s really no reason for you to put these in your store procedures, especially because at some point you might, like turn on query plans and want to like get the actual execution plans for your procedure, which is completely fine.

A normal thing to have to do for me, why I put these in my analysis procedures is because sometimes I’ll leave actual execution plans turned on by accident and go to run my procedure. I’ll be like, why is this taking so long?

And, uh, you know, that’s not a good time. So there’s a good reason for that. For me, there may not be a good reason for these for you. And then of course, uh, set no count on is a fun one, right?

We do it. Cause we don’t want row counts from every single thing that runs. And, uh, exact abort is a good one to turn on so that you don’t have like, like, like say you have a store procedure that runs and it does like an insert and then an update and then a delete. And let’s say the insert and the update run fine, but the delete doesn’t run.

So let’s say the delete hits an error and you don’t want the insert and the update to have completed and committed and like leave that data in there. You want those to roll back too, because something that you should have deleted maybe from that or from something else, uh, it will still be lingering around.

So you need to want to make sure that all three of those roll back so that you don’t have like dangling data sitting around in your table. So, uh, that’s all that.

Now, when I was writing this, I came across kind of a funny thing, uh, with the ANSI padding setting that, uh, that caught me off guard and was not in the documentation either for the ANSI padding, ANSI padding, ANSI padding.

I’m just gonna, good ANSI, good ANSI, uh, for the ANSI padding setting, uh, that I didn’t see documented anywhere. And that is that ANSI padding behaves differently with, um, uh, table variables and temp tables, uh, and that table variables do not respect your ANSI padding settings at all.

So, um, what I’ve done is I’ve, I’ve actually stolen some code from the documentation because I was like sanity checking myself on all this and being like, this is, this is too weird.

This, this can’t actually be this way. Uh, and, uh, let’s see, uh, I, just to compare what happens with a, with temp tables and with table variables, with ANSI, the ANSI padding setting changed to on or off.

Uh, I’m just gonna run this whole thing at once. I don’t think there are any errors involved, which is nice. Uh, uh, and so this is what happens. Uh, now with the table variable, you can see up here that, um, no matter what I do, the results, oops, the results are precisely the same between these, right?

And this is testing with the tape with ANSI padding on and off for the table variable. ANSI padding behaves in the exact same way for both of these. If you look down here with the temp table one, you’ll notice that with ANSI padding off, things do look different, right?

Like just to compare the differences down here with ANSI padding on, uh, and I, and on, I get trailing blanks, right? Uh, I get, right, like, like trailing blanks when I should.

And this, uh, this adds the zeros in, um, rather than, uh, not have them in for the bar binary column. But when I said ANSI padding off for the temp table, look what happens.

Much different, right? Like this, this row, uh, this row here looks a lot different from this row here, right? So like ANSI padding has an effect, has an effect on temp tables.

ANSI padding does not have an effect on table variables. So this is stuff that, you know, probably doesn’t make that much of a difference in the world because ANSI padding is a fairly rare thing to see people like messing with. But, you know, uh, just thought it was kind of a little bit of sort of interesting SQL Server trivia.

Probably the, like, I don’t, I don’t have a good reason why this is the case, why table variables don’t respect ANSI padding when temp tables do. The closest I got to an explanation was that table variables were added, uh, in SQL Server 2000 and did not come with the backwards compatibility for the ANSI padding setting, which was already like deprecated or something by then.

So that’s probably why, but, um, you know, that’s, that’s, that’s as far as I got with it. Anyway, uh, I think that brings us to is about as much as we can talk about for the ANSI settings in SQL Server. Uh, we’re going to talk about, I don’t know, whatever the next topic was next.

I think it was, I think it was commenting or code formatting, one of those two, but we’re going to talk about that next. So, uh, I hope you enjoyed yourselves. I hope you learned something. I hope that you will be careful and judicious with your ANSI settings when you’re creating and executing store procedures. And, uh, yeah, I don’t know.

I think that’s about the end of that. Um, cool. Yeah. All right. Well, we’re going to, we’re going to close this one out. We’re going to say goodbye. Uh, actually, you know what?

I, I realized I forgot, I forgot to sort of tell you that the reason why I set these at, at runtime here, there’s probably an important, important factor with this is that, uh, coming back to like what executes a store procedure.

If it’s an agent job, if it’s an ORM that doesn’t use the correct connection string settings, you can correct them within the store procedure and override the, whatever the connections, uh, context was from the ORM.

So that’s why I do that there. In case I didn’t say that before, I’m saying it now. Anyway. All right, cool. I’m going to leave now. That that’s, that’s enough. All right.

Goodbye.

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.