The best part of my job is dealing with interesting questions related to SQL Server. Just earlier I published a video and I received lots of interesting questions. Let me share them with you. However, before you read today’s COUNT(*) and Index Frequently Asked Questions I suggest you watch the video COUNT(*) and Index – SQL in Sixty Seconds #175.
I hope you have enjoyed COUNT(*) and Index Frequently Asked Questions.
Q1: Should I use NoLock with Count(*)?
A1: You can for sure use NOLOCK however, I personally do not suggest you use COUNT(*) if you just counting rows for the entire table. Here is another video I suggest you should watch for it. Fastest Way to Retrieve Rowcount for a Table – SQL in Sixty Seconds #096
Q2: I always use COUNT(1) instead of COUNT(*), will that give better performance?
Q2: I have read it somewhere in the past that theoretically, COUNT(1) should get you better performance. However, I have run so many different tests on so many different environments, I have yet to see the difference in actual performance when I use COUNT(1) or COUNT(*). However, if you feel better by using COUNT(1), go ahead, it goes not hurt to use it. However, I will not create a new project where I will convert all of my COUNT(*) to COUNT(1).
Q3: SELECT * gives error when executed alone, but SELECT COUNT (*) does not. Why?
A3: Actually, I have written a whole blog post about this topic and you should definitely read that one Solution – Puzzle – SELECT * vs SELECT COUNT(*). I am confident that you will like this blog post.
Well, that’s it for today. If you have any further question, do reach out to me on youtube or Twitter. I will be happy to answer all your questions.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
