Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Finding rows count in a table without Select... Expand / Collapse
Author
Message
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
Posted Friday, March 26, 2010 2:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 5:24 AM
Points: 23, Visits: 152
Hi, but are the results correct or approximate?
Post #890435
Posted Friday, March 26, 2010 3:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 7, 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
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: Friday, August 8, 2014 1:13 AM
Points: 488, Visits: 1,337
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
Posted Friday, March 26, 2010 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 7, 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
Posted Friday, March 26, 2010 5:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 7, 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
Posted Friday, March 26, 2010 7:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 736, Visits: 3,718
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 !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #890612
Posted Friday, March 26, 2010 7:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 736, Visits: 3,718
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 !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #890613
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: Friday, August 8, 2014 1:13 AM
Points: 488, Visits: 1,337
@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
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: Tuesday, August 19, 2014 2:37 PM
Points: 278, Visits: 1,071
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 123»»»

Permissions Expand / Collapse