Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Manish Mittal » Finding rows count in a table without...
Add to briefcase
22 posts, Page 1 of 3
123»»»

Finding rows count in a table without Select... Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Manish Mittal
Posted Wednesday, March 24, 2010 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 31, 2013 1:35 AM
Points: 19, Visits: 60
Comments posted to this topic are about the item Finding rows count in a table without Select...
Post #888948
Raju Lalvani
Posted Friday, March 26, 2010 2:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 6:51 AM
Points: 22, Visits: 144
Hi, but are the results correct or approximate?
Post #890435
Johnc
Posted Friday, March 26, 2010 3:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 2:34 AM
Points: 13, Visits: 31
This is an interesting (and much quicker) method but I found the figures returned don't correlate exactly with results from 'select count(*) from <table name>'
e.g.
Select count = 14459745; SP_ROWCOUNT = 14459739
Select count = 1907910; SP_ROWCOUNT = 1907541

This may not be too important in whole table rowcounts, but do you know why this is?

Cheers

John


Post #890494
arr.nagaraj
Posted Friday, March 26, 2010 4:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191
Thats because statistics for the table is not updated
Fire 'update statistics tablename' and check the results again.

Regards,
Raj

Strictlysql.blogspot.com
Post #890502
Johnc
Posted Friday, March 26, 2010 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 2:34 AM
Points: 13, Visits: 31
OK thanks. Of course that makes it a little less quick if you have to do an update statistics on the table before you can query it


Post #890521
Johnc
Posted Friday, March 26, 2010 5:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 2:34 AM
Points: 13, Visits: 31
Well I just ran update statistics and re-ran SP_ROWCOUNT. I still got the same figures, so not only does update stats take longer than select count... it doesn't update the rowcount stats either

Maybe I'll stick with select count...


Post #890524
SQLQuest29
Posted Friday, March 26, 2010 7:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 713, Visits: 2,842
Hello,

The script is fine but using MS_foreach is undocumented !
I prefer to use:

SELECT table_name,table_rows FROM (
select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC
from sysobjects so
join sysindexes si on so.id = si.id and so.type = 'U'
)F WHERE RC = 1
ORDER BY table_name

HTH,
\\K

HTH,
Cheers !

"Never take life too seriously, nobody gets out of it anyways !
When your love and skills unite, expect a masterpiece !"
Post #890612
SQLQuest29
Posted Friday, March 26, 2010 7:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 713, Visits: 2,842
Hello,

The script is fine but using MS_foreach is undocumented !
I prefer to use:

SELECT table_name,table_rows FROM (
select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC
from sysobjects so
join sysindexes si on so.id = si.id and so.type = 'U'
)F WHERE RC = 1
ORDER BY table_name

HTH,
\\K

HTH,
Cheers !

"Never take life too seriously, nobody gets out of it anyways !
When your love and skills unite, expect a masterpiece !"
Post #890613
arr.nagaraj
Posted Friday, March 26, 2010 8:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:27 AM
Points: 483, Visits: 1,191
@JohnC,

If you need accuarate numbers then go for count(*). If approximate stuff wud do then use sp_spaceused/SP_ROWCOUNT provided in the article.


Probably your Update stats took a sample scan. Try,

Update statistics tablename with fullscan.

In case if you need to understand statistics, please read two excellent articles
written by the great Gail Shaw

http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/
http://sqlinthewild.co.za/index.php/2008/11/13/identifying-inaccurate-statistics/

Regards,
Raj

Strictlysql.blogspot.com
Post #890644
Chris Howarth-536003
Posted Friday, March 26, 2010 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:01 AM
Points: 266, Visits: 1,023
I prefer to use system objects that aren't marked for removal from a future release of SQL Server, I prefer not to use undocumented stored procs, and I prefer accuracy in my results:

SELECT	QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(ps.object_id)) AS TableName,
	SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats ps
WHERE ps.index_id <= 1
    AND OBJECTPROPERTY(ps.object_id, 'IsMSShipped') = 0
GROUP BY ps.object_id
ORDER BY TableName

Chris
Post #890668
« Prev Topic | Next Topic »

Add to briefcase
22 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.