.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Get Row Count with sysIndexes

Posted By:Rajshree       Posted Date: September 21, 2010    Points: 10    Category: SQL Query    URL: http://www.dotnetspark.com  

Get Table Row Count with batter performance
 

Hello

Generally we use
Select count(*) from emp
to count the no of rows but actually it slow down the speed

The statement might consume significant resources if your tables are very big because scanning a large table or index can consume a lot of I/O. Avoid these type of queries to the maximum


Here is one other way you can find the total number of rows in a table. SQL Server Books Online (BOL) documents the structure of sysindexes; the value of sysindexes.indid will always be 0 for a table and 1 for a clustered index. If a table doesn't have a clustered index, its entry in sysindexes will always have an indid value of 0. If a table does have a clustered index, its entry in sysindexes will always have an indid value of 1.



Here is the query used to count the rows of the table using sysIndexes:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2







     

Further Readings:

Responses
Author: Deco         Company URL: http://www.dotnetspark.com
Posted Date: September 21, 2010

what will be the result if there is a stored procedure with the name "tbTest"?
Author: Rajshree         Company URL: http://www.dotnetspark.com
Posted Date: September 22, 2010

No if the tbTest is Sp it would not show any result as indexes are only created on Table or View
In case of either tablename or viewname it will work

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend