Constant scan in execution plan retrives incorrect results

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

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

SQL

SELECT COUNT(1) 
FROM dbo.TableName

To my surprise it returned result as 1, to verify I ran

SQL

SELECT *
FROM dbo.TableName

this returned no records.

Then again I ran

SQL

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?

Posted 21-Mar-15 19:36pm

Saral S Stalin

Updated 21-Mar-15 19:40pm

v2

Add a Solution

Comments

phil.o 22-Mar-15 4:54am

If you count on your primary key field instead of const 1, do you get the same issue?
SELECT COUNT(TablePK) FROM TableName

Saral S Stalin 22-Mar-15 5:12am

The issue is no longer replicable. But still I want to know how such wrong results can come when the execution plan is different.

phil.o 22-Mar-15 5:18am

Hard to say; maybe some caching mechanism in action.
What I know is that the COUNT aggregation function should be used with * (SELECT COUNT(*)) or a column name (SELECT COUNT(yourcolumn)). I cannot see the meaning using an integer constant in this place. Do weird queries, expect weird results ;)

Saral S Stalin 22-Mar-15 5:22am

In my early days of programming I was asked to put integer constant if we just require row count, since it will improve the performance by not making sql server to expand '*' to get the columns or counting some column. I never experimently validated this, but followed this convention from that day on-wards.

Just to add the issue was replicable with COUNT(*) as well that time.

phil.o 22-Mar-15 5:40am

I agree with you on the use of *. I personnaly never use it, nor in count queries, neither in select ones. I always count on the PK column, taking advantage of the usage of the clustered index.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)