SELECT FORMAT(COUNT(1),’N’) FROM TableName WITH (NOLOCK);
9December 26, 2023 by Kenneth Fisher
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?
SQL Studies 
p.s. For a “onesy” like this, FORMAT is ok (Although I just flat avoid it to keep from developing a bad habit). Just don’t use it for lots of rows. See the following for why.
It would also help if I included the link to have a look at…
https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format
Eh… not sure why my first coded post didn’t show up. Add just one more character to get rid of the decimal places….
SELECT FORMAT(COUNT(1),’N0′) FROM TableName WITH (NOLOCK) WHERE ToBeDeleted = 1;
Nice! Thanks!
[…] Kenneth Fisher has a way: […]
@Ken,
I submitted another post yesterday. I had a test data generator, some code, some and some run results. It also had a link to another article. It hasn’t shown up yet and I wonder if Akismet may have eaten it.
If you want you could email it to me and I can try to get it into the comment. If not I can just add it at the bottom of the post.
Also simple enough to put something like COUNT(1)/1000 or COUNT(1)/1000000 if you only want a rough count of a large number.
For what it’s worth, you can modify the number of decimal places displayed by including an integer after the N such as this:
SELECT FORMAT(COUNT(1),’N0’) FROM TableName WITH (NOLOCK)
to display 23,521,401
or
SELECT FORMAT(COUNT(1),’N4’) FROM TableName WITH (NOLOCK)
to display 23,521,401.0000