SQL Server Interview: Which Index uses by COUNT(*) statement

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing an interesting SQL Server Interview Question which may ask in the interview of SQL Server Database Developer.

As a database developer, we are executing COUNT(*) many times to check the total number of table records. But have you ever check the execution plan of your COUNT(*) statement?

If your answer is No, then you should check below demonstration.
If your answer is Yes, then don’t be surprised by checking below demonstration because COUNT(*) statement first uses a lightweight index of a table. If an index does not present, it will do a Table Scan.

Create a table with sample data:

Check query execution plan of COUNT statement:
It performed Table scan because didn’t find any indexes on a table.

Now create an index on NAME column:

Check query execution plan of COUNT statement:
You can find Index scan for idx_tbl_Count_Name.

Now create an index on ID2 column:

Check query execution plan of COUNT statement:
You can find Index scan for idx_tbl_Count_ID2 (smallint) because it lightweight than idx_tbl_Count_Name (char).