ll About SQL Server Stored Procedures: Table Variables for Logging
Video Summary
In this video, I delve into the nuances of using table variables in SQL Server stored procedures, particularly focusing on their role in logging errors and handling rollbacks. I explore how table variables can be useful for tracking progress within transactions, especially when you need to maintain a record even if an error occurs or the transaction is rolled back. However, I also highlight that while they are handy for certain scenarios, table variables might not always be the best choice for logging data, especially in complex operations involving large datasets. The video includes practical examples and discussions on when it’s appropriate to use table variables versus other methods, ensuring you make informed decisions about their implementation.
Full Transcript
Erik Darling here with Darling Data. And in today’s exciting, outstanding, completely AI-free video, we’re going to talk about store procedures, still. Carry on talking about SQL Server store procedures. But we’re going to talk specifically about using table variables in the context of logging things about your store procedure in the face of errors and rollbacks. Because for some reason that I cannot explain, every time you are talking about the performance differences between temp tables and table variables, and you’re like saying, hey, if we change this table variable to a temp table, we can make this procedure go faster, someone will decide to chime in with the time in with the time in with the time in with, but table variables, they survive errors and rollbacks. And you’re like, so what? Except with more words, so instead of a long so, inject some colorful language into the so what? Because completely irrelevant to the topic at hand. We’re talking about, when we’re talking about performance, we care about performance, we don’t care about errors and rollbacks. It’s like we’re looking at a reporting store procedure that doesn’t even do any, there’s not a transaction in here. There’s not an error, there’s not a rollback, there’s not a commit. There’s nothing. Why? Why do you bring this up? Why? Why did it, why do you decide to inject the conversation with this meaningless knowledge? You just, you just need to act like you learned something at some point? I don’t understand your point of view on this. Anyway, before we do that, let’s talk about you and me and the birds and the bees. So if you like this channel content just enough to spend four bucks a month, on it, you can use the link in the video description to join the channel as a member. If you do not, if you have not quite reached, not quite reached consensus or formed an opinion on the channel, you can do other stuff. In the meantime, you can like, you can comment, you can subscribe. And if you feel so inclined, you can even ask me questions at this link, which is also done in the video description, that I will answer on my office hours episodes.
When I answer five of your questions at a time and we have fun. If you need help with SQL Server, this is an unruly beast that needs taming that needs performancing. You can, of course, pay me money to take care of them. Health checks, performance analysis, hands on query index server tuning, you name it, responding to performance emergencies and training your developers so that you do not run into as many performance emergencies. is all the name of my game and as always, my rates are reasonable. Promise. Take a look around. If you would like to get some training from me in lieu of perhaps other things, if you would like some real high value content, you can get all 24 hours of my training for 75% off.
That is around 150 US dollars and that comes to you for the rest of your life. Link to do all that stuff again down in the video description. Upcoming events, we still have SQL Saturday, New York City taking place in the Microsoft offices in lovely Times Square in Manhattan.
It would be a great time. You can come, you can take, we can take selfies. I don’t know, we can do whatever cool fun stuff people are still allowed to do at conferences these days. Barring any, of course, code of conduct breaches. We don’t want to do that.
We want to have a nice family friendly time at SQL Saturday. With that out of the way though, let’s talk about table variables and logging stuff. So here is pseudocode.
And you know what? You just reminded me that I need to fix a small typo in here before I keep talking. So we’re going to do that and we’re going to pretend that didn’t happen and then we’re going to look at the rest of this stuff. So let’s say that this is our pseudocode and like reasonably intelligent people, we are going to, within the context of our store procedure, set no count in exact abort on.
Then we are going to begin to try. Every day I begin to try. Sometimes things happen along the way that interrupt that. And then we are going to end try and, well, you know, there are a lot of reasons to end trying, I’ll tell you that.
But then in between all that, we have a begin transaction and a commit transaction. Now, of course, you don’t want to really need to do this for a single query. If you have a select or an update or a delete or an insert, you don’t really need this, right?
Because SQL Server is going to be working in auto commit mode where that query will happen within its own transaction anyway. But if you have a group or if you have a flock or a murder of transactions, of queries rather, that you need to put in a single transaction because they all need to complete or not complete as one group. Like Wemmings, they either need to like make it to the top of the hill or fly off the cliff together.
Then you would want to do this. If within this transaction, you want to figure out where along the way you have done things and how long they took and how many rows are affected and things like that, then you can log that stuff to a table variable. And if you hit an error or in like, you know, you like, you know, you hit an error and all this stuff rolls back, then you can still put that data from that table variable into a logging table that you can review later.
You don’t just have to return it out to like whatever client is running down in the commit transaction or rather down after the commit transaction in the begin catch block. We will, of course, do this. Say if trend count is greater than zero, we’re going to roll stuff back and then we’ll insert into our logging table whatever data we have logged in our table variable that has survived the rollback. Because remember, we don’t roll back things that got inserted or updated or deleted from table variables here.
That table variable will be alive until we do this. We can put that into our logging table in the catch block and then review that actual logging table later. You can put all sorts of like good information in here, you know, proc ID, error number, error line, error message, all that other stuff.
There’s lots of things that you can put in there that will make life somewhat easier for you when troubleshooting a problem with a procedure. One thing that I’ve not really come up with a good way to manage is like logging the data that was in there. Like if it’s a small amount of data, it’s not a big deal.
But if you’re doing this for like ETL and you need to move millions of rows around table variables, table variables are just going to hurt more than they help. So probably don’t do that. The second way that you could do this is if you have a store procedure that does something like this where, and again, you don’t need to put like, like wrapping single queries and this stuff is not really useful.
But if you have like groups again, like just like the last one where every query in the procedure had to go and like, you know, ride or die. If you have groups of queries, maybe not like the whole list of queries, but like, you know, groups of queries where along the way, like in all of these do stuff blocks need to ride or die together. Let’s say there’s a, there’s like two or more queries in here that need to complete and, you know, do that stuff.
And you need, and you want to log progress for each query in here, then a table variable would still be your friend. Because if anything happens within any of these down here in the catch block, we can still do this. If, and this is a big, if you are only logging stuff around each transaction, meaning like, let’s say that like before each begin transaction and after each commit transaction, you’re like set, you know, current step.
Equals, you know, transaction one. And then after the commit, you’re like set current transaction equals step two. Then the table variable becomes less useful because it’s not logging anything within the begin transaction commit.
So there’s like the way of thinking about this is anything that’s happening within a transaction could be useful to log to a table variable. If you’re going to do something with it, otherwise it’s probably not. Otherwise you could probably either don’t need a table variable or you just don’t need to be logging stuff, I guess.
This is the easy way, easy way of saying that. But the key thing here is that if you’re not, if you’re not logging, if you’re not doing stuff within each of those transactions, then you can still do the logging down here if you want. Right.
Like that’s easy enough. You just pull out whatever data came along with that. But, you know, for this part, if you are logging stuff about each step between the begin transaction commit, then you would want to put the stuff into the logging table from the logging table variable. And I am not sad to say that this is the very end of where table variables are useful for logging errors, rollbacks and data about things.
At least in my experience, practically speaking, you could probably overly engineer other, you know, solutions to do things like this. But the table variable will not be necessarily useful or even required, will not be a necessity for doing that. That would be your choice to use a table variable when you just didn’t need to.
You could have just used regular variables and you could have just logged very simple, easy information about the error you hit, the procedure you hit the error in, all that other stuff where you didn’t need to put that in the table variable. You just chose to do that for some weird reason, because you are a white knight for table variables and you, you just need to let them, you just need to make them shine. Right? You need to force them to shine like a, like a boy band, just manufacture them being good at anything.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will not manufacture use cases for table variables. I hope that you will only use them when they are necessary, required and pertinent to the solution at hand.
And yeah, in the next video, we will talk about the thing that really matters when you are choosing temporary. And that is, of course, performance. We’re going to go over some material that I covered in a video somewhat recently that makes sense in this context as well. So I will see you then. And until then, I hope, I hope you are smiling.
Hope you are a happy camper, just like me.
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.