Databases
StackOverflow2013
Postsdbo
POSQL server 2012 returns different results than SQL server 2008
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL server 2012 returns different results than SQL server 2008
    primarykey
    data
    text
    <p>I have a piece of script using cursor to sequence rows in a table. For example, the table looks like this, SSN, Kid_SSN, Kid_DOB, Seq# to list every person with 1 or more kids. I want to update Seq# to label each kid as 1,2,3... based on Date Of Birth. I used cursor for update to get it done successfully in SQL server 2008. My current trouble is that this same script wouldn't run as expected in sql server 2012. The problem is in SQL 2012 cursor fetch NEXT more than 1 row at a time. So question is where can I set the cursor fetch size? I searched around but came up no good answer. Anyone here can shed some light? thanks.</p> <p>Scrip looks like this:</p> <pre><code>DECLARE @SocialSecurity varchar(9), @PersonID int, @Dep_SSN varchar(9) DECLARE @LastName varchar(20), @FirstName varchar(20), @BirthDate datetime, @Number int DECLARE @ssn varchar(9) = '000000000' DECLARE @Mem int = 1 DECLARE cur cursor FOR SELECT * FROM kids FOR UPDATE OF Number; OPEN cur; FETCH NEXT FROM cur INTO @SocialSecurity, @PersonID, @Dep_SSN, @LastName, @FirstName, @BirthDate, @Number; WHILE @@FETCH_STATUS = 0 BEGIN IF @SocialSecurity = @ssn BEGIN UPDATE kids SET Number = @Mem+1 WHERE CURRENT OF cur SET @Mem = @Mem+1 END; ELSE BEGIN SET @ssn = @SocialSecurity SET @Mem = 1 END; FETCH NEXT FROM cur INTO @SocialSecurity, @PersonID, @Dep_SSN, @LastName, @FirstName, @BirthDate, @Number; END; CLOSE cur; DEALLOCATE cur; </code></pre> <p>More info on this problem. kids is a temp table generated by 'SELECT .. INTO' and a simplified set looks like this</p> <pre><code>SSN Kid_SSN DOB Seq# 123123123 987987987 1/1/2000 1 123123123 987987988 1/1/2003 1 123123125 890890890 2/3/2002 1 </code></pre> <p>So all seq# are initiated to 1. After going through the script above, I expect table kids to look like this</p> <pre><code>SSN Kid_SSN DOB Seq# 123123123 987987987 1/1/2000 1 123123123 987987988 1/1/2003 2 123123125 890890890 2/3/2002 1 </code></pre> <p>The script ran perfectly to achieve this on server 2008 R2, but not on server 2012. Furthermore, I found that it only updated row 88, 176 and so on, if applicable. That is why I think cursor fetch 88 rows at a time. But on server 2008 it apparently fetch 1 row at a time as I expected. Hope this will explain the problem I have. I want to force cursor fetching 1 row at a time to make it work on server 2012, though it's not efficient. Or, how to do sequencing without using cursor? Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKeith
    1. USY. Liu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL server 2012 returns different results than SQL server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODoes `kids` have a primary key? I think there is a MUCH more efficient way to write this query in about one line, and there is certainly a better way to write the cursor if you really want to stay with a cursor for some reason. Also, what kind of predictable result do you expect from your cursor? You didn't bother specifying an ORDER BY!
      singulars
      1. POSQL server 2012 returns different results than SQL server 2008
      1. USAaron Bertrand
    2. COI'm with @AaronBertrand here, don't understand how that ever worked, unless it was basically down to luck. All my cursors when straight across from 2008 to 2012 with no issues. I'm certain fetch next is not fetching two records, it's just not fetching them in the order it used to.
      singulars
      1. POSQL server 2012 returns different results than SQL server 2008
      1. USTony Hopkinson
    3. COFinally, do you plan to run this cursor across the entire table after every insert/update/delete? Since your "numbers" will get all messed up if, say, you are sorting by DOB and an older/younger kid joins. Much better to derive this result in a query than to store it, if all it is is some meaningless rank. If the numbers mean something, then describe what that is. As Tony suggested, this is not fetching the wrong number of rows, it's just not fetching in the order you probably expect.
      singulars
      1. POSQL server 2012 returns different results than SQL server 2008
      1. USAaron Bertrand
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload