Welcome to plansplaining, part 32, where we once more look at cursors. We already discussed the basics, and looked at static cursors and dynamic cursors. It is now time to cast our gaze upon the keyset cursor. The keyset cursor is sort of in between the static cursor (which presents a snapshot of the data is at was when the cursor was opened and disregards future changes) and the dynamic cursor (that always shows the current data). To be precise, a keyset cursor sees changes made to already existing rows, but does not see new rows that were added after…

We’re already at part 31 of the plansplaining series. And this is also the third part in my discussion of execution plans for cursors. After explaining the basics, and after diving into static cursors, it is now time to investigate dynamic cursors. As a quick reminder, recall that a static cursor presents data as it was when the cursor was opened (and does so by simply saving a snapshot of that data in tempdb), whereas a dynamic cursor is supposed to see all changes that are committed while the cursor is open. Let’s see how this change in semantics affects…

In part 30 of the plansplaining series, we’ll continue our discussion of cursor processing. I recommend first reading the previous post, where I explain all the necessary basics. Sample query Throughout the series, I will keep using the same sample query, that grabs some sales and product data for sales of more than 10 units, within a specified range of sales orders. SELECT soh.SalesOrderID, soh.OrderDate, sod.SalesOrderDetailID, sod.OrderQty, sod.ProductID, p.ProductID, p.Name FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = soh.SalesOrderID INNER JOIN Production.Product AS p ON p.ProductID = sod.ProductID WHERE soh.SalesOrderID BETWEEN 69401 AND 69410 AND sod.OrderQty…

This is already the 29th part of the plansplaining series, where I look in detail at interesting examples of execution plans in SQL Server. With this part, I’ll kick off another mini-series, this time on cursor processing Don’t use cursors! And yes, I know the mantra. Do not use cursors. They are slow. There is (almost) always a faster set-based alternative available. So why would I even waste blog space on cursors, when the only smart thing to do is to rip them out and replace them with a set-based alternative? Well, there are, in fact, many reasons. The “almost”…

In part twenty-eight of the plansplaining series, I’ll wrap up the six-part mini-series on window functions. After covering the basics, fast-track optimization, window frames ending at UNBOUNDED FOLLOWING, window frames specified with RANGE instead of ROWS, and LAG and LEAD, we will look at the LAST_VALUE and FIRST_VALUE analytical functions, and find that a function we would have expected to be available as an internal aggregate function does not exist at all! We’ll also find out how SQL Server works around that. LAST_VALUE We already encountered the LAST_VALUE internal aggregate function in the previous post, where it is used to…