bugg (6/25/2015)
ChrisM@Work (6/25/2015)
Yes, or this which looks a little cleaner:
SELECTrn = ROW_NUMBER() OVER(PARTITION BY s.fruit ORDER BY s.Preference),
max_rn = COUNT(*) OVER(PARTITION BY s.fruit),
s.*,
p.OrderQuantity
INTO #StreamedData
FROM @Tmp s
INNER JOIN @purchase p
ON p.fruit = s.fruit
-- This unique clustered index will make the query sing.
CREATE UNIQUE CLUSTERED INDEX ucx_Helper ON #StreamedData (fruit, rn)
I really appreciate all the help I've had on this. I've read about how indexes on temp tables are one of the benefits of a temp table. This is probably a silly question but if this code is going to be called frequently, a few times a second would creating an index each time be okay?
There's no definitive answer to this. Some folks believe that temporary tables should rarely if ever be indexed. In this instance you are likely to find an improvement. You should always test, using a dataset representative of what you expect to have in production, and include the time taken for creating the index.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden