Alternate title: How do I tell how far I am on that command?
This little command (frequently with a WHERE clause) is a great way to tell how far along you are on any given command. Specifically, I’ve been running a bunch of DELETEs recently. I’ve got something like 5-50 million rows of data to delete and I’d like to know how far along I am. A common way to do this would be just a simple count.
SELECT COUNT(1) FROM TableName WHERE ToBeDeleted = 1;
Unfortunately, while the DELETE is running I’m not going to get a result. Blocking is a thing, which makes this one of the few times where NOLOCK is useful. I don’t need my results to be 100% accurate and I need to read some data while someone else is writing. So we run:
SELECT COUNT(1) FROM TableName WITH (NOLOCK) WHERE ToBeDeleted = 1;
And for years this is what I used. With lots of results like this:
And then some mental effort to add commas and I know I’ve got something like 23 million rows left. I’m tired, it’s a holiday, and mental effort is not my friend (James, Andrew, you didn’t see that last bit.) So I decided to try adding FORMAT.
SELECT FORMAT(COUNT(1),'N') FROM TableName WITH (NOLOCK) WHERE ToBeDeleted = 1;
I don’t know about y’all but I find that a LOT easier to read. And adding the FORMAT was not that hard. Yes, it added a decimal, but really, who cares?