Dlaczego TVP muszą być READONLY, a dlaczego parametry innych typów nie mogą być READONLY

Zgodnie z tym blogiem parametry funkcji lub procedury składowanej są zasadniczo przekazywane przez wartość, jeśli nie są OUTPUTparametrami, i zasadniczo są traktowane jako bezpieczniejsza wersja przekazywania przez odniesienie, jeśli są OUTPUTparametrami.

Na początku myślałem, że celem wymuszenia deklaracji TVP READONLYbyło wyraźne zasygnalizowanie programistom, że TVP nie może być użyty jako OUTPUTparametr, ale musi być więcej, ponieważ nie możemy zadeklarować, że nie jest to TVP READONLY. Na przykład nie powiedzie się:

create procedure [dbo].[test]
@a int readonly
as
    select @a

Msg 346, poziom 15, stan 1, test procedury
Parametru „@a” nie można ODCZYTAĆ ODCZYTU, ponieważ nie jest to parametr wyceniony w tabeli.

1. Ponieważ statystyki nie są przechowywane w TVP, jakie jest uzasadnienie zapobiegania operacjom DML?
2. Czy jest to związane z tym, że OUTPUTz jakiegoś powodu nie chcę, aby TVP była parametrami?

Wyjaśnienie wydaje się być powiązane z kombinacją: a) szczegółu z połączonego bloga, który nie został wspomniany w tym pytaniu, b) pragmatyki dopasowania TVP do sposobu, w jaki parametry zawsze były przekazywane i wychodzące, c) i natury zmiennych tabeli.

1. Brakujący szczegół zawarty w łączonym poście na blogu to dokładnie sposób, w jaki zmienne są przekazywane i wyprowadzane z przechowywanych procedur i funkcji (co odnosi się do sformułowania w pytaniu „bezpieczniejsza wersja przekazywania przez odniesienie, jeśli są parametrami WYJŚCIA”) :

   TSQL używa semantyki kopiowania / kopiowania do przekazywania parametrów do procedur przechowywanych i funkcji ....

   ... gdy zapisany proces zakończy wykonywanie (bez popełniania błędu), wykonywana jest kopia, która aktualizuje przekazany parametr o wszelkie zmiany, które zostały wprowadzone w zapisanym proc.

   Prawdziwą zaletą tego podejścia jest przypadek błędu. Jeśli błąd wystąpi w trakcie wykonywania procedury składowanej, wszelkie zmiany dokonane w parametrach nie zostaną przeniesione z powrotem do programu wywołującego.

   Jeśli słowo kluczowe WYJŚCIE nie jest obecne, nie jest wykonywane kopiowanie.

   Podsumowując:
   Parametry do przechowywanych proc nigdy nie odzwierciedlają częściowego wykonania zapisanego proc, jeśli napotkał błąd.

   Część 1 tej układanki polega na tym, że parametry są zawsze przekazywane „przez wartość”. I dopiero wtedy, gdy parametr zostanie oznaczony jako OUTPUT i procedura składowana zakończy się powodzeniem, bieżąca wartość zostanie faktycznie odesłana. Gdyby OUTPUTwartości były rzeczywiście przekazywane „przez odniesienie”, to wskaźnik do lokalizacji w pamięci tej zmiennej byłby rzeczą, która została przekazana, a nie samą wartością. A jeśli podasz wskaźnik (tj. Adres pamięci), wszelkie wprowadzone zmiany zostaną natychmiast odzwierciedlone, nawet jeśli następny wiersz Procedury składowanej spowoduje błąd i przerwie wykonywanie.

   Podsumowując Część 1: wartości zmiennych są zawsze kopiowane; nie są przywoływane przez ich adres pamięci.

2. Mając na uwadze Część 1, zasada zawsze kopiowania wartości zmiennych może prowadzić do problemów z zasobami, gdy przekazywana zmienna jest dość duża. I nie zostało przetestowane, aby zobaczyć, jak są obsługiwane typy blob ( VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, i te, które nie powinny być stosowane już: TEXT, NTEXT, i IMAGE), ale jest to na pewno powiedzieć, że każda tabela danych są przekazywane w może być dość duża. Rozsądnym byłoby, aby osoby opracowujące funkcję TVP pragnęły prawdziwej zdolności „przejścia przez odniesienie”, aby zapobiec ich nowej fajnej funkcji niszczenia zdrowej liczby systemów (tj. Chcieć bardziej skalowalnego podejścia). Jak widać w dokumentacji , zrobili to:

   Transact-SQL przekazuje parametry o wartościach przechowywanych w tabeli do procedur przez odwołanie, aby uniknąć tworzenia kopii danych wejściowych.

   Ponadto problem zarządzania pamięcią nie był nową koncepcją, ponieważ można go znaleźć w interfejsie API SQLCLR, który został wprowadzony w SQL Server 2005 (TVP zostały wprowadzone w SQL Server 2008). Po przejściu NVARCHARi VARBINARYdane do kodu SQLCLR (tj parametrów wejściowych na metodach NET w ramach Zgromadzenia SQLCLR), masz możliwość, aby przejść z podejściem „wartością” za pomocą jednej SqlStringlub SqlBinaryodpowiednio, czy można przejść z „przez odniesienie „podejście przy użyciu odpowiednio jednego SqlCharslub SqlBytesdrugiego. SqlCharsI SqlBytestypy pozwalają na pełną streaming danych do .NET CLR takie, że można wyciągnąć małe kawałki dużych wartościach w przeciwieństwie do kopiowania całe 200 MB (do 2 GB, prawy) wartość.

   Podsumowując Część 2: TVP ze swojej natury mieliby skłonność do konsumowania dużej ilości pamięci (a tym samym pogorszenia wydajności), jeśli pozostaliby w modelu „zawsze kopiuj wartość”. Dlatego TVP robią prawdziwy „przekaz przez odniesienie”.

3. Ostatnia część jest tak ważna, ponieważ część 2 ma znaczenie: dlaczego przekazanie TVP naprawdę „przez odniesienie”, zamiast robić jego kopię, by cokolwiek zmienić. Odpowiada na to cel projektu, który jest podstawą Części 1: Procedury składowane, które nie zostały pomyślnie ukończone, nie powinny w żaden sposób zmieniać żadnego z parametrów wejściowych, niezależnie od tego, czy są one oznaczone jako OUTPUTczy nie. Zezwolenie na operacje DML miałoby bezpośredni wpływ na wartość TVP, ponieważ istnieje ona w kontekście wywoływania (ponieważ przekazywanie przez referencję oznacza zmianę rzeczy, która została przekazana, a nie kopię tego, co zostało przekazane).

   Teraz ktoś gdzieś w tym miejscu prawdopodobnie rozmawia ze swoim monitorem, mówiąc: „Cóż, po prostu zbuduj narzędzie do automatyzacji, aby cofnąć wszelkie zmiany dokonane w parametrach TVP, jeśli zostały wprowadzone do Procedury składowanej. Cóż. Problem rozwiązany”. Nie tak szybko. Tu właśnie pojawia się natura Zmiennych Tabeli: zmiany wprowadzone w Zmiennych Tabeli nie są związane Transakcjami! Nie ma więc możliwości wycofania zmian. I w rzeczywistości jest to sztuczka używana do zapisywania informacji generowanych w ramach transakcji, jeśli konieczne jest wycofanie :-).

   Podsumowując Część 3: Zmienne tabelowe nie pozwalają na „cofanie” wprowadzonych do nich zmian w przypadku błędu, który powoduje przerwanie procedury składowanej. I to narusza cel projektowy, aby parametry nigdy nie odzwierciedlały częściowego wykonania (Część 1).

Ergo:READONLY kluczowe jest potrzebna, aby zapobiec operacji DML na TVPs ponieważ są one Tabela Zmienne, które są faktycznie przeszły „przez powołanie”, a więc wszelkie ich modyfikacje będą natychmiast widoczne, nawet jeśli procedura składowana napotka błąd, a tam nie ma inny sposób, aby temu zapobiec.

Ponadto parametry innych typów danych nie mogą być używane, READONLYponieważ są już kopiami tego, co zostały przekazane, więc nie chroniłoby niczego, co nie jest jeszcze chronione. To i sposób, w jaki parametry innych typów danych miały być przeznaczone do odczytu i zapisu, więc prawdopodobnie jeszcze więcej pracy byłoby zmodyfikować ten interfejs API, aby teraz zawierał koncepcję tylko do odczytu.

_{Odpowiedź Wiki Wiki wygenerowana z komentarza Martina Smitha do pytania}

Aktywny element Connect (przesłany przez Erlanda Sommarskoga) do tego celu:

Złagodzenie ograniczenia, że ​​parametry tabeli muszą być odczytywane tylko wtedy, gdy SP nawołują się nawzajem

Jak dotąd jedyną odpowiedzią Microsoftu jest (wyróżnienie dodane):

Dzięki za opinie na ten temat. Otrzymaliśmy podobne opinie od dużej liczby klientów. Zezwolenie na odczyt / zapis parametrów o wartościach z tabeli wymaga sporo pracy po stronie silnika SQL, a także protokołów klienta. Ze względu na ograniczenia czasowe / zasoby, a także inne priorytety, nie będziemy mogli podjąć się tej pracy w ramach wersji SQL Server 2008. Jednak zbadaliśmy ten problem i mamy go zdecydowanie w swoim radarowym, aby rozwiązać w ramach kolejnej wersji SQL Server. Dziękujemy za informację zwrotną.

Srini Acharya
Starszy menedżer programu
SQL Server Relational Engine