This part of your code causes a full table scan regardless of the TOP 52 because of the ORDER BY in the ROW_NUMBER(). Since you're doing the ORDER BY on the StockCode table, it also does a shedload of unnecessary reads. Also, there is no guarantee that you'll return 52 rows because there's no guarantee that the Data.Stock table will start with or be maintained in such a fashion as to always have at least 52 rows in it because it's a user table rather than a utility or fix system table.
SELECT TOP 52 ROW_NUMBER() OVER (ORDER BY StockCode) AS Num
This part of your code causes a full table scan because the WHERE clause is non-SARGable as well as there being no indexes on the table.
SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,DatePart(wk, SaleDate) AS WeekNo
WHERE YEAR(SaleDate) = 2016
GROUP BY DatePart(wk, SaleDate)
Also, you should always use the 2 part naming convention both in the FROM clause (which you've dome) and the SELECT clause of a joined select (which you've not done).
Also, what is a "week"? DATEPART(wk) will only return 2 days for the first week of Jan 2016. The only reason why it returns 7 days for the last week of 2016 is because you get lucky and average of once every 7 years.
Last but not least, your sample data model in the PrestigeCars database is devoid of any and all Primary Keys and indexes.
is pronounced "ree-bar
" and is a "Modenism
" for R
ow.First step towards the paradigm shift of writing Set Based code:________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
Helpful Links:How to post code problemsHow to Post Performance ProblemsCreate a Tally Function (fnTally)