Toad World® Forums

Toad for SQL Server 6.7.0.176 - Problem with row count

I attach a screenshot for details :

As you can see I execute a simple - select count (*) from TABLE_NAME.

The results show me the row counts equal to 6932.

If you see in the object Explorer, same table, row counts is equal to 6695.

What's the real value of row counts ?

From my point of view the first, 6932, but this is very strange or I never set correctly some parameters.

I stay tune for your consideration.

Best regards,

Sergio

Hello Sergio,

Thank you for your post - we will try to reproduce the problem on our side and let you know about our findings.

-Martin

Hello Martin,

many thank’s for your support.

For your information I use SQL Server 2012 Developer edition with service pack 3. The problem is present too if I try the same statement on a SQL Server 2012 Advanced Express Edition 2012 with service pack 3.

I never try with another version of SQL Server, for example SQL Server 2014,etc,etc,…

I hope this can be helpfull for you and I stay tune for your consideration.

Best regards,

Sergio

Can you please confirm which row count is correct using SQL Server Management studio (hope you have it installed)? I believe running the same query will do just fine.

Hi Martin,

if I open Microsoft SSMS and execute the sql select count() from TABLE_NAME I obtain the same result like execute the same select count() etc,etc, in last version of TOAD 6.7 .

Select count(*) from TABLE_NAME works fine both in SSMS and TOAD 6.7 .

Best regards,

Sergio

Hi Martin,

last beta version of Toad for SQL Server Beta 6.8.0.636 (32 bit) and the problem is still present.

Have you some news about this bug ?

I stay tune for your answer,

Best regards,

Sergio

Hi Martin,

First of all sorry for so late response.

For calculating row count Toad uses the same script for getting row count as below
SELECT sc.name +``'.'``+ ta.name``TableName
,``SUM``(``pa.rows``) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.``OBJECT_ID = ta.``OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (``1``,``0``)
GROUP BY sc.name``,``ta.name
ORDER BY SUM``(``pa.rows``) DESC

It’s common way to get row count for collection
As you can see Toad uses sum by sys.partitions.rows column. If you go to msdn you will see description for this table - ‘Indicates the approximate number of rows in this partition’
So, unfortunately it’s approximate row count

Best regards,
Vasiliy

Hello Vasiliy,

many thank’s for your answer.

Now the situation is more clear.

At this point I uncheck option to show rows count in object explorer because this generate misunderstanding, and I continue to count numbers of records in the classical mode with select count(*) on tablename.

This option is not useful for me.

Best regards,

Sergio

Hello Sergio,

but you know that select Count(*) produce a lot of io traffic ?

This because SQL Server made a full table scan and he blocks the full table for a read scan. I think, that is not a valid way in a production Environment.

For more Information:

https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/

or

https://sqlperformance.com/2014/10/t-sql-queries/bad-habits-count-the-hard-way

greetings

Henrik

Hello Henrik,

many thank’s for your clarification.

I’m agree with you about select count(*) from…, etc,etc,…

Usually I don’t use - select count(*) … - statement, but last year, I’ve used it, on a small db I notice the difference and at this point I don’t understand what is the real value of number of records.

I delete all records from a table and after re-import it via import wizard. I exported about 6000 records from source and I want to have the same records number in the destination.

If I see row count property I have 6000 records, if I execute select count(*)… I have 6150 records. At this point I don’t understand why ?

This generate the problem.

At the end of the story I disable - row count - property in object explorer and occasionally i use select count(*) to count my records.

Many thanjk’s for your reply.

Best regards,

Sergio