[MSSQL] How to efficiently query the total number of records in a table

How to efficiently query the total number of records in a table? [Summary-collation-Mark]

The first thought of the natural is to apply the Count function on the table primary key to query, this is the most used method, no one

SELECT COUNT (1) ROWS from product

Here are some other methods, these methods will be some restrictions, or may not seem so "perfect", but there is a certain reference meaning, the amount of information is not good, treat the official slowly

Alternative Method 1

Take full advantage of the system's own stored procedures sp_spaceused, detailed use of recommended Google or degree Niang, see Code

EXEC sp_spaceused ' PRODUCT '

The result is as shown

Alternative Method 2

According to international practice, many tables will design a 1-step self-growth integer (INT) column as the table primary key, the practice is not discussed here, based only on this "habit", plus a hypothesis: the field of continuous inaction, you can use the Max function, limit more ha

1, self-growing column

2, the column continuously

SELECT MAX (ProductCode) TotalCount from Product

Output

If I have such a self-increment primary key table, but not continuous, then you can not use it? In fact, you could use the Row_number () function to repair, and then take Max is the same, in order not too much water, here is still classified as alternative Method 2, review row_number students point here

Select COUNT (*) from C6.dbo.AddressUser

Alternative Method 3

The advantage of using the information in the system tables is that it is fast enough! The disadvantage is that the data is inaccurate, so it is appropriate to use this method in conjunction with the characteristics of your own project, and the Code

SELECT ROWS from [sysindexes] WHERE id = object_id (' product ') and Indid < 2

Output results

Alternative Method 3 repair data imprecise method, execute the following SQL script, please do not use in the production environment!

DBCC updateusage (0, ' product ') with Count_rows

[MSSQL] How to efficiently query the total number of records in a table