Indexing and looking for a cursor in an update statement

 From this page:

http://www.artfulsoftware.com/infotree/qrytip.php?id=531&m=0

I wanted to see if, in SQL Server, this same mySQL query 

1. Could be reproduced
2. Could be sped up with an index
3. Would show up as a cursor in the estimated execution plan for the update statement 

1. Reproduction

So, yes, this is a fairly simple script to reproduce.

set nocount on; use enron; go if object_id('dbo.photos') is null create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT); else truncate table dbo.photos; go if object_id('dbo.votes') is null create table votes(id INT identity(1,1) primary key, userID INT, photoID INT); else truncate table dbo.votes; go DECLARE @counter INT; SET @counter = 1; WHILE @counter < 500 BEGIN insert into dbo.photos(photo) values(rand() * 1000) SET @counter = @counter + 1 END; GO DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256); SET @counter = 1; WHILE @counter < 50000 BEGIN SELECT @userid = rand() * 100; select @photoid = rand() * 1000; if (@photoid between 0 and 500) insert into votes(userid, photoid) values (@userid, @photoid) SET @counter = @counter + 1; END; GO

However, in my version, I loaded a larger data set into the tables, as I wanted to have enough data to get a measureable difference when applying the index in step two.

Note also, that in my version, the values are randomly generated, but controlled, so they can only be inserted if they match a photo that we expect to actually have.

2. Indexing

Yes, the statement can be sped up with an index.

Here's the properties of the script, before index application:

Here's after applying the index:

It's a third of the cost. Win!

3. Cursor

There are loops - which is expected - but SQL Server doesn't dissolve into an explicit cursor to handle the update.