Posts

Welcome back to my plansplaining blog series, where I dissect execution plans in order to increase our understanding of how SQL Server processes specific queries. We’re at part 33 already, and I’m still looking at cursors. After discussing the basics, static cursors, dynamic cursors, and keyset cursors, I will now look at the last of the cursor types: the fast forward cursor. Fast forward cursor One of the things that has always bothered me about the fast forward cursor type is the shocking lack of documentation of what it does exactly. Okay, the name suggests that it is fast. But…

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”…