Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people don't know what to do about how to get the total number of records of the table in SQLServer. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

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

Our usual practice is: selectcount (*) ascfromtableA. However, for tables with a large number of records, this can be very time-consuming. In the experiment on the DELL4400 server, the MSSqlserver2000 database executes the above statements for more than 1 minute on the simple data table recorded by 1 million. If you do a clustered index on a field of a table, the time to execute the statement for the first time is about the same as that without an index, and then the above statement is executed very fast, within 1 second, but when the number of records in the table changes greatly, executing the statement will go through another time-consuming process. And not every table is suitable for clustering index. for a large number of tables, if frequent add and delete operations are needed, it is very unwise to build a clustered index, which will greatly affect the speed of adding and deleting. So is there an easy way to get the total number of records in a table quickly? The answer is yes.

Each table in the MSSQL database has at least one record in the sysindexes system table, and the rows field in that record periodically records the total number of records in the table.

What are the meanings of the related records of the sysindexes table

Column name data type description

Idint table ID (if indid=0 or 255). Otherwise, the ID of the table to which the index belongs

Indidsmallint index ID:

0 = table

1 = clustered index

> 1 = non-clustered index

255A table entry with text or image data.

Rowsint is based on the number of data-level rows of indid=0 and indid=1, which is repeated for indid > 1. If indid=255,rows is set to 0.

Indid=0 is 1 if the table does not have a clustered index.

So now that you know how to get the total number of records for the table, just execute the following statement:

Selectrowsfromsysindexeswhereid=object_id (tablename) andindidin (0jue 1)

The speed of this method to obtain the total number of records of the table is very fast, which can be completed in milliseconds, which is tens of thousands of times faster than selectcount (*), but it must be important for everyone to use this method. The total number of records of the table obtained by this method is not an exact value, because MSSQL does not update the value of the field in real time, but regularly, when in practice, the general error between the value and the exact value is small. If you want a quick rough estimate of the size of the table, it is recommended that you use this method. If you want to get the exact value, execute DBCCUpdateUSAGE (DatabaseName, [TABLENAME]) WITHROW_COUNTS before executing the above statement to force the update of the field, but the first update will take a lot of time, and the effect is not much different from that of the table selectcount (*) with a clustered index, so if you want to get the exact total number of records of the table relatively quickly, you have two options Create a clustered index or DBCC before using the above method.

After reading the above, have you mastered how to get the total number of records of the table in SQLServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.