Kürzlich wurde der kummulative Update 7 (CU7) für den SQL Server 2005 SP3 veröffentlicht. Darin wird ein Problem beseitig, das man nur schwer entdecken kann, wenn man betroffen ist: Bei einem Cursor auf eine View oder eine Table-Valued-Fumction kann der Cursor falsche Ergebnisse liefern. Hier ein Repro, dass auch mit SQL Server 2008 funktioniert:
SET NOCOUNT ON;
go
USE tempdb
go
BEGIN TRY DROP TABLE dbo.tg_table1 END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP TABLE dbo.tg_table2 END TRY BEGIN CATCH END CATCH;
BEGIN TRY DROP FUNCTION dbo.f_PivotAndOuterJoin END TRY BEGIN CATCH END CATCH;
go
– create table 1
SELECT cy, value, type
INTO dbo.tg_table1
FROM ( SELECT 2002, 1, 3 UNION ALL
SELECT 2003, 1, 3 UNION ALL
SELECT 2004, 1, 3 UNION ALL
SELECT 2005, 1, 3 UNION ALL
SELECT 2006, 1, 3 UNION ALL
SELECT 2007, 1, 3 UNION ALL
SELECT 2008, 1, 3 UNION ALL
SELECT 2009, 1, 3 UNION ALL
SELECT 2008, 1, 3 UNION ALL
SELECT 2009, 1, 3) AS x(cy, value, type);
go
– create table 2
SELECT cy
INTO dbo.tg_table2
FROM ( SELECT 2003 UNION ALL
SELECT 2004 UNION ALL
SELECT 2005 UNION ALL
SELECT 2006 UNION ALL
SELECT 2007 UNION ALL
SELECT 2008) AS x(cy);
CREATE CLUSTERED INDEX c_1 ON dbo.tg_table2 (cy);
go
CREATE FUNCTION dbo.f_PivotAndOuterJoin ()
RETURNS TABLE AS
RETURN
SELECT d.cy, [3] AS value
FROM tg_table1 AS t1
PIVOT ( MAX(value) FOR t1.type in ([3]) ) AS d
LEFT OUTER JOIN dbo.tg_table2 AS t2
ON d.cy=t2.cy
go
DECLARE @year int;
PRINT 'wrong results:';
DECLARE cCursor CURSOR FOR
SELECT cy FROM dbo.f_PivotAndOuterJoin() ORDER BY cy;
OPEN cCursor;
FETCH NEXT FROM cCursor INTO @year;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT convert(varchar, @year);
FETCH NEXT FROM cCursor INTO @year;
END
CLOSE cCursor;
DEALLOCATE cCursor;
PRINT ' '; – only to get an empty line
go
DECLARE @year int;
PRINT 'correct result (with STATIC cursor):';
– STATIC, FAST_FORWARD, or READ_ONLY are OK,
– but FORWARD_ONLY, DYNAMIC, SCROLL, or KEYSET are not OK
DECLARE cCursor CURSOR STATIC FOR
SELECT cy FROM dbo.f_PivotAndOuterJoin () ORDER BY cy;
OPEN cCursor;
FETCH NEXT FROM cCursor INTO @year;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT convert(varchar, @year);
FETCH NEXT FROM cCursor INTO @year;
END
CLOSE cCursor;
DEALLOCATE cCursor;
/* output:
wrong results:
2009
2004
2005
2006
2007
2008
2009
correct result (with STATIC cursor):
2002
2003
2004
2005
2006
2007
2008
2009
*/
Die genauen Bedingungen sind im KB-Artikel 976565 beschrieben:
# In Microsoft SQL Server 2005, you have a query that opens a cursor of type FORWARD_ONLY, DYNAMIC, SCROLL or KEYSET.
# The cursor is in an inline table-valued function or a view.
# The inline table-valued function or the view uses either a PIVOT operator or an UNPIVOT operator.
# In addition to the source table of the PIVOT or UNPIVOT operator, there is at least one other table that is referenced in the inline table-valued function or the view.
Alle Bedingungen müssen zutreffen. Meine intuitive Reaktion ist berechenbar: schon wieder ein Argument gegen Cursor. Aber leider gibt es immer noch einige Situationen in denen man unbedingt Cursor benötigt. Das oben gesagt trifft natürlich auch für client-seitige Cursor zu.