Press "Enter" to skip to content

Category: Stored Procedures

Isolation Levels and Stored Procedures

Published 2023-12-21 by Kevin Feasel

Erik Darling goes into isolation:

I’ve talked about isolation levels a bit lately because I need you all to understand that no isolation level is perfect, and that most everyone is completely wrong about how they really work.

For a very high percentage of workloads, Read Committed Snapshot isolation is the best choice. Why?

Read on for that answer. I think Erik is right about people misunderstanding how the different isolation levels work, as well as the root cause of not having a great place to try it out. You can build out demos of how different transaction isolation levels will work but some of the nuanced operations can be hard for one person with a couple new query tabs open to emulate.

Comments closed

Debugging Stored Procedures

Published 2023-12-13 by Kevin Feasel

Erik Darling shares some tips:

Debugging, like error handling, is a design choice that is fairly easy to make at the outset of writing a stored procedure, and is usually a lot easier to get in there if you do it from the get-go.

The number of times I’ve had to go back and add debugging into something only to introduce debugging bugs is actually a bit tragic.

If you’ve never brought down a system with your monitoring process or introduced bugs via debugging code, you’re not living life to its fullest.

Comments closed

Error Handling in T-SQL Stored Procedures

Published 2023-12-12 by Kevin Feasel

Erik Darling intimates that some of our code might occasionally have errors or might experience circumstances in which not everything is in perfect alignment:

Okay, look, the best post about this is, of course, by Erland Sommarskog: Error and Transaction Handling in SQL Server

Just like Erland, it has three parts and three appendices. If you want to learn how to do a whole lot of things right, give yourself a couple days to read through and digest all that.

What I’m here to talk about is some of the stuff you should think about before implementing any error handling at all.

I agree with most of Erik’s opinion here. My very mild disagreement is that I’ll still protect against things like invalid parameters or logic errors (start date before end date) in the stored procedure. I do that for three reasons:

  • Defense in depth isn’t just a security principle–it’s also a code practices principle.
  • The app gets things wrong, too. Sometimes, the app dev accidentally sends parameters in the wrong order, and it’s better to get an error early on in development versus thinking everything works because the procedure called successfully and ship it.
  • Even if “the” app correctly handles inputs, there’s always a chance some other app or process will call this stored procedure and it might not have the same error handling code built in.
Comments closed

Formatting Your Stored Procedure Code

Published 2023-12-11 by Kevin Feasel

Erik Darling takes a tour of the land mine garden:

When you think about formatting code, you probably think primarily of organizing a single query so that it’s inoffensive to civilized society.

But you should also apply this to your code as a whole, too. People who use words wrong will call this “holistic”.

I won’t get too deep into level of agreement here (probably about 60-70% of Erik’s list I can agree on), but I do argue that the best standards are the ones your team agrees on. It’s frustrating seeing hairball messes of T-SQL. Especially when developers’ non-SQL code looks a lot neater.

Comments closed

Leaving Good Comments in a Stored Procedure

Published 2023-12-07 by Kevin Feasel

Erik Darling comments on your comments:

Possibly the least helpful, but most humorous, way of leaving comments, is a large block of green text up at the top of a module.

There are all sorts of helpful insights buried in those comments to help me as a consultant understand my audience.

But… 

I agree with a lot of where Erik is going with his thoughts. The area where we probably have some daylight is that I’d rather limit comments to statements of why rather than what. Sure, when I’m pseudo-coding out a procedure, I’ll have a bunch of little “do this thing here” types of comments, but I remove those as I build out the code. Instead, explain why you’re doing something if it isn’t patently obvious, if you rewrote a query in a more complicated to read fashion because it performs much better, that kind of thing.

But in fairness, as long as your comments actually reflect the code, it’s really hard to say any code base is ever over-commented. It’s way easier to go the opposite direction.

Comments closed

ANSI and SET Options in Stored Procedures

Published 2023-12-06 by Kevin Feasel

Erik Darling starts a new series:

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

Click through to see what Erik sets by default. It’s a good list, though a bit more than I think to do. Probably because I haven’t been burned enough yet.

Comments closed

Thoughts on Testing Stored Procedures

Published 2023-09-14 by Kevin Feasel

Erik Darling has a plan:

While most of it isn’t a security concern, though it may be if you’re using Row Level Security, Dynamic Data Masking, or Encrypted Columns, you should try executing it as other users to make sure access is correct.

When I’m writing stored procedures for myself or for clients, here’s what I do.

Click through for Erik’s guidance. The premise is couched in security testing, though much of this is functionality and performance testing Regardless, it’s a good plan.

Comments closed

Debugging SQL Server Stored Procedures

Published 2023-09-06 by Kevin Feasel

Erik Darling lays out a good opinion:

This can really save your hide when you hit a reproducible issue. Proper error handling is part of proper debugging, too.

I generally detest PowerShell, but I love that some commands have a -WhatIf parameter to test what would happen. For normal SQL Server queries, you don’t get that.

But if you’re writing dynamic SQL that may execute database commands (backup, checkdb, settings changes, etc.), you should also include a parameter that prevents dynamic SQL execution so you can debug without worrying about wreaking havoc.

Read the whole thing. I might handle the specific mechanisms of debugging slightly differently, but Erik’s packed this post full of good advice.

Comments closed

CREATE OR ALTER and sp_ Procedures

Published 2023-08-28 by Kevin Feasel

Louis Davidson hits an edge case:

It isn’t that such objects are to be completely avoided, it is that they are ONLY to be used when you need the special qualities. Ola Hallengren’s backup solution creates a dbo.sp_BackupServer procedure so you can run the backup command from any database.

But if you don’t need the special properties of the sp_procedure, they are bad for the reasons Aaron stated, the reason I stumbled upon today being just a special subset. In this case CREATE OR ALTER behaves differently than CREATE in a way that was really confusing to me as I was working on a piece of code today.

Read on for Louis’s tale of woe and confusion, but mostly confusion.

Comments closed