SQL Server T-SQL bug? CURSOR does not respect TABLESAMPLE

ymdaylpp  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(125)

Ran into this today. Code as follows, with tablesample commented out. Run it, get the expected results of 5000050000.

-- tested in SQL Server 2014

use tempdb;

IF OBJECT_ID('dbo.ints', 'U') IS NOT NULL  
    DROP TABLE ints;

CREATE TABLE ints(x bigint NOT NULL);

WITH MakeInts AS
(
    SELECT CAST(1 AS bigint) AS x
    UNION ALL
    SELECT x + 1 AS x
    FROM MakeInts
    WHERE x < 100000
)
INSERT INTO ints 
    SELECT *
    FROM MakeInts
    OPTION (MAXRECURSION 0);

DECLARE crsr CURSOR READ_ONLY
FOR select x 
from ints
--tablesample (5 percent)
;

DECLARE @x bigint;
DECLARE @sum bigint = 0;
OPEN crsr;

FETCH NEXT FROM crsr INTO @x;
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @sum += @x;
    END
    FETCH NEXT FROM crsr INTO @x;
END

CLOSE crsr;
DEALLOCATE crsr;

print @sum;

Now comment tablesample back in and run again. Same result of 5000050000.

Am I overlooking something completely obvious?

(Martin Smith's comment pointed out that tablesample samples at the page level and my table was small enough to fit on one page. Good catch, and amended code above so it makes a much larger table. Doesn't fix though)

ryhaxcpt

ryhaxcpt1#

This behaviour isn't documented on the TABLESAMPLE details so is somewhat speculative but certainly the demo in the question would indicate that the option is just being silently ignored.

For a dynamic cursor I can see why this might be the case. Dynamic cursor plans need to support moving both forwards and backwards so this would require storing some additional cursor state for just this edge case and add complexity to the fetch next.

It does seem maybe a bug that it doesn't just silently change the type to static though (as it would if you added an order by x for example).

But in any event if you explicitly request static you do get the table sampling applied before the rows are inserted to the worktable.

相关问题