In the SSWUG Virtual Conference this morning, in the chat room following my Kill the Cursor session, Boris asked, "How can I find the last five events for every device, without a cursor?"
Here's the script, Boris. Essentially it uses the Over clause with a Partition by in a subquery to segment by device and number each row. The outer query then has a where clause to restrict the results to the last five.
Boris, I'm interested in the performance gains you see with the set-based query over the cursor. Once you test this will you please post your findings? thanks,
USE TempDB
CREATE TABLE Events (
EventID INT IDENTITY NOT NULL PRIMARY KEY,
Device CHAR(2),
EventTime DATETIME,
Data CHAR(10)
)
GO
INSERT Events( Device, EventTime, Data)
VALUES ('a', GETDATE(), 'asd')
INSERT Events( Device, EventTime, Data)
VALUES ('b', GETDATE(), 'qwe')
INSERT Events( Device, EventTime, Data)
VALUES ('c', GETDATE(), 'sdf')
-- waitfor delay '00:00:00.100'
GO 1000
INSERT Events( Device, EventTime, Data)
VALUES ('a', GETDATE(), 'last five')
INSERT Events( Device, EventTime, Data)
VALUES ('b', GETDATE(), 'last five')
INSERT Events( Device, EventTime, Data)
VALUES ('c', GETDATE(), 'last five')
WAITFOR delay '00:00:00.100'
GO 5
SELECT *
FROM (
SELECT EventID, Device, EventTime,
Row_Number() OVER(partition BY device
ORDER BY eventtime DESC) AS RowNum
FROM Events) sq
WHERE RowNum <= 5