連接虛擬表中的 NEWID() 導致意外的交叉應用行為

我的實際工作查詢是內部聯接，但這個帶有交叉聯接的簡單範例似乎幾乎總是重現問題。

SELECT *
FROM (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )
CROSS JOIN (
   SELECT NEWID() TEST_ID
) BB ( B )

通過我的內部連接，我有很多行，我使用 NEWID() 函式為每行添加了一個 GUID，對於 10 個這樣的行中的大約 9 個，與 2 行虛擬表的乘法產生了預期的結果，只有 2 個副本相同的 GUID，而十分之一會產生不同的結果。這至少可以說是出乎意料的，讓我很難在我的測試數據生成腳本中找到這個錯誤。

如果您使用非確定性 getdate 和 sysdatetime 函式查看以下查詢，您將看不到這一點，我無論如何也不會看到 - 我總是在兩個最終結果行中看到相同的 datetime 值。

SELECT *
FROM (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )
CROSS JOIN (
   SELECT GETDATE() TEST_ID
) BB ( B )

SELECT *
FROM (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )
CROSS JOIN (
   SELECT SYSDATETIME() TEST_ID
) BB ( B )

我目前正在使用 SQL Server 2008，我現在的工作是在完成我的隨機數據生成腳本之前將帶有 GUID 的行載入到表變數中。一旦我將它們作為表而不是虛擬表中的值，問題就消失了。

我有一個解決方法，但我正在尋找沒有實際表或表變數的解決方法。

在寫這篇文章時，我嘗試了這些可能性但沒有成功：1）將 newid() 放入嵌套的虛擬表中：

SELECT *
FROM (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )
CROSS JOIN (
   SELECT TEST_ID
   FROM (
       SELECT NEWID() TEST_ID
   ) TT
) BB ( B )

2. 將 newid() 包裝在強制轉換錶達式中，例如：

SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID

3. 顛倒連接表達式中虛擬表的出現順序

SELECT *
FROM (
   SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )

4）使用不相關的交叉應用

SELECT *
FROM (
   SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
   SELECT 1 UNION ALL
   SELECT 2
) AA ( A )

就在最終發布這個問題之前，現在我成功地嘗試了這個，似乎相關的交叉應用：

SELECT *
FROM (
   SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
   SELECT A
   FROM (
       SELECT 1 UNION ALL
       SELECT 2
   ) TT ( A )
   WHERE BB.B IS NOT NULL
) AA ( A )

有人有其他更優雅、更簡單的解決方法嗎？如果我不需要的話，我真的不想使用交叉應用或相關來進行簡單的行乘法。

此行為是設計使然，如本 Connect 錯誤報告中詳細說明的那樣。為方便起見，下面複製了最相關的 Microsoft 回复（以防連結在某個時候失效）：

Microsoft 於 2008 年 7 月 7 日上午 9:27 發布

關閉循環。. . 我已經與開發團隊討論過這個問題。最終我們決定不改變目前的行為，原因如下：

1. 優化器不保證標量函式的執行時間或次數。這是一個由來已久的原則。這是基本的“迴旋餘地”，它允許優化器有足夠的自由度來顯著改進查詢計劃的執行。
2. 這種“每行一次的行為”並不是一個新問題，儘管它沒有被廣泛討論。我們在 Yukon 版本中開始調整它的行為。但是，在所有情況下，很難準確地確定它的確切含義！例如，它是否適用於“在途中”計算到最終結果的臨時行？- 在這種情況下，它顯然取決於選擇的計劃。還是僅適用於最終將出現在完成結果中的行？- 這裡有一個討厭的遞歸，我相信你會同意的！
3. 正如我之前提到的，我們預設為“優化性能”——這適用於 99% 的情況。1% 的可能會改變結果的情況很容易發現——諸如 NEWID 之類的副作用“功能”——並且很容易“修復”（因此交易性能）。這種“優化性能”的預設設置由來已久，並且被接受。（是的，這不是編譯器為傳統程式語言選擇的立場，但就這樣吧）。

所以，我們的建議是：

1. 避免依賴非保證時間和執行次數語義。
2. 避免在表表達式中使用 NEWID()。
3. 使用 OPTION 強制執行特定行為（交易性能）

希望這個解釋有助於澄清我們將這個錯誤關閉為“無法修復”的原因。

GETDATEand函式確實是不確定的SYSDATETIME，但它們被視為特定查詢的執行時常量。從廣義上講，這意味著函式的值在查詢執行開始時被記憶體，結果被重新用於查詢中的所有引用。

問題中的任何“解決方法”都不安全。無法保證下次編譯計劃時、下次應用服務包或累積更新時行為不會改變……或其他原因。

唯一安全的解決方案是使用某種臨時對象——例如變數、表或多語句函式。使用基於觀察在今天看來可行的解決方法是在未來體驗意外行為的好方法，通常以周日凌晨 3 點的尋呼警報的形式出現。

引用自：https://dba.stackexchange.com/questions/30345