How to quickly get the total number of records in the table in MS SQLServer

zhaozj2021-02-08  252

In the design of the database application, we tend to get the total number of records of some tables, and whether the total number of records used is too large, whether backup data is required. Our usual practice is: select count (*) as c from Tablea. However, the above practice will be very time consuming for a huge number of records. Do a trial on the Dell 4400 server, the MS SQLServer 2000 database performs the above statement for 1 million recorded simple data tables, and time is more than 1 minute. If you do a clustered index on a field of the table, the time for the first execution of the statement and the time without the index are similar, then perform the above statement, the speed is very fast, within 1 second, but the number of records occur After large change, the statement will be executed again to experience a time-consuming process. And not every table is suitable for clustered indexes, for a huge table, if you need to change the operation, the cluster index is a very unwise practice, which will greatly affect the speed of the increase. So do you have a relatively simple method to quickly get the total number of records? The answer is yes. Each table in the MS SQL database has at least one record in the SysIndexes system table. The ROWS field in the record will record the total number of records of the table. Below is the meaning of the relevant record of the SysIndexes table:

Column Data Type Description ID INT Table ID (if IndID = 0 or 255). Otherwise, the IDID SMALLINT index ID: 0 = Table 1 = Cluster index> 1 = Non clustered index 255 = table entries having text or image data. Rows INT is based on Indid = 0 and Indid = 1, which is repeated to Indid> 1. If IndID = 255, ROWS is set to 0. Indid = 0 otherwise when the table does not have a clustered index.

So now, you should know how to get the total number of records, just do the following statement: select rows from sysindexes where id = Object_id (TableName) and Indid in (0, 1)

转载请注明原文地址:https://www.9cbs.com/read-1784.html

New Post(0)