From the very brief description you've given, it sounds as if:
LocationID
should be the lead clustering key.

If ItemID is unique within LocationID, then the full key should be:

Probably the typical way to do that using a CASE expression would be:

WHERE
1 = CASE @Fruit
  WHEN 'Apples' THEN CASE WHEN...

A loop is just as efficient, or close enough to it, for doing what you want here as any other method would be.

I suppose you avoid a loop...

1) I don't see why READPAST would lead to deadlocking.
2) I would think you do need a transaction to make the READPAST work properly.  You want the first UPDATE lock...

For a temporary "staging" table, where data just waits to be loaded into permanent table(s), no.

But all permanent tables should have at least one candidate key -- i.e....

I have very little time right now, but I want to point out at least a few things.

All tables: Verify the tables are in 2NF and 3NF.

Other...

If you'll (almost) always look up by certain key value(s), and those keys are inherently terrible for clustering (such as a guid), then cluster on all of them.  Yes, in...

My best guess is that it means an error occurred.  By default, a stored proc will return 0 (as the return code).  So that's typically taken as meaning a "good"...

By far the most important thing is to a logical data design before doing a physical one. [You can Google "logical data design" for more details.  A physical design is...

Just adding a clustered index shouldn't slow it down that much, unless perhaps the fillfactor is (way) too low.  Be sure to explicitly specify something like 95+% for the fillfactor...

I prefer the format "<table_name>__DF_<column_name>".  I think it's more useful to prefix constraints with the table name rather than "DF_".  Just because MS did it that way doesn't mean it's...

The second-highest score should always give you the desired value:

SELECT t.ID, t.Score1, t.Score2, t.Score3, ca1.FinalScore
FROM #tmp t
CROSS APPLY (
    SELECT TOP...

If the tables are not too large, you could even consider using CASCADE only when deliberately making changes, i.e., drop the FK constraints, recreate them as CASCADE, change the name(s),...

Just UNION (not UNION ALL) the two tables:


SELECT *
FROM #temp1
UNION
SELECT *
FROM #temp2
ORDER BY <column_name>