The Art Of The SQL Server Stored Procedure: ANSI/SET Options

Top Down

---

I start off all of my stored procedures with the following block:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET STATISTICS TIME, IO OFF;
GO

This aligns my stored procedures with the necessary settings to accomplish a couple things:

• Allow the optimizer to use indexed views, filtered indexes, and computed columns
• Avoid errors when modifying tables involved with indexed views, filtered indexes, and computed columns

Many ORMs mess up with ARITHABORT, and let’s be honest, most of them time ORMs aren’t calling stored procedures.

If you’ve spent some time right-clicking around in SSMS at all, you might have seen how missing index requests and various module templates are generated.

They all start off with enabling ANSI_NULLS and QUOTED_IDENTIFIER, but there are some others that it makes total sense to include.

That’s why I have that list up there.

AS BEGIN

---

Inside of your stored procedures, you may want to do some other stuff, depending on how you’ll use it.

Inside of a real, production-ready stored procedure, you’ll probably want to do this:

SET NOCOUNT, XACT_ABORT ON;

If your stored procedure are being called by a Java or Python app, you’ll probably want this too:

SET IMPLICIT_TRANSACTIONS OFF;

Because by default, they both use implicit transactions, and you probably don’t want those happening across all the queries in a stored procefure.

That can cause a whole hell of a lot of blocking.

NOCOUNT is of course a good idea to not send additional results back.

XACT_ABORT is a great idea for stored procedures with multiple queries that modify data, because it allows you to roll all of the changes they made back without a TRY/CATCH block surrounding the whole ordeal.

If your query is just reading a bunch of data from different places and there’s nothing to roll back, XACT_ABORT matters not a bit.

Otherwise, use it, because it is wise to do so.

Locally Owned

---

If you’re writing a stored procedure (like most of my troubleshooting procedures) where you don’t want to:

• Fail completely if you hit one error
• Get blocked by all the transient stuff that locks system views and functions
• Accidentally get query plans for your troubleshooting procedures

You’ll probably want to do something like this:

SET STATISTICS XML OFF;
SET NOCOUNT ON;
SET XACT_ABORT OFF;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

That’s what I do inside all of my admin procedures to avoid those things.

Of course, you should not use some of those if you care about:

• Easily troubleshooting query performance (getting actual execution plans in SSMS)
• Leaving some changes in place when another part of the procedure fails (XACT_ABORT)
• Not returning data from potentially dirty reads (READ UNCOMMITTED)

SET NOCOUNT on is still a generally wise idea, regardless of the intent or application of the code.

Deal with it.

Exercise

---

I’ve been bitten by a lot of different weird things by not doing this stuff in the past.

At one point I had suggested a really helpful filtered index to a client that used Cold Fusion as a front end, which used some weird driver (Adobe, maybe?) that used a bunch of bad ANSI settings.

Implementing the index immediately caused a bunch of queries coming from there to start failing. It was an easy fix — rebuild the index without the filter — tada!

But the better fix was to move the queries to a stored procedure that requested the correct SET options, and re-filter the index.

Consulting life teaches you a lot of weird lessons, probably the first is to be paranoid of everything, and never trust that even the largest companies are doing anything correctly.

In tomorrow’s post, we’ll talk about one of my favorite subjects: helpful and professional (ha ha ha) code comments.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.