SQL Server Bug?

Question

Hi There,

I am writing a function to process some CSV data. This is what I have written so far...

CREATE FUNCTION dbo.FGetCommaSeperatedValues(@csv as text)
RETURNS @tblIds TABLE(id int,csvlength int) 
AS
BEGIN
    DECLARE @csvlength AS int
    --SET @csvlength = datalength(@csv);
    SET @csvlength = 7685
    DECLARE @currentIndex AS int
    SET @currentIndex = 0
     WHILE @currentIndex < @csvlength
      BEGIN
       --INSERT INTO @tblIds SELECT @currentIndex,@csvlength
       INSERT INTO @tblIds (id,csvlength) values (@currentIndex,@csvlength)
       SET @currentIndex = @currentIndex+1
      END
    RETURN
END

My issue is that when I execute the function, using the following command...

SELECT * FROM FGetCommaSeperatedValues('')

The table returned does not show the results that I expect.

Everything is fine until around row 3624 or so (as expected, id column increments by 1) Then the values increment erratically.

My colleague tested this code in SQL Server 2008 and everything works fine. This appears to be isolated to SQL server 2000.

Does anyone know of this bug, and/or it's resolution?

Answer 1

In order to gaurantee sort order in SQL Server you must use the ORDER BY clause.

ORDER BY CLAUSE