SQL Server quickly obtains the total number of table records

In the design of database applications, we often need to obtain the total number of records in some tables to determine whether the total number of records in the table is too large and whether data needs to be backed up. Our common practice is: select count (*) as c from tableA. However, for tables with a large number of records, the above practice will be very time-consuming. In a test on a DELL 4400 server, the MS Sqlserver 2000 database executes the preceding statement on a simple data table recorded in 1 million for over 1 minute.

If a clustered index is performed on a field in the table, the first time the statement is executed is similar to the time when no index is available. Then, the preceding statement is executed, which is fast and within 1 second, however, when the number of records in the table changes significantly, executing this statement will go through a time-consuming process. Not every table is suitable for clustering indexes. for a large number of tables that require frequent addition or deletion operations, it is unwise to create clustering indexes, this will greatly affect the speed of addition and deletion. Is there a simple way to quickly obtain the total number of table records? The answer is yes.

In the ms SQL database, each table has at least one record in the sysindexes system table. The rows field in this record regularly records the total number of records in the table.

The following are the meanings of related records in the sysindexes table:

Column name data type description

Id int table ID (if indid = 0 or 255 ). Otherwise, it is the ID of the table to which the index belongs.

Indid smallint index ID:

0 = table

1 = clustered Index

> 1 = non-clustered Index

255 = table entries with text or image data.

Rows int is based on indid = 0 and indid = 1 to repeat indid> 1. If indid = 255, rows is set to 0.

If the table does not have a clustered index, Indid = 0; otherwise, it is 1.

Now you should know how to get the total number of records of the table. You only need to execute the following statement:

select rows from sysindexes where id = object_id(tablename) and indid in (0,1)

This method is used to obtain the total number of records of a table at a very high speed, which can be completed in milliseconds, Which is tens of thousands of times faster than select count, however, when using this method, you must make sure that the total number of records in the table obtained by this method is not an exact value because ms SQL does not update the value of this field in real time, it is a regular update. In practice, this value and the exact value are generally slightly different. If you want to quickly estimate the table size, we recommend that you use this method.

If you want to obtain the exact value, execute DBCC UpdateUSAGE (DatabaseName, [TABLENAME]) WITH ROW_COUNTS to force update the value of this field before executing the preceding statement, however, the first update will consume a lot of time. The effect of this operation is slightly different from that of the select count (*) table with a clustered index, therefore, if you want to quickly obtain the total number of records in a precise table, you have two options: Create a clustered index or use DBCC first.

1. How to perform MySQL database table Fault Detection
2. MySQL-optimized data table Processing
3. How to lock MySQL database tables