CODEWITHSUNDEEP
sqlsql-servert-sql
Abhineet
Abhineet

Reputation: 403

SELECT * Returns no rows but, SELECT Count(*) returns non zero value

I run following queries :

a. select * from TABLE_TEMP

The query is executed successfully but, no rows are returned. the returned dataset is empty and contains 0 rows. The execution plan of this query can be downloaded from : Execution Plan Query a

b. select count(*) from TABLE_TEMP

This query is also executed successfully but, it returns some finite value The execution plan of this query can be downloaded from : Execution Plan Query b

Can you provide me any pointers to resolve this issue.

PFB the screenshot: Weird behavior

Upvotes: 4

Views: 2210

Answers (2)

ta.speot.is
ta.speot.is

Reputation: 27214

You might be suffering from some sort of corruption in your database. Run DBCC CHECKDB (or DBCC CHECKTABLE) after familiarising yourself with the documentation. In particular, the section on index checking with respect to database compatibility levels and:

In earlier versions of SQL Server, the values for the per-table and per-index row count and page counts can become incorrect. Under certain circumstances, one or more of these values might even become negative. In SQL Server 2005 and later, these values are always maintained correctly. Therefore, databases that are created on SQL Server 2005 and later should never contain incorrect counts; however, databases that are upgraded to SQL Server 2005 and later might.

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 57

Count is Function in SQL it returns Int value i.e. how many records affected.

Upvotes: -1

Related Questions