Because now , sqlserver can use an index.

the ltrim ( rtrim are stopping that from happening.

This is the difference between Sargable and Non-Sargable.

Using a function (there are a few exceptions)...

Are they doing the same quantity of physical reads ?

--EDIT, also go back to the developers / designers / third party provider and tell them that this is an unacceptable...

Try investigating the ranking functions.

http://www.sqlservercentral.com/articles/T-SQL/69717/

Count also supports the over clause.

So that gives you the tripid and the stopnumber of the duplicated data.

Why is that not what you need ?

The plans are the same 'shape' , there is a slight difference in the number of estimated rows (not much though).

The Difference in your io statistics you quoted

--old machine io...

Please post the execution plans as per

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Hi ,

please see this article , essential reading imo

http://www.karaszi.com/SQLServer/info_datetime.asp

You do seem to of overcompilcated this somewhat, have you used the "having" clause before ?

select id,Stopnumber,count(*)

from tbl_Details

group by id,Stopnumber

having count(*) >1

Hi , Sorry i am not understanding your issue. Can you restate it ?

With proper indexing , it shouldnt be to bad

Alternatively comparing the min and max values can give you the same results

Create table BigTable

(

id integer identity,

RId integer not null

)

go

create index...

try using "count(*) over (partition by...."

Curious , since both queries include the non-sargable expression ltrim(rtrim(<value>)), both should result in then same , very poorly performing , execution plan.

Can you post both the plans ?

You may...

GPO (10/7/2010)

---

What's the point of ten posts a day that say nothing more than "Nice post Jeff", or "Good one Gail" or whatever? It just adds noise to the...

roelofsleroux (10/7/2010)

---

So basicly I assume what you are saying is to try and stay away from optional parameters and the "catch-all" saga. But if I have to do "catch-all" (for...

ALTER PROCEDURE [dbo].[uspListExecutionTransaction]

(@SessionKey UniqueIdentifier) AS BEGIN

SELECT * FROM EXECS.tblExecutionTransaction WHERE SessionKey = @SessionKey

END

Assuming that EXECS.tblExecutionTransaction is a table with an index on sessionkey, then caching of plans is exactly...