Unexpected table scan for id != id

Question

One application causes a heavy load on our Sql Server 2005 database. We don't control the application that runs this query hundres of times a minute:

select id,col1,col2,col3 from table where id != id

Note the id != id, meaning a row is not equal to itself. Unsurprisingly, the result is always no rows found. However, Sql Server does a clustered index scan scan every time it runs this query!

The id column is defined as:

varchar(15) not null primary key

The query plan shows a huge number for "Estimated Number of Rows". Does anyone have an idea why Sql Server needs the table scan to figure out the obvious?

Answer 1

I would fake this query out... abstract away with a view, and dupe the query.

Rename your existing table 'table' to 'table_org' or something else, and create a view like this:

CREATE VIEW table
AS
SELECT * FROM table_org
WHERE id='BOGUSKEY'

Now, you should get your 1 scan through the table on the primary key, and it should (like the original query) find nothing. The application knows none the wiser...

Answer 2

You're biggest problem isn't the table scan. Your two biggest problems are:

• You have an absolutely useless query running 100's of times per minute against your database. My guess BTW, is that the query is actually trying to get the column names from the table as Marc Gravell suggests.

and more importantly:

• You don't have control over who or what is accessing your database.

That second problem especially is going to most likely cause you endless headaches. Assuming that you are part of the data team in your organization (since you're the one trying to solve this problem), you really should be looking to make the organizational changes necessary to do your job.

Good luck!