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