An alternative method for SQL Server to obtain the total number of table records

To get the total number of records for a table, the common practice is: The reference content is as follows: selectcount (*) from table name; this practice 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,

To get the total number of records in a table, the common practice is: The reference content is as follows: select count (*) from table name; this practice 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,

To obtain the total number of records in a table, the common practice is:

Reference content is as follows:

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: Timing 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:

Reference content is as follows:

UseAdventureWorks

Select id, object_name (id) as tableName, indid, rows, rowcnt

From sys. sysindexes where indid in (0, 1)

Get: