Count Number Of Rows In Table Within A Milisecond

Count the number of rows in table within a milisecond with more than 28 lakh records.

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.

USE[TestDB]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/23/2016 10:10:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE[dbo].[Table_1](
    [ID][nvarchar](50) NULL, [RandomNo][nvarchar](50) NULL, [Create_Date][datetime] NULL) ON[PRIMARY]
GO

I have inserted “2808924” rows.

Method-1 (Using Count() )

Query

SET STATISTICS TIME ON
SELECT COUNT(ID) FROM TABLE_1

Result

SQL Server Execution Times:

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

Second time

SET STATISTICS TIME ON
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
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