Msg 16943, Level 16, State 4, Line 16
Could not complete cursor operation because the table schema changed after the cursor was declared.
Adding or removing non clustered indexes seems like a low risk performance tuning operation, right? Well, under normal circumstances it is true as you are not modifying the underlying data nor changing the table schema. I found out the hard way today that it could cause rather nasty errors like the one above if it is performed while there is a cursor loop running in another session.
I will show you in a quick demo. Let’s start by creating a table and inserting some data.
create table temp ( id int not null identity(1, 1) primary key , data varchar(10) not null default('test') ) go insert temp default values go 50
We will then kick off a simple cursor looping through the table.
declare cur cursor for select id from temp open cur declare @id int fetch cur into @id while @@fetch_status = 0 begin print @id waitfor delay '00:00:01' fetch cur into @id end close cur deallocate cur
While the session is running, we will pretend that we have no knowledge of the running cursor code and we’ve identified a performance tuning opportunity to add a non clustered index to the table. We will do just that but in a separate session window.
create index idx_temp on temp (data)
Once the index is created, we then switch back to the session window with the cursor and should expect to see the error in the Messages tab.
1 2 3 4 5 Msg 16943, Level 16, State 4, Line 16 Could not complete cursor operation because the table schema changed after the cursor was declared.
So there you have it, I’ve just demonstrated that no matter how seemingly harmless an operation is, there is always a potential risk for unintended side effects.