Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 25th, 2006, 02:03 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default Row count Discrepancy

Row Count Discrepancy

When I run the following statement
Select count(*) from table_x
I get row count value of 391 rows.

When I use SQL Enterprise Manager and right click on the table and select properties, I see a rowcount of 463.

I ran dbcc checkdb, newallow, checktable, sp_updatestats, checkcatalog.

Still show the row count discrepancy exists.

SQL2000 SP4, Windows2003 SP1

Anyone see this before? Thanks, BPH


 
Old January 25th, 2006, 02:10 PM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Run DBCC UPDATEUSAGE and try again

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
 
Old January 25th, 2006, 02:47 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Good Effort. Didn't solve the issue. I'm stumped.
 
Old January 25th, 2006, 02:56 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

How many records do you see when execute the following:

SELECT * FROM table_x

Do you get 391 or 463?

 
Old January 25th, 2006, 03:00 PM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

are you doing count(*) or count(field)

look at this difference

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)


SELECT COUNT(*) FROM testnulls --3
SELECT count(id) FROM testnulls --2



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/
 
Old January 25th, 2006, 03:37 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I'm using select count(*) from tablex and get 391.

463 comes from properties in SQL EM

 
Old January 25th, 2006, 03:39 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I exported the data to a test table, and then the properties and the select coutn(*) are the same at 391.

Wierd.

 
Old January 25th, 2006, 05:59 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Your table is correct, and you can try to save it by using DBCC DBREINDEX (Indexes may be messed up). Other than that, I think it's a goner.

 
Old January 25th, 2006, 06:00 PM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Sorry, I meant to say "Your original table is corrupt"...

 
Old January 26th, 2006, 02:04 PM
bph bph is offline
Friend of Wrox
 
Join Date: Dec 2003
Posts: 102
Thanks: 2
Thanked 0 Times in 0 Posts
Default

You're probably right. thanks for the help everyone.






Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLDataSource row count areid ASP.NET 2.0 Basics 3 June 5th, 2010 06:02 AM
Row Count in each table ayazhoda Access VBA 0 April 15th, 2008 04:31 PM
advance count (2 counts in a row) avishni SQL Language 3 November 23rd, 2006 05:04 AM
SELECT COUNT as a ROW?? t0ny SQL Language 3 December 8th, 2004 05:31 AM
Row Count Walden SQL Server 2000 6 June 9th, 2003 12:47 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.