SQL Query Optimization. Why is it so hard to get right?

Zmienne tabelaryczne i funkcje niedeterministyczne.

Wykorzystanie w zapytaniu zmiennych tabelarycznych oraz funkcji niedeterministycznych może dawać ciekawe, niespodziewane wyniki.

Poniższe zapytania są właściwie identyczne. Pierwsze bazuje na zwykłej tabeli, drugie na tabeli tymczasowej, a trzecie na zmiennej tabelarycznej. Idea jest jednak taka sama w każdym z 3 przypadków. Kluczowe jest tu podzapytanie, które ma zwrócić dowolną wartość (TOP 1) z tabeli. Wartość ta będzie za każdym razem inna ze względu na użycie w ORDER BY funkcji NEWID().

W przypadku zwykłej tabeli oraz tabeli tymczasowej podzapytanie zwraca za każdym razem inną wartość (przy ponownym wywołani zapytania), ale wartość jest taka sama dla każdego z rekordów, które zwraca zapytanie zewnętrzne. Tego bym się spodziewał.
W przypadku zmiennej tabelaryczne sytuacja się zmienia. Wartość podzapytania jest różna dla każdego rekordu zapytania zewnętrznego. Tego właśnie się nie spodziewałem i zrobiłem duże oczy.

Przykład nr 1 (tabela):

USE TEMPDB;
GO

IF OBJECT_ID('T') IS NOT NULL
DROP TABLE T

CREATE TABLE T (NUMBER INT);

WITH NUMBERS
AS
(
SELECT 1 AS NUMBER
UNION ALL
SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10
)
INSERT INTO T
SELECT NUMBER
FROM NUMBERS;

SELECT NUMBER
, (SELECT TOP 1 NUMBER FROM T ORDER BY NEWID()) AS J
FROM T;
GO

Przykładowe wyniki:
NUMBER J
1 2
2 2
3 2
4 2
5 2
6 2
7 2
8 2
9 2
10 2

Przykład nr 2 (tabela tymczasowa):
USE TEMPDB;
GO

IF OBJECT_ID('#T') IS NOT NULL
DROP TABLE #T

CREATE TABLE #T (NUMBER INT);

WITH NUMBERS
AS
(
SELECT 1 AS NUMBER
UNION ALL
SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10
)
INSERT INTO #T
SELECT NUMBER
FROM NUMBERS;

SELECT NUMBER
, (SELECT TOP 1 NUMBER FROM #T ORDER BY NEWID()) AS J
FROM #T;
GO

Przykładowe wyniki:
NUMBER J
1 6
2 6
3 6
4 6
5 6
6 6
7 6
8 6
9 6
10 6

Przykład nr 3 (zmienna tabelaryczna):

USE TEMPDB;

GO

DECLARE @T TABLE(NUMBER INT);

WITH NUMBERS

AS

(

SELECT 1 AS NUMBER

UNION ALL

SELECT NUMBER + 1 FROM NUMBERS WHERE NUMBER < 10

)

INSERT INTO @T

SELECT NUMBER

FROM NUMBERS;

SELECT NUMBER

, (SELECT TOP 1 NUMBER FROM @T ORDER BY NEWID()) AS J

FROM @T;

GO

Przykładowe wyniki:

NUMBER J

1 4

2 8

3 2

4 8

5 5

6 5

7 7

8 3

9 4

10 8

Kluczowe w przypadku zmiennej tabelarycznej jest brak operatora Table Spool.

Generowanie skryptów w SSMS.

W opcjach SQL Management Studio mamy możliwość ustawień skryptowania. Mamy np. możliwość ustawić 'Script for Server Version'. Nie wiem czemu, ale wydawało mi się, że jest to ustawienie globalne dla SSMS i że niezależnie gdzie będę wygenerowany skrypt będzie on zgodny z wersją jaką ustawiłem w opcjach. Okazało się, że się myliłem.

Gdy generujemy skrypt dla jakiegoś obiektu klikając na niego prawym klawiszem myszy i wybierając np. CREATE TO to wygenerowany skrypt będzie zgodny z naszymi ustawieniami. Jeżeli jednak będziemy chcieli wygenerować tzw. 'Change Script' z okna, w którym pracujemy na diagramie to wówczas nasze ustawienia odnośnie zgodności wersji skryptu do wersji SQL ma się nijak. Co ciekawe, w moim przypadku, nawet strona kodowa jest inna. Normalnie mam ANSII, a skrypt generowany z okna pracy na diagramach jest w UCS-2.

Hmmm... Ktoś może wie dlaczego tak?