Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
);
share|improve this question

migrated from stackoverflow.com Sep 25 '12 at 9:00

This question came from our site for professional and enthusiast programmers.

3  
"cursor is populated with a select statement which queries only one row from a table" - but I bet it's written with the usual cursor loop after the fetch (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:50
1  
See Halloween Problem –  Damien_The_Unbeliever Sep 25 '12 at 6:54
3  
Well, you're able to perform such checks - we're not, because you haven't shown us anything - no table definitions, no stored procedure code. We have to resort to guessing in such a case. But it certainly sounds like a Halloween Problem. –  Damien_The_Unbeliever Sep 25 '12 at 7:22
3  
Please edit your question with this information. It is impossible to read in the comments. –  Martin Smith Sep 25 '12 at 9:20
2  
Please share the DECLARE CURSOR statement and WHILE loop code as well. –  Paul White Sep 25 '12 at 11:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.