Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization.
A stored procedure is written for our application which contains a cursor in it. The cursor is populated with a select statement which queries only one row from a table.
But, when the stored procedure is executed, the while loop within the cursor begins to run infinite times and we received timeout error in our application. We are wondering and struggled to find the root cause for the issue. Even after doing several changes it never worked.
As a R & D, one of our Application team user dropped the clustered index from the table. It happened like a miracle, the cursor executed only once. I am totally confused, the existence of clustered index makes the loop to run infinite times and dropping of clustered index makes the cursor to run properly.
Is it a bug on SQL Server 2008 R2? If anyone knows the answer can you please share it?
CREATE TABLE [dbo].[BEMProcessorList]
(
[Claim_Code] [nvarchar](200) NULL,
[Employee_Id] [int] NULL,
[Order] [nchar](2) NULL,
[role] [nvarchar](20) NULL,
[Approved] [char](1) NULL,
[Is_Present] [char](1) NULL,
[ApprovalLimit] [nvarchar](200) NULL,
[Id] [int] IDENTITY(1, 1) NOT NULL,
[Rec_Req] [char](1) NULL,
[Rec_Man] [char](1) NULL
);
CREATE CLUSTERED INDEX [IX_CEA]
ON [dbo].[BEMProcessorList]
(
[Claim_Code] ASC,
[Employee_Id] ASC,
[Approved] ASC
);
WHILE @@FETCH_STATUS=0
), it's not a static cursor, and an update is occurring against a column which forms part of the clustered index. – Damien_The_Unbeliever Sep 25 '12 at 6:50DECLARE CURSOR
statement andWHILE
loop code as well. – Paul White Sep 25 '12 at 11:41