Count the number of rows in a table within a millisecond with more than 28 lakh records. In SQL Server, you can get the total number of rows using the following methods.

  • sys.dm_db_partition_stats tables
  • sysindexes tables
  • Count()
  • Count() With No locks
The best method is sysindexes in this to get the count within milliseconds.
Create a table. Copy the script given below.
  1. USE[TestDB]
  2. GO
  3. /****** Object: Table [dbo].[Table_1] Script Date: 11/23/2016 10:10:54 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE TABLE[dbo].[Table_1](
  9. [ID][nvarchar](50) NULL, [RandomNo][nvarchar](50) NULL, [Create_Date][datetime] NULL) ON[PRIMARY]
  10. GO

I have inserted “2808924” rows.

Method-1 (Using Count() )

Query

  1. SET STATISTICS TIME ON
  2. SELECT COUNT(ID) FROM TABLE_1

Result

SQL Server Execution Times:

CPU time = 812 ms, elapsed time = 835 ms.

Second time

  1. SET STATISTICS TIME ON
  2. SELECT COUNT(ID) FROM TABLE_1

Result:

SQL Server parse and compile time

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times

CPU time = 328 ms, elapsed time = 326 ms.

Method-2 (Using sysindexes )

  1. SET STATISTICS TIME ON
  2. SELECT CONVERT(bigint, rows) FROM sysindexes WHERE id = OBJECT_ID(‘TABLE_1’) AND indid < 2

SQL Server parse and compile time

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Conclusion

Using count(), its excution time is CPU time = 328 ms, elapsed time = 326 ms whenever the SQL sysindex table takes excution time CPU time = 0 ms, elapsed time = 0