Hi Geeks,
SQL Server eager spool operator takes the entire input then store each row in a hidden object in temdb database. This operator reads all rows from the previous operator at one time and is a logical operator.
This is a blocking operator, which means it needs to read all the rows from its input then process all the rows and then return data. It means, unless all the rows are processed we don’t have access to the data. As we talk about a blocking operator, I take this opportunity to specify that operators like Nested Loop Joins works bit different than a blocking operator i.e. they reads one row at a time, process it then return the output for each row.
The following script will create a table with three columns ProdID, ProdName, and Price and then populate the table with sample data.
USE [tempdb]
SET NOCOUNT ON
IF OBJECT_ID('Inventory') IS NOT NULL
DROP TABLE Inventory
GO
CREATE TABLE Inventory(ProdID Int IDENTITY(1,1) PRIMARY KEY,
ProdName VarChar(50),
Price Numeric(20,2));
GO
DECLARE @Var1 SmallInt
SET @Var1 = 0
WHILE @Var1 < 1500
BEGIN
INSERT INTO Inventory(ProdName, Price)
SELECT 'TestProd', ABS(CONVERT(Numeric(20,2),(CHECKSUM(NEWID()) / 10000000.0)))
SET @Var1 = @Var1 + 1
END
CREATE NONCLUSTERED INDEX IX_Price ON Inventory(Price)
GO
Data looks like following after executing above script.
Let’s say we need to update Price of a product whose ID is 5 to 220.
USE [tempdb] UPDATE Inventory SET Price = 220.00 WHERE ProdID = 5
Now all updates are performed in two steps, first to identify the rows to be updated using a read cursor, then update the value using a write cursor. So, first execution of the above query is to locate the rows to be updated and the second step is to update the values. Point here is, if there is an index on the column to be updated, then index should also be updated and all non-clustered index needs to be updated when a value changes that has been used as an index.
Problem here is, when SQL Server choses to read this data using an index during first step this value might have been changed when it updates the value (second step). Let’s say I want to increase product price by 10% where the price is less than 100.
USE [tempdb] UPDATE Inventory SET Price = Price * 1.1 FROM Inventory WHERE Price < 100.00
From the above plan, in the clustered index scan SQL Server selects all rows to be updated and in clustered index update it updated both clustered index as well as non-clustered index IX_Price. With this logic, the first selected row is 2 and once it is updated then SQL Server selects 3 and so on.
Before Update
And if SQL Server continues this logic, all rows will be updated correctly. Tomorrow, we are going to explore what happens if SQL Server choose to select non-clustered index to read the rows to be updated.
Stay tuned.
Regards,
Kanchan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook
