I understand, but the first query gives me an incrementing (the inner join)...

but anyway, you're right - sounds like this is somewhere we should be exercising "equine medicine" to it....

Are you looking for some way to identify packed data? Mainframe data stored as hex with a subsequent sign of some kind?

You might need to read some of these...

It's odd - because the CTE is giving me nothing but 1's in the ENGCount (not incrementing).

And I don't doubt you're not since I've seen you use CTE's a lot,...

Or, you might care to FORCE the recompile each time it's run, so that it will optimize for the branch that will actually run....

That's adding a WITH RECOMPILE to the...

True - but if records with ID 40-60 are gone, then record with ID=100 isn't the 100th record anymore...

Sorry to jump in AFTER the final post - but unless I'm missing something - the CTE code posted is not even equivalent to either the cross join or the...

You need to use the CROSS APPLY predicate on the function call - that should get you what you want.

CROSS APPLY allows you to create an "implicit join" between a...

That was the same line of thinking I used when I threw in the ROW_NUMBER() solution. At least you know you won't get a gap that way.

you could also use a table with said 15 values. With an index I'd imagine that would be the fastest of all (with an inner join).

Nothing jumps out - but have you tried casting the date field to a varchar or a char? I've had to do that on occasion to get the conversion not...

My example is not the greatest, however the part that still stands is that it won't allow the use of non-shared or non-clustered resources.

If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track...

A picture is worth 1000 words....

SQL Injection[/url]

If the column is an identity field, you can't insert a specific value into that field unless you set the identity_insert flag to on for the specific table.

The preferred method...

We could help a whole lot more effectively if you were to post the body of the stored procedure (the one giving you the error). That error is one...