{"id":4015,"date":"2010-01-03T11:13:37","date_gmt":"2010-01-03T10:13:37","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/?p=4015"},"modified":"2010-01-05T13:34:27","modified_gmt":"2010-01-05T12:34:27","slug":"vorsicht-bei-cursor-auf-tvfview-mit-pivot","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2010\/01\/03\/sql-talk\/vorsicht-bei-cursor-auf-tvfview-mit-pivot","title":{"rendered":"Vorsicht bei Cursor auf TVF\/View mit PIVOT"},"content":{"rendered":"

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:<\/p>\n

SET NOCOUNT ON;
\ngo
\nUSE tempdb
\ngo
\nBEGIN TRY DROP TABLE dbo.tg_table1 END TRY BEGIN CATCH END CATCH;
\nBEGIN TRY DROP TABLE dbo.tg_table2 END TRY BEGIN CATCH END CATCH;
\nBEGIN TRY DROP FUNCTION dbo.f_PivotAndOuterJoin END TRY BEGIN CATCH END CATCH;
\ngo
\n– create table 1
\nSELECT cy, value, type
\n\tINTO dbo.tg_table1
\n\tFROM (\tSELECT 2002, 1, 3 UNION ALL
\n\t\t\tSELECT 2003, 1, 3 UNION ALL
\n\t\t\tSELECT 2004, 1, 3 UNION ALL
\n\t\t\tSELECT 2005, 1, 3 UNION ALL
\n\t\t\tSELECT 2006, 1, 3 UNION ALL
\n\t\t\tSELECT 2007, 1, 3 UNION ALL
\n\t\t\tSELECT 2008, 1, 3 UNION ALL
\n\t\t\tSELECT 2009, 1, 3 UNION ALL
\n\t\t\tSELECT 2008, 1, 3 UNION ALL
\n\t\t\tSELECT 2009, 1, 3) AS x(cy, value, type);
\ngo
\n– create table 2
\nSELECT cy
\n\tINTO dbo.tg_table2
\n\tFROM (\tSELECT 2003 UNION ALL
\n\t\t\tSELECT 2004 UNION ALL
\n\t\t\tSELECT 2005 UNION ALL
\n\t\t\tSELECT 2006 UNION ALL
\n\t\t\tSELECT 2007 UNION ALL
\n\t\t\tSELECT 2008) AS x(cy);<\/p>\n

CREATE CLUSTERED INDEX c_1 ON dbo.tg_table2 (cy);
\ngo
\nCREATE FUNCTION dbo.f_PivotAndOuterJoin ()
\nRETURNS TABLE AS
\nRETURN
\n\tSELECT d.cy, [3] AS value
\n\t\tFROM tg_table1 AS t1
\n\t\t\tPIVOT ( MAX(value) FOR t1.type in ([3]) ) AS d
\n\t\tLEFT OUTER JOIN dbo.tg_table2 AS t2
\n\t\t\tON\td.cy=t2.cy<\/p>\n

go<\/p>\n

DECLARE @year int;<\/p>\n

PRINT 'wrong results:';<\/p>\n

DECLARE cCursor CURSOR FOR
\n\tSELECT cy FROM dbo.f_PivotAndOuterJoin() ORDER BY cy;<\/p>\n

OPEN cCursor;
\nFETCH NEXT FROM cCursor INTO @year;<\/p>\n

WHILE @@FETCH_STATUS = 0 BEGIN
\n\tPRINT convert(varchar, @year);<\/p>\n

\tFETCH NEXT FROM cCursor INTO @year;
\nEND
\nCLOSE cCursor;
\nDEALLOCATE cCursor;<\/p>\n

PRINT ' '; – only to get an empty line
\ngo
\nDECLARE @year int;<\/p>\n

PRINT 'correct result (with STATIC cursor):';<\/p>\n

– STATIC, FAST_FORWARD, or READ_ONLY are OK,
\n– but FORWARD_ONLY, DYNAMIC, SCROLL, or KEYSET are not OK
\nDECLARE cCursor CURSOR STATIC FOR
\n\tSELECT cy FROM dbo.f_PivotAndOuterJoin () ORDER BY cy;<\/p>\n

OPEN cCursor;
\nFETCH NEXT FROM cCursor INTO @year;<\/p>\n

WHILE @@FETCH_STATUS = 0 BEGIN
\n\tPRINT convert(varchar, @year);<\/p>\n

\tFETCH NEXT FROM cCursor INTO @year;
\nEND
\nCLOSE cCursor;
\nDEALLOCATE cCursor;<\/p>\n

\/* output:<\/p>\n

wrong results:
\n2009
\n2004
\n2005
\n2006
\n2007
\n2008
\n2009<\/p>\n

correct result (with STATIC cursor):
\n2002
\n2003
\n2004
\n2005
\n2006
\n2007
\n2008
\n2009
\n*\/<\/code><\/p>\n

Die genauen Bedingungen sind im KB-Artikel 976565<\/a> beschrieben:<\/p>\n

# In Microsoft SQL Server 2005, you have a query that opens a cursor of type FORWARD_ONLY, DYNAMIC, SCROLL or KEYSET.
\n# The cursor is in an inline table-valued function or a view.
\n# The inline table-valued function or the view uses either a PIVOT operator or an UNPIVOT operator.
\n# 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.<\/p><\/blockquote>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[48,303,51,305],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/4015"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=4015"}],"version-history":[{"count":6,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/4015\/revisions"}],"predecessor-version":[{"id":4087,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/4015\/revisions\/4087"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=4015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=4015"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=4015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}