In the design of database application, we often need to get the total number of records of some tables, to determine whether the total number of records in the table is too large, the need to back up data and so on. Our usual practice is to select COUNT (*) as C from TableA. However, these practices can be time-consuming for a large number of records. Experimenting on the Dell 4400 server, the MS SQL Server 2000 database executes the above statement for a simple data table of 1 million records for more than 1 minutes. If you do a clustered index on a field in a table, the time of the first execution of the statement is about the same as the time without the index, and then the above statement executes quickly, within 1 seconds, but when the number of records in the table changes significantly, it takes a time-consuming process to execute the statement. And not every table is suitable for clustered index, for a large number of tables, if you need to add and delete operations, building a clustered index is a very unwise approach, will greatly affect the speed of additions and deletions. So is there a simpler way to quickly get the total number of records in a table? The answer is yes.
In an MS SQL database, each table has at least one record in the sysindexes system table, and the Rows field in the record records the total number of records in the table. The following are the meanings of the related records of the sysindexes table:
Column name Data type description
ID int table ID (If indid = 0 or 255). Otherwise, the ID of the table to which the index belongs
indid smallint index ID:
0= table
1= Clustered Index
>1= Non-clustered index
255= a table entry with text or image data.
Rows int is based on the number of indid=0 and indid=1 data-level rows, which are duplicated for indid>1. If the indid=255,rows is set to 0.
When the table does not have a clustered index, indid = 0 is otherwise 1.
So now everyone should know how to get the total number of records in the table, just execute the following statement:
Select rows from sysindexes where ids = object_id (tablename) and indid in (0,1)
This method gets the total number of records in the table very quickly, can be done at the millisecond level, tens of thousands of times times faster than select COUNT (*), but it is important to use this method, the total number of tables obtained by this method is not an exact value because Ms SQL does not update the value of the field in real time. Rather, it is a regular update, and it is recommended that you use this method if you want a quick and rough estimate of the size of the table, when it is practical to see that the value and the exact value are generally not very large. If you want to get the exact value, execute the DBCC UPDATEUSAGE (Databasename,[tablename]) with row_counts to force the update of the value of the field before executing the above statement, but it can take a lot of time to update the first time. The effect of this is not quite the same as the table select COUNT (*) with the clustered index, so if you want to get the exact total number of records on the table relatively quickly, you have two options, a clustered index, or DBCC to use the above method.
