MSSQL tips to quickly get the total number of records of a table [SQL Server]

Judging the total number of records in a table is the most common thing for a developer. I think the common practice is:

 
Select Count(*)FromTable Name;
 

This is not done. I am mainly talking about efficiency issues here. When the number of records in a data table is not too large, the efficiency of getting the total number of records is not a problem. However, if the total number of records in a table exceeds several million or tens of millions, It would be unbearable to get the total number of records using the preceding SQL statement. Someone will say that you can create a clustered index on a table. It's good. If you create a clustered index on a field in the table, the first execution of this statement is similar to the time when no index is available. Then, the execution of the preceding statement is very fast. However, if you need to add, delete, and modify data frequently, it is not wise to create a clustered index, which will greatly affect the speed of adding, deleting, and modifying data. The total number of records in a data table is often used in the following aspects:

1. Obtain the total number of records when paging data.

2. Determine whether the number of records in the data table is too large and whether data needs to be backed up.

3. user requirements.

After talking about this, how can we quickly obtain the total number of records of a data table? The answer is: use the System View of the sqlserver database.SYS. sysindexes

In the ms SQL database, each data table has at least one record in the SYS. sysindexes system table. The rows or rowcnt field in the record regularly records the total number of records in the table. Note:TimingThis indicates that the total number of records obtained using this method is not an exact value because MSSQL does not update the value of this field in real time, but regularly updates the value, however, in practice, the difference between this value and the exact value is not big. If you want to quickly estimate the total number of records in the table, we recommend that you use this method.

Sqlsever Help File pairSYS. sysindexesIn the system view, each index and table in the Current Database correspond to one row in the table. After talking about this, you can easily understand the operation.

Open sqlserver and execute the following statement:

 
1Useadventureworks
2  SelectID,Object_name(ID)AsTablename, indid, rows, rowcnt
3  FromSYS. sysindexesWhereIndidIn(0,1)
 

Get:

2010Erichu

For original works, please refer to the author and source for the post. Leave this information.

 

 

------------------------------------------------

Cnblobs:Http://www.cnblogs.com/huyong/
Csdn:Http://blog.csdn.net/chinahuyong