Toad World® Forums

How to get the Correct row Count for a table - TIP_002


#1

Helpful little tip you might want to review !!!

Everyone does the following to get the correct row count…

select count(*) from JustEnergy.dbo.DirectDebitCreditCard

But maybe you should use !!!

select count ( * ) from JustEnergy.dbo.DirectDebitCreditCard option (MAXDOP 1)

You can also use the following which is a hidden Stored Procedure to get the
‘rows’…

use justenergy

go

sp_spaceused ‘dbo.DirectDebitCreditCard’

Which will return the following as an example

name rows reserved
data index_size unused

DirectDebitCreditCard 559028 69808 KB 62136 KB 6752
KB 920 KB

However !!!

SQL Server may not necessarily be giving you the right answer !!

Because if you read this link below you can find out why…

  /**

  DBCC UPDATEUSAGE(0)  --Please reference this page for explainatin of

negative values for your need to run this command first

http://sqlserver2000.databases.aspfaq.com/why-does-sp-spaceused-return-inaccurate-values.html

  **/

So you might want to do a à DBCC CHECKTABLE(‘tablename’)

Note: The following in the article is incorrect syntax use the above syntax of :
option (MAXDOP 1)

SELECT COUNT(*) FROM tablename MAXDOP(1)

Sooo !!!

DBCC CheckTable( ‘dbo.DirectDebitCreditCard’ ) – which can take about
10(+) seconds on 560k rows.

Then run the command again just to make sure.

use justenergy

go

sp_spaceused ’ dbo .DirectDebitCreditCard’

name rows reserved
data index_size unused

DirectDebitCreditCard 559028 69808 KB 62136 KB 6752
KB 920 KB

sp_spaceused ’ webje .BillGroup824’

name rows reserved data
index_size unused

BillGroup824 372 16 KB 8 KB 8 KB
0 KB

Notation: Please take note of the schema name change between the two examples
both of which are in the same database. It is necessary because sometimes
you have identical table names in a schema as well as ‘dbo’.

The Prize !!!

If this means anything to you, I have a script I have written that will take a
database name as a variable and cycle through all the tables in a database and
give some really good simple statistics.

This script is available upon request.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

image001.gif

image002.jpeg


#2

SEE ATTACHMENT
image002.jpeg


#3

SEE ATTACHMENT
IMAGE.xxx (43 Bytes)


#4

SEE ATTACHMENT
image004.gif


#5

SEE ATTACHMENT
image001.gif


#6

When I get to work, I will send it along !!!

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg


#7

When I get to work, I will send it along !!!

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image001.gif


#8

When I get to work, I will send it along !!!

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary
image002.jpeg