How to quickly get the total number of records in a table in MS SQLSERVER_PHP Tutorial

In the design of database applications, we often need to obtain the total number of records in certain tables to determine whether the total number of records in the table is too large and whether data needs to be backed up. Our usual approach is: select count(*) as c from tableA. However, for a table with a huge number of records, the above approach will be very time-consuming. During the test on the DELL 4400 server, the MS Sqlserver 2000 database executed the above statement on a simple data table with 1 million records, which took more than 1 minute. If you create a clustered index on a certain field of the table, the time it takes to execute the statement for the first time is almost the same as when there is no index. After that, the above statement is executed very quickly, within 1 second, but when the number of records in the table changes After major changes, executing the statement again will go through a time-consuming process. And not every table is suitable for a clustered index. For a huge number of tables, if frequent additions and deletions are required, it is unwise to build a clustered index, which will greatly affect the speed of additions and deletions. So is there a simpler way to quickly get the total number of records in the table? The answer is yes.
In MS SQL database, each table has at least one record in the sysindexes system table, and the rows field in the record will regularly record the total number of records in the table. The following is the meaning of the related records of 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 = has text or image data table entry.
rows int Number of data-level rows based on indid=0 and indid=1. This value is repeated for indid>1. If indid=255, rows is set to 0.


Indid = 0 when the table does not have a clustered index and 1 otherwise.
Now you should know how to get the total number of records in the table. Just execute the following statement:
select rows from sysindexes where id = object_id(tablename) and indid in (0,1)
This method gets The total number of records in the table is very fast. It can be completed in milliseconds. It is tens of thousands of times faster than select count(*). However, when using this method, you must pay attention to the total number of records in the table obtained by this method. It is not an exact value. The reason is that MS SQL does not update the value of this field in real time, but updates it regularly. In practice, the error between this value and the precise value is generally not large. If you want to quickly and roughly estimate the size of the table, it is recommended. You take that approach. If you want to get the exact value, please execute DBCC UPDATEUSAGE(DatabaseName,[TABLENAME]) WITH ROW_COUNTS before executing the above statement to force the value of the field to be updated, but this will take a lot of time during the first update. The effect of this The effect of select count (*) is not much different from that of a table with a clustered index, so if you want to get the accurate total number of records in the table relatively quickly, then you have two options, build a clustered index or use DBCC first and then use the above method.

http://www.bkjia.com/PHPjc/631131.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631131.htmlTechArticleIn the design of database applications, we often need to obtain the total number of records in certain tables to determine the number of records in the table. Whether the total number of records is too large, whether data needs to be backed up, etc. Our usual approach...