3

I have a SQL Cursor that's behaving incorrectly as soon as we added an index to a table. Here's a trimmed-down example of the query causing issue:

DECLARE @CUR_CRDATE     NVARCHAR(8) 

DECLARE CUR_SRCHGT_UPDATE   CURSOR FOR
    SELECT  CRDATE  
    FROM    
        SRCHGT      
    WHERE   
        CUS_CD      = '0001031486'
    AND IO_GB       = '021'
    AND TOTDATE = '20150406'
    AND TOT_NO = '000001'
    AND TOT_NO_SEQ = '000003'
    AND REQ_YN = 'O'
FOR UPDATE OF JHSTAT

OPEN CUR_SRCHGT_UPDATE

FETCH NEXT FROM CUR_SRCHGT_UPDATE
INTO    @CUR_CRDATE

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'TEST'

    UPDATE  SRCHGT
    SET JHSTAT      = '005'
    WHERE CURRENT OF CUR_SRCHGT_UPDATE

    FETCH NEXT FROM CUR_SRCHGT_UPDATE
    INTO    @CUR_CRDATE
END

CLOSE   CUR_SRCHGT_UPDATE
DEALLOCATE  CUR_SRCHGT_UPDATE

The problem is that the above while statement always runs double the count of number of rows returned. For example, if the above select statement defined in the cursor returns one row, the while loop would run twice.

Strangely, this only happens when I add a particular index to the table. Here's what the index looks like:

CREATE NONCLUSTERED INDEX [IX_SRCHGT] ON [Samsonite].[SRCHGT]
(
    [IO_GB] ASC,
    [MAT_CD] ASC,
    [JCOLOR] ASC,
    [CANCEL_YN] ASC,
    [CUS_CD] ASC,
    [JHSTAT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I've never heard of indices causing issues on cursors like this. Is this a SQL Server bug or am I looking at the wrong places?

Addendum1:

SQL Server version is the following:

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Addendum 2:

Cursors are a necessity in our logic. We have no practical means to remove them.

Edit1:

Upon request, Here's the DDL for the table

USE [samsonite_20150406]
GO

CREATE TABLE [Samsonite].[SRCHGT](
    [CRDATE] [nvarchar](8) NOT NULL,
    [CUS_CD] [nvarchar](10) NOT NULL,
    [IO_GB] [nvarchar](6) NOT NULL,
    [CRESEQ] [nvarchar](10) NOT NULL,
    [CRESEQ_SEQ] [nvarchar](10) NOT NULL,
    [MAT_CD] [nvarchar](5) NOT NULL,
    [JCOLOR] [nvarchar](4) NOT NULL,
    [CANCEL_YN] [nvarchar](1) NOT NULL,
    [STYLE] [nvarchar](5) NULL,
    [SJCODE] [nvarchar](5) NULL,
    [JPSIZE] [nvarchar](30) NULL,
    [DANGUB] [nvarchar](1) NULL,
    [T_SDANGA] [numeric](7, 0) NULL CONSTRAINT [DF_SRCHGT_SDANGA1]  DEFAULT ((0)),
    [T_SUPPLY_AMT] [numeric](12, 0) NULL CONSTRAINT [DF_SRCHGT_SUPPLY_AMT1]  DEFAULT ((0)),
    [T_VAT_AMT] [numeric](12, 0) NULL CONSTRAINT [DF_SRCHGT_VAT_AMT1]  DEFAULT ((0)),
    [T_CREQTY] [numeric](5, 0) NULL CONSTRAINT [DF_SRCHGT_CREQTY1]  DEFAULT ((0)),
    [JDANGA] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__JDANGA__7C1770C8]  DEFAULT ((0)),
    [SDANGA] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__SDANGA__7D0B9501]  DEFAULT ((0)),
    [SUPPLY_AMT] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__SUPPLY_A__7DFFB93A]  DEFAULT ((0)),
    [VAT_AMT] [numeric](12, 0) NULL CONSTRAINT [DF__SRCHGT__VAT_AMT__7EF3DD73]  DEFAULT ((0)),
    [DESCNT] [numeric](5, 2) NULL,
    [MAJINN] [numeric](5, 2) NULL,
    [CREQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__CREQTY__7FE801AC]  DEFAULT ((0)),
    [CHGQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__CHGQTY__02C46E57]  DEFAULT ((0)),
    [YETQTY] [numeric](5, 0) NULL CONSTRAINT [DF__SRCHGT__YETQTY__03B89290]  DEFAULT ((0)),
    [JHSTAT] [nvarchar](3) NULL CONSTRAINT [DF__SRCHGT__JHSTAT__04ACB6C9]  DEFAULT ('001'),
    [CHDATE] [nvarchar](8) NULL,
    [REMARK] [nvarchar](200) NULL,
    [POS_REMARK] [nvarchar](50) NULL,
    [SAP_REMARK] [nvarchar](50) NULL,
    [REQ_YN] [nvarchar](1) NULL CONSTRAINT [DF_SRCHGT_REQ_YN]  DEFAULT ('O'),
    [ORDER_GB] [nvarchar](1) NULL,
    [TOTDATE] [nvarchar](8) NULL,
    [TOT_NO] [nvarchar](6) NULL,
    [TOT_NO_SEQ] [nvarchar](6) NULL,
    [TAX_FREE] [nvarchar](3) NULL CONSTRAINT [DF_SRCHGT_WEB_HS]  DEFAULT ('N'),
    [INDATE] [datetime] NULL,
    [SUDATE] [datetime] NULL,
    [EMPNUM] [nvarchar](10) NULL,
    [JEMEIP_YN] [nvarchar](1) NULL,
    [BEF_JHSTAT] [nvarchar](3) NULL,
 CONSTRAINT [PK_SRCHGT] PRIMARY KEY CLUSTERED 
(
    [CRDATE] ASC,
    [CUS_CD] ASC,
    [IO_GB] ASC,
    [CRESEQ] ASC,
    [CRESEQ_SEQ] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO
  • 1 - Because you don't specify what kind of cursor, this is the default 2 - when you update the column JHSTAT - then this is reflected in raw table and in the index (by rearrange it in the right place) 3 - cursor is re reading the new row ( the old row + updated column) – Sabin Bio Apr 6 '15 at 5:27
  • Can you reproduce this with the sample you provided? Can you add the DDL for the table involved? – Max Vernon Apr 6 '15 at 5:33
  • solution: try to update base on ID / primary key that is remain the same or use a STATIC / KEYSET type of cursor – Sabin Bio Apr 6 '15 at 5:33
  • 1
    @max you're responsible for avoiding the issue yourself with dynamic cursors. See this section by Ken Henderson books.google.co.uk/… – Martin Smith Apr 6 '15 at 9:55
  • 3
    @MartinSmith I did not know that. What an insidious problem. It is impossible to test for since it can appear out of nowhere in case of a plan change. This shows again how nice set-based DML is compared to cursors. The logical model is much simpler. – usr Apr 6 '15 at 10:08
2

Try with this modification:

Once with :

WITH (INDEX (IX_SRCHGT))   -- the present case
Once with 
WITH (INDEX (PK_SRCHGT)) 


DECLARE CUR_SRCHGT_UPDATE   CURSOR FOR
SELECT  CRDATE  
FROM    
    SRCHGT     WITH (INDEX (IX_SRCHGT)) 
    --SRCHGT     WITH (INDEX (PK_SRCHGT)) 
WHERE  

or use keyset as type of the Cursor

DECLARE CUR_SRCHGT_UPDATE   CURSOR KEYSET FOR
SELECT  CRDATE  
FROM    
   SRCHGT    
...
  • With index IX_SRCHGT produces same issue, but with index PK_SRCHGT eliminates it. However, this is still problematic because we can't possibly append every queries with a particular index we're trying to update with. Also, selecting the data with PK is very slow in our case. – l46kok Apr 6 '15 at 7:48
  • Then add the type of cursor like CURSOR KEYSET FOR – Sabin Bio Apr 6 '15 at 7:52
  • 1
    Wow.. thats bizzare that solved our issue. Time to look into what this KEYSET magic is. If you want to edit your answer, i'll accept it. – l46kok Apr 6 '15 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.