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
8
  • 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 B
    Apr 6, 2015 at 5:27
  • Can you reproduce this with the sample you provided? Can you add the DDL for the table involved?
    – Hannah Vernon
    Apr 6, 2015 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 B
    Apr 6, 2015 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/… Apr 6, 2015 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, 2015 at 10:08

1 Answer 1

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    
...
3
  • 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.
    – TtT23
    Apr 6, 2015 at 7:48
  • Then add the type of cursor like CURSOR KEYSET FOR
    – Sabin B
    Apr 6, 2015 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.
    – TtT23
    Apr 6, 2015 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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