The total number of records in SQL Server that quickly get mass data

server|sqlserver| data
February wrote a SQL Server optimization method for massive queries http://blog.csdn.net/great_domino/archive/2005/02/01/275839.aspx

Recently, some colleagues have encountered SQL Server statistics millions above the number of record bar too slow problem, SP often encounter statistics a large number of data problems, perhaps the cause of coincidence, find a very simple way!

Wrote an essay

The common practice when we are counting records is to select COUNT (*) as C from table. However, these practices can be time-consuming for a large number of records. Experimenting on the Dell 4400 server, the MS SQL Server 2000 database executes the above statement for a simple data table of 1 million records for more than 1 minutes. If you do a clustered index on a field in a table, the time of the first execution of the statement is about the same as the time without the index, and then the above statement executes quickly, within 1 seconds, but when the number of records in the table changes significantly, it takes a time-consuming process to execute the statement. And not every table is suitable for clustered index, for a large number of tables, if you need to add and delete operations, building a clustered index is a very unwise approach, will greatly affect the speed of additions and deletions. So is there a simpler way to quickly get the total number of records in a table? The answer is yes. In an MS SQL database, each table has at least one record in the sysindexes system table, and the Rows field in the record records the total number of records in the table. The following are the meanings of the related records of the sysindexes table:

Column name        data type               Description id            int                          Table ID (If indid = 0 or 255). Otherwise, the idindid       smallint         of the table to which the index belongs         Index id:                                           0= Table                                            1= Clustered Index                                            >1= non-clustered index                                           255= table entries with text or image data. rows       int                        the number of data-level rows based on indid=0 and indid=1, which is duplicated for indid>1. If the indid=255,rows is set to 0.      when the table does not have a clustered index, indid = 0 is otherwise 1.