In February, I wrote a method http://blog.csdn.net/great_domino/archive/2005/02/01/275839.aspx for SQL Server to optimize massive queries.
Recently, some colleagues have encountered the problem that SQL Server has slow records of more than one million records. SP often encountered a large number of data statistics problem, which may be a coincidence. Find a simple method!
I wrote an article
The common practice for statistical records is: Select count (*) as C from table. However, for tables with a large number of records, the above practice will be very time-consuming. In a test on a Dell 4400 server, the MS sqlserver 2000 database executes the preceding statement on a simple data table recorded in 1 million for over 1 minute. If a clustered index is performed on a field in the table, the first time the statement is executed is similar to the time when no index is available. Then, the preceding statement is executed, which is fast and within 1 second, however, when the number of records in the table changes significantly, executing this statement will go through a time-consuming process. Not every table is suitable for clustering indexes. for a large number of tables that require frequent addition or deletion operations, it is unwise to create clustering indexes, this will greatly affect the speed of addition and deletion. Is there a simple way to quickly obtain the total number of table records? The answer is yes.
In the ms SQL database, each table has at least one record in the sysindexes system table. The Rows field in this record regularly records the total number of records in the table. The following are the meanings of related records in the sysindexes table:
Column name data type description
Id int table ID (if indid = 0 or 255 ). Otherwise, it is the ID of the table to which the index belongs.
Indid smallint index ID:
0 = table
1 = clustered Index
> 1 = non-clustered Index
255 = table entries with text or image data.
Rows int is based on indid = 0 and indid = 1 to repeat indid> 1. If indid = 255, rows is set to 0.
If the table does not have a clustered index, indid = 0; otherwise, it is 1.