Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding rows count in a table without Select...


Finding rows count in a table without Select...

Author
Message
Manish Mittal
Manish Mittal
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 61
Comments posted to this topic are about the item Finding rows count in a table without Select...
Raju Lalvani
Raju Lalvani
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 154
Hi, but are the results correct or approximate?
Johnc
Johnc
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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



arr.nagaraj
arr.nagaraj
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 1588
Thats because statistics for the table is not updated
Fire 'update statistics tablename' and check the results again.

Regards,
Raj

http://Strictlysql.blogspot.com
Johnc
Johnc
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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 Ermm



Johnc
Johnc
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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 Exclamation Mark

Maybe I'll stick with select count...



SQLQuest29
SQLQuest29
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 4316
Hello,

The script is fine but using MS_foreach is undocumented ! Hehe
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 :-)
SQLQuest29
SQLQuest29
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 4316
Hello,

The script is fine but using MS_foreach is undocumented ! Hehe
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 :-)
arr.nagaraj
arr.nagaraj
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 1588
@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

http://Strictlysql.blogspot.com
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 1159
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search