Optimize query to count total number of records in a table in ms sql server

In this ms sql server tutorial we will learn how to count total number of records (rows) in a table. Many people use given below query for this purpose

SELECT COUNT(*) AS ROWS FROM MY_TABLE

Let me tell you this query is really a killer query if table contains records more than one lac and worst case is more than one million because this query scans the whole table right from first row to last row.

Now what should we do? What is the optimize query? These are the questions might be coming in your mind, ok, below given is the optimized query that you guys must use for counting records purpose.

SELECT ROWS FROM SYSINDEXES WHERE ID = OBJECT_ID('MY_TABLE') AND INDID < 2

That's it. Hopefully you will find this tutorial very helpful. I love to hear your feedback.