SQL Server Select and Update in the same statement?

Question

Hello!

I have an SQL Query like this:

SELECT TOP (@TopType) AdvertID, IsAdvertImage, AdvertData
FROM Adverts
WHERE AdvertSize = @AdvertSize
ORDER BY NEWID()

Each row in the table Adverts also has a column called Views, is it possible to also increase views with 1 for the banners that has been fetched? So I set TopType to 4 and get 4 banners, these banners have their views increased with 1 so that I can keep track of how many times a banner has been displayed.

Answer 1

WITH cte AS (
 SELECT TOP (@TopType) 
 AdvertID, IsAdvertImage, AdvertData, Views
 FROM Adverts
 WHERE AdvertSize = @AdvertSize
 ORDER BY NEWID())
UPDATE cte 
 SET Views = Views + 1
OUTPUT INSERTED.AdvertID
 , INSERTED.IsAdvertImage
 , INSERTED.AdvertData;

But ORDER BY NEWID() is going to perform horrible. Even with TOP (1) still has to scan the entire table, spool the selected columns in tempdb, sort, the take TOP from that. And is also going to be an update conflict bonanza between the Views = Views + 1 X-lock and the full scan S-locks...