Shulou(Shulou.com)06/01 Report--

Preface

I wrote a blog post, "how to quickly query the number of records in a table in a relational database", which introduced the problem of using sp_spaceused to check whether the number of records in a table is correct, as follows:

With regard to question 3: is there any inconsistency in the number of records for tables with multiple indexes?

Answer: personal tests and statistics show that when multiple indexes are temporarily found, the number of rows records in sys.partitions is the same. For the time being, no inconsistencies have been found, and of course, special circumstances cannot be ruled out.

About question 5: what about partitioned tables?

Answer: there is no difference between a partition table and a regular table.

About question 6: is the table record function obtained by the object catalog view sys.partitions and sp_spaceused accurate?

Answer: the number of table records obtained by the object catalog views sys.partitions and sp_spaceused is accurate.

However, there is a problem today, which directly overturns the conclusion made in the previous blog. As shown in the screenshot below, it is found that the number of records varies with different indexes. So question 3 should be answered like this:

In most cases, the number of rows records in sys.partitions is the same. However, there are also cases where rows inconsistencies are found in different indexes.

In addition, it is also found that the number of records returned in sp_spaceused is not consistent with SELECT COUNT (*). However, from the fragments, statistics and other aspects of the analysis, it is not clear what causes this situation. After looking for it in the database, it is found that such cases are very rare, but they do exist. Here is a record.

Sp_spaceused 'dbo.spcecial_table'; SELECT partition_id, object_id, index_id,row_count FROM sys.dm_db_partition_stats WHERE object_id= OBJECT_ID (' dbo.spcecial_table') SELECT object_id, index_id, rows FROM sys.partitionsWHERE object_id= OBJECT_ID ('dbo.spcecial_table') SELECT object_id, index_id, rows FROM sys.partitionsWHERE object_id= OBJECT_ID (' spcecial_table'); SELECT COUNT (*) FROM spcecial_table

As shown in the screenshot below, the number of records obtained by sp_spaceused is 8718528, but SELECT COUNT (*) is 8735537.

About question 6: is the table record function obtained by the object catalog view sys.partitions and sp_spaceused accurate?

Answer: the number of table records obtained by the object catalog views sys.partitions and sp_spaceused is accurate.

Correct answer: the number of table records obtained by the object catalog views sys.partitions and sp_spaceused is mostly accurate. However, there are also cases where the number of records is not accurate. It's just that it's not clear in what scenarios inaccuracies will occur.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.