In SQL Server 2000, normal way of finding the total number of records in a table is by using Count() function. Though it would give the exact result, it has a performance issue. Its because it would do a table scan on that table.
i.e, Select count(*) from Authors --- This would do a table scan on Authors table.
The work around is to fetch the number of records from SysIndexes table. But please be adviced that though it would be faster, the number of records returned would not always be perfect. Only if the database has uptodate statistics it would show the exact count.
i.e., Select Rows from SysIndexes Where [ID] = Object_ID('Authors') And indid < 2
Moreover it is not advisable to query the Sytem tables directly. It's because if Microsoft changes the schema of this SysIndexes table in future all your existing code won't work.
In SQL Server 2005 they have come up Partitioned Table concept. So its pretty easy to find the row count of a table without using Count function.
Select Sum(Rows) from Sys.Partitions Where Index_ID < 2 and Object_name(Object_ID) in ('Sales')
It is not necessary that we need to have upto date statistics for this.
Technorati tags: SQL, SQL Server 2005, SQL Server 2000
i.e, Select count(*) from Authors --- This would do a table scan on Authors table.
The work around is to fetch the number of records from SysIndexes table. But please be adviced that though it would be faster, the number of records returned would not always be perfect. Only if the database has uptodate statistics it would show the exact count.
i.e., Select Rows from SysIndexes Where [ID] = Object_ID('Authors') And indid < 2
Moreover it is not advisable to query the Sytem tables directly. It's because if Microsoft changes the schema of this SysIndexes table in future all your existing code won't work.
In SQL Server 2005 they have come up Partitioned Table concept. So its pretty easy to find the row count of a table without using Count function.
Select Sum(Rows) from Sys.Partitions Where Index_ID < 2 and Object_name(Object_ID) in ('Sales')
It is not necessary that we need to have upto date statistics for this.
Technorati tags: SQL, SQL Server 2005, SQL Server 2000
Comments