SQL Server 2016 Hosting – HostForLIFE.eu :: How to Count Number Of Rows In Table Within A Milisecond?

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] NULLON[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 )

SET STATISTICS TIME ON

  1. SELECT CONVERT(bigintrowsFROM 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

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. They deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. They have customers from around the globe, spread across every continent. They serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.