Toad World® Forums

How to get the Correct row Count for a table - TIP_002 [1 Attachment]


#1

How to get the Correct row Count for a table - TIP_002 [1 Attachment]


#2

[ Attachment(s) from Hank_Freeman included below]

To all:

I experienced a system issue( mother board was failing) when I thought I was
sending out the latest version on my attached script, via email. I can not find
that it went out, so I am sending it again.

If you have received this script with a previous date you should know this this
one has been updated …

Sorry if I have duplicated this email…

I have updated this to now work with multiple schemas like what you find in
Adventureworks.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

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’.

Attachment(s) from Hank_Freeman

1 of 1 File(s)

SCRIPT_Table_Size_Info(2000-2008)10-Oct-2010(FINAL).sql
image002.jpeg


#3

[ Attachment(s) from Hank_Freeman included below]

To all:

I experienced a system issue( mother board was failing) when I thought I was
sending out the latest version on my attached script, via email. I can not find
that it went out, so I am sending it again.

If you have received this script with a previous date you should know this this
one has been updated …

Sorry if I have duplicated this email…

I have updated this to now work with multiple schemas like what you find in
Adventureworks.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

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’.

Attachment(s) from Hank_Freeman

1 of 1 File(s)

SCRIPT_Table_Size_Info(2000-2008)10-Oct-2010(FINAL).sql
image001.gif