临时表上的主键会混淆结果(Primary Key on a temp

临时表上的主键会混淆结果(Primary Key on a temp-table messes up the results)

这是“为什么会发生这种情况？ - 问题”

我有以下脚本：

DECLARE @sql_stmt nvarchar(max) SET @sql_stmt = ' select top 100000 id as id from dat.sev_sales_event order by id ' DECLARE @preResult TABLE ( sales_event_id INT NOT NULL PRIMARY KEY) INSERT INTO @preResult(sales_event_id) EXEC sp_executesql @sql_stmt SELECT * FROM @preResult

如果我运行此脚本，结果可能会在每次执行时有所不同

只需从临时表中删除“PRIMARY KEY” ，结果就会保持稳定

有人能告诉我这种行为的理论吗？

亲切的问候Jürgen

This is a "Why does this happen??? - Question"

I have the following script:

DECLARE @sql_stmt nvarchar(max) SET @sql_stmt = ' select top 100000 id as id from dat.sev_sales_event order by id ' DECLARE @preResult TABLE ( sales_event_id INT NOT NULL PRIMARY KEY) INSERT INTO @preResult(sales_event_id) EXEC sp_executesql @sql_stmt SELECT * FROM @preResult

If I run this script, results may vary each time it's executed

By simply removing "PRIMARY KEY" from the temporary-table, results stay stable

Can someone tell me the theory to this behaviour?

Kind regards Jürgen

最满意答案

您确定结果集是不同的还是只是以不同的顺序？

将主键添加到临时表应该导致表的内容按数字顺序排序，因此显示为“稳定”。 删除它将删除固有的顺序。

Are you sure the result set is different or just in a different order?

Adding a primary key to the temporary table should result in the contents of the table being ordered numerically ascending, and so appear 'stable'. Removing this will remove the inherent ordering.