Hello experts,
I have faced a weired problem, let me give you some background first.
I was loading data into a table after truncating it in SQL Sever 2008 R2.
After truncating the table, I ran the below statement to verify
SELECT COUNT(1)
FROM dbo.TableName
To my surprise it returned result as
1, to verify I ran
SELECT *
FROM dbo.TableName
this returned no records.
Then again I ran
SELECT COUNT(1)
FROM dbo.TableName
this time I got result as 0.
Since I had truncated some more tables, I investigated this by analysing the execution plan.
First time SQL Server used constant scan operator to return results, which gave me the wrong value of 1. After running the SELECT * command, execution plan changed for COUNT(1) and it used a table scan operator which gave correct result.
This is the first time I encountered this kind of issue.
Anyone aware of this behaviour? Is it a bug in 2008 R2 or I missed something?