I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view which has an INSTEAD OF UPDATE trigger where I can intercept and fully control the update. I am very aware that cursors are evil, but I am forced to work with them because I cannot modify the source programs.
When the UPDATE...WHERE CURRENT OF statement executes, it's performing a clustered index scan rather than a seek to locate the record that the cursor is currently pointing at. I cannot determine why the optimizer is doing a scan when it could be doing a seek. I believe that it's a combination of cursor + view + instead of update trigger that is causing the problem, because if I remove any one of these 3 variables from my tests, the index seek is used properly.
Note that it doesn't matter how many rows are in the table; the optimizer always uses a scan, according to the execution plan. I've even removed all of the logic within the trigger to further simplify the test.
Here's some simple code to recreate the issue:
CREATE TABLE [dbo].[Person]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](40) NULL, CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO CREATE VIEW [dbo].[vPerson] AS SELECT Id, Name FROM dbo.Person GO CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson] INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON END GO INSERT INTO Person VALUES('John Doe'); GO --This will use a seek UPDATE dbo.vPerson SET Name = 'Jane Doe' WHERE Id = 1 GO --This will not use a seek DECLARE c CURSOR FOR SELECT * FROM dbo.vPerson WHERE Id = 1 FOR UPDATE OPEN c FETCH NEXT FROM c UPDATE dbo.vPerson SET Name = 'Jane Doe' WHERE CURRENT OF c CLOSE c DEALLOCATE c GO
Any help or suggestions on things to try would be greatly appreciated.