MS SQL Server posiada zbiór predefiniowanych procedur składowanych, które zapisane są w:
- bazie master – nazwy tych procedur rozpoczynają się od prefiksu sp_.
Służą one do pobierania i konfigurowania opcji serwera i baz danych. - jako pliki bibliotek dynamicznych dll – nazwy tych procedur rozpoczynają się od prefiksu xp_.
Istnieje również możliwość tworzenia:
- własnych procedur składowanych, które przechowywane są w bazie użytkownika,
- tymczasowych procedur składowanych, które charakteryzują się tym, że nazwy tych procedur rozpoczynają się pojedynczym lub podwójnym znakiem #.
MS SQL Server zapisuje ciało procedury składowanej w tabeli systemowej syscomments, a nazwę w tabeli sysobjects.
W trakcie pierwszego uruchomieniu procedury optymalizator SQL Server przygotowuje i kompiluje plan wykonania. Plan ten przechowywany jest w buforze procedury (Procedure cache) skąd jest pobierany przy ponownym uruchomieniu procedury.
W tym momencie chcę przedstawić kilka spostrzeżeń:
Po pierwsze:
Wiele osób wzorując się na konwencji nazewniczej stosowanej dla procedur systemowych tworzy własne procedury składowane używając prefiksu ’sp_’. Jest to błędne postępowanie ponieważ ze względu na wydajność nie należy postępować w ten sposób, no chyba że procedury składowane użytkownika zostaną zapisane w bazie master.
Dla przykładu:
- należy stworzyć dwie proste procedury w bazie użytkownika czy też w bazie tempdb i nazwać je odpowiednio: dbo.foo i dbo.sp_foo
- następnie należy wywołać utworzone procedury w celu skompilowania planu wykonania.
- kolejny krok to uruchomienie Profilera i stworzenie w nim zdarzenia klasy Stored Procedur Cache Miss
- następnie należy ponownie wywołać dwie utworzone wcześniej procedury tj. dbo.foo i dbo.sp_foo.
W przypadku uruchomienia procedury dbo.foo Profiler nie zanotował żadnego zdarzenia, a w przypadku uruchomienia procedury dbo.sp_foo w Profilerze widać zdarzenie SP:CacheMiss. Oznacza to, że SQL Server domyślnie szuka planu wykonania dla procedur o prefiksie sp_ w buforze bazy master. Nie znajdując go tam skompilował ponownie procedurę sp_foo.
W przypadku odwoływania się do procedury za pomocą jej pełnej nazwy, opisany powyżej problem nie wystąpi.
Po drugie:
- Zaleca się aby nie tworzyć żadnych procedur z prefiksem sp_. SQL Server używa prefiksu sp_ do oznaczenia systemowych procedur składowanych.
- Może zdarzyć się, że w kolejnych wersjach MS SQL Server może nastąpić konflikt nazw tzn. mogą pojawić się systemowe procedury składowane o tej samej nazwie.
- Jeśli aplikacja wywołuje procedurę poprzez samą jej nazwę lub jest w schemacie dbo nie korzystając z wyspecyfikowanych nazw schemy to wywoła się systemowa procedura składowana, a nie procedura użytkownika.
Po trzecie:
Używanie prefiksu sp_ jest wątpliwą praktyką, ponieważ stwarza możliwość posiadania dwóch różnych wersji tej samej procedury i można przez pomyłkę wywołać tą złą.
Po czwarte:
Systemowa procedura składowana tak naprawdę jest specjalnie oznakowana w tabelach systemowych, a prefiks to jedynie nazewnictwo. Samo dodanie prefiksu do nowo utworzonej procedury nie sprawi, że będzie ona procedurą systemową.
Po piąte:
Nie mniej jednak przestrzegając pewnych zasad można tworzyć nie tylko procedury składowane ale również tabele i widoki w bazie master z prefiksem sp_ . Obiekty te są wtedy dostępne z poziomu dowolnej bazy danych użytkownika.
W ten sposób tworzy się procedury, które służą nam jako własne narzędzia.
Linki:
