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.

The Art Of The SQL Server Stored Procedure: What You Need To Know

For Your Eyes Only


I’ve been keeping a mental list of things of things I often have to adjust or fix in stored procedures I work on.

Some of them are a touch pedantic, but the majority of them are lessons and practices I’ve learned (often the hard way) over the years while working on things:

  • Performance tuning
  • Building processes
  • Fixing issues and errors

The thing is, I see the same mistakes over and over again. You probably do too, but you may not know they’re mistakes.

Judging by the code I see, most developers out there don’t.

No offense, it’s just… if all you do is begin a transaction and then try to commit it at the end, with no thought given to error handling or rolling back, what is the point of you?

If you do this, go ahead and tell me why in the comments. I’d love to know.

Anyway.

Table Of Contents


Here’s what I’ll be talking about over the next month or so.

  1. The Art Of The SQL Server Stored Procedure: What You Need To Know
  2. The Art Of The SQL Server Stored Procedure: ANSI/SET Options
  3. The Art Of The SQL Server Stored Procedure: How To Leave Good Comments
  4. The Art Of The SQL Server Stored Procedure: Formatting Code
  5. The Art Of The SQL Server Stored Procedure: Error Handling
  6. The Art Of The SQL Server Stored Procedure: Debugging
  7. The Art Of The SQL Server Stored Procedure: Batching Modifications
  8. The Art Of The SQL Server Stored Procedure: Transactions
  9. The Art Of The SQL Server Stored Procedure: Locking Hints
  10. The Art Of The SQL Server Stored Procedure: Isolation Levels
  11. The Art Of The SQL Server Stored Procedure: Data Types
  12. The Art Of The SQL Server Stored Procedure: Conditional Logic
  13. The Art Of The SQL Server Stored Procedure: Local Variables
  14. The Art Of The SQL Server Stored Procedure: Wrapper Stored Procedures
  15. The Art Of The SQL Server Stored Procedure: Dynamic SQL
  16. The Art Of The SQL Server Stored Procedure: Temporary Objects
  17. The Art Of The SQL Server Stored Procedure: Parameter Sniffing
  18. The Art Of The SQL Server Stored Procedure: Dynamic Searches
  19. The Art Of The SQL Server Stored Procedure: Pagination
  20. The Art Of The SQL Server Stored Procedure: Cursors And Loops

The list might change a little, but it’s the basic outline of what I care to talk about.

Some posts might be shorter than others, because there’s not a lot to say about certain things beyond “this is right, and this is why”.

Hopefully these lessons start to sink in.

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.

Optimizing SQL Server Query Plans With Eager Index Spools In Them

Optimizing SQL Server Query Plans With Eager Index Spools In Them


 

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.

Optimizing SQL Server Query Plans With Row Count Spools In Them

Optimizing SQL Server Query Plans With Row Count Spools In Them


 

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.

Optimizing SQL Server Query Plans With Table Spools In Them

Optimizing SQL Server Query Plans With Table Spools In Them


 

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.

Optimizing Complex SQL Server Queries By Using Batch Mode

Optimizing Complex SQL Server Queries By Using Batch Mode


 

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.

Optimizing Common Table Expression Queries In SQL Server

Optimizing Common Table Expression Queries In SQL Server


 

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.

Optimizing Complex SQL Server Queries

Optimizing Complex SQL Server Queries


 

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.

Methods For Optimizing Paging Queries In SQL Server

Methods For Optimizing Paging Queries In SQL Server


 

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.

Optimizing Paging Queries With Indexes In SQL Server

Optimizing Paging Queries With Indexes In SQL Server


 

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.