So after you change it to an inner join , are you still getting different plans ?

Although your solution may work perfectly well, you are not thinking about scale.

What happens when you get more than 10 rows ?

Compare your solution to a simple select as show...

Maybe useless to you 🙂 but the optimizer is chosing to do something different because of it.

What about the outer join ? Is that what is intended ?

Hmm,

There is a slight difference in the code , the 'fast' version includes the extra clause @pJobId > 0

The optimizer is probably promoting this to the JobWorkJob.JobOID which give a...

Hi ganotedp,

The article does state

If there were ties and we wanted each of them treated the same then we would use the DENSE_RANK function...

Since SQL Server is generally quite poor a string manipulation, the obvious thing to do would be to move this logic into the layer that is most approriate ie the...

Hi,

I would be careful about making any form of 'X is faster than Y' statements , ever!

In this case i have found ,annecdotally, that PIVOT has a relatively high startup...

Is there a large result set ?

SSMS can be slow to consume the results, this could cause you to be interpretting the metrics wrong.

Turn on 'Discard Results' in options and...

It is certainly true that two plans will be built ?

Are the plans built using exactly the same input parameters ?

If not that could explain the difference, plan a...

Looks like you have a leading space in ' BENEFITS N/A'

The query is working fine (probably) 🙂

Hi,

Try this article on Catch All Queries

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

CREATE TYPE tabletype_StationLists AS TABLE (

StationID varchar(40) NOT NULL PRIMARY KEY)

go

DECLARE @Stations tabletype_StationLists

INSERT INTO @Stations(StationID)

Values ('DAP'),('CAP')

DECLARE @StartDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 5)

DECLARE @EndDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6)

DECLARE...

Didnt want to "Do a...

Why not normalize the CARAC values to a different table , add a 'SortOrder' Column and Bobs you uncle ?

Tabled valued parameters are your friend here