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