SQL Server 中的查询不稳定

发布于 2024-10-20 16:02:49 字数 959 浏览 1 评论 0原文

我使用的是 SQL Server 2008-R2 Express 版。

我编写了如下所示的查询来生成从 1 到 @n 的数字序列。在测试它时（纯粹在查询窗口中），我发现如果我显着更改 @n 的值，我会得到不正确的结果。重新执行会产生相同的错误。但是，如果我打开一个新的查询窗口，结果是完美的。

看看算法，我不明白为什么我应该得到不稳定的结果（甚至查询可以针对固定输入产生不同的结果）。

DECLARE @n INT;

SET @n = 65536;

DECLARE @t TABLE (n INT NOT NULL PRIMARY KEY);

IF @n > 0 BEGIN
    DECLARE @r INT, @i INT, @l INT;

    SET @r = FLOOR(1.442695040888964 * LOG(@n));
    SET @i = 1;
    SET @l = 0;

    INSERT INTO @t (n) VALUES (1);

    WHILE @l < @r BEGIN
        INSERT INTO @t (n) SELECT n + @i FROM @t;

        SET @i = @i * 2;
        SET @l = @l + 1;
    END;

    INSERT INTO @t (n) SELECT TOP (@n - @i) n + @i FROM @t;
END;

--SELECT COUNT(1) FROM @t;

select * from @t

编辑

将 65536 更改为 5000，执行，更改回 65536，然后向下滚动到第 169,770 行。我得到行 169770 = 40000。在新窗口中，它运行正确。

EDIT2

除了随机获得正确/不正确的结果之外，似乎还有其他问题。我现在得到的某些数字始终不正确，例如 655360。

原文

I am using SQL Server 2008-R2 Express Edition.

I wrote the query shown below to generate number sequences from 1 to @n. In testing it (purely in a query window), I found that if I significantly change the value of @n, I get incorrect results. Re-execution yields the same errors. However, if I open a new query window, the results are perfect.

Looking at the algorithm, it makes no sense to me as to why I should be getting unstable results (or even that a query can produce varying results against a fixed input).

DECLARE @n INT;

SET @n = 65536;

DECLARE @t TABLE (n INT NOT NULL PRIMARY KEY);

IF @n > 0 BEGIN
    DECLARE @r INT, @i INT, @l INT;

    SET @r = FLOOR(1.442695040888964 * LOG(@n));
    SET @i = 1;
    SET @l = 0;

    INSERT INTO @t (n) VALUES (1);

    WHILE @l < @r BEGIN
        INSERT INTO @t (n) SELECT n + @i FROM @t;

        SET @i = @i * 2;
        SET @l = @l + 1;
    END;

    INSERT INTO @t (n) SELECT TOP (@n - @i) n + @i FROM @t;
END;

--SELECT COUNT(1) FROM @t;

select * from @t

EDIT

Change the 65536 to 5000, execute, change back to 65536, and scroll down to say row 169,770. I get row 169770 = 40000. In a new window, it runs correctly.

EDIT2

Besides getting randomly correct/incorrect results, it appears something else is wrong. I now am getting consistently incorrect results for some numbers such as 655360.

分享到QQ

分享到微博