|
|
|
|
Because SELECT COUNT(*) statement make a full table scan to return the total table's row count,
it can take very many time for the large table.
There is another way to determine the total row count in a table. You can use sysindexes system table,
in this case. There is ROWS column in the sysindexes table.
This column contains the total row count for each table in your database. So,
you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,
you can improve the speed of such queries in several times. |
 Click here to copy the following block | Use Northwind
GO
select
a.Table_Name as tablename
,(SELECT rows FROM sysindexes WHERE id = OBJECT_ID(a.Table_Name) AND indid < 2) as TotalRowcount
from INFORMATION_SCHEMA.TABLES a
WHERE TABLE_TYPE='BASE TABLE' order by TotalRowcount desc |
|
|
|
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
 |
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
|
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|