Powiedzmy, że muszę zaimplementować fragment kodu T-SQL, który jako wynik musi zwracać tabelę. Mogę zaimplementować funkcję wycenioną w tabeli lub procedurę składowaną, która zwraca zestaw wierszy. Czego powinienem użyć?
Krótko mówiąc, chcę wiedzieć:
Jakie są główne różnice między funkcjami a procedurami składowanymi? Jakie kwestie muszę wziąć pod uwagę, korzystając z jednego lub drugiego?
Odpowiedzi:
Jeśli prawdopodobnie będziesz chciał połączyć wynik tego fragmentu kodu z innymi tabelami, to oczywiście funkcja wyceniana w tabeli pozwoli ci skomponować wyniki w jednej instrukcji SELECT.
Ogólnie istnieje hierarchia (Widok <Funkcja TV <Zapisany proces). Możesz zrobić więcej w każdym z nich, ale zdolność komponowania wyników i rzeczywistego zaangażowania optymalizatora zmniejsza się wraz ze wzrostem funkcjonalności.
Dlatego użyj tego, który minimalnie pozwala wyrazić pożądany rezultat.
Funkcje muszą być deterministyczne i nie mogą być używane do wprowadzania zmian w bazie danych, podczas gdy procedury składowane umożliwiają wstawianie i aktualizowanie itp.
Należy ograniczyć korzystanie z funkcji, ponieważ stanowią one ogromny problem ze skalowalnością w przypadku dużych, złożonych zapytań. Stają się czymś w rodzaju „czarnej skrzynki” dla optymalizatora zapytań, a zobaczysz ogromne różnice w wydajności między używaniem funkcji a zwykłym wstawieniem kodu do zapytania.
Ale są one zdecydowanie przydatne w przypadku zwrotów wycenianych w tabeli w bardzo szczególnych przypadkach.
Jeśli potrzebujesz przeanalizować listę rozdzielaną przecinkami, aby zasymulować przekazywanie tablicy do procedury, funkcja może zamienić listę w tabelę. Jest to powszechna praktyka w Sql Server 2005, ponieważ nie możemy jeszcze przekazywać tabel do procedur składowanych (możemy to zrobić w 2008).
Jeśli procedura składowana spełnia następujące kryteria, jest dobrym kandydatem do ponownego zapisania jako funkcja z wartościami przechowywanymi w tabeli:
Logika jest możliwa do wyrażenia w pojedynczej instrukcji SELECT, ale jest procedurą składowaną, a nie widokiem, tylko ze względu na potrzebę parametrów.
Procedura składowana nie wykonuje operacji aktualizacji, z wyjątkiem zmiennych tabeli.
Nie ma potrzeby stosowania dynamicznych instrukcji EXECUTE.
Procedura składowana zwraca jeden zestaw wyników.
Podstawowym celem procedury składowanej jest tworzenie wyników pośrednich, które mają być ładowane do tabeli tymczasowej, która jest następnie odpytywana w instrukcji SELECT.
Mam zamiar napisać kilka interesujących różnic między procedurami składowanymi i funkcjami.
Nie możemy używać funkcji niedeterministycznych w funkcjach, ale możemy używać funkcji niedeterministycznych w procedurach składowanych. Teraz pojawia się pytanie, czym jest funkcja niedeterministyczna. Odpowiedź brzmi: -
Funkcja niedeterministyczna to funkcja, która zwraca różne wyniki dla tych samych wartości wejściowych w różnym czasie, jak getdate (). Zawsze zwraca inną wartość po każdym uruchomieniu.
Wyjątek:-
Wcześniejsze wersje serwera sql starsze niż sql 2000 nie pozwalają na użycie funkcji getdate () w funkcjach zdefiniowanych przez użytkownika, ale wersja 2005 i nowsze pozwalają nam używać funkcji getdate () w ramach funkcji zdefiniowanej przez użytkownika.
Newid () jest kolejnym przykładem funkcji niedeterministycznej, ale nie można jej używać w funkcjach zdefiniowanych przez użytkownika, ale możemy jej użyć w procedurze składowanej.
Możemy używać instrukcji DML (wstawianie, aktualizowanie, usuwanie) w ramach procedury składowanej, ale nie możemy używać instrukcji DML w funkcjach tabel fizycznych lub tabel trwałych. Jeśli chcemy wykonać operację DML w funkcjach, możemy to zrobić na zmiennych tabeli, a nie na stałych tabelach.
Nie możemy używać obsługi błędów w ramach funkcji, ale możemy obsługiwać błędy w procedurach składowanych.
Procedura może zwrócić zero lub n wartości, podczas gdy funkcja może zwrócić jedną wartość, która jest obowiązkowa.
Procedury mogą mieć parametry wejściowe / wyjściowe, podczas gdy funkcje mogą mieć tylko parametry wejściowe.
Procedura dopuszcza w niej zarówno instrukcję select, jak i DML, podczas gdy funkcja pozwala tylko na instrukcję select.
Funkcje można wywołać z procedury, podczas gdy procedur nie można wywołać z funkcji.
Wyjątek może być obsługiwany przez blok try-catch w procedurze, podczas gdy blok try-catch nie może być używany w funkcji.
Możemy przejść do zarządzania transakcjami w procedurze, podczas gdy nie możemy przejść do funkcji.
Procedury nie mogą być używane w instrukcji select, podczas gdy funkcja może być osadzona w instrukcji select.
Funkcja UDF (funkcja zdefiniowana przez użytkownika) może być używana w instrukcjach SQL w dowolnym miejscu w sekcji WHERE/ HAVING/, SELECTpodczas gdy procedury składowane nie mogą.
UDF, które zwracają tabele, można traktować jako inny zestaw wierszy. Można to wykorzystać w JOINinnych tabelach.
Wbudowane UDF mogą być traktowane jako widoki, które przyjmują parametry i mogą być używane w JOIN operacjach na zestawach wierszy i innych operacjach.
Jeśli masz funkcję, możesz na przykład użyć jej jako części instrukcji SQL
SELECT function_name(field1) FROM table
W przypadku procedur składowanych nie działa to w ten sposób.
Przeprowadziłem kilka testów z długo działającym bitem logiki, z tym samym bitem kodu (długa instrukcja SELECT) działającym zarówno w funkcji wartościowanej w tabeli, jak iw procedurze składowanej oraz prostym EXEC / SELECT, a każdy z nich był identyczny.
Moim zdaniem zawsze używaj funkcji wycenianej w tabeli, a nie procedury składowanej, aby zwrócić zestaw wyników, ponieważ sprawia, że logika jest znacznie łatwiejsza i czytelna w zapytaniach, które następnie łączą się z nimi, i umożliwia ponowne użycie tej samej logiki. Aby uniknąć zbyt dużego spadku wydajności, często używam parametrów „opcjonalnych” (tj. Możesz przekazać im NULL), aby umożliwić funkcji zwracanie zestawu wyników w celu szybszego, np .:
CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN
SELECT DISTINCT SiteID, PersonID
FROM dbo.SiteViewPermissions
WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
AND (@optSiteID IS NULL OR @optSiteID = SiteID)
AND @RegionID = RegionID
W ten sposób możesz używać tej funkcji w wielu różnych sytuacjach i nie odnieść wielkiego sukcesu. Uważam, że jest to bardziej wydajne niż późniejsze filtrowanie:
SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1
Używałem tej techniki w kilku funkcjach, czasami z długą listą „opcjonalnych” parametrów tego typu.
Osobiście używam funkcji wartościowanych w tabeli, gdy wszystko, co zwracam, to pojedyncza tabela bez wpływu. Zasadniczo traktuję je jak sparametryzowane widoki.
Jeśli potrzebuję zwrócenia wielu zestawów rekordów lub jeśli w tabelach zostaną zaktualizowane wartości, używam procedury składowanej.
Moje 2 centy
Jak wspomniano powyżej, funkcje są bardziej czytelne / komponowalne / samodokumentujące się, ale ogólnie są mniej wydajne i mogą być znacznie mniej wydajne, jeśli dasz się ponieść takim połączeniom, jak
SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
ON (tvf1.JoinId = tvf2.JoinId)
Często wystarczy zaakceptować nadmiarowość kodu, którą tvf może wyeliminować (przy niedopuszczalnym koszcie wydajności).
Inną kwestią, o której jeszcze nie wspomniałem, jest to, że nie można używać tabel tymczasowych zmieniających stan bazy danych wewnątrz tvf z wieloma instrukcjami. Najbardziej funkcjonalnym mechanizmem równoważnym z tabelą tymczasową jest zmiana stanu, w zmiennej tabeli pamięci, aw przypadku dużych zestawów danych tabela tymczasowa będzie prawdopodobnie bardziej wydajna niż zmienna tabeli. (Inne alternatywy obejmują dynamiczne tabele i wspólne wyrażenia wyceniane w tabelach, ale na pewnym poziomie złożoności przestają one być dobrą opcją IMO).
Procedury składowane to wstępnie skompilowane zapytania, które wykonują się szybciej i oszczędzają z iniekcji sql. Mogą zwracać wartości 0 lub N. Możemy wykonywać operacje DML wewnątrz procedur składowanych. Możemy używać funkcji wewnątrz procedur i możemy używać funkcji w zapytaniu wybierającym. Funkcje służą do zwracania wartości i operacji DML niemożliwych w funkcjach. funkcje są dwojakiego rodzaju: skalarne i tabelaryczne. funkcja skalarna zwraca pojedynczą wartość, funkcję z wartościami tabelarycznymi używaną do zwracania wierszy tabel.