Would you mind posting the code. That would help us to look at further.

Whats the variable datatype?

dfine (10/18/2011)

---

Yes, I know that column included in the select query also consider by Query Optimizer.

however, if you see the second query's excution plan it uses the non clustered...

There are no indexes satisfying your second query.

So the SMART query optimizer uses the productid index as long as which seems to be a better possible index key and...

Please configure the perfmon counters for the latency and see the result.

Object: SQLServer:SQL Statistics

Counter:...

Page split will cause performance issues for both insert and select. SQL has to...

Quite Curious....What do you mean by "shortorder"???

Try this....

select type,(sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB

from sys.dm_os_memory_cache_counters

Group by type

Order by plan_cache_in_GB desc

To me, Nothing is free. Do only for required tables...

You may have to follow certain pattern to help you better!!!

Otherwise people wont take effort to create tables and data for you which would be areal example.

Answering your question,

you have...

Please have a look at this blog too...

http://sqlwithmanoj.wordpress.com/2011/03/02/clustered-vs-nonclustered-indexes-and-data-sorting/

If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine...

There is no gurantee for this...

I guess, you can achieve by the below way,

SELECT

CASE IsNull(COL1,'') WHEN '' THEN 'Is Null'

WHEN '0' THEN 'Is Zero'

WHEN '1' THEN 'Is One'

WHEN '2'...

Split the query into multiple queries to remove the LEFT join. That would do the trick here.