How to get the Correct row Count for a table - TIP_002 [1 Attachment]
[ 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
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
[ 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
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
