Alternative way for SQL Server to get total table records

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

The following are the referenced contents:

Select COUNT (*) from table name;

This is not done, I am mainly talking about the problem of efficiency. The efficiency of the total number of records is not a problem when the number of records in a datasheet is not too long. But imagine that if a table has a total of more than millions of or tens of millions of records, it's a bit slow to get the total number of records in the above SQL statement. Some people would say that you can set up a clustered index on a table, yes, if you set up a clustered index on a field in a table, the first time you execute the statement is about the same as the time without the index, and then the above statement executes quickly. However, if you want to make frequent additions and deletions to the operation, the establishment of clustered index is not a sensible approach, will greatly affect the speed of deletion and modification. The total number of records that get a data table is often used in the following ways:

First, do the data paging to get the total number of records.

Second, the data table to determine whether the record number is too large, the need to back up data.

Third, user requirements and so on.

Having said so much, how can you quickly get the total number of records in a datasheet? The answer is: using the system view of SQL Server database sys.sysindexes

In the MS SQL Serverl database, each datasheet has at least one record in the sys.sysindexes system table, and the rows or rowcnt fields in the record will periodically record the total number of records in the table. Attention is timed, which shows that the total number of records obtained by this method is not an exact value, the reason is that MSSQL does not update the value of the field in real time, but it is timed to update, but in practice the value and exact value are not very error, and it is recommended that you use this method if you want to quickly and roughly estimate the total number of records in the table.

The description of the SQL Server Help file for the Sys.sysindexes system view is that each index and table in the current database correspond to one row in the table. Said so much, direct hands-on operation will be at a glance.

Open SQL Server and execute the following statement:

The following are the referenced contents:

Useadventureworks

Select Id,object_name (ID) as tablename,indid,rows,rowcnt

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

Get: