Blog

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.  ANSI/SET options
  2.  Commenting
  3.  Formatting
  4.  Error Handling
  5.  Debugging
  6.  Batching Modifications
  7.  Transactions
  8.  Isolation Levels
  9.  Locking Hints
  10.  Parameter data types
  11.  Table valued parameters/Passing lists
  12.  Conditional logic
  13.  Local variables
  14.  Wrapper stored procedures
  15.  Dynamic sql
  16.  Temporary objects
  17.  Parameter sniffing
  18.  Dynamic searches
  19.  Pagination
  20.  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.

How To Fix Performance Problems With Optional Parameters In SQL Server Stored Procedures Part 2

How To Fix Performance Problems With Optional Parameters In SQL Server Stored Procedures Part 2


 

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.