SQL Server tips to quickly get the total number of records of a table

Judging the total number of records in a table is the most common thing for a developer. I think the common practice is:

 
 

  
  
Select count (*) from table name;
 
 

This is not done. I am mainly talking about efficiency issues here. When the number of records in a data table is not too large, the efficiency of getting the total number of records is not a problem. However, if the total number of records in a table exceeds several million or tens of millions, It would be unbearable to get the total number of records using the preceding SQL statement. Someone will say that you can create a clustered index on a table. It's good. If you create a clustered index on a field in the table, the first execution of this statement is similar to the time when no index is available. Then, the execution of the preceding statement is very fast. However, if you need to add, delete, and modify data frequently, it is not wise to create a clustered index, which will greatly affect the speed of adding, deleting, and modifying data. The total number of records in a data table is often used in the following aspects:

1. Obtain the total number of records when paging data.

2. Determine whether the number of records in the data table is too large and whether data needs to be backed up.

3. user requirements.

After talking about this, how can we quickly obtain the total number of records of a data table? The answer is: use the System View of the SqlServer Database sys. sysindexes

In the ms SQL Serverl database, each data table has at least one record in the sys. sysindexes system table. The rows or rowcnt field in the record regularly records the total number of records in the table. Note:TimingThis indicates that the total number of records obtained using this method is not an exact value because MsSQL does not update the value of this field in real time, but regularly updates the value, however, in practice, the difference between this value and the exact value is not big. If you want to quickly estimate the total number of records in the table, we recommend that you use this method.

The SQL Server Help File describes the sys. sysindexes system view as follows: each index and table in the current database corresponds to a row in the table. After talking about this, you can easily understand the operation.

Open SQL Server and execute the following statement:

 
 

  
  
useAdventureWorks  
  
  
select id,object_name(id) as tableName,indid,rows,rowcnt  
  
  
from sys.sysindexes where indid in(0,1) 
 
 

Get:

Edit recommendations]