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)
1条答案
按热度按时间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.