How To Get Table Row Counts Quickly And Painlessly

Question

Post reply

How To Get Table Row Counts Quickly And Painlessly

Viewing 15 posts - 61 through 75 (of 108 total)

You must be logged in to reply to this topic. Login to reply

Oleg-461731 SSChasing Mays Points: 619 More actions · Answer 1

How to count rows in # or ## table ?

Can described approaches be applied to temp tables?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

select count(*) from #mytemptable 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 3

TheSQLGuru (9/4/2009)
select count(*) from #mytemptable 😀

I'll add a :laugh: to that

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Oleg-461731 SSChasing Mays Points: 619 More actions · Answer 4

Paul White (9/4/2009)
TheSQLGuru (9/4/2009)
select count(*) from #mytemptable 😀
I'll add a :laugh: to that

no, it is impossible, try to find another way :w00t:

because I asked about tables # and ## , but you've used #mytemptable.

:hehe:

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 5

I believe we can use the procs sp_spaceused and sp_statistics for temporary tables by changing the context to "tempdb" database. But that can be done only in the session in which the temporary table has been created 🙂

Reginald J Ray Jr Old Hand Points: 383 More actions · Answer 6

I'd say 80 to 90 percent of ours still use SQL Server 2000. We have just one using SQL Server 2008.

Paul White SSC Guru Points: 150467 More actions · Answer 7

SELECT row_count = SUM(row_count)

FROM sys.dm_db_partition_stats DPS

WHERE DPS.[object_id] = OBJECT_ID(N'tempdb.dbo.[#AnyTempTable]', N'U')

AND DPS.index_id IN (0, 1);

sp_statistics requires you to specify 'E' for the @accuracy parameter in order to get good results (the default is 'Q' fo quick - see BOL).

It is also fine for visual inspection, but less convenient for auditing - you'd need to have an appropriately structured table to INSERT...EXEC the results into. It also does quite a lot of extra work (aside from cardinality) so it will be slower than methods like the one above.

In a repeat of Jeff's earlier million-row test, COUNT(*) takes 2.1s, sp_statistics takes 93 milliseconds, and the DMV method takes a reported zero microseconds.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

randall.c.newcomb Say Hey Kid Points: 695 More actions · Answer 8

We are still predominately SQL 2K. We hope to migrate to 2005 early in the next decade. :sick:

- Randall Newcomb

Peter Petrov SSCommitted Points: 1516 More actions · Answer 9

jcrawf02 (9/2/2009)
"Quickly and Painlessly" doesn't seem to fit the method, when SELECT COUNT(*) FROM myTable is just four little words (ok, a function ain't a word, whatever) long, and the alternative is joining three tables together and using a WHERE clause. Point taken that the system objects hold this data, but not sure that's easier.

We don't virtually need sys.indexes and sys.objects tables. 😉

SELECTOBJECT_NAME(object_id) AS table_name, SUM(row_count) AS row_count

FROMsys.dm_db_partition_stats

WHEREindex_id < 2 AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0

GROUPBY object_id

ORDERBY 1;

Paul White SSC Guru Points: 150467 More actions · Answer 10

I think Peter may be working his way through from post #1!

:laugh:

edit: "ORDER BY 1" :sick:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Paul White (9/4/2009)
I think Peter may be working his way through from post #1!
:laugh:
edit: "ORDER BY 1" :sick:

HAH!! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRocker SSCrazy Points: 2217 More actions · Answer 12

SSMS 2008 does a good job in displaying the row count next to the table itself.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

SQLRocker (9/4/2009)
SSMS 2008 does a good job in displaying the row count next to the table itself.

DBArtisan has been doing this for about a decade now, but it is the same "not guaranteed to be precise" value that SSMS uses.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 14

Paul White, you made some excellent clarifications. Thank you for expanding on what I was trying to say.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

rja.carnegie SSC Eights! Points: 923 More actions · Answer 15

Sanjay Rohra (9/4/2009)
I believe we can use the procs sp_spaceused and sp_statistics for temporary tables by changing the context to "tempdb" database. But that can be done only in the session in which the temporary table has been created 🙂

DBCC CHECKDB(N'tempdb') 🙂

Unless you can decode the "true object name" of temporary objects ("#___(......)_000000004F5B") to identify the owner - which I think really can be

done only by the owner on a per-object level (but all of mine will be named "...4F5B" apparently) - the limitation you mention still stands. But this also is still the situation - non-global temporary objects specifically - where keeping track of each @@ROWCOUNT may be sufficient. But also may create the need to do, for instance,

SELECT @returnerror = @@ERROR, @rows = @rows + @@ROWCOUNT

- because you can't get those one at a time.