Migracja Oracle na MSSQL cz. IV
Definiowanie obiektów bazy danych
Możliwa jest łatwa migracja obiektów bazy danych Oracle (tabel, widoków oraz indeksów) do SQL Server, ponieważ każdy RDBMS dokładnie spełnia warunki standardu SQL-92 w dziedzinie definicji obiektów.
Więcej informacji na ten temat można znaleźć w dziale „Logiczne komponenty bazy danych” (ang. Logical Database Components) w Książkach w Sieci o SQL Server (ang. SQL Server Books Online).
Konwersja tabel, indeksów oraz definicji widoków SQL z Oracle na tabele, indeksy oraz definicje widoków SQL Server wymaga tylko względnie prostych zmian składni. Poniższa tabela pokazuje kilka różnic między Oracle a SQL Server w obiektach bazy danych.
Kategoria |
SQL Server firmy Microsoft |
Oracle |
Liczba kolumn |
1,024 |
1,000 |
Rozmiar wiersza |
8,060 bajtów, włącznie z 16 bajtami wskazującymi na każdą kolumnę tekstową lub obrazu |
Nieograniczony (na jeden wiersz może przypadać maksymalnie jeden typ long lub long raw) |
Maksymalna liczba wierszy |
Nieograniczona |
Nieograniczona |
Pamięć typu BLOB (ang. Binary Large OBject - duży obiekt binarny) |
16-bajtowy wskaźnik przechowywany w wierszu. Dane przechowywane na innych stronach danych |
Na jeden wiersz może przypadać maksymalnie jeden typ long lub long raw, musi znajdować się on na końcu wiersza, dane przechowywane są w tym samym bloku (blokach), co wiersz |
Zgrupowane indeksy tabel |
1 na tabelę |
1 na tabelę (tabele zorganizowane według indeksów) |
Niezgrupowane indeksy tabel |
249 na tabelę |
Bez ograniczeń |
Maksymalna liczba kolumn w pojedynczym indeksie |
16 |
32 |
Maksymalna długość wartości kolumn w ramach jednego indeksu |
900 bajtów |
40% bloku |
Konwencja nazywania tabel |
[[[Serwer.]baza_danych.]właściciel.] |
[schemat.]nazwa_tabeli |
Konwencja nazywania widoków |
[[[Serwer.]baza_danych.]właściciel.] |
[schemat.]nazwa_tabeli |
Konwencja nazywania indeksów |
[[[Serwer.]baza_danych.]właściciel.] |
[schemat.]nazwa_tabeli |
Przyjęto założenie, że osoba dokonująca migracji posiada skrypt lub program Oracle SQL używany do tworzenia obiektów danej bazy danych. Ten skrypt lub program należy skopiować i dokonać w nim poniżej opisanych zmian. Każda zmiana zostanie dokładnie omówiona w dalszej części niniejszego działu. Należy więc:
Upewnić się, że identyfikatory obiektów bazy danych odpowiadają konwencji nazewniczej SQL Server firmy Microsoft. Konieczna może być tylko zmiana nazw indeksów.
Wziąć pod uwagę parametry pamięci przechowującej, których wymagać będzie baza danych SQL Server. W przypadku używania RAID nie są wymagane żadne parametry pamięci.
Zmodyfikować definicje ograniczeń Oracle tak, aby działały na SQL Server. Jeśli tabele zajmują kilka baz danych, należy użyć triggerów do wymuszenia relacji klucza obcego.
Zmodyfikować wyrażenia CREATE INDEX w celu wykorzystania zalet indeksów klastrowych (ang. clustered), które wymuszają fizyczne umieszczenie rekordów w określonej kolejności.
Użyć Usług Transformacji Danych (DTS - ang. Data Transformation Services) do utworzenia nowych wyrażeń CREATE TABLE. Należy obejrzeć te wyrażenia, zwracając uwagę na to, jak typy danych Oracle są mapowane na typu danych SQL Server.
Usunąć wszystkie wyrażenia CREATE SEQUENCE i zastąpić ich użycie kolumnami IDENTITY lub uniqueidentifier w wyrażeniach CREATE TABLE lub ALTER TABLE.
W razie konieczności - zmodyfikować wyrażenia CREATE VIEW.
Usunąć wszelkie odwołania do synonimów.
Ocenić wykorzystanie tymczasowych tabel SQL Server oraz ich przydatność dla danej aplikacji.
Zmienić wszystkie polecenia CREATE TABLE…AS SELECT z Oracle na wyrażenia SELECT…INTO SQL Server.
Ocenić potencjalne wykorzystanie zdefiniowanych przez użytkownika zasad, typów danych oraz wartości domyślnych.
Identyfikatory obiektów bazy danych
Poniższa tabela porównuje sposoby obsługiwania identyfikatorów obiektów przez Oracle oraz SQL Server. W większości przypadków zmiana nazwy obiektów przy migracji do SQL Server nie jest konieczna.
Oracle |
SQL Server firmy Microsoft |
1-30 znaków długości. |
1-128 znaków Unikodu długości |
Nazwy identyfikatorów muszą zaczynać się znakiem z alfabetu oraz zawierać znaki alfanumeryczne lub znaki _, $ oraz #. |
Nazwy identyfikatorów muszą zaczynać się znakiem alfanumerycznym, lub _, a dalej mogą zawierać dowolne inne znaki. |
Nazwy przestrzeni tabel muszą być unikalne. |
Nazwy baz danych muszą być unikalne |
Nazwy identyfikatorów muszą być unikalne w ramach kont użytkownika (schematów). |
Nazwy identyfikatorów muszą być unikalne w ramach kont użytkownika bazy danych. |
Nazwy kolumn muszą być unikalne w ramach tabel oraz widoków. |
Nazwy kolumn muszą być unikalne w ramach tabel oraz widoków. |
Nazwy indeksów muszą być unikalne w ramach schematów użytkownika. |
Nazwy indeksów muszą być unikalne w ramach nazw tabel bazy danych. |
Kwalifikowanie nazw tabel
Podczas sięgania do tabel istniejących w koncie użytkownika w systemie Oracle, tabelę można wybrać po prostu używając jej nazwy - bez kwalifikatora. Sięganie do tabel w innych schematach systemu Oracle wymaga poprzedzenia nazwy tabeli nazwą schematu oddzieloną pojedynczą kropką (.). Synonimy systemu Oracle mogą zapewnić dodatkową przezroczystość lokalizacji.
SQL Server używa innej konwencji odwoływania się do tabel. Ponieważ jedno konto logowania SQL Server może utworzyć tabele o jednakowej nazwie w kilku bazach danych, w celu uzyskania dostępu do tabel oraz widoków używana jest następująca konwencja: [[nazwa_bazy_danych.]nazwa_właściciela.]nazwa_tabeli.
Uzyskiwanie dostępu do tabeli… |
Oracle |
SQL Server firmy Microsoft |
We własnym koncie |
SELECT * |
SELECT * FROM USER_DB.STUDENT_ |
W innym schemacie |
SELECT * FROM STUDENT_ADMIN.STUDENT |
SELECT * FROM OTHER_DB.STUDENT_ |
Poniżej przedstawiono wskazówki dotyczące nazywania tabel oraz widoków SQL Server:
Używanie nazwy bazy danych oraz nazwy użytkownika jest opcjonalne. Kiedy tabela jest określana tylko przez jej nazwę (na przykład: STUDENT), SQL Server poszukuje tej tabeli w koncie aktualnego użytkownika w aktualnej bazie danych. Jeśli jej nie znajdzie, poszukuje obiektu o tej samej nazwie w danej bazie danych, posiadanego przez użytkownika o zastrzeżonej nazwie dbo. Nazwy tabel muszą być unikalne w ramach danego konta użytkownika w ramach danej bazy danych.
To samo konto logowania SQL Server może posiadać tabele o tej samej nazwie, znajdujące się w kilku bazach danych. Dla przykładu, konto ENDUSER1 posiada następujące obiekty bazy danych: USER_DB.ENDUSER1.STUDENT oraz OTHER_DB.ENDUSER1.STUDENT. Kwalifikatorem jest nazwa użytkownika bazy danych, a nie nazwa logowania do SQL Server, ponieważ nie muszą one być jednakowe.
Jednocześnie inni użytkownicy w tych bazach danych mogą posiadać obiekty o tej samej nazwie:
USER_DB.DBO.STUDENT
USER_DB.DEPT_ADMIN.STUDENT
USER_DB.STUDENT_ADMIN.STUDENT
OTHER_DB.DBO.STUDENT
Dlatego też zaleca się dołączanie nazwy właściciela jako części odwołania do obiektu w bazie danych. Jeśli aplikacja posiada kilka baz danych, zaleca się dołączenie także nazwy bazy danych jako części odwołania do obiektu. Jeśli zapytanie obejmuje kilka serwerów, należy dołączyć także nazwę serwera.
Każde połączenie do SQL Server posiada bieżący kontekst bazy danych ustalany w momencie logowania za pomocą wyrażenia USE. Dla przykładu załóżmy następujący scenariusz:
Użytkownik, używając konta ENDUSER1, zaloguje się do bazy danych USER_DB. Następnie ten użytkownik żąda dostępu do tabeli STUDENT. SQL Server szuka tabeli ENDUSER1.STUDENT. Jeśli tabela zostaje znaleziona, SQL Server wykonuje żądaną operację na tabeli USER_DB.ENDUSER1.STUDENT. Jeśli tabela nie zostanie znaleziona w koncie bazy danych o nazwie ENDUSER1, SQL Server zacznie szukać tabeli USER_DB.DBO.STUDENT w koncie dbo dla tej bazy danych. Jeśli tabeli nadal nie można znaleźć, SQL Server zwraca komunikat o błędzie sygnalizujący, że dana tabela nie istnieje.
Jeśli inny użytkownik, na przykład DEPT_ADMIN, jest właścicielem tej tabeli, jej nazwę trzeba poprzedzić nazwą użytkownika bazy danych (DEPT_ADMIN.STUDENT). W przeciwnym przypadku zakłada się, że nazwą bazy danych jest nazwa bazy danych będącej w danej chwili w kontekście.
Jeśli poszukiwana tabela znajduje się w innej bazie danych, nazwa tej bazy danych musi zostać użyta jako część odwołania. Na przykład w celu uzyskania dostępu do tabeli STUDENT, której właścicielem w bazie danych OTHERDB jest ENDUSER1, należy użyć odwołania w postaci OTHER_DB.ENDUSER1.STUDENT.
Właściciela obiektu można pominąć rozdzielając nazwę tabeli oraz nazwę bazy danych dwoma kropkami. Na przykład, jeśli aplikacja odwołuje się do STUDENT_DB..STUDENT, SQL Server poszukuje co następuje:
STUDENT_DB.aktualny_użytkownik.STUDENT
STUDENT_DB.DBO.STUDENT
Jeśli aplikacja używa tylko pojedynczej bazy danych w danym momencie, pominięcie jej nazwy w odwołaniu do obiektu ułatwia wykorzystanie aplikacji do innej bazy danych. Wszystkie odwołania do obiektów domyślnie korzystają z aktualnie używanej bazy danych. Jest to użyteczne, jeśli testowa baza danych ma być przechowywana na tym samym serwerze, co produkcyjna baza danych.
Migracja Oracle na MSSQL cz. V
Data publikacji: 2003-05-13 9:58|
Odsłon: 1095|
Dodał: Jacek Kolonko
Tworzenie tabel
Ponieważ Oracle oraz SQL Server obsługują konwencje podstawowego poziomu SQL-92 związane z identyfikowaniem obiektów RDBMS, składnia CREATE TABLE jest podobna.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE |
CREATE TABLE [serwer.][baza_danych.][właściciel.] nazwa_tabeli |
W nazwach obiektów bazy danych Oracle duże i małe litery nie są rozróżniane. W przypadku SQL Server wielkość znaków w nazwach obiektów bazy danych może być rozróżniana - zależy to on wybranych opcji instalacji.
Podczas pierwszej konfiguracji SQL Server, domyślnym porządkiem sortowania jest porządek słownikowy z rozróżnianiem wielkości liter. (Ustawienie to można zmienić używając Programu Konfiguracyjnego SQL Server.) Ponieważ nazwy obiektów w Oracle są zawsze unikalne, nie powinny pojawić się żadne problemy podczas migracji obiektów bazy danych do SQL Server. Zaleca się, aby wszystkie nazwy kolumn oraz tabel zarówno w Oracle, jak i SQL Server składały się z całości z dużych liter - pomoże to uniknąć kłopotów w przypadku zainstalowania przez użytkownika SQL Server rozróżniającego duże i małe litery.
Parametry pamięci indeksów oraz tabel
W przypadku SQL Server firmy Microsoft użycie RAID zwykle upraszcza rozmieszczanie obiektów bazy danych. Klastrowe (ang. clustered) indeksy SQL Server są zintegrowane w strukturze tabeli, podobnie jak zorganizowane według indeksów tabele w Oracle.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE DEPT_ADMIN.DEPT(
) |
CREATE TABLE |
Tworzenie tabel za pomocą wyrażeń SELECT
W przypadku używania Oracle tabelę można utworzyć za pomocą dowolnego właściwego polecenia SELECT. SQL Server firmy Microsoft oferuje taką samą funkcjonalność za pomocą innej składni.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE STUDENTBACKUP AS SELECT * FROM STUDENT |
SELECT * INTO STUDENTBACKUP |
Wyrażenie SELECT …INTO tworzy nową tabelę i zapełnia ją wynikami wyrażenia SELECT. Referencyjne definicje integralności nie są przenoszone do nowej tabeli. Jeśli tryb odtwarzania bazy danych jest ustawiony na FULL (pełny), wtedy wyrażenie SELECT …INTO zostanie zarejestrowane w dzienniku transakcji - możliwe będzie odtworzenie od tego punktu w czasie.
Więcej informacji na temat modeli odtwarzania baz danych można znaleźć w dziale „Wybieranie Modelu Odtwarzania” (ang. Selecting a Recovery Model) w dokumentacji SQL Server Books Online.
Widoki
Składnia używana do tworzenia widoków w SQL Server jest podobna do składni w Oracle.
Oracle |
SQL Server firmy Microsoft |
CREATE [OR REPLACE] [FORCE | |
CREATE VIEW [właściciel.] nazwa_widoku |
Widoki SQL Server wymagają, aby tabele istniały, a właściciel widoku miał prawa dostępu do tabel wskazanych w wyrażeniu SELECT (podobnie jak w przypadku opcji FORCE w Oracle).
Domyślnie wyrażenia modyfikujące dane na widokach nie są sprawdzane pod kątem tego, czy wiersze, na które ma to wpływ, znajdują się w obszarze widoku. Aby sprawdzić wszystkie modyfikacje, należy użyć opcji WITH CHECK OPTION (z opcją sprawdzania). Podstawowa różnica opcji WITH CHECK OPTION pomiędzy Oracle oraz SQL Server jest taka, że Oracle definiuje ją jako ograniczenie, a SQL Server - nie. Poza tym w obu działa tak samo.
Oracle udostępnia przy definiowaniu widoków opcję WITH READ ONLY (z tylko do odczytu). Oparte na SQL Server aplikacje mogą uzyskać ten sam wynik dając tylko prawa dostępu SELECT użytkownikom widoku.
Zarówno widoki SQL Server, jaki i Oracle obsługują kolumny wyprowadzone, wykorzystujące wyrażenia arytmetyczne, funkcje oraz wyrażenia stałe. Niektóre z charakterystycznych cech SQL Server to:
Wyrażenia modyfikujące dane (INSERT oraz UPDATE) mogą działać na wielu widokach, jeśli wyrażenie modyfikujące dane ma wpływ tylko na tabelę podstawową. Wyrażenia modyfikujące dane nie mogą być użyte w jednym wyrażeniu w stosunku do więcej niż jednej tabeli.
READTEXT oraz WRITETEXT nie mogą zostać użyte na kolumnach text oraz image znajdujących się w widokach.
Klauzule ORDER BY, COMPUTE, FOR BROWSE oraz COMPUTE BY nie mogą być używane.
Słowo kluczowe INTO nie może być używane w widoku.
Gdy widok jest zdefiniowany z zewnętrznym złączeniem i zostanie w stosunku do niego zadane zapytanie z kwalifikatorem wskazującym kolumnę z wewnętrznej tabeli zewnętrznego złączenia, wyniki działania w przypadku SQL Server oraz Oracle mogą się różnić. W większości przypadków widoki Oracle są łatwe do przełożenia na widoki SQL Server .
Oracle |
SQL Server firmy Microsoft |
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA |
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA |
Widoki indeksowane
Wersja korporacyjna (ang. Enterprise Edition) pakietu SQL Server 2000 wprowadza widoki indeksowane. Podobnie jak widoki zmaterializowane w Oracle, widoki indeksowane są widokami, które fizycznie przechowują ich indeksowane zbiory wynikowe na dysku. Widoki indeksowane są automatycznie uaktualniane w momencie uaktualnienia ich danych źródłowych. Widoki indeksowane są w stanie znacznie poprawić wydajność systemów wspomagających podejmowanie decyzji, w których ogromne ilości danych muszą zostać podsumowane, oraz w systemach aktywnego przetwarzania transakcji (OLTP - ang. online transaction processing), w których wiele złączeń jest używanych do podsumowywania powoli zmieniających się danych.
Widoki indeksowane SQL Server nie mogą odwoływać się do obiektów w zdalnych bazach danych. SQL Server posiada wiele wewnętrznych schematów replikacji, włącznie z replikacją Snapshot (`Zdjęcie') oraz Transactional (`Transakcyjna'), które zapewniają większą funkcjonalność pod względem przenoszenia danych pomiędzy serwerami.
W przypadku SQL Server, aby widok mógł stać się widokiem indeksowanym, musi zostać zdefiniowany z opcją SCHEMABINDING blokującą leżące poniżej schematy tabel, do których odwołuje się widok. Widok może także odnosić się tylko do funkcji niedeterministycznych. Pierwszy indeks utworzony w stosunku do widoku musi być zgrupowanym unikalnym indeksem. Jest to wymagane, aby SQL Server był w stanie szybko lokalizować wiersze w momencie modyfikacji danych w podstawowym schemacie.
Więcej informacji na ten temat można znaleźć w dziale „Projektowanie Widoku Indeksowanego” (Designing an Indexed View) w SQL Server Books Online.
Migracja Oracle na MSSQL cz. VI
Data publikacji: 2003-05-16 9:25|
Odsłon: 2739|
Dodał: Jacek Kolonko
Indeksy
SQL Server firmy Microsoft udostępnia zgrupowaną oraz niezgrupowaną strukturę indeksów. Indeksy te składają się ze stron tworzących rozgałęziającą się strukturę, znaną jako B-drzewo (podobną do struktury B-drzewa indeksów w Oracle). Strona startowa (poziom „korzenia”) określa zakresy wartości w tabeli. Każdy zakres na stronie na poziomie „korzenia” wskazuje na następną stronę (węzeł decyzyjny), która zawiera bardziej ograniczony zakres wartości dla tabeli. Następnie te węzły decyzyjne mogą wskazywać na inne węzły decyzyjne, dalej zawężając zakres wyszukiwania. Końcowy poziom tej rozgałęziającej się struktury określany jest jako poziom liści.
Indeksy B-drzewa
Indeksy klastrowe
Indeksy klastrowe są implementowane w Oracle jako tabele zorganizowane według indeksów. Indeks klastrowy to indeks, który został fizycznie zespolony z tabelą. Tabela i ten indeks znajdują się w tym samym obszarze pamięci. Taki indeks fizycznie przestawia wiersze danych zgodnie z porządkiem indeksowania, formując pośrednie węzły decyzyjne. Strony liści indeksu zawierają dane z samej tabeli. Taka architektura zezwala wyłącznie na jeden indeks zgrupowany na każdą tabelę. SQL Server firmy Microsoft automatycznie tworzy indeks zgrupowany dla tabeli zawsze, kiedy ograniczenie PRIMARY KEY lub UNIQUE jest ustanowione dla tej tabeli. Indeksy zgrupowane są przydatne dla:
podstawowych kluczy
kolumn, które nie są uaktualniane.
zapytań zwracających zakres wartości za pomocą operatorów takich jak BETWEEN (pomiędzy), >, >=, <, oraz <=, dla przykładu:
SELECT * FROM STUDENT WHERE GRAD_DATE
BETWEEN '1/1/97' AND '12/31/97'zapytań zwracających duże zbiory wyników, jak na przykład:
SELECT * FROM STUDENT WHERE LNAME = 'SMITH'kolumn używanych do operacji sortowania (ORDER BY, GROUP BY).
Przykładowo w tabeli STUDENT pomocne może być dołączenie niezgromadzonego indeksu na podstawowym kluczu ssn oraz indeksu zgromadzonego utworzonego na kolumnach lname, fname (nazwisko, imię), ponieważ właśnie w ten sposób studenci są często grupowani.dystrybucji czynności uaktualniających po całej tabeli w celu uniknięcia powstawania „gorących miejsc” (ang. hot spots). „Gorące miejsca” często powstają w wyniku operacji wstawiania przez wielu użytkowników do tabeli z rosnącym kluczem. Ten scenariusz aplikacji zwykle odnosi się do niskopoziomowego blokowania.
Porzucanie oraz odtwarzanie zgrupowanych indeksów to popularna technika reorganizacji tabeli w SQL Server. Jest to łatwy sposób na zapewnienie, że strony danych stanowią na dysku ciągły obszar, oraz na przywrócenie wolnego miejsca w tabeli. Przypomina to eksportowanie, porzucanie, a następnie importowanie tabeli w Oracle.
Zgrupowany indeks w SQL Server zupełnie nie przypomina zgrupowania (klastra) w Oracle. Klaster w Oracle to fizyczne zgrupowanie jednej lub więcej tabel współdzielących te same bloki danych oraz używających wspólnych kolumn w charakterze klucza klastra. SQL Server nie posiada struktury podobnej do klastra z Oracle.
Ogólnie rzecz biorąc, zdefiniowanie zgrupowanego indeksu dla danej tabeli poprawia wydajność SQL Server oraz zarządzania przestrzenią. Jeśli wzorce uaktualnień lub zapytań dla danej tabeli nie są znane, możliwe jest tworzenie zgrupowanych indeksów w oparciu o klucz podstawowy.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE STUDENT_ADMIN.GRADE ( ORGANIZATION INDEX |
CREATE TABLE STUDENT_ADMIN.GRADE ( |
Indeksy zwykłe
W indeksach zwykłych dane indeksu oraz dane tabeli są fizycznie rozdzielone, a wiersze w tabeli nie się przechowywane zgodnie z porządkiem indeksu. Możliwe jest przeniesienie definicji indeksów z Oracle do SQL Server w postaci definicji indeksów zwykłych (co pokazano na poniższym przykładzie). Jednakże, w związku z wydajnością, lepsze może być wybranie jednego z indeksów danej tabeli i utworzenie go jako indeksu zgrupowanego.
Oracle |
SQL Server firmy Microsoft |
CREATE INDEX |
CREATE NONCLUSTERED INDEX |
Nazewnictwo oraz składnia indeksów
W Oracle nazwa indeksu jest unikalna w ramach danego konta użytkownika. W SQL Server nazwa indeksu musi być unikalna w ramach nazwy tabeli, ale nie musi być unikalna w ramach konta użytkownika ani w ramach bazy danych. A zatem podczas tworzenia oraz porzucania indeksu w SQL Server, należy określić zarówno nazwę tabeli, jak i nazwę indeksu. Dodatkowo wyrażenie DROP INDEX SQL Server jest w stanie porzucać wiele indeksów za jednym razem.
Oracle |
SQL Server firmy Microsoft |
CREATE [UNIQUE] INDEX [schemat].nazwa_indeksu DROP INDEX ABC; |
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] DROP INDEX USER_DB.STUDENT.DEMO_IDX, USER_DB.GRADE.DEMO_IDX |
Parametry pamięci danych indeksu
Opcja FILLFACTOR (współczynnik wypełnienia) w SQL Server działa pod wieloma względami tak samo, jak zmienna PCTFREE działa w Oracle. W miarę jak tabela rośnie, strony indeksu dzielą się w celu pomieszczenia nowych danych i czasami indeks musi się zreorganizować by było to możliwe. Współczynnik wypełnienia (w procentach) jest używany tylko podczas tworzenia indeksu i nie jest już potem utrzymywany.
Opcja FILLFACTOR (wartości zawierają się w przedziale od 0 do 100) kontroluje, jak dużo przestrzeni pozostaje wolne na stronie indeksu podczas tworzenia tego indeksu. Jeśli nie podano żadnej wielkości, używany jest domyślny współczynnik wypełnienia równy 0 - to powoduje kompletne wypełnienie stron-liści indeksu oraz pozostawienie miejsca we wszystkich stronach węzłów decyzyjnych na minimum jeden wpis (dwa wpisy w przypadku nieunikalnych indeksów zgrupowanych).
Parametr FILLFACTOR i struktura indeksu
Niższa wartość współczynnika wypełnienia początkowo redukuje dzielenie stron indeksu i zwiększa liczbę poziomów w strukturze B-drzewa indeksu. Wyższa wartość współczynnika wypełnienia powoduje efektywniejsze wykorzystanie przestrzeniu stron indeksu, wymaga mniejszej liczby operacji wejścia-wyjścia do uzyskania dostępu do danych indeksu oraz redukuje liczbę poziomów w strukturze B-drzewa indeksu.
Opcja PAD_INDEX określa, że ustawienie współczynnika wypełnienia dotyczyć będzie stron węzłów decyzyjnych oraz stron danych w indeksie.
Pomimo iż może być konieczne dostosowanie parametru PCTFREE w celu uzyskania optymalnej wydajności w Oracle, rzadko konieczne jest dołączenie opcji FILLFACTOR do wyrażenia CREATE INDEX. Współczynnik wypełnienia jest dostępny w celu dokładnego dostrojenia wydajności. Użyteczny jest wyłącznie podczas tworzenia nowego indeksu dla tabeli z istniejącymi danymi, a i wtedy jest przydatny tylko wtedy, kiedy możliwe jest dokładne przewidzenie przyszłych zmian w tych danych.
W przypadku ustawienia opcji PCTFREE równej 0 dla indeksów w Oracle, należy wziąć pod uwagę użycie współczynnika wypełnienia równego 100. Takiego ustawienia używa się, jeśli do danej tabeli nie będzie już nic dopisywane ani nie będą dokonywane żadne zmiany (tabela tylko-do-odczytu). Gdy współczynnik wypełnienia jest równy 100, SQL Server tworzy indeks z każdą stroną indeksu zapełnioną w 100 procentach.
Ignorowanie zduplikowanych kluczy
Zarówno w przypadku Oracle, jak i SQL Server użytkownicy nie mogą wstawiać zduplikowanych wartości do unikalnie indeksowanej kolumny lub kolumn. Taka próba spowoduje wygenerowanie komunikatu o błędzie. Niemniej jednak SQL Server pozwala programiście na wybranie sposobu reakcji wyrażenia INSERT oraz UPDATE na wystąpienie błędu.
Jeśli w wyrażeniu CREATE INDEX podano opcję IGNORE_DUP_KEY oraz wykonano wyrażenie INSERT lub UPDATE tworzące zduplikowany klucz, SQL Server generuje komunikat z ostrzeżeniem i ignoruje (nie wstawia) zduplikowanego wiersza. Jeśli dla danego indeksu opcja IGNORE_DUP_KEY nie została podana, SQL Server generuje komunikat błędu i anuluje całe wyrażenie INSERT (wycofując transakcję). Więcej informacji na temat tych opcji można znaleźć w SQL Server Books Online.
Indeksy dla kolumn wyliczanych
Oracle pozwala na opieranie indeksów bezpośrednio na funkcjach. SQL Server firmy Microsoft pozwala też na opieranie indeksów na kolumnach wyliczonych w ramach danej tabeli. W SQL Server tabela może składać się z wielu kolumn wyliczanych, ale musi zawierać co najmniej jedną kolumnę, która nie jest kolumną wyliczoną. Kolumny wyliczone mogą składać się albo z funkcji SQL Server, albo funkcji zdefiniowanych przez użytkownika, ale funkcje muszą być funkcjami deterministycznymi. Oznacza to, że funkcje muszą zwracać tę samą wartość za każdym razem, kiedy są wywoływane z identycznymi parametrami. Przykładowo funkcja SQL Server GETDATE() nie jest funkcją deterministyczną, ponieważ, wywoływana zawsze z tym samym parametrem, za każdym razem zwraca inną wartość (bieżącą datę).
Więcej informacji na temat tych opcji można znaleźć w dziale „Tworzenie Indeksów na Kolumnach Wyliczanych” (Creating Indexes on Computed Columns) w SQL Server Books Online.
Migracja Oracle na MSSQL cz. VII
Data publikacji: 2003-05-19 10:06|
Odsłon: 905|
Dodał: Jacek Kolonko
Używanie tabel tymczasowych
Aplikacja Oracle czasem może musieć utworzyć tabele istniejące tylko przez krótki czas. Jednak taki system musi upewnić się, że wszystkie tabele tworzone w tym celu są w którymś momencie porzucane (kasowane). Jeśli aplikacja tego nie zrobi, przestrzeń tabel szybko mogą się zapełnić - a co więcej będą miały zupełnie niezarządzalną strukturę.
Serwer SQL firmy Microsoft udostępnia obiekty baz danych w postaci tymczasowych tabel, automatycznie kasowanych gdy nie są już potrzebne. Tabele te są zawsze tworzone w bazie danych tempdb. Nazwa tabeli określa, jak długo pozostanie ona w bazie danych tempdb.
Nazwa tabeli |
Opis |
#table_name |
Ta lokalna tabela tymczasowa istnieje tylko przez czas trwania sesji lub działania procedury, która ją utworzyła. Jest automatycznie porzucana w momencie wylogowania się użytkownika lub zakończenia działania procedury, która tę tabelę utworzyła. Tabele te nie mogą być współdzielone przez użytkowników. Żadni inni użytkownicy bazy danych nie mogą uzyskać dostępu do takiej tabeli. Nie jest możliwe udzielanie ani odbieranie praw dostępu do tej tabeli. |
##table_name |
To jest globalna tymczasowa tabela, która także zwykle istnieje przez czas trwania sesji lub działania procedury, która ją utworzyła. Taka tabela może być współdzielona przez kilku użytkowników. Jest automatycznie porzucana po rozłączeniu sesji ostatniego użytkownika, który się do niej odwołuje. Wszyscy inni użytkownicy bazy danych mogą z niej korzystać. Nie jest możliwe udzielanie ani odbieranie praw dostępu do tej tabeli. |
Indeksy mogą być definiowane także dla tabel tymczasowych. Widoki mogą być definiowane tylko dla tabel bezpośrednio utworzony w tempdb - bez prefiksów # lub ##. Poniższy przykład pokazuje utworzenie tymczasowej tabeli oraz związanego z nią indeksu. W momencie wylogowania się użytkownika tabela oraz indeks są automatycznie porzucane.
SELECT SUM(ISNULL(TUITION_PAID,0)) SUM_PAID, MAJOR INTO #SUM_STUDENT
FROM USER_DB.STUDENT_ADMIN.STUDENT GROUP BY MAJOR
CREATE UNIQUE INDEX SUM STUDENT IDX ON #SUM STUDENT (MAJOR)
Może się okazać, że korzyści związane z używaniem tabel tymczasowych uzasadniają zmiany w kodzie programu.
Migracja Oracle na MSSQL cz. VIII
Data publikacji: 2003-05-20 19:04|
Odsłon: 880|
Dodał: Jacek Kolonko
Typy danych
Chociaż niektóre konwersje typów z Oracle na SQL Server są proste, konwersje innych typów mogą wymagać wzięcia pod uwagę kilku opcji. Zalecane jest użycie Kreatora Importu/Eksportu do zautomatyzowania tworzenia nowych wyrażeń CREATE TABLE. Wyrażenia te dostarczają programiście zalecaną konwersję typów danych. Potem można w razie potrzeby modyfikować te wyrażenia.
Oracle |
SQL Server firmy Microsoft |
CHAR |
char jest zalecany. Dostęp do kolumn typu char jest nieco szybszy w porównaniu z kolumnami varchar, ponieważ używają one ustalonej wielkości pamięci. |
VARCHAR2 |
varchar lub text. (Jeśli długość wartości danych w kolumnie Oracle wynosi nie więcej niż 8000 bajtów, należy użyć varchar; w przeciwnym przypadku należy użyć text.) |
RAW oraz |
varbinary lub image. (Jeśli długość wartości danych w kolumnie Oracle wynosi nie więcej niż 8000 bajtów, należy użyć varbinary; w przeciwnym przypadku należy użyć image.) |
NUMBER |
Jeśli jest to liczba całkowita pomiędzy 1 a 255, należy użyć tinyint.
Jeśli jest to liczba całkowita pomiędzy -2^63 a 2^63 należy użyć bigint. |
DATE |
datetime. |
ROWID |
Należy użyć typu kolumny identity lub typu danych uniqueidentifier oraz funkcji NEWID. |
CURRVAL, NEXTVAL |
Należy użyć typu kolumny identity, zmiennej globalnej @@IDENTITY oraz funkcji IDENT_SEED() i IDENT_INCR(). |
SYSDATE |
GETDATE() |
USER |
USER |
Używanie danych w Unikodzie
Specyfikacja Unikodu definiuje pojedynczy schemat kodowania dla praktycznie wszystkich znaków używanych w firmach na całym świecie. Wszystkie komputery spójnie tłumaczą wzorce bitowe w danych Unikodu na znaki za pomocą pojedynczej specyfikacji Unikodu. To gwarantuje, że te same wzorce bitowe będą zawsze konwertowane na te same znaki na wszystkich komputerach. Dane mogą być swobodnie przenoszone z jednej bazy danych lub komputera na inne bez martwienia się o to, czy system docelowy poprawnie przetłumaczy wzorce bitowe na znaki.
Jednym z problemów w przypadku stosowania typów danych używających jednego bajtu do kodowania znaków jest to, że ten typ danych może reprezentować tylko 256 różnych znaków. To wymusza stosowanie wielu specyfikacji kodowania (stron kodowych) dla różnych alfabetów. Nie jest także możliwa obsługa systemów takich jak japońskie znaki Kanji lub koreańskie znaki Hangul, ponieważ te alfabety mają tysiące znaków.
SQL Server firmy Microsoft przekłada wzorce bitowe w kolumnach char, varchar oraz text na znaki za pomocą definicji w stronach kodowych zainstalowanych wraz z SQL Server. Komputery klienckie używają do interpretowania wzorców bitowych znaków stron kodowych zainstalowanych w ich systemach operacyjnych. Istnieje wiele różnych stron kodowych - niektóre znaki występują w jednych, ale już nie występują w innych. Niektóre znaki są definiowane przez jeden wzorzec bitowy w jednej stronie kodowej, a w innych stronach kodowych przez inne wzorce bitowe. Podczas tworzenia międzynarodowych systemów, które muszą obsługiwać różne języki, trudne staje się takie wybranie strony kodowej dla wszystkich komputerów, które spełniałoby wymagania wielu krajów. Ponadto trudne jest także zapewnienie, by każdy komputer dokonywał właściwych translacji w przypadku łączenia się z systemem używającym innej strony kodowej.
Specyfikacja Unikodu rozwiązuje ten problemza pomoca 2 bajtów do zakodowania pojedynczego znaku. W dwóch bajtach można zawrzeć wystarczającą liczbę wzorów (65 536) w pojedynczej specyfikacji, aby obsłużyć większość używanych w biznesie języków. Ponieważ wszystkie systemy Unikodu świadomie używają tych samych wzorców bitowych do reprezentowania tych samych znaków, nie ma żadnych problemów związanych z konwersją znaków podczas przechodzenia z jednego systemu do drugiego.
W SQL Server dane Unikodu są obsługiwane przez typy danych nchar, nvarchar oraz ntext. Więcej informacji na temat typów danych w SQL Server można znaleźć w dokumentacji SQL Server Books Online.
Zdefiniowane przez użytkownika typy danych
Zdefiniowane przez użytkownika typy danych mogą być tworzone dla bazy danych model lub dla bazy danych jednego użytkownika. Jeśli zdefiniowany przez użytkownika typ danych został zdefiniowany dla bazy model, to ten typ danych jest dostępny dla wszystkich nowych baz danych użytkownika tworzonych od tego momentu włącznie. Zdefiniowany przez użytkownika typ danych jest definiowany za pomocą systemowej procedury przechowywanej sp_addtype. Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Zdefiniowanych prze użytkownika typów można używać w wyrażeniach CREATE TABLE oraz ALTER TABLE oraz dołączać do nich wartości domyślne izasady. Jeśli atrybut „zerowalności” (ang. nullability), określającego czy pole może przyjomwać wartość „pustą” - NULL, jest bezpośrednio zdefiniowany w momencie użycia zdefiniowanego przez użytkownika typu danych podczas tworzenia tabeli, jest ono ważniejsze od „zerowalności” zdefiniowanej podczas tworzenia tego typu danych.
Poniższy przykład pokazuje, jak tworzyć definiowane przez użytkownika typy danych. Argumentami są: nazwa typu użytkownika, typ danych oraz zerowalność:
sp_addtype gender_type, 'varchar(1)', 'not null'
go
Początkowo może wydawaćsię, że ta możliwość pozwoli rozwiązywać problem migracji skryptów tworzenia tabel z Oracle do SQL Server. Dla przykładu - łatwo można dodać typ danych DATE z Oracle:
sp_addtype date, datetime
Ta metoda nie działa jednak z typami danych wymagającymi zmiennych rozmiarów, takimi jak typ danych NUMBER z Oracle. Zwracany jest komunikat błędu sygnalizujący, że należy podać także długość:
sp_addtype varchar2, varchar
Go
Msg 15091, Level 16, State 1
You must specify a length with this physical type.
Kolumny timestamp SQL Server
Kolumny timestamp pozwalają na użycie uaktualnień w trybie BROWSE (przeglądanie) oraz poprawiają wydajność operacji uaktualniania kursora. timestamp to typ danych, który jest automatycznie uaktualniany za każdym razem, gdy wiersz zawierający kolumnę timestamp jest wstawiany lub uaktualniany.
Wartości timestamp w kolumnach nie są przechowywane jako data lub czas, ale jako typ binary(8) lub varbinary(8) określający kolejność zdarzeń w wierszach w tabeli. Tabela może zawierać najwyżej jedną kolumnę timestamp.
Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Migracja Oracle na MSSQL cz. IX
Data publikacji: 2003-05-26 1:30|
Odsłon: 735|
Dodał: Jacek Kolonko
Prawa dostępu na poziomie obiektów
Prawa dostępu na poziomie obiektów w SQL Server firmy Microsoft mogą być udzielane, odmawiane oraz odbierane innym użytkownikom bazy danych, grup bazy danych oraz roli public. SQL Server - w przeciwieństwie do Oracle - nie pozwala właścicielowi obiektu na udzielanie praw dostępu ALTER TABLE oraz CREATE INDEX dla obiektów. Te prawa dostępu muszą pozostać w wyłącznym posiadaniu właściciela obiektu.
Wyrażenie GRANT tworzy wpis w systemie bezpieczeństwa pozwalający użytkownikowi z bieżącej bazy danych na pracę z danymi z bieżącej bazy danych oraz na wykonywanie konkretnych wyrażeń Transact-SQL. Składnia wyrażenia GRANT jest taka sama w Oracle oraz w SQL Server.
Wyrażenie DENY z Transact-SQL tworzy wpis w systemie bezpieczeństwa zabraniający dostępu z konta bezpieczeństwa bieżącej bazy danych oraz uniemożliwia temu kontu bezpieczeństwa dziedziczenie praw dostępu poprzez członkostwo w grupach lub rolach. Oracle nie posiada wyrażenia DENY.
Wyrażenie REVOKE z Transact-SQL usuwa poprzednio wprowadzony wpis przydzielający prawa dostępu lub zabraniający dostępu dla użytkownika z bieżącej bazy danych.
Oracle |
SQL Server firmy Microsoft |
GRANT {ALL [PRIVILEGES][lista_kolumn] | lista_zezwoleń [lista_kolumn]} |
GRANT
REVOKE [GRANT OPTION FOR]
{TO | FROM}
DENY |
Więcej informacji na temat praw dostępu na poziomie obiektów można znaleźć w dokumentacji SQL Server Books Online.
W Oracle prawo dostępu REFERENCES może zostać przydzielone wyłącznie użytkownikowi. SQL Server pozwala na przydzielenie tego przywileju zarówno użytkownikom bazy danych, jak i grupom bazy danych. Przywileje INSERT, UPDATE, DELETE oraz SELECT są przydzielane w taki sam sposobów zarówno w Oracle, jak i Serwerze SQL.
Migracja Oracle na MSSQL cz. X
Data publikacji: 2003-06-03 22:57|
Odsłon: 911|
Dodał: Jacek Kolonko
Egzekwowanie zasad integralności danych
Egzekwowanie integralności danych gwarantuje jakość danych bazy danych. Dwa ważne kroki, które należy zrobić podczas planowania tabel, to określenie prawidłowych wartości dla kolumny oraz wybranie sposobu egzekwowania integralności danych w tej kolumnie. Integralność danych może należeć do czterech kategorii i może być egzekwowana na różne sposoby.
Typ integralności |
Metoda egzekwowania |
Integralność jednostki |
ograniczenie PRIMARY KEY |
Integralność domeny |
definicja domeny DEFAULT |
Integralność referencyjna |
ograniczenie FOREIGN KEY |
Integralność zdefiniowana przez użytkownika |
Wszystkie ograniczenia na poziomie kolumn oraz tabel w CREATE TABLE |
Integralność jednostki
Integralność jednostki definiuje wiersz jako unikalną jednostkę dla danej tabeli. Integralność jednostki egzekwuje integralność kolumny (kolumn) identyfikatora oraz podstawowego klucza tabeli poprzez indeksy, ograniczenia UNIQUE, ograniczenia PRIMARY KEY lub właściwości IDENTITY.
Ograniczenia nazewnictwa
Wszystkie ograniczenia należy zawsze dokładnie, bezpośrednio nazywać. W przeciwnym przypadku Oracle oraz SQL Server firmy Microsoft będą używać innych konwencji nazewnictwa do domyślnego nazwania ograniczenia. Te różnice w nazewnictwie mogą niepotrzebnie komplikować proces migracji. Różnica pojawi się w momencie porzucania lub wyłączania ograniczeń, ponieważ ograniczenia muszą być porzucane według nazwy. Składnia bezpośredniego nazywania ograniczeń jest taka sama dla Oracle oraz SQL Server:
CONSTRAINT constraint_name
Podstawowe klucze oraz unikalne kolumny
Standard SQL-92 wymaga, aby wszystkie wartości klucza podstawowego były unikalne oraz kolumny nie pozwalały na wartości puste. Zarówno Oracle, jak i Microsoft SQL Server egzekwują unikalność poprzez automatyczne tworzenie unikalnych indeksów zawsze, gdy definiowane jest ograniczenie PRIMARY KEY lub UNIQUE. Dodatkowo kolumny klucza podstawowego są automatycznie definiowane jako NOT NULL (niepuste). Dopuszczalny jest tylko jeden klucz podstawowy na tabelę.
Domyślnie dla podstawowego klucza tworzony jest zgrupowany indeks SQL Server, chociaż na życzenie może to być także indeks niezgrupowany. Indeks Oracle dotyczący kluczy podstawowych może zostać usunięty poprzez porzucenie lub wyłącznie ograniczenia, podczas gdy indeks SQL Server może być usunięty tylko poprzez porzucenie ograniczenia.
W obu RDBMS alternatywne klucze mogą zostać zdefiniowane z ograniczeniem UNIQUE. Na każdej tabeli można zdefiniować wiele ograniczeń UNIQUE. Kolumny z ograniczeniem UNIQUE są zerowalne. W przypadku SQL Server domyślnie tworzony jest indeks niezgrupowany, chyba że określono inaczej.
Podczas przeprowadzania migracji aplikacji ważne jest zauważenie, że SQL Server pozwala tylko jednemu wierszowi na posiadanie wartości NULL dla kompletnego unikalnego klucza (indeks dla jednej lub wielu kolumn), natomiast Oracle pozwala dowolnej liczbie wierszy posiadać wartości NULL dla kompletnego unikalnego klucza.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE DEPT_ADMIN.DEPT |
CREATE TABLE USER_DB.DEPT_ADMIN.DEPT |
Dodawanie oraz usuwanie ograniczeń
Wyłączanie ograniczeń może poprawić wydajność bazy danych oraz zoptymalizować efektywność procesu replikacji danych. Na przykład podczas odtwarzania lub replikacji danych tabeli na zdalnej maszynie nie jest konieczne powtarzanie kontroli ograniczeń, ponieważ integralność danych została sprawdzona w momencie ich pierwotnego wpisania do tabeli. Można tak zaprogramować aplikację Oracle, aby wyłączała oraz włączała ograniczenia (z wyjątkiem PRIMARY KEY oraz UNIQUE). Zalecane jest użycie klauzuli NOT FOR REPLICATION w celu zawieszenia ograniczeń działających na poziomie kolumn związanych z obcym kluczem oraz CHECK podczas replikacji.
Jeśli dane nie są replikowane, a konieczne jest usunięcie ograniczenia, można tego dokonać w SQL Server firmy Microsoft za pomocą opcji CHECK oraz WITH NOCHECK w wyrażeniu ALTER TABLE.
Poniższa ilustracja pokazuje porównanie tego procesu.
Porównanie zasad ustalania ograniczeń w Oracle i SQL Server 2000
W przypadku SQL Server można pominąć wszystkie ograniczenia tabeli, używając słowa kluczowego ALL wraz z klauzulą NOCHECK.
Jeśli aplikacja Oracle używa opcji CASCADE do wyłączenia lub porzucenia ograniczenia PRIMARY KEY lub UNIQUE, konieczne może być przepisanie części kodu, ponieważ opcja CASCADE wyłącza lub porzuca ograniczenia integralności zarówno dla obiektów-rodziców, jak i dla związanych z nimi potomków.
Oto przykład używanej składni:
DROP CONSTRAINT DEPT_DEPT_PK CASCADE
Oparte na SQL Server aplikacje muszą zostać tak zmodyfikowane, aby najpierw porzucały ograniczenia w obiektach potomnych, a następnie w obiektach-rodzicach. Na przykład do porzucenia ograniczenia PRIMARY KEY dla tabeli DEPT porzucone muszą być obce klucze dla kolumn STUDENT.MAJOR oraz CLASS.DEPT. Oto przykład używanej składni:
ALTER TABLE STUDENT
DROP CONSTRAINT STUDENT_MAJOR_FK
ALTER TABLE CLASS
DROP CONSTRAINT CLASS_DEPT_FK
ALTER TABLE DEPT
DROP CONSTRAINT DEPT_DEPT_PK
Składnia ALTER TABLE dodająca oraz porzucająca ograniczenia jest prawie identyczna dla Oracle oraz dla SQL Server.
Migracja Oracle na MSSQL cz. XI
Generowanie wartości unikalnych
Tabela SQL Server może mieć jedną kolumnę zdefiniowaną jako kolumnę tożsamości (ang. identity), która będzie automatycznie zwiększającym się polem liczbowego typu danych. SQL Server automatycznie śledzi wstawienia i dodaje do rekordu wartość dla kolumny tożsamości. Jeśli aplikacja wykorzystuje do generowania unikalnych wartości całkowitych dla kolumny opcji SEQUENCE z Oracle, ten mechanizm można zastąpić polem tożsamości.
Kategoria |
Mechanizm IDENTITY SQL Server firmy Microsoft |
Składnia |
CREATE TABLE nowi_pracownicy |
Liczba kolumn tożsamości na tabelę |
Jedna |
Dozwolone wartości puste |
Nie |
Użycie domyślnych ograniczeń, wartości |
Nie można używać |
Egzekwowanie unikalności |
Tak |
Generowanie zapytań o maksymalny bieżący numer tożsamości po zakończeniu działania wyrażeń INSERT, SELECT INTO lub wyrażenia masowego kopiowania |
@@IDENTITY (funkcja) |
Zwraca wartość `ziarna' podaną podczas tworzenia kolumny tożsamości |
IDENT_SEED('nazwa_tabeli') |
Zwraca wartość przyrostu podaną podczas tworzenia kolumny tożsamości |
IDENT_INCR('nazwa_tabeli ') |
Składnia SELECT |
Słowo kluczowe IDENTITYCOL może zostać użyte w wyrażeniach SELECT, INSERT, UPDATE oraz DELETE w miejsce nazwy kolumny przy odwoływaniu się do kolumny posiadającej właściwość IDENTITY. |
Chociaż właściwość IDENTITY automatyzuje numerowanie wierszy w ramach jednej tabeli, osobne tabele, każda z własną kolumną identyfikatorów, mogą generować takie same wartości. Jest to spowodowane tym, że właściwość IDENTITY ma gwarancję unikalności tylko dla tabeli, w której została użyta. Jeśli aplikacja musi wygenerować kolumnę identyfikatora, która jest unikalna w całej bazie danych lub w każdej bazie danych w każdym komputerze podłączonym do sieci na całym świecie, należy użyć właściwości ROWGUIDCOL, typu danych uniqueidentifier oraz funkcji NEWID. SQL Server używa globalnie unikalnych kolumn identyfikatorów do replikacji typu merge w celu zapewnienia unikalnej identyfikacji wierszy w wielu kopiach tabeli.
Jeśli dana aplikacja używa wyrażenia SEQUENCE z Oracle do generowania unikalnej wartości, która jest następnie łączona z inną wartością w celu utworzenia unikalnego łańcucha znaków, konieczne będzie napisanie własnego kodu - w postaci triggera lub procedury przechowywanej - który będzie sam generował połączony łańcuch znaków.
Więcej informacji na temat tworzenia oraz modyfikowania kolumn uniqueidentifier można znaleźć w dokumentacji SQL Server Books Online.
Integralność domeny
Integralność domeny egzekwuje prawidłowe wpisy dla danej kolumny i jest egzekwowana poprzez ograniczanie typu (poprzez typy danych), formatu (poprzez ograniczenia CHECK) lub zakres możliwych wartości (poprzez ograniczenia REFERENCE oraz CHECK).
Ograniczenia DEFAULT oraz CHECK
Oracle traktuje parametr domyślny (ang. default) jako właściwość kolumny, zaś SQL Server traktuje go jako ograniczenie. Ograniczenie DEFAULT SQL Server może zawierać wartości stałe, wbudowane funkcje niepobierające argumentów (funkcje bezparametrowe) albo NULL.
W celu ułatwienia migracji właściwości kolumny DEFAULT z Oracle, należy w SQL Server zdefiniować ograniczenia DEFAULT na poziomie kolumn bez stosowania nazw ograniczeń. SQL Server generuje unikalne nazwy dla każdego ograniczenia DEFAULT.
Składnia używana do definiowania ograniczeń CHECK jest taka sama w Oracle i w SQL Server. Warunek przeszukiwania musi sprowadzać się do wyrażenia typu Boolean i nie może zawierać podkwerend. Ograniczenie CHECK na poziomie kolumny może odnosić się tylko do ograniczanej kolumny, a na poziomie tabeli - tylko do kolumn w ograniczanej tabeli. Dla jednej tabeli można zdefiniować wiele ograniczeń CHECK. Składnia SQL Server pozwala na utworzenie tylko jednego ograniczenia CHECK dla kolumny w wyrażeniu CREATE TABLE, a to ograniczenie może mieć wiele warunków.
Najlepszym sposobem na przetestowanie zmodyfikowanych wyrażeń CREATE TABLE jest użycie Analizatora Zapytań SQL (Query Analyzer) w SQL Server i zanalizowanie samej składni. Panel wyników pokazuje ewentualne błędy. Więcej informacji na temat składni można znaleźć w dokumentacji SQL Server Books Online.
Oracle |
SQL Server firmy Microsoft |
CREATE TABLE STUDENT_ADMIN.STUDENT ( |
CREATE TABLE USER_DB.STUDENT |
Uwaga Składnię zasad oraz wartości domyślnych w SQL Server pozostawiono w celu zachowania kompatybilności wstecz, ale użycie ograniczeń CHECK oraz DEFAULT jest zalecane w przypadku tworzenia nowych aplikacji. Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Zerowalność czyli wartości NULL
SQL Server firmy Microsoft oraz Oracle tworzą ograniczenia kolumn w celu wyegzekwowania zerowalności (innymi słowy - określenia czy dana kolumna może przyjmować wartość „pustą” NULL czy też nie).. Domyślną wartością w kolumnie Oracle jest NULL, chyba że w wyrażeniach CREATE TABLE lub ALTER TABLE podano parametr NOT NULL. W przypadku SQL Server ustawienia bazy danych oraz sesji mogą znieść domyślne zachowanie zerowalności typu danych użytego w definicji kolumny.
Wszystkie skrypty SQL (czy to z Oracle, czy z SQL Server) powinny bezpośrednio definiować zarówno NULL, jak i NOT NULL dla każdej kolumny. Jeśli ta opcja nie zostanie bezpośrednio podana, to czy dane pole będzie mogło przyjmować NULL czy też nie zależy od poniższych warunków:
Ustawienie paramettrów dla NULL |
Opis |
Kolumna jest zdefiniowana za pomocą zdefiniowanego przez użytkownika typu danych |
SQL Server używa zasad postępowania z wartościami NULL określonymi podczas tworzenia tego typu danych. W celu uzyskania informacji na ten temat należy użyć systemowej procedury przechowywanej sp_help. |
Kolumna jest zdefiniowana za pomocą systemowego typu danych |
Jeśli zawarty w systemie typ danych ma tylko jedną opcję, jest ona istotna. Aktualnie typ danych bit może zostać zdefiniowany wyłącznie jako NOT NULL. Jeśli jakiekolwiek ustawienia sesji są włączone - ON (ustawione poleceniem SET) - wtedy: · Jeśli opcja ANSI_NULL_DFLT_ON jest włączona, przypisywany jest atrybut NULL. · Jeśli opcja ANSI_NULL_DFLT_OFF jest włączona, przypisywany jest atrybut NOT NULL. Jeśli jakiekolwiek ustawienia bazy danych są skonfigurowane (zmienione wyrażeniem ALTER DATABASE), wtedy: · Jeśli opcja ANSI NULL DEFAULT ma wartość TRUE (prawda), przypisywany jest atrybut NULL. · Jeśli opcja ANSI NULL DEFAULT ma wartość FALSE (fałsz), przypisywany jest atrybut NOT NULL. |
NULL/NOT NULL |
Jeśli nie jest to bezpośrednio podane (żadna z opcji ANSI_NULL_DFLT nie jest ustawiona), sesja nie została zmieniona i baza danych ma ustawienia domyślne (ANSI NULL DEFAULT ma wartość FALSE), wtedy Serwer SQL przypisuje NOT NULL. |
Integralność referencyjna
Poniższa tabela zawiera porównanie składni używanej do definiowania ograniczeń integralności referencyjnej.
Ograniczenie |
Oracle |
SQL Server firmy Microsoft |
PRIMARY KEY |
[CONSTRAINT nazwa_ograniczenia] |
[CONSTRAINT nazwa_ograniczenia] |
UNIQUE |
[CONSTRAINT nazwa_ograniczenia] |
[CONSTRAINT nazwa_ograniczenia] |
FOREIGN KEY |
[CONSTRAINT nazwa_ograniczenia] |
[CONSTRAINT nazwa_ograniczenia]
[ON DELETE CASCADE | No Action] |
DEFAULT |
Właściwość kolumny, nieograniczenie |
[CONSTRAINT nazwa_ograniczenia] |
CHECK |
[CONSTRAINT nazwa_ograniczenia] |
[CONSTRAINT nazwa_ograniczenia] |
Klauzula NOT FOR REPLICATION jest używana do zawieszenia podczas replikacji ograniczeń z poziomu kolumn FOREIGN KEY oraz CHECK.
Klucze obce
Zasady definiowania obcych kluczy są podobne w obu RDBMS. Liczba kolumn oraz typ danych każdej kolumny podany w klauzuli obcego klucza musi odpowiadać klauzuli REFERENCES. Niepusta wartość wprowadzona to tej kolumny (kolumn) musi istnieć w tabeli oraz kolumnie (kolumnach) zdefiniowanych w klauzuli REFERENCES, zaś kolumny tabeli, do której następuje odniesienie, muszą mieć ograniczenia PRIMARY KEY lub UNIQUE.
Ograniczenia SQL Server zapewniają możliwość odwoływania się do tabel w ramach tej samej bazy danych. Do zaimplementowania integralności referencyjnej pomiędzy bazami danych należy użyć opartych na tabelach triggerów.
Zarówno Oracle, jak i SQL Server obsługują tabele odwołujące się do siebie samych, tabele, w których odwołanie (klucz obcy) może zostać oparty na jednej lub więcej kolumnach tej samej tabeli. Na przykład kolumna prereq w tabeli CLASS może odwoływać się do kolumny ccode w tabeli CLASS w celu upewnienia się, że jako warunek wstępny kursu wprowadzony został właściwy kod kursu.
W SQL Server 2000 obce klucze posiadają klauzulę ON DELETE używaną do określenia, jaką akcję należy podjąć, jeśli klucz-kandydat, na który wskazuje obcy klucz, zostanie skasowany. Opcja NO ACTION powoduje, że próba skasowania nie powiedzie się i wygeneruje błąd. Opcja CASCADE wywołuje kasowanie kaskadowe obejmujące wszystkie wiersze odwołujące się do danych wewnątrz ograniczenia FOREIGN KEY.
Migracja Oracle na MSSQL cz. XII
Integralność zdefiniowana mechanizmami stworzonymi przez programistę
Integralność zdefiniowana przez użytkownika pozwala na samodzielne zdefiniowanie dowolnych zasad określających związki pomiędzy danymi przechowywanymi w bazie.
Procedury przechowywane
Procedury przechowywane SQL Server firmy Microsoft używają do przyjmowania oraz zwracania dostarczonych przez użytkownika parametrów wyrażenia CREATE PROCEDURE. Z wyjątkiem tymczasowych procedur przechowywanych, procedury przechowywane tworzone są w bieżącej bazie danych. Poniższa tabelka pokazuje składnię dla Oracle oraz SQL Server.
Oracle |
SQL Server firmy Microsoft |
CREATE OR REPLACE PROCEDURE [użytkownik.]procedura |
CREATE PROC[EDURE] nazwa_procedury [;numer] |
W SQL Server tymczasowe procedury są tworzone w bazie danych tempdb poprzez poprzedzanie nazwy_procedury pojedynczym znakiem 'hasz' (#nazwa_procedury) w przypadku lokalnych procedur tymczasowych oraz podwójnym znakiem 'hasz' (##nazwa_procedury) w przypadku globalnych procedur tymczasowych.
Lokalna procedura tymczasowa może być używana tylko przez tego użytkownika, który ją utworzył. Zezwolenia na wykonywanie lokalnych procedur tymczasowych nie mogą być udzielane innym użytkownikom. Lokalne procedury tymczasowe są automatycznie porzucane po zakończeniu sesji użytkownika.
Globalna procedura tymczasowa jest dostępna dla wszystkich użytkowników SQL Server. W przypadku utworzenia globalnej procedury tymczasowej wszyscy użytkownicy mogą z niej korzystać, a prawo dostępu nie może zostać bezpośrednio odebrane. Globalne procedury tymczasowe są porzucane po zakończeniu sesji ostatniego użytkownika używającego danej procedury.
Procedury przechowywane SQL Server mogą być zagnieżdżone do 32 poziomów. Poziom zagnieżdżenia zwiększa się, gdy wywołana procedura rozpoczyna działanie, a zmniejsza, gdy wywołana procedura kończy swoją pracę.
Poniższy przykład pokazuje, jak procedura przechowywana w Transact-SQL może być użyta do zastąpienia zapakowanej funkcji z PL/SQL z Oracle. W poniższym przykładzie wersja w Transact-SQL jest znacznie prostsza dzięki możliwości SQL Server zwracania zbiorów wyników bezpośrednio z wyrażeń SELECT w procedurze przechowywanej - bez użycia kursora.
Oracle |
SQL Server firmy Microsoft |
CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS
'||WORKREC.LNAME||
EXCEPTION |
CREATE PROCEDURE |
Server nie obsługuje żadnych konstrukcji podobnych do pakietów Oracle oraz nie obsługuje opcji CREATE OR REPLACE przy tworzeniu procedur przechowywanych. Zamiast tego SQL Server obsługuje wyrażenia CREATE oraz ALTER służące do tworzenia oraz modyfikowania procedur przechowywanych.
Opóźnianie wykonywania procedury przechowywanej
SQL Server firmy Microsoft udostępnia wyrażenie WAITFOR, które pozwala programistom na określenie czasu, przedziału czasowego lub zdarzenia wyzwalającego wykonanie bloku wyrażeń, procedury przechowywanej lub transakcji. Jest to odpowiednik w Transact-SQL istniejącego w Oracle dbms_lock.sleep.
WAITFOR {DELAY 'czas' | TIME 'czas'}
gdzie:
DELAY:
Nakazuje Serverowi SQL poczekać na upłynięcie określonego czasu - maksymalnie do 24 godzin.
'czas'
Ilość czasu, jaką serwer ma odczekać. czas może zostać podany w jednym z akceptowalnych formatów dla danych datetime, może także być podany jako lokalna zmienna. Nie można określać dat; a zatem część z datą wartości datetime nie jest dopuszczalna.
TIME:
Nakazuje Serverowi SQL poczekać na określoną godzinę.
Na przykład:
BEGIN
WAITFOR TIME '22:20'
EXECUTE update_all_stats
END
Określanie parametrów w procedurze przechowywanej
W celu określenia parametru wewnątrz procedury przechowywanej należy użyć poniższej składni.
Oracle |
SQL Server firmy Microsoft |
Nazwa_zmiennej typ_danych |
{@parametr typ_danych } [VARYING] |
Triggery
Zarówno Oracle, jak i SQL Server firmy Microsoft posiadają triggery, które nieco różnią się w swojej implementacji.
Opis |
Oracle |
SQL Server firmy Microsoft |
Liczna triggerów na tabelę |
Bez ograniczeń |
Bez ograniczeń |
Triggery wykonywane przed INSERT, UPDATE, DELETE |
Tak |
Tak. Tę funkcjonalność można stworzyć za pomocą opcji INSTEAD OF. |
Triggery wykonywane po INSERT, UPDATE, DELETE |
Tak |
Tak |
Triggery Poziomu Wyrażeń |
Tak |
Tak |
Triggery Poziomu Wiersza |
Tak |
Nie |
Sprawdzanie ograniczeń przed wykonaniem |
Tak, chyba że trigger jest wyłączony. |
Tak. Dodatkowo jest to opcja w Usługach Transformacji Danych (ang. Data Transformation Services). |
Odwoływanie się do starych lub poprzednich wartości w triggerach UPDATE oraz DELETE |
:old |
DELETED.column |
Odwoływanie się do nowych wartości w triggerze INSERT |
:new |
INSERTED.column |
Wyłączanie triggerów |
ALTER TRIGGER |
Opcja w Usługach Transformacji Danych |
Triggery mogą być utworzone tak, aby wykonywały się albo po (trigger AFTER) wyrażeniach INSERT, UPDATE oraz DELETE, albo zamiast tych wyrażeń (trigger INSTEAD OF). Jeśli potrzebne jest wykorzystanie triggerów wykonujące się przed wyrażeniami INSERT, UPDATE oraz DELETE (jak triggery BEFORE z Oracle), konieczne będzie dodanie tych wyrażeń do kodu triggerów INSTEAD OF.
Tabele deleted (skasowane) oraz inserted (wstawione) są tabelami logicznymi (konceptualnymi) tworzonymi przez SQL Server dla wyrażeń triggerów. Strukturalnie są one podobne do tabel, dla których trigger został zdefiniowany, i przechowują stare wartości lub nowe wartości wierszy, które mogą ulec zmianie w wyniku działań użytkownika. Te tabele śledzą niskopoziomowe zmiany w Transact-SQL. Spełniają taką samą funkcję, jak niskopoziomowe triggery w Oracle. W momencie wykonania przez SQL Server wyrażenia INSERT, UPDATE lub DELETE, wiersze są dodawane jednocześnie do tabeli triggera oraz do tabel inserted oraz deleted.
Tabele inserted oraz deleted są takie same jak tabela triggera, mają takie same nazwy kolumn i takie same typy danych. Na przykład: jeśli w tabeli GRADE umieszczony zostanie trigger, tabele inserted oraz deleted będą miały poniższą strukturę.
GRADE |
inserted |
deleted |
SSN CHAR(9) |
SSN CHAR(9) |
SSN CHAR(9) |
Tabele inserted oraz deleted mogą być badane przez trigger w celu określenia, jakie rodzaje działań triggera powinny zostać podjęte. Tabela inserted jest używana w przypadku wyrażeń INSERT oraz UPDATE. Tabela deleted jest używana w przypadku wyrażeń DELETE oraz UPDATE.
Wyrażenie UPDATE korzysta zarówno z tabeli inserted, jak i z tabeli deleted, ponieważ serwer SQL zawsze kasuje stary wiersz i wstawia nowy za każdym razem, gdy wykonywana jest operacja UPDATE (uaktualnianie). W konsekwencji, gdy wykonywana jest operacja UPDATE, wiersze z tabeli inserted są zawsze duplikowane w wierszach tabeli deleted.
Poniższy przykład wykorzystuje tabele inserted oraz deleted do zastąpienia niskopoziomowego triggera PL/SQL. Do wypytania wszystkich wierszy w obu tabelach wykorzystywane jest pełne złączenie zewnętrzne.
Oracle |
SQL Server firmy Microsoft |
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES |
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES |
Trigger można utworzyć tylko w bieżącej bazie danych, chociaż możliwe jest odwoływanie się do obiektów spoza bieżącej bazy danych. W przypadku użycia nazwy właściciela jako kwalifikatora triggera, należy tego samego kwalifikatora użyć także przy nazwie tabeli.
Możliwe jest zdefiniowanie wielu triggerów AFTER dla każdego zdarzenia modyfikującego dane dla danej tabeli. Jednakże tylko jeden trigger INSTEAD OF może zostać zdefiniowany dla danej tabeli.
Triggery mogą być zagnieżdżone na głębokość 32 poziomów. Jeśli trigger zmienia tabelę, dla której istnieje inny trigger, uaktywniany jest ten drugi, a ten może następnie wywołać trzeci i tak dalej. Jeśli którykolwiek z triggerów w łańcuchu zainicjuje nieskończoną pętlę, przekroczony zostanie poziom zagnieżdżenia i trigger zostanie anulowany. Dodatkowo, jeśli trigger uaktualnienia z jednej kolumny tabeli powoduje uaktualnienie innej kolumny, trigger uaktualnienia jest aktywowany tylko raz. Deklaratywna integralność referencyjna (DRI - ang. declarative referential integrity) SQL Server nie zapewnia integralności referencyjnej pomiędzy bazami danych. Jeśli integralność referencyjna pomiędzy bazami danych jest potrzebna, należy użyć triggery. Poniższe wyrażenia nie są dozwolone w triggerze Transact-SQL:
Wyrażenia CREATE (DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, SCHEMA oraz VIEW)
Wyrażenia DROP (TRIGGER, INDEX, TABLE, PROCEDURE, DATABASE, VIEW, DEFAULT, RULE)
Wyrażenia ALTER (DATABASE, TABLE, VIEW, PROCEDURE, TRIGGER)
TRUNCATE TABLE
GRANT, REVOKE, DENY
UPDATE STATISTICS
RECONFIGURE
UPDATE STATISTICS
RESTORE DATABASE, RESTORE LOG
LOAD LOG, DATABASE
Wyrażenia DISK
SELECT INTO (ponieważ tworzy tabelę)
Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Migracja Oracle na MSSQL cz. XIII
Data publikacji: 2003-06-18 1:22|
Odsłon: 1072|
Dodał: Jacek Kolonko
Transakcje, blokowanie oraz współbieżność
Niniejszy dział wyjaśnia sposób wykonywania transakcji zarówno w Oracle, jak i w SQL Server firmy Microsoft i prezentuje różnice między procesami blokowania oraz kwestie współbieżności w obu typach baz danych.
Transakcje
W Oracle transakcja jest uruchamiana automatycznie w momencie wykonywania operacji wstawiania (ang. insert), uaktualniania (ang. update) lub kasowania (ang. delete). Aplikacja musi wydać polecenie COMMIT, aby zatwierdzić i zapisać zmiany w bazie danych. Jeśli polecenie COMMIT nie zostanie wykonane, wszystkie zmiany będą wycofywane i automatycznie anulowane.
Domyślnie SQL Server automatycznie wykonuje wyrażenie COMMIT po każdej operacji wstawiania, uaktualniania lub kasowania. Ponieważ dane zapisywane są automatycznie, nie jest możliwe cofnięcie jakichkolwiek zmian.
Możliwe jest uruchamianie transakcji SQL Server jako transakcji jawnych (ang. explicit), autozatwierdzanych (ang. autocommit) lub niejawnych (ang. implicit). Autozatwierdzanie jest zachowaniem domyślnym, które można zmienić używając trybu niejawnego albo jawnego.
Transakcje autozatwierdzane
To jest domyślny tryb dla SQL Server. Każde poszczególne wyrażenie Transact-SQL jest automatycznie zatwierdzane i zapisywane po zakończeniu działania. Nie jest koniecznie podawanie żadnych wyrażeń kontrolujących transakcje.
Transakcje niejawne
Tak samo jak i w Oracle, transakcja niejawna jest uruchamiana przy każdym wykonaniu INSERT, UPDATE, DELETE lub jakiejkolwiek innej funkcji manipulującej danymi. Aby zezwolić na wykonywanie transakcji niejawnych, należy użyć wyrażenia SET IMPLICIT_TRANSACTIONS ON.
Jeśli ta opcja jest ustawiona na ON (włączone) i nie ma żadnych oczekujących transakcji, każde wyrażenie SQL automatycznie rozpoczyna transakcję. Jeśli są jeszcze jakieś otwarte transakcje, żadna nowa nie jest uruchamiana. Otwarte transakcje muszą zostać w sposób jawny zatwierdzone przez użytkownika za pomocą wyrażenia COMMIT TRANSACTION - aby zmiany zostały wprowadzone, a wszystkie blokady zwolnione.
Transakcje jawne
Transakcja jawna to zgrupowanie wyrażeń SQL otoczonych następującymi ogranicznikami transakcji (wymagane są BEGIN TRANSACTION oraz COMMIT TRANSACTION):
BEGIN TRANSACTION [nazwa_transakcji]
COMMIT TRANSACTION [nazwa_transakcji]
ROLLBACK TRANSACTION [nazwa_transakcji | nazwa_punktu_zapisania]
SAVE TRANSACTION { nazwa_punktu_zapisania | @zmienna_punktu_zapisania }
Wyrażenie SAVE TRANSACTION działa w taki sam sposób, jak polecenie SAVEPOINT z Oracle - ustanawia w transakcji punkt zapisu, który pozwala na częściowe odwołania.
W poniższym przykładzie departament „English” zostaje zmieniony na departament „Literature”. Należy zwrócić uwagę na użycie wyrażeń BEGIN TRANSACTION oraz COMMIT TRANSACTION.
Oracle |
SQL Server firmy Microsoft |
INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) |
BEGIN TRANSACTION |
Transakcje mogą być zagnieżdżone jedna w drugiej. Jeśli takie zagnieżdżenie wystąpi, najbardziej zewnętrzna para tworzy i zatwierdza transakcję, zaś pary wewnętrzne śledzą poziom zagnieżdżenia. W momencie napotkania zagnieżdżonej transakcji zwiększana jest wartość funkcji @@TRANCOUNT. Zwykle takie zagnieżdżenie pojawia się w przypadku procedur przechowywanych lub triggerów z parami BEGIN…COMMIT wywołujących się nawzajem. Chociaż transakcje mogą być zagnieżdżone, mają one mały wpływ na zachowanie się wyrażeń ROLLBACK TRANSACTION (odwołanie transakcji).
W przypadku procedur przechowywanych oraz triggerów liczba wyrażeń BEGIN TRANSACTION musi odpowiadać liczbie wyrażeń COMMIT TRANSACTION. Procedura przechowywana zawierająca „niesparowane” wyrażenia BEGIN TRANSACTION lub COMMIT TRANSACTION w momencie uruchomienia wywołuje komunikat błędu. Składnia dopuszcza wywoływanie procedur przechowywanych oraz triggerów z wewnątrz transakcji, jeśli zawierają one wyrażenia BEGIN TRANSACTION oraz COMMIT TRANSACTION.
Zawsze, kiedy to tylko możliwe, należy rozbijać duże transakcje na mniejsze. Należy też upewnić się, że każda transakcja jest dobrze zdefiniowana w ramach pojedynczych wsadów. W celu zminimalizowania możliwych konfliktów związanych ze współbieżnością transakcje nie powinny zajmować kilku „wsadów” czy innych długotrwających operacji ani oczekiwać na instrukcje od użytkownika. Grupowanie wielu wyrażeń Transact-SQL w jedną, długo działającą transakcję może negatywnie wpłynąć na czas działania systemu i a na pewno spowoduje problemy ze współbieżnością.
W przypadku programowania z użyciem ODBC można za pomocą funkcji SQLSetConnectOption wybrać tryb jawny lub niejawny obsługiwania transakcji. W trybie niejawnym, każde zadanie przesyłane za pośrednictwem ODBC jest dodatkowo „zamykane” w nawiasy transakcyjne. Wybranie przez program ODBC jednego lub drugiego zależy od opcji połączenia AUTOCOMMIT. Jeśli opcja AUTOCOMMIT jest włączona (domyślne ustawienie ON), będzie to tryb jawny. Jeśli opcja AUTOCOMMIT jest wyłączona (OFF), będzie to tryb niejawny.
W przypadku wydawania skryptu poprzez Analizator Zapytań SQL lub jakiekolwiek inne narzędzia zapytań można albo dołączyć jawnie wyrażenie BEGIN TRANSACTION pokazane poprzednio, albo rozpocząć skrypt wyrażeniem SET IMPLICIT_TRANSACTIONS ON. Podejście wykorzystujące BEGIN TRANSACTION jest bardziej elastyczne, zaś podejście z transakcjami niejawnymi jest bardziej kompatybilne z Oracle.
Blokowanie oraz izolacja transakcji
Jedną z kluczowych funkcji DBMS (czyli systemu zarządzania bazami danych) jest zapewnienie możliwości czytania oraz zapisywania rekordów w bazie danych przez wielu użytkowników bez ryzyka odczytania niespójnych zbiorów rekordów w wyniku zmian będących w toku oraz bez wzajemnego nieumyślnego nadpisywania zmian. Oracle oraz SQL Server podchodzą do tego zadania z różnymi strategiami blokowania oraz izolacji. Podczas dokonywania konwersji aplikacji z Oracle na SQL Server należy wziąć pod uwagę te różnice - w przeciwnym przypadku wynikowa aplikacja może nie skalować się dobrze i nie być w stanie obsłużyć równocześnie wysokiej liczby użytkowników.
Oracle używa wielowersyjnego modelu spójności dla wszystkich wyrażeń SQL, które odczytują dane zarówno jawnie, jak i niejawnie. W tym modelu czytający dane - domyślnie - ani nie ustawiają blokad, ani nie czekają na zwolnienie innych blokad przed odczytaniem wierszy danych. Kiedy czytający zażąda danych, które zostały zmienione, ale jeszcze nie zostały zatwierdzone przez innych piszących (czyli procesy z aktywną transakcją), Oracle odtwarza „stare” dane za pomocą własnych segmentów odwołania w celu odtworzenia „migawki” wierszy.
Zapisujący dane w Oracle żądają blokad na danych, które uaktualniają, kasują lub wstawiają. Te blokady są utrzymywane aż do zakończenia transakcji, chroniąc nie zatwierdzone dane przed nadpisaniem przez innych użytkowników.
Jednak SQL Server - w przeciwieństwie do Oracle - używa współdzielonych blokad do zagwarantowania, że czytający dane widzą tylko dane zatwierdzone. Ci czytający biorą i zwalniają współdzielone blokady w miarę czytania danych. Te współdzielone blokady nie mają wpływu na innych czytających. Czytający czeka, aż zapisujący zatwierdzi zmiany, zanim zacznie czytać rekord. Czytający, który trzyma współdzielone blokady, blokuje także piszącego próbującego zapisać te same dane.
Szybkie zwalnianie blokad w przypadku aplikacji obsługujących dużą liczbę użytkowników jest ważniejsze w przypadku SQL Server niż w przypadku Oracle. Szybkie zwalnianie blokad sprowadza się zwykle do kwestii dbania o to, żeby transakcje były krótkie. Jeżeli jest to możliwe, transakcja nie powinna wykonywać wielu okrężnych „wypraw” do serwera ani też czekać na odpowiedź użytkownika. Należy więc tak kodować swoją aplikację, żeby pobierała dane najszybciej, jak to tylko możliwe, ponieważ nawet proste przeszukiwanie danych danych może utrzymywać współdzielone blokady na serwerze, blokując tym samym uaktualnienia.
Migracja Oracle na MSSQL cz. XIV
Data publikacji: 2003-06-27 14:29|
Odsłon: 1218|
Dodał: Jacek Kolonko
Dynamiczne blokowanie
SQL Server używa dynamicznej strategii blokowania do określenia najbardziej efektywnych pod względem kosztów blokad. SQL Server automatycznie określa - na podstawie cech charakterystycznych schematu oraz zapytania - które blokady są najwłaściwsze w momencie wywołania zapytania. Na przykład, w celu zredukowania narzutu wywołanego blokowaniem optymalizator może wybrać - podczas skanowania indeksu - blokady na poziomie stron w tym indeksie. Dynamiczne blokowanie ma następujące zalety:
Uproszczone zarządzanie bazą danych, ponieważ administratorzy bazy danych nie muszą już zajmować się progami eskalacji blokad.
Zwiększona wydajność, ponieważ SQL Server minimalizuje narzuty systemowe poprzez używanie blokad właściwych dla danego zadania.
Twórcy aplikacji mogą skoncentrować się na ich tworzeniu, ponieważ SQL Server będzie automatycznie dostosowywał blokowanie.
Niezdolność Oracle do eskalacji niskopoziomowych blokad może sprawiać kłopoty w zapytaniach zawierających klauzulę UPDATE oraz w wyrażeniach UPDATE żądających wielu wierszy. Załóżmy na przykład, że tabela STUDENT ma 100'000 wierszy, a użytkownik Oracle wywołał następujące wyrażenie (należy zauważyć, że wpływa ono na wszystkie 100'000 wierszy):
UPDATE STUDENT set (col) = (value);
RDBMS Oracle blokuje każdy wiersz w tabeli STUDENT, po jednym wierszu na raz; może to zająć sporo czasu i wiele zasobów systemowych. Oracle nie dokonuje eskalacji żądania do zablokowania całej tabeli.
To samo wyrażenie w SQL Server spowoduje eskalację standardowych niskopoziomowych blokad do blokady na poziomie tabeli, co jest zarówno efektywne, jak i szybkie.
Zmiana domyślnego sposobu blokowania
Zarówno SQL Server firmy Microsoft, jak i Oracle używają tego samego domyślnego poziomu izolacji transakcji - READ COMMITTED (odczyt zatwierdzonych). Obie bazy danych pozwalają także programiście na zażądanie niestandardowego blokowania oraz izolacji. W przypadku Oracle najpopularniejszym mechanizmami do tego służącymi są: klauzula FOR UPDATE, polecenie SELECT, polecenie SET TRANSACTION READ ONLY oraz bezpośrednie polecenie LOCK TABLE.
Ze względu na tak duże różnice strategii blokowania Oracle oraz SQL Server mapowanie tych opcji z jednego systemu na drugi nie jest łatwe. W celu lepszego zrozumienia tego procesu ważne jest poznanie opcji dostępnych w SQL Server służących do zmiany standardowego wzorca blokowania.
W przypadku SQL Server najczęściej stosowanymi mechanizmami służącymi do zmiany standardowego wzorca blokowania są: wyrażenie SET TRANSACTION ISOLATION LEVEL oraz wskazówki blokowania obsługiwane przez wyrażenia SELECT i UPDATE. Wyrażenie SET TRANSACTION ISOLATION LEVEL ustala poziomy izolacji transakcji na czas trwania sesji użytkownika. Ten ustalony wzorzec postępowania staje się dla danej sesji domyślny, chyba że w na poziomie tabeli w klauzuli FROM w wyrażeniu SQL podano wskazówkę blokowania. Izolację transakcji ustawia się w następujący sposób:
SET TRANSACTION ISOLATION LEVEL
{
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
READ COMMITTED (odczyt zatwierdzonego)
Ta opcja jest domyślna w SQL Server. Współdzielone blokady są podtrzymywane podczas odczytu danych w celu uniknięcia „brudnego” odczytu, ale dane mogą ulec zmianie przed zakończeniem transakcji, dając w wyniku niepowtarzalne odczyty lub dane-fantomy.
READ UNCOMMITTED (odczyt nie zatwierdzonego)
Implementuje „brudny” odczyt, czyli blokowanie poziomu 0 izolacji, co oznacza, że nie wydawane są żadne współdzielone blokady oraz żadne wyłączne blokady nie są honorowane. Podczas ustawienia tej opcji możliwe jest odczytanie nie zatwierdzonych - „brudnych” - danych; wartości danych mogą ulec zmianie, a wiersze w zbiorze danych mogą się pojawiać i znikać przed zakończeniem transakcji. Ta opcja ma takie samo działanie jak ustawienie parametru NOLOCK we wszystkich tabelach we wszystkich wyrażeniach SELECT w transakcji. Jest to najmniej restrykcyjny ze wszystkich czterech poziomów izolacji.
REPEATABLE READ (powtarzalny odczyt)
Blokady są umieszczane na wszystkich danych używanych w zapytaniu, co uniemożliwia innym użytkownikom uaktualnienie tych danych, ale nowe wiersze-fantomy mogą zostać wstawione do zbioru danych przez innego użytkownika i są uwzględniane w późniejszych odczytach w bieżącej transakcji. Ponieważ współbieżność jest niższa niż w przypadku domyślnego poziomu izolacji, tę opcję należy stosować tylko w razie konieczności.
SERIALIZABLE (serializowalny)
Umieszczana jest blokada zakresu na zbiorze danych, co uniemożliwia innym wstawianie wierszy oraz ich uaktualnianie w zbiorze danych aż do zakończenia transakcji. Jest to najbardziej restrykcyjny ze wszystkich czterech poziomów izolacji. Ponieważ współbieżność jest niższa, tę opcję należy stosować tylko w razie konieczności. Ta opcja ma takie samo działanie, jak ustawienie opcji HOLDLOCK dla wszystkich tabel we wszystkich wyrażeniach SELECT w danej transakcji.
SQL Server implementuje wszystkie cztery poziomy izolacji transakcji standardu SQL-92; Oracle implementuje tylko READ COMMITTED (poziom domyślny) oraz SERIALIZABLE.
Serwer SQL bezpośrednio nie obsługuje nienależącego do standardu SQL-92 poziomu izolacji transakcji READ ONLY (tylko do odczytu) dostępnego w Oracle. Jeśli transakcja w aplikacji wymaga powtarzalnego odczytywania, konieczne może być użycie poziomu izolacji SERIALIZABLE dostępnego w SQL Server. Jeśli w dostępie do bazy danych chodzi tylko o odczyt, można poprawić wydajność ustawiając opcję READ ONLY bazy danych SQL Server.
SELECT…FOR UPDATE
Wyrażenie SELECT…FOR UPDATE jest używane w Oracle wtedy, gdy aplikacja potrzebuje wygenerować pozycjonowane uaktualnienie lub kasowanie na kursorze przy użyciu składni WHERE CURRENT OF. W takim przypadku opcjonalnie można usunąć klauzulę FOR UPDATE; w SQL Server kursory są domyślnie uaktualniane.
Kursory SQL Server zwykle nie utrzymują blokad pod pobranymi wierszami -do ochrony przed wzajemnym nadpisywaniem się uaktualnień optymistycznej używają strategii współbieżności. Jeśli jeden użytkownik próbuje uaktualnić lub skasować wiersz, który został zmieniony od momentu jego wczytania do kursora, SQL Server wykrywa ten problem i generuje komunikat błędu. Aplikacja może „schwytać” (ang. trap) ten komunikat błędu i powtórzyć - w razie potrzeby - uaktualnienie lub skasowanie.
Ta optymistyczna technika obsługuje wyższą współbieżność, w przypadku gdy konflikty pomiędzy uaktualnieniami są rzadkie. Jeśli aplikacja rzeczywiście potrzebuje zapewnienia, że wiersz po jego pobraniu nie zostanie zmieniony, można użyć - w celu osiągnięcia tego efektu - wskazówki UPDLOCK w wyrażeniu SELECT.
Ta wskazówka nie blokuje innych czytających, ale uniemożliwia uzyskanie przez jakiegokolwiek potencjalnego zapisującego uzyskanie blokady uaktualnienia na tych danych. W przypadku korzystania z ODBC, można także uzyskać podobny efekt, używając SQLSETSTMTOPTION (…,SQL_CONCURRENCY)= SQL_CONCUR_LOCK. Obie te opcje redukują współbieżność.
Jawne żądanie blokad na poziomie tabeli
Serwer SQL firmy Microsoft SQL Server firmy Microsoft udostępnia takie same funkcje blokowania tabel, co Oracle.
Funkcje |
Oracle |
SQL Server firmy Microsoft |
Zablokowanie całej tabeli - pozwala innym na czytanie tabeli, ale uniemożliwia jej uaktualnianie. Standardowo blokada jest utrzymywana aż do zakończenia wykonywania wyrażenia. |
LOCK TABLE…IN SHARE MODE |
SELECT…nazwa_tabeli (TABLOCK) |
Zablokowanie tabeli aż do końca transakcji |
|
SELECT… nazwa_tabeli (TABLOCK REPEATABLEREAD) |
Blokada wyłączna - uniemożliwia innym odczytanie oraz uaktualnienie danej tabeli i utrzymuje się aż do zakończenia wykonywania polecenia lub transakcji. |
LOCK TABLE…IN EXCLUSIVE MODE |
SELECT… nazwa_tabeli (TABLOCKX) |
Określenie liczby milisekund, jaką wyrażenie będzie oczekiwać na zwolnienie blokady. |
NOWAIT działa jak “LOCK_TIMEOUT 0” |
LOCK_TIMEOUT |
Obsługa zakleszczeń
Zakleszczenie występuje wtedy, gdy jeden z procesów zablokuje zasób potrzebny w drugim procesie, a drugi proces zablokuje zasób, którego potrzebuje pierwszy proces. SQL Server automatycznie wykrywa i rozwiązuje pojawiające się zakleszczenia. W przypadku wykrycia takiej sytuacji, serwer wybiera jeden z procesów do przerwania (szasuje „koszt” przerwania procesu).
Po każdej zmianie danych kod programu powinien sprawdzić, czy nie pojawił się komunikat o kodzie 1205, który sygnalizuje zakleszczenie. Jeśli zwrócony został ten numer komunikatu, to znaczy, że taka sytuacja wystąpiła, a transakcja została odwołana. W takiej sytuacji aplikacja musi ponownie wykonać daną operację.
Zakleszczeń można zwykle uniknąć, stosując kilka prostych technik:
Korzystać z tabel w takiej samej kolejności we wszystkich częściach aplikacji.
Używać zgrupowanych indeksów w przypadku każdej tabeli w celu wymuszenia jawnego uporządkowania wierszy.
Dbać, aby transakcje były krótkie.
Więcej informacji na ten temat można znaleźć w artykule z Bazy Wiedzy firmy Microsoft (ang. Microsoft Knowledge Base) pod tytułem „Wykrywanie oraz unikanie zakleszczeń w SQL Server firmy Microsoft” (ang. Detecting and Avoiding Deadlocks in Microsoft SQL Server).
Migracja Oracle na MSSQL cz. XV
Data publikacji: 2003-07-09 16:44|
Odsłon: 1146|
Dodał: Jacek Kolonko
Zdalne transakcje
Aby przeprowadzać zdalne transakcje w Oracle, należy mieć dostęp do zdalnego węzła bazy danych poprzez łącze bazy danych. W SQL Server trzeba mieć dostęp do zdalnego serwera. Zdalny serwer to serwer sieciowy z działającym SQL Server, do którego dostęp użytkownicy mogą uzyskać poprzez swoje lokalne serwery. Gdy serwer jest skonfigurowany jako zdalny, użytkownicy mogą korzystać z jego procedur systemowych oraz procedur przechowywanych bez jawnego logowania się na tym serwerze.
Zdalne serwery ustawiane są w parach i obydwa należy tak skonfigurować, aby rozpoznawały się nawzajem jako zdalne serwery. Nazwa każdego z serwerów musi być dodana do jego partnera za pomocą systemowej procedury przechowywanej sp_addlinkedserver lub SQL Server Enterprise Manager .
Po skonfigurowaniu zdalnego serwera należy użyć systemowej procedury przechowywanej sp_addremotelogin lub SQL Server Enterprise Manager do skonfigurowania identyfikatorów zdalnego logowania dla użytkowników, którzy muszą korzystać ze zdalnego serwera. Po wykonaniu tego kroku, należy udzielić zezwolenia na wykonywanie procedur przechowywanych.
Następnie, w celu uruchomienia procedur na zdalnym serwerze, używane jest wyrażenie EXECUTE. W poniższym przykładzie jest wykonywana procedura przechowywana validate_student na zdalnym serwerze STUDSVR1, która zapisuje zwrócony status wskazujący na sukces lub porażkę w @retvalue1:
DECLARE @retvalue1 int
EXECUTE @retvalue = STUDSVR1.student_db.student_admin.validate_student '111111111'
Więcej informacji na ten temat można znaleźć w dziale „Fizyczna Architektura Bazy Danych „ (ang. „Physical Database Architecture”) w Książkach w Sieci o Serwerze SQL (ang. SQL Server Books Online).
Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Transakcje rozproszone
Oracle automatycznie inicjuje transakcję rozproszoną w momencie dokonywania zmian w tabelach znajdujących się w dwóch lub więcej połączonych siecią węzłach bazy danych. Transakcje rozproszone SQL Server używają dwufazowych usług zatwierdzania Koordynatora Transakcji Rozproszonych firmy Microsoft (MS DTC - ang. Microsoft Distributed Transaction Coordinator) dołączonego do SQL Server.
Standardowo SQL Server musi zostać poinstruowany, by wziął udział w transakcji rozproszonej. Udział SQL Server w transakcji MS DTC może zostać zainicjowany przez jedno z poniższych:
Wyrażenie BEGIN DISTRIBUTED TRANSACTION - to wyrażenie rozpoczyna nową transakcję MS DTC.
Klienta aplikacji bezpośrednio wywołującego interfejsy transakcji MS DTC.
W poniższym przykładzie należy zwrócić uwagę na rozproszone uaktualnianie zarówno lokalnej tabeli GRADE, jak i zdalnej tabeli CLASS (za pomocą procedury class_name):
BEGIN DISTRIBUTED TRANSACTION
UPDATE STUDENT_ADMIN.GRADE
SET GRADE = 'B+' WHERE SSN = '111111111' AND CCODE = '1234'
DECLARE @retvalue1 int
EXECUTE @retvalue1 = CLASS_SVR1.dept_db.dept_admin.class_name '1234', 'Basketweaving'
COMMIT TRANSACTION
GO
Jeśli aplikacja nie może zakończyć transakcji, program aplikacji przerywa jej działanie za pomocą wyrażenia ROLLBACK TRANSACTION (odwołanie transakcji). Jeśli aplikacja ulegnie awarii lub biorący udział w procesie menedżer zasobów ulegnie awarii, MS DTC anuluje transakcję. MS DTC nie obsługuje rozproszonych punktów zapisu ani wyrażeń SAVE TRANSACTION (zapisanie transakcji). Jeśli transakcja MS DTC zostanie przerwana lub anulowana, cała transakcja jest odwoływana aż do początku transakcji rozproszonej, niezależnie od punktów zapisu.
Dwufazowe przetwarzanie zatwierdzania
Dwufazowe mechanizmy zatwierdzania Oracle oraz MS DTC są podobne w działaniu. W pierwszej fazie dwufazowego zatwierdzania SQL Server menedżer transakcji żąda od każdego wpisanego na listę menedżera zasobów przygotowania zatwierdzenia. Jeśli którykolwiek menedżer zasobów nie jest w stanie go przygotować, menedżer transakcji rozsyła decyzję o przerwaniu do wszystkich jednostek biorących udział w transakcji.
Jeśli wszyscy menedżerowie DTC zasobów mogą się skutecznie przygotować, menedżer transakcji rozsyła decyzję o zatwierdzeniu - to jest druga faza procesu zatwierdzania. Kiedy menedżer zasobów jest gotowy do zatwierdzenia, nie wie, czy transakcja została zatwierdzona, czy przerwana. MS DTC prowadzi sekwencyjny dziennik, aby jego decyzje o zatwierdzeniu oraz przerwaniu były trwałe. Jeśli menedżer zasobów lub menedżer transakcji ulegną awarii, po ponownym nawiązaniu połączenia „uzgadniają” wątpliwe transakcje.
Migracja Oracle na MSSQL cz. XVI
Data publikacji: 2003-07-19 19:58|
Odsłon: 1676|
Dodał: Jacek Kolonko
Wsparcie języka SQL
W niniejszym dziale naszkicowane są podobieństwa oraz różnice między składnią języków Transact-SQL oraz PL/SQL oraz przedstawione strategie konwersji.
Wyrażenie SELECT oraz wyrażenia manipulacji danymi
Podczas przeprowadzania migracji z wyrażeń DML oraz programów PL/SQL z Oracle na SQL Server należy postępować zgodnie z następującymi wskazówkami, a więc należy:
Sprawdzić, czy składnia wszystkich wyrażeń SELECT, INSERT, UPDATE oraz DELETE jest poprawna i poczynić stosowne zmiany.
Zmienić wszystkie zewnętrzne złączenia tak, aby były zgodne ze standardem SQL-92 składni zewnętrznych złączeń.
Zastąpić funkcje Oracle stosownymi funkcjami SQL Server.
Sprawdzić wszystkie operatory porównań.
Zastąpić operator łączenia łańcuchów „||” operatorem łączenia łańcuchów „+”.
Zastąpić programy PL/SQL programami Transact-SQL.
Zastąpić wszystkie kursory PL/SQL albo nie zawierającymi kursorów wyrażeniami SELECT, albo kursorami Transact-SQL.
Zastąpić procedury, funkcje oraz pakiety PL/SQL procedurami Transact-SQL.
Dokonać konwersji triggerów PL/SQL na triggery Transact-SQL.
Użyć wyrażenia SET SHOWPLAN aby zoptymalizować wydajność zapytań.
Wyrażenia SELECT
Składnia wyrażenia SELECT używanego w Oracle jest podobna do tej z SQL Server firmy Microsoft.
Oracle |
SQL Server firmy Microsoft |
SELECT [/*+ optimizer_hints*/] |
SELECT select_list Dodatkowo:
UNION operator |
Przeznaczone specjalnie dla Oracle oparte na kosztach podpowiedzi optymalizatora nie są obsługiwane w SQL Server i muszą zostać usunięte. Zalecaną techniką jest użycie opartej na kosztach optymalizacji SQL Server. Więcej informacji na ten temat można znaleźć w niniejszym rozdziale w dziale „Optymalizacja wyrażeń SQL”.
SQL Server nie obsługuje klauzuli START WITH…CONNECT BY z Oracle. Można ją w SQL Server zastąpić własną procedurą przechowywaną, która będzie spełniała to samo zadanie. Więcej informacji na ten temat można znaleźć w rozdziale „Rozszerzanie hierarchii” (ang. Expanding Hierarchies) w SQL Server Books Online lub poszukać właściwych artykułów w Bazie Wiedzy Microsoft pod adresem http://support.microsoft.com<A HREF="http://www.microsoft.com/isapi/redir.dll?prd=support&ar=home" target=_blank>this Microsoft Web site</A>.
Operatory zbioru INTERSECT oraz MINUS z Oracle nie są obsługiwane przez SQL Server. W SQL Server do uzyskania tego samego rezultatu można użyć klauzul EXISTS oraz NOT EXISTS.
W poniższym przykładzie użyto operatora INTERSECT do znalezienia kodu kursu oraz nazwy kursu dla wszystkich zajęć, na które uczęszczają studenci. Należy zwrócić uwagę na to, jak operator EXISTS zastępuje użycie operatora INTERSECT. Zwracane dane są identyczne.
Oracle |
SQL Server firmy Microsoft |
SELECT CCODE, CNAME |
SELECT CCODE, CNAME |
Poniższy przykład używa operatora MINUS do znalezienia tych zajęć, na których nie ma studentów.
Oracle |
SQL Server firmy Microsoft |
SELECT CCODE, CNAME |
SELECT CCODE, CNAME |
Wyrażenia INSERT
Składnia wyrażenia INSERT używanego w Oracle jest podobna do tej w SQL Server firmy Microsoft.
Oracle |
Serwer SQL firmy Microsoft |
INSERT INTO |
INSERT [INTO]
| tabela_wynikowa |
Język Transact-SQL obsługuje wstawianie do tabel oraz widoków, ale nie obsługuje operacji wstawiania do wyrażeń SELECT. Jeśli kod aplikacji Oracle wykonuje wstawianie do wyrażeń SELECT, trzeba to zmienić.
Oracle |
SQL Server firmy Microsoft |
INSERT INTO (SELECT SSN, CCODE, GRADE FROM GRADE) |
INSERT INTO GRADE (SSN, CCODE, GRADE) |
Parametr Transact-SQL lista_wartości udostępnia standardowe słowo kluczowe SQL-92 - DEFAULT - które nie jest obsługiwane przez Oracle. To słowo kluczowe określa, że podczas przeprowadzania operacji wstawiania ma zostać użyta domyślna wartość dla kolumny. Jeśli dla danej kolumny nie istnieje wartość domyślna, wstawiana jest wartość pusta (NULL). Jeśli kolumna nie dopuszcza wartości pustych, zwracany jest komunikat o błędzie. Jeśli kolumna jest określona jako typ danych timestamp, wstawiana jest wartość następna w kolejności.
Słowo kluczowe DEFAULT nie może być używane w stosunku do kolumny tożsamości. Aby została wygenerowana następna w kolejności wartość, kolumny z ustawioną właściwością IDENTITY nie mogą znaleźć się na liście lista_kolumn ani klauzula_wartości. Nie jest też konieczne użycie słowa kluczowego DEFAULT w celu uzyskania domyślnej wartości dla kolumny. Podobnie jak w Oracle, jeśli nie ma odwołań do kolumny w liście lista_kolumn i jednocześnie ma ona wartość domyślną, ta domyślna wartość jest w kolumnie umieszczana. To jest najbardziej kompatybilne podejście możliwe do użycia podczas przeprowadzania migracji.
Jedną z użytecznych opcji Transact-SQL (EXECute nazwa_procedury) jest wywołanie procedury oraz wysłanie jej potoku wyjściowego do docelowej tabeli lub widoku. W Oracle nie jest to możliwe.
Wyrażenia UPDATE
Ponieważ Transact-SQL obsługuje większą część składni używanej przez polecenie UPDATE z Oracle, potrzebna jest tylko minimalna informacja.
Oracle |
SQL Server firmy Microsoft |
UPDATE |
UPDATE
| rowset_function_limited |
Wyrażenie UPDATE w Transact-SQL nie obsługuje operacji uaktualniania w stosunku do wyrażeń SELECT. Jeśli aplikacja Oracle wykonuje uaktualnienia w stosunku do wyrażeń SELECT, można zamienić wyrażenie SELECT na widok, a następnie użyć nazwy widoku w wyrażeniu UPDATE SQL Server. (Przykład został podany powyżej w dziale „Wyrażenia INSERT”).
Polecenie Oracle UPDATE może używać tylko zmiennych programu z wewnątrz bloku PL/SQL. Język Transact-SQL nie wymaga użycia bloków, aby było możliwe użycie zmiennych.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE |
w Serwerze SQL
W SQL Server słowo kluczowe DEFAULT może zostać użyte do nadania kolumnie wartości domyślnych. W Oracle nie jest możliwe nadanie kolumnie wartości domyślnej za pomocą polecenia UPDATE.
Transact-SQL oraz Oracle SQL obsługują podzapytania w wyrażeniu UPDATE. Jednakże klauzula FROM w Transact-SQL może być użyta do utworzenia uaktualnienia (UPDATE) opartego na złączeniu. Ta możliwość czyni składnię UPDATE bardziej czytelną i w niektórych przypadkach może poprawić wydajność.
Oracle |
SQL Server firmy Microsoft |
UPDATE STUDENT_ADMIN.STUDENT S |
Podzapytanie:
UPDATE STUDENT_ADMIN.STUDENT S Klauzula FROM:
UPDATE STUDENT_ADMIN.STUDENT S |
Wyrażenia DELETE
Na ogół nie jest konieczne modyfikowanie wyrażeń DELETE. W przypadku usuwania rekordów wybranych operatorem SELECT w bazie Oracle, należy zmodyfikować ich składnię dla SQL Server, ponieważ Transact-SQL nie obsługuje tej funkcji.
Transact-SQL obsługuje użycie podzapytań w klauzuli WHERE, tak samo jak i złączenia w klauzuli FROM. To drugie rozwiązanie daje bardziej wydajne rozwiązania. (Przykład został podany powyżej w dziale „Wyrażenia UPDATE”).
Oracle |
SQL Server firmy Microsoft |
DELETE [FROM] |
DELETE
[WHERE |
Wyrażenie TRUNCATE TABLE
Składnia TRUNCATE TABLE używanego w Oracle jest podobna do składni w Serwerze SQL . Wyrażenie TRUNCATE TABLE jest używane do usuwania wszystkich wierszy z tabeli, natomiast struktura tabeli oraz wszystkich jej indeksów pozostaje. W przeciwieństwie do wyrażenia DELETE, które jawnie usuwa każdy rekord tabeli, TRUNCATE TABLE usuwa dane z tabeli poprzez zwolnienie stron danych tabeli oraz zapisanie tych zwolnień do dziennika transakcji. Ponieważ wiersze nie są jawnie usuwane, to triggery DELETE nie są wyzwalane. Jeśli ograniczenie FOREIGN KEY odwołuje się do tabeli, nie może ona zostać skasowana.
Oracle |
SQL Server firmy Microsoft |
TRUNCATE TABLE nazwa_tabeli |
TRUNCATE TABLE nazwa_tabeli |
W SQL Server tylko właściciel tabeli może wygenerować to wyrażenie. W Oracle to polecenie może zostać wydane przez właściciela oraz użytkowników posiadających przywilej systemowy DELETE TABLE.
W Oracle polecenie TRUNCATE TABLE może opcjonalnie zwolnić pamięć zajmowaną przez wiersze tabeli. W Serwerze SQL wyrażenie TRUNCATE TABLE zawsze zwalnia pamięć zajmowaną przez dane tabeli oraz związane z nią indeksy.
Migracja Oracle na MSSQL cz. XVII
Data publikacji: 2003-07-30 13:57|
Odsłon: 1673|
Dodał: Jacek Kolonko
Szczególne przypadki wyrażeń SQL
Manipulowanie danymi w kolumnach Identity oraz timestamp
Sekwencje Oracle są obiektami bazy danych, które nie są bezpośrednio związane z żadną daną tabelą ani kolumną. Związek między kolumną oraz sekwencją jest implementowany w aplikacji poprzez programowe przypisanie wartości sekwencji do kolumny. Dlatego Oracle nie egzekwuje żadnych zasad podczas pracy z sekwencjami. Jednakże w SQL Server firmy Microsoft w kolumnach tożsamości (identity) wartości nie można uaktualnić, a słowo kluczowe DEFAULT nie może zostać użyte.
Domyślnie danych nie można wstawiać bezpośrednio do kolumny tożsamości. Kolumna tożsamości automatycznie generuje unikalny, kolejny numer dla każdego nowego wiersza wstawionego do tabeli. To ustawienie domyślne może zostać zmienione za pomocą poniższego wyrażenia SET:
SET IDENTITY_INSERT nazwa_tabeli ON
Przy ustawieniu opcji IDENTITY_INSERT na ON użytkownik może wstawiać dowolne wartości do kolumny tożsamości nowego wiersza. Aby uniemożliwić wprowadzenie zdublowanego numeru, na podstawie tej kolumny mysi zostać utworzony unikalny indeks. Celem tego wyrażenia jest umożliwienie użytkownikowi odtworzenie wartości wiersza, jeśli został on przypadkowo skasowany. W celu uzyskania ostatniej wartości tożsamości można użyć funkcji @@IDENTITY.
Wyrażenie TRUNCATE TABLE resetuje kolumnę tożsamości do jej oryginalnej wartości początkowej SEED. Jeśli nie jest to pożądane, należy użyć zamiast wyrażenia TRUNCATE TABLE wyrażenia DELETE bez klauzuli WHERE. Konieczna będzie też ocena wpływu tego zjawiska na migrację z Oracle, ponieważ ORACLE SEQUENCES nie są resetowane zgodnie z poleceniem TRUNCATE TABLE.
W przypadku kolumn timestamp możliwe są tylko operacje kasowania oraz wstawiania. Przy próbie uaktualnienia kolumny timestamp pojawi się następujący komunikat błędu:
Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.
Blokowanie żądanych wierszy
W celu zablokowania wierszy wybranych w poleceniu SELECT Oracle używa klauzuli FOR UPDATE. Nie jest konieczne użycie równoważnej klauzuli w SQL Server firmy Microsoft, ponieważ jest to zachowanie domyślne.
Podsumowania wierszy oraz klauzula Compute
Klauzula COMPUTE SQL Server jest używana do generowania funkcji podsumowujących wiersze (SUM, AVG, MIN, MAX oraz COUNT) pojawiające się jako dodatkowe w wynikach zapytania. Klauzula COMPUTE pozwala na obejrzenie szczegółowych oraz sumarycznych wierszy w jednym zbiorze wyników. Możliwe jest obliczanie wartości sumarycznych dla podgrup, można także wyliczyć więcej niż jedną funkcję podsumowującą dla tej samej grupy.
Składnia wyrażenia SELECT w Oracle nie obsługuje klauzuli COMPUTE. Niemniej jednak klauzula COMPUTE SQL Server działa dokładnie tak, jak polecenie COMPUTE istniejące w narzędziu zapytań Oracle SQL*Plus.
Klauzule Join
Microsoft SQL Server 2000 pozwala w klauzuli złączającej (join) na złączenie do 256 tabel - zarówno stałych, jak i tymczasowych. W Oracle nie ma limitu złączeń.
Jeżeli używane są zewnętrzne złączenia w Oracle, operator złączenia zewnętrznego (+) jest zwykle umieszczany obok potomka (obcy klucz) kolumny w złączeniu, a operator (+) identyfikuje kolumnę z mniejszą liczbą unikalnych wartości. I tak jest zawsze, chyba że obcy klucz pozwala na wartości puste - w takim przypadku operator (+) można umieścić na kolumnie-rodzicu (ograniczenie PRIMARY KEY lub UNIQUE). Nie można postawić operatora (+) po obu stronach znaku równości (=).
W przypadku SQL Server można użyć operatorów złączenia zewnętrznego *= oraz =*. Znak * jest używany do zidentyfikowania kolumny, która ma więcej unikalnych wartości. Jeśli potomek (klucz obcy) nie dopuszcza wartości pustych, znak * jest umieszczany po stronie kolumny-rodzica (ograniczenie PRIMARY KEY lub UNIQUE) znaku równości. Umieszczenie znaku * jest w gruncie rzeczy w Oracle odwrócone - nie jest możliwe umieszczenie znaku * po obu stronach znaku równości (=).
Operatory *= oraz =* są uznawane za archaiczne operatory złączenia. SQL Server obsługuje także wymienione poniżej operatory złączenia standardu SQL-92. Zalecane jest używanie tej właśnie składni. Standardowa składnia SQL-92 ma znacznie większe możliwości i mniej ograniczeń niż operatory *.
Operacja złączenia |
Opis |
CROSS JOIN |
To będzie skrzyżowanie dwóch tabel - zwraca te same wiersze, jak w przypadku złączenia starego typu, jeżeli nie podano klauzuli WHERE. Ten typ złączenia w Oracle nosi nazwę iloczynu kartezjańskiego. |
INNER |
To złączenie określa, że zwracane będą wszystkie wiersze wewnętrzne. Wszelkie niepasujące wiersze zostaną odrzucone. Ta opcja jest identyczna ze standardowym złączaniem tabel w Oracle. |
LEFT [OUTER] |
Ten typ złączenia określa, że zwrócone mają być wszystkie skrajne lewe wiersze tabeli, nawet jeśli nie zostały znalezione żadne pasujące kolumny. Ta opcja działa jak zewnętrzne złączanie (+) w Oracle. |
RIGHT [OUTER] |
Ten typ złączenia określa, że zwrócone mają być wszystkie skrajne prawe wiersze tabeli, nawet jeśli nie zostały znalezione żadne pasujące kolumny. Ta opcja działa jak zewnętrzne złączanie (+) w Oracle. |
FULL [OUTER] |
Jeśli wiersz z którejkolwiek z tabel nie spełnia kryterium wyboru, ten wiersz zostanie umieszczony w zbiorze wynikowym, a kolumny wynikowe odpowiadające drugiej tabeli uzyskają wartość pustą (NULL). Ta opcja jest równoważna z umieszczeniem w Oracle operatorów zewnętrznych złączeń po obu stronach znaku „=” (col1(+) = col2(+)), co jest niedozwolone. |
Poniższy kod przykładowy zwraca listę zajęć, na które uczęszczają wszyscy studenci. Zewnętrzne złączenia są definiowane pomiędzy danym studentem a tabelą ocen, co pozwala na pojawienie się także tych studentów, którzy nie zostali zapisani na jakiekolwiek zajęcia. Zewnętrzne złączenia są także dodawane do tabeli zajęć w celu umożliwienia zwrócenia nazw zajęć. Gdyby zewnętrzne złączenia nie zostały dodane do tabel zajęć, studenci nie zapisani na żadne zajęcia nie byliby zwracani, ponieważ mają pusty kod kursu (CCODE).
Oracle |
SQL Server firmy Microsoft |
SELECT S.SSN AS SSN, |
SELECT S.SSN AS SSN, |
Użycie wyrażeń SELECT w charakterze nazw tabel
Zarówno SQL Server firmy Microsoft, jak i Oracle wspierają użycie wyrażeń SELECT w charakterze tabel źródłowych podczas zadawania zapytań. SQL Server wymaga zdefiniowania aliasu; w przypadku Oracle jest to opcjonalne.
Oracle |
SQL Server firmy Microsoft |
SELECT SSN, LNAME, FNAME, |
SELECT SSN, LNAME, FNAME, |
Odczytywanie oraz modyfikowanie obiektów BLOB
SQL Server firmy Microsoft implementuje duże obiekty binarne (BLOB - ang. Binary Large OBject) dla kolumn text (tekst) oraz image (obraz). Oracle implementuje BLOB-y dla kolumn LONG oraz LONG RAW. W Oracle polecenie SELECT może sprawdzać wartości w kolumnach LONG oraz LONG RAW.
W SQL Server do odczytywania kolumn text oraz image można użyć standardowego wyrażenia Transact-SQL lub wyspecjalizowanego wyrażenia READTEXT. Wyrażenie READTEXT pozwala na odczytanie fragmentów kolumn text oraz image. Oracle nie obsługuje równoważnego wyrażenia działającego na kolumnach LONG oraz LONG RAW.
Wyrażenie READTEXT wykorzystuje text_pointer (wskaźnik tekstu), który można uzyskać za pomocą funkcji TEXTPTR. Funkcja TEXTPTR zwraca wskaźnik na kolumnę text lub image w określonym wierszu lub na kolumnę text lub image w ostatnim wierszu zwróconym przez zapytanie, jeśli zwrócony został więcej niż jeden wiersz. Ponieważ funkcja TEXTPTR zwraca 16-bajtowy łańcuch binarny, najlepiej jest zadeklarować lokalną zmienną do przechowywania wskaźnika na tekst, a następnie użyć tej zmiennej w READTEXT.
Wyrażenie READTEXT określa, jak wiele bajtów ma zostać zwróconych. Wartość w funkcji @@TEXTSIZE, która jest ogranicznikiem liczby zwracanych znaków lub bajtów, zastępuje wartość określoną w wyrażeniu READTEXT, jeśli READTEXT jest od niej mniejsza.
Wyrażenie SET może być użyte razem z parametrem TEXTSIZE do określenia rozmiaru, w bajtach, danych tekstowych zwracanych za pomocą wyrażenia SELECT. Jeśli rozmiar tekstu określono (TEXTSIZE) jako 0, rozmiar jest resetowany do wartości domyślnej (4 KB). Ustawienie parametru TEXTSIZE wpływa na funkcję @@TEXTSIZE. Sterownik ODBC Serwera SQL automatycznie ustawia parametr TEXTSIZE w momencie zmiany opcji wyrażenia SQL_MAX_LENGTH.
W Oracle polecenia UPDATE oraz INSERT używane są do zmieniania wartości w kolumnach LONG oraz LONG RAW. W SQL Server można użyć standardowych wyrażeń UPDATE oraz INSERT, można też użyć wyrażeń UPDATETEXT oraz WRITETEXT. Zarówno UPDATETEXT, jak i WRITETEXT dopuszczają opcję bez zalogowania, a UPDATETEXT pozwala na częściowe uaktualnianie kolumn text oraz image.
Wyrażenie UPDATETEXT może być użyte do zastąpienia istniejących danych, usunięcia istniejących danych lub do wstawienia nowych danych. Nowo wstawione dane mogą być wartością stałą, nazwą tabeli oraz wskaźnikiem tekstowym.
Wyrażenie WRITETEXT całkowicie zastępuje wszelkie dane istniejące w kolumnie, którą zmienia. Wyrażenia WRITETEXT należy używać do zastępowania danych, zaś wyrażenia UPDATETEXT - do modyfikowania danych tekstowych. Wyrażenie UPDATETEXT jest bardziej elastyczne, ponieważ zmienia tylko część tekstu lub obrazu, a nie całość.
Więcej informacji na ten temat można znaleźć w dokumentacji SQL Server Books Online.
Migracja Oracle na MSSQL cz. XVIII
Data publikacji: 2003-08-08 1:12|
Odsłon: 3554|
Dodał: Jacek Kolonko
Funkcje
Tabele w tym dziale pokazują zależności między funkcjami skalarnymi w Oracle oraz SQL Server. Pomimo iż nazwy wydają się być takie same, funkcje różnią się pod względem liczby oraz typów argumentów. Ponadto funkcje istniejące tylko w SQL Server firmy Microsoft nie zostaną tu omówione, ponieważ niniejszy rozdział jest ograniczony do ułatwiania migracji z istniejących aplikacji Oracle. Przykładowe funkcje nie obsługiwane przez Oracle to: stopnie (DEGREES), liczba p (PI) oraz liczba losowa (RAND).
Funkcje liczbowe/matematyczne
Poniżej przedstawiono funkcje liczbowe/matematyczne obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Wartość bezwzględna |
ABS |
taka sama |
Arcus cosinus |
ACOS |
taka sama |
Arcus sinus |
ASIN |
taka sama |
Arcus tangens z n |
ATAN |
taka sama |
Arcus tangens z n oraz m |
ATAN2 |
ATN2 |
Najmniejsza liczba całkowita >= wartości |
CEIL |
CEILING |
Cosinus |
COS |
taka sama |
Cosinus hiperboliczny |
COSH |
COT |
Wartość wykładnicza |
EXP |
taka sama |
Największa liczba całkowita <= wartości |
FLOOR |
taka sama |
Logarytm naturalny |
LN |
LOG |
Logarytm, dowolna podstawa |
LOG(N) |
niedostępna |
Logarytm, przy podstawie 10 |
LOG(10) |
LOG10 |
Modulo (reszta) |
MOD |
należy użyć operatora modulo (%) |
Potęga |
POWER |
taka sama |
Liczba losowa |
niedostępna |
RAND |
Zaokrąglenie |
ROUND |
taka sama |
Znak liczby |
SIGN |
taka sama |
Sinus |
SIN |
taka sama |
Sinus hiperboliczny |
SINH |
niedostępna |
Pierwiastek kwadratowy |
SQRT |
taka sama |
Tangens |
TAN |
taka sama |
Tangens hiperboliczny |
TANH |
niedostępna |
Obcięcie |
TRUNC |
niedostępna |
Największa liczba z listy |
GREATEST |
niedostępna |
Najmniejsza liczba z listy |
LEAST |
niedostępna |
Konwersja liczby, jeśli NULL |
NVL |
ISNULL |
Funkcje znakowe
Poniżej przedstawiono funkcje znakowe obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Zamiana znaku na kod ASCII |
ASCII |
taka sama |
Połączenie łańcuchów |
CONCAT |
(wyrażenie + wyrażenie) |
Zamiana kodu ASCII na znak |
CHR |
CHAR |
Zwraca punkt początkowy znaku w łańcuchu znaków (od lewej) |
INSTR |
CHARINDEX |
Zamienia wszystkie znaki na małe litery (LOWER) |
LOWER |
taka sama |
Zamienia wszystkie znaki na duże litery (UPPER) |
UPPER |
taka sama |
Wypełnienie lewej strony łańcucha znaków |
LPAD |
niedostępna |
Usunięcie początkowych pustych miejsc |
LTRIM |
taka sama |
Usunięcie końcowych pustych miejsc |
RTRIM |
taka sama |
Punkt początkowy podanego wzorca w łańcuchu znaków |
INSTR |
PATINDEX |
Wielokrotne powtórzenie łańcucha znaków |
RPAD |
REPLICATE |
Fonetyczna reprezentacja łańcucha znaków |
SOUNDEX |
taka sama |
Łańcuch składający się z kilku znaków odstępu |
RPAD |
SPACE |
Dane znakowe przekształcone z danych numerycznych |
TO_CHAR |
STR |
Wycinek łańcucha znaków |
SUBSTR |
SUBSTRING |
Zastąpienie znaków |
REPLACE |
STUFF |
Zamiana pierwszego znaku każdego słowa na dużą literę |
INITCAP |
niedostępna |
Translacja łańcucha znaków |
TRANSLATE |
niedostępna |
Długość łańcucha znaków |
LENGTH |
DATALENGTH lub LEN |
Najdłuższy łańcuch znaków w liście |
GREATEST |
niedostępna |
Najkrótszy łańcuch znaków w liście |
LEAST |
niedostępna |
Konwersja łańcucha, jeśli NULL |
NVL |
ISNULL |
Funkcje daty
Poniżej przedstawiono funkcje związane z datą obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Sumowanie dat |
(kolumna daty +/- wartość) lub |
DATEADD |
Różnica pomiędzy datami |
(kolumna daty +/- wartość) lub |
DATEDIFF |
Bieżąca data oraz czas |
SYSDATE |
GETDATE() |
Ostatni dzień miesiąca |
LAST_DAY |
niedostępna |
Konwersja strefy czasowej |
NEW_TIME |
niedostępna |
Pierwszy weekend po zadanej dacie |
NEXT_DAY |
niedostępna |
Tekstowa reprezentacja daty |
TO_CHAR |
DATENAME |
Reprezentacja daty w postaci liczby całkowitej |
TO_NUMBER(TO_CHAR)) |
DATEPART |
Zaokrąglenie daty |
ROUND |
CONVERT |
Obcięcie daty |
TRUNC |
CONVERT |
Zamiana łańcucha tekstowego na datę |
TO_DATE |
CONVERT |
Konwersja daty jeśli NULL |
NVL |
ISNULL |
Funkcje konwersji
Poniżej przedstawiono funkcje konwersji obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Liczba na znak |
TO_CHAR |
CONVERT |
Znak na liczbę |
TO_NUMBER |
CONVERT |
Data na znak |
TO_CHAR |
CONVERT |
Znak na datę |
TO_DATE |
CONVERT |
Szesnastkowa na binarną |
HEX_TO_RAW |
CONVERT |
Binarna na szesnastkową |
RAW_TO_HEX |
CONVERT |
Inne funkcje poziomu wiersza
Poniżej przedstawiono inne niskopoziomowe funkcje obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Zwraca pierwsze nie puste wyrażenie |
DECODE |
COALESCE |
Wartość bieżącej sekwencji |
CURRVAL |
niedostępna |
Wartość następnej sekwencji |
NEXTVAL |
niedostępna |
Jeśli exp1 = exp2, zwraca null |
DECODE |
NULLIF |
Numer identyfikacyjny logowania użytkownika |
UID |
SUSER_ID |
Nazwa logowania użytkownika |
USER |
SUSER_NAME |
Numer identyfikacyjny bazy danych użytkownika |
UID |
USER_ID |
Nazwa bazy danych użytkownika |
USER |
USER_NAME |
Bieżący Użytkownik |
CURRENT_USER |
taka sama |
Środowisko użytkownika (ślad audytu) |
USERENV |
niedostępna |
Poziom w klauzuli CONNECT BY |
LEVEL |
niedostępna |
Funkcje podsumowywania
Poniżej przedstawiono funkcje podsumowywania obsługiwane przez Oracle oraz ich odpowiedniki w SQL Server firmy Microsoft.
Funkcja |
Oracle |
SQL Server firmy Microsoft |
Średnia |
AVG |
taka sama |
Liczba elementów |
COUNT |
taka sama |
Maksimum |
MAX |
taka sama |
Minimum |
MIN |
taka sama |
Odchylenie standardowe |
STDDEV |
STDEV lub STDEVP |
Podsumowanie |
SUM |
taka sama |
Wariancja |
VARIANCE |
VAR lub VARP |
Testy warunkowe
Zarówno wyrażenie DECODE w Oracle oraz wyrażenie CASE w Serwerze SQL firmy Microsoft wykonują testy warunkowe. Kiedy wartość w polu wartość_testowa odpowiada któremuś z poniższych wyrażeń, zwracana jest związana z nim wartość. Jeśli nie znaleziono odpowiednika - zwracana jest wartość_domyślna. Jeśli takowej nie podano, zarówno DECODE jak i CASE zwracają w takiej sytuacji wartość pustą (NULL). Poniższa tabela pokazuje składnię oraz przykład skonwertowanego polecenia DECODE.
Oracle |
SQL Server firmy Microsoft |
DECODE (test_value,
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA |
CASE wartość_testowa
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA |
Wyrażenie CASE pozwala na użycie wyrażeń SELECT przy przeprowadzaniu testów logicznych, na co nie pozwala polecenie DECODE. Więcej informacji na temat wyrażenia CASE można znaleźć w dokumentacji SQL Server Books Online.
Konwertowanie wartości na różne typy danych
Funkcje CONVERT oraz CAST SQL Server firmy Microsoft są wielofunkcyjnymi narzędziami konwersji. Działają podobnie - konwertują wyrażenia z jednego typu danych na inny i obsługują różne specjalne formaty danych:
CAST (wyrażenie AS typ_danych)
CONVERT (typ_danych[(długość)], wyrażenie[, styl])
CAST to funkcja standardowa SQL-92 i wykonuje te same działania, co funkcje Oracle: TO_CHAR, TO_NUMBER, TO_DATE, HEXTORAW oraz RAWTOHEX.
Typem danych może być dowolny systemowy typ danych, na który wyrażenie ma zostać przekonwertowane. Nie można używać zdefiniowanych przez użytkownika typów danych. Parametr długość jest opcjonalny i jest używany w przypadku typów danych char, varchar, binary oraz varbinary. Maksymalna dopuszczalna długość to 8000.
Konwersja |
Oracle |
SQL Server firmy Microsoft |
Znak na liczbę |
TO_NUMBER('10') |
CONVERT(numeric, '10') |
Liczba na znak |
TO_CHAR(10) |
CONVERT(char, 10) |
Znak na datę |
TO_DATE('04-LIP-97') |
CONVERT(datetime, '04-JUL-97') |
Data na znak |
TO_CHAR(sysdate) |
CONVERT(char, GETDATE()) |
Liczba szesnastkowa na binarną |
HEXTORAW('1F') |
CONVERT(binary, '1F') |
Liczba binarna na szesnastkową |
RAWTOHEX(kolumna_binarna) |
CONVERT(char,binarna_kolumna) |
Należy zwrócić uwagę na sposób, w jaki łańcuchy tekstowe są konwertowane na daty. W Oracle domyślnym modelem formatu danych jest „DD-MIES-RR”. W przypadku użycia jakiegokolwiek innego formatu należy dostarczyć właściwy model formatu daty. Funkcja CONVERT dokonuje automatycznej konwersji standardowych formatów danych bez konieczności podawania modelu formatu.
Podczas konwertowania daty na łańcuch tekstowy standardowym wyjściem generowanym przez funkcję CONVERT jest „dd mies rrrr gg:mm:ss:mmm(24h)”. Numeryczny kod jest używany do formatowania wyjścia do innych typów modeli formatu danych. Więcej informacji na ten temat funkcji CONVERT można znaleźć w dokumentacji SQL Server Books Online.
Poniższa tabela zawiera domyślne wyjście funkcji dat w SQL Server firmy Microsoft.
Bez stulecia |
Ze stuleciem |
Standard |
Wyjście |
- |
0 lub 100 (*) |
Domyślny |
mies dd rrrr gg:ms AM (lub PM) |
1 |
101 |
USA |
mm/dd/rr |
2 |
102 |
ANSI |
rr.mm.dd |
3 |
103 |
Brytyjski/francuski |
dd/mm/rr |
4 |
104 |
Niemiecki |
dd.mm.rr |
5 |
105 |
Włoski |
dd-mm-rr |
6 |
106 |
- |
dd mon rr |
7 |
107 |
- |
mon dd, rr |
8 |
108 |
- |
gg:mm:ss |
- |
9 lub 109 (*) |
Domyślny z milisekundami |
mies dd rrrr gg:mi:ss:mmm (AM lub PM) |
10 |
110 |
USA |
mm-dd-rr |
11 |
111 |
Japoński |
Rr/mm/dd |
12 |
112 |
ISO |
rrmmdd |
- |
13 lub 113 (*) |
Domyślny europejski |
dd mies rrrr gg:mm:ss:mmm(24h) |
14 |
114 |
- |
gg:mi:ss:mmm(24h) |
Funkcje zdefiniowane przez użytkownika
Składnia tworzenia zdefiniowanych przez użytkownika funkcji zwracających skalarny typ danych jest podobna w Oracle oraz w SQL Server 2000.
Oracle |
SQL Server firmy Microsoft |
SELECT ssn, fname, lname, tuition_paid, |
SELECT ssn, fname, lname, tuition_paid, tuition_paid/sum_major as percent_major |
CREATE OR REPLACE FUNCTION get_sum_major |
CREATE FUNCTION get_sum_major (@inmajor varchar(40)) |
Zdefiniowane przez użytkownika funkcje SQL Server także mogą zwracać dane typu tabela. Te funkcje mogą być zarówno jednoliniowymi funkcjami składającymi się po prostu z wyrażenia SELECT, jak i wielowyrażeniowymi funkcjami składającymi się z wielu wyrażeń używanych do budowy tabel. Oba rodzaje funkcji mogą przyjmować parametry i być wykorzystywane w klauzuli FROM wyrażenia Transact-SQL. Te funkcje stanowią poważną alternatywę zarówno dla procedur przechowywanych, ponieważ z ich wyników można skorzystać bezpośrednio w wyrażeniu Transact-SQL, jak i widoków, gdyż mogą przyjmować parametry do zawężenia zbioru wynikowego.
Poniżej przedstawiono składnię służącą do utworzenia zdefiniowanych przez użytkownika typów funkcji zwracających tabele.
Składnia dla funkcji inline-table (tabela-w-linii)
CREATE FUNCTION [ nazwa_właściciela. ] nazwa_funkcji
( [ { @nazwa_parametru [AS] typ_danych_parametru_skalarnego [ = wartość_domyślna ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH <function_option > [ [,] ...n ] ]
[ AS ]
RETURN [ ( ] wyrażenie_selectt [ ) ]
Składnia dla wielowyrażeniowej funkcji tabelowej
CREATE FUNCTION [nazwa_właściciela. ] nazwa_funkcji
( [ { @ nazwa_parametru [AS] typ_danych_parametru_skalarnego [ = wartość_domyślna] } [ ,...n ] ] )
RETURNS @zmienna_zwracana TABLE < definicja_typu_tabeli >
[ WITH < opcja_funkcji> [ [,] ...n ] ]
[ AS ]
BEGIN
kod_funkcji
RETURN
END
Operatory porównania
Operatory porównania są w Oracle oraz SQL Server firmy Microsoft niemal identyczne.
Operator |
Oracle |
SQL Server firmy Microsoft |
Równy |
(=) |
Taki sam |
Większy niż |
(>) |
Taki sam |
Mniejszy niż |
(<) |
Taki sam |
Większy lub równy |
(>=) |
Taki sam |
Mniejszy lub równy |
(<=) |
Taki sam |
Różny |
(!=, <>,^=) |
Taki sam |
Nie większy niż, nie mniejszy niż |
Nie dostępny |
!> , !< |
Znajduje się zbiorze |
IN |
Taki sam |
Nie znajduje się w zbiorze |
NOT IN |
Taki sam |
Dowolna wartość w zbiorze |
ANY, SOME |
Taki sam |
Odnosi się do wszystkich wartości w zbiorze. |
!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL |
Taki sam |
Jak wzorzec |
LIKE |
Taki sam |
Nie jak wzorzec |
NOT LIKE |
Taki sam |
Wartość pomiędzy x a y |
BETWEEN x AND y |
Taki sam |
Wartość nie pomiędzy |
NOT BETWEEN |
Taki sam |
Wartość istnieje |
EXISTS |
Taki sam |
Wartość nie istnieje |
NOT EXISTS |
Taki sam |
Wartość {jest | nie jest} pusta (NULL) |
IS NULL, IS NOT NULL |
Taki sam. Obsługuje także = NULL, != NULL w celu zachowania zgodności ze starymi wersjami (użycie nie zalecane). |
Dopasowywanie wzorców
Słowo kluczowe SQL Server LIKE udostępnia przydatne opcje wyszukiwania na podstawie wzorców/masek, które nie są dostępne w Oracle. Poza obsługiwaniem znaków masek % oraz _ używanym w obu systemach RDBMS, Serwer SQL obsługuje także znaki [ ] oraz [^].
Zbiór znaków [ ] jest używany do wyszukiwania dowolnego pojedynczego znaku w podanym przedziale. Dla przykładu, w przypadku poszukiwania znaków od a do f można użyć instrukcji LIKE '[a-f]' lub LIKE '[abcdef]'. Przydatność tych dodatkowych masek została pokazana w poniższej tabeli.
Oracle |
SQL Server firmy Microsoft |
SELECT * FROM STUDENT_ADMIN.STUDENT |
SELECT * FROM STUDENT_ADMIN.STUDENT |
Zbiór znaków maski [^] jest używany do określenia znaków NIE w podanym przedziale. Na przykład, jeśli każdy znak - z wyjątkiem znaków od a do f - jest do przyjęcia, używa się sekwencji LIKE '[^a - f]' lub LIKE '[^abcdef]'.
Więcej informacji na temat słowa kluczowego LIKE można znaleźć w dokumentacji SQL Server Books Online.
Używanie parametru NULL w porównaniach
Chociaż SQL Server firmy Microsoft tradycyjnie obsługiwał zarówno zachowanie zgodne ze standardem SQL-92, jak i niektóre niestandardowe zachowania NULL, obsługuje użycie NULL w Oracle.
Parametr ANSI_NULLS powinien być ustawiony za pomocą instrukcji SET na ON, aby umożliwić obsługę zapytań rozproszonych.
Sterownik ODBC SQL Server oraz Dostawca OLE DB dla SQL Server włączają to ustawienie automatycznie podczas połączenia. To ustawienie można skonfigurować w źródłach danych ODBC, w atrybutach połączenia ODBC oraz we właściwościach połączenia OLE DB, które są ustawiane w aplikacji przed połączeniem z SQL Server. SET ANSI_NULLS domyślnie jest ustawiane na OFF w przypadku połączeń z aplikacjami DB-Library.
Jeśli SET ANSI_DEFAULTS = ON, SET ANSI_NULLS jest włączone.
Więcej informacji na ten temat użycia NULL można znaleźć w dokumentacji SQL Server Books Online.
Łączenie łańcuchów tekstowych
Oracle używa symbolu (||) w charakterze operatora łączenia łańcuchów tekstowych, natomiast SQL Server używa znaku (+). Ta różnica wymaga wprowadzenia małych poprawek w kodzie aplikacji.
Oracle |
SQL Server firmy Microsoft |
SELECT FNAME||' '||LNAME AS NAME |
SELECT FNAME +' '+ LNAME AS NAME |
Migracja Oracle na MSSQL cz. XIX
Data publikacji: 2003-08-15 19:04|
Odsłon: 3103|
Dodał: Jacek Kolonko
Język kontroli przepływu
Język kontroli przepływu kontroluje przebieg wykonywania wyrażeń SQL, bloków wyrażeń oraz procedur przechowywanych. PL/SQL oraz Transact-SQL udostępniają wiele podobnych konstrukcji, różniących się jednak nieco składnią.
Słowa kluczowe
Poniżej zamieszczono słowa kluczowe obsługiwane przez oba systemy RDBMS.
Wyrażenie |
Oracle PL/SQL |
SQL Server firmy Microsoft |
Deklaracja zmiennych |
DECLARE |
DECLARE |
Blok wyrażeń |
BEGIN...END; |
BEGIN...END |
Przetwarzanie warunkowe |
IF…THEN, |
IF…[BEGIN…END] |
Bezwarunkowe wyjście |
RETURN |
RETURN |
Bezwarunkowe wyjście do wyrażenia występującego zaraz po bieżącym bloku programu |
EXIT |
BREAK |
Wznowienie pętli WHILE |
Niedostępne |
CONTINUE |
Oczekiwanie przez określony czas |
Niedostępne (dbms_lock.sleep) |
WAITFOR |
Kontrola pętli |
WHILE LOOP…END LOOP;
|
WHILE <warunek> LABEL…GOTO LABEL |
Komentarze programu |
/* … */, -- |
/* … */, -- |
Wydruk |
RDBMS_OUTPUT.PUT_LINE |
|
Wywołanie błędu programu |
RAISE_APPLICATION_ERROR |
RAISERROR |
Uruchomienie programu |
EXECUTE |
EXECUTE |
Zakończenie wyrażenia |
Średnik (;) |
Niedostępne |
Deklarowanie zmiennych
Zmienne w Transact-SQL oraz PL/SQL są tworzone za pomocą słowa kluczowego DECLARE. Zmienne Transact-SQL są identyfikowane poprzez @) i, podobnie jak zmienne w PL/SQL, są wtedy inicjowane wartością pustą.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE |
Transact-SQL nie obsługuje definicji zmiennych typów danych %TYPE oraz %ROWTYPE. Zmienna w Transact-SQL nie może zostać zainicjowana w poleceniu DECLARE. Słowa kluczowe NOT NULL oraz CONSTANT z Oracle nie mogą zostać użyte w definicjach typów danych SQL Server firmy Microsoft.
Podobnie jak typy danych z Oracle - LONG oraz LONG RAW - typy danych text oraz image nie mogą być używane w deklaracjach zmiennych. Dodatkowo, definicje rekordów oraz tabel, takie jak w PL/SQL, nie są obsługiwane.
Przypisywanie zmiennym wartości
Oracle oraz SQL Server firmy Microsoft oferują następujące sposoby przypisywania wartości zmiennym lokalnym.
Oracle |
SQL Server firmy Microsoft |
Operator przypisania (:=) |
SET @lokalna_zmienna = wartość |
SELECT...INTO składnia wybierająca wartości kolumny z pojedynczego wiersza |
SELECT @lokalna_zmienna = wyrażenie [FROM…] do przypisywania dosłownej wartości, wyrażenia angażującego inne zmienne lokalne lub wartości kolumny z pojedynczego wiersza |
FETCH…INTO składnia |
FETCH…INTO składnia |
Poniżej przedstawiono kilka przykładów składni.
Oracle |
SQL Server firmy Microsoft |
DECLARE VSSN CHAR(9); |
DECLARE @VSSN CHAR(9), |
Bloki wyrażeń
PL/SQL z Oracle oraz Transact-SQL SQL Server firmy Microsoft obsługują użycie terminologii BEGIN…END (początek...koniec) do określania bloków wyrażeń. Transact-SQL nie wymaga użycia bloku wyrażeń po wyrażeniu DECLARE. Bloki wyrażeń BEGIN…END są wymagane w SQL Server firmy Microsoft dla wyrażeń IF oraz pętli WHILE - jeśli uruchamiane jest więcej niż jedno wyrażenie.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE |
Przetwarzanie warunkowe
Wyrażenia warunkowe języka Transact-SQL SQL Server firmy Microsoft obejmują IF oraz ELSE -zamiast wyrażenia ELSIF z PL/SQL z Oracle. Wielokrotne wyrażenia IF mogą być zagnieżdżane w celu osiągnięcia tego samego skutku. W przypadku obszernych testów warunkowych bardziej przejrzyste może być wyrażenie CASE.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE |
Wielokrotne wykonywanie wyrażeń (pętle)
PL/SQL udostępnia w Oracle bezwarunkową pętlę LOOP oraz FOR LOOP. Transact-SQL udostępnia do tych celów pętlę WHILE oraz wyrażenie GOTO.
WHILE wyrażenie_logiczne
{wyrażenie_sql | blok_wyrażeń}
[BREAK] [CONTINUE]
Pętla WHILE - dla wielokrotnego wykonywania jednego lub kilku wyrażeń - testuje wyrażenie logiczne. Wyrażenie/wyrażenia są powtarzane dopóty, dopóki to wyrażenie logiczne ma wartość TRUE (prawda). Jeśli wykonywanych ma być kilka wyrażeń, muszą one być umieszczone w bloku BEGIN…END.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE |
Wykonywanie wyrażeń może zostać przerwane z wewnątrz pętli za pomocą słów kluczowych BREAK oraz CONTINUE. Słowo kluczowe BREAK powoduje bezwarunkowe opuszczenie pętli WHILE, natomiast słowo kluczowe CONTINUE powoduje ponowne uruchomienie pętli WHILE - wszelkie dalsze wyrażenia w pętli są pomijane. Słowo kluczowe BREAK jest równoważnikiem słowa kluczowego EXIT z Oracle PL/SQL. Oracle nie ma odpowiednika słowa kluczowego CONTINUE.
Wyrażenie GOTO
Zarówno Oracle, jak i SQL Server firmy Microsoft obsługują wyrażenia GOTO, ale mają odmienną składnię. Wyrażenie GOTO powoduje wykonanie przez Transact-SQL skoku do danej etykiety. Żadne wyrażenia pomiędzy wyrażeniem GOTO a tą etykietą nie są wykonywane.
Oracle |
SQL Server firmy Microsoft |
GOTO etykieta; |
GOTO etykieta |
Wyrażenie PRINT
Wyrażenie PRINT z Transact-SQL wykonuje taką samą operację, jak procedura RDBMS_OUTPUT.wstawić_linię z PL/SQL. Jest używane do wypisywania komunikatów użytkownika.
Limit długości komunikatu dla wyrażenia PRINT to 8'000 znaków. Zmienne zdefiniowane jako typ danych char lub varchar mogą być osadzone w wypisywanym wyrażeniu. Jeśli używane są jakiekolwiek inne dane, konieczne jest użycie funkcji CONVERT lub CAST. Wypisywane mogą być tak lokalne, jak i globalne zmienne oraz tekst. W celu oznaczenia tekstu można użyć zarówno pojedynczego, jak i podwójnego cudzysłowu.
Zwracanie danych przez funkcje przechowywane
Zarówno SQL Server firmy Microsoft, jak i Oracle obsługują wyrażenie RETURN. RETURN pozwala programowi na bezwarunkowe wyjście z zapytania lub procedury. Wyjście za pomocą RETURN jest natychmiastowe oraz kompletne i może zostać użyte w dowolnym punkcie do opuszczenia procedury, pliku wsadowego lub bloku wyrażeń. Wyrażenia występujące po RETURN nie są uruchamiane.
Oracle |
SQL Server firmy Microsoft |
RETURN wyrażenie: |
RETURN wyrażenie_całkowite |
Wywoływanie błędów programowych
Wyrażenie RAISERROR z Transact-SQL zwraca zdefiniowany przez użytkownika komunikat błędu i ustawia flagę systemową, by zasygnalizować wystąpienie błędu. Wyrażenie to działa podobnie jak procedura obsługi wyjątku raise_application_error z PL/SQL.
Wyrażenie RAISERROR pozwala klientowi na użycie wpisu z tabeli komunikatów systemowych sysmessages lub dynamiczne utworzenie komunikatu wraz z własną wiadomością oraz poziomem dotkliwości (czy mówiąc inaczej - kodem „ważności” błędu) i informacją o stanie. Po zdefiniowaniu komunikat jest odsyłany do klienta jako systemowy komunikat błędu.
RAISERROR ({identyfikator_komunikatu | tekst_komunikatu}, dotkliwość, stan
[, argument1 [, argument2]])
[WITH opcje]
Podczas konwertowania programów z PL/SQL może nie być konieczne użycie wyrażenia RAISERROR. W poniższym przykładzie kodu program w PL/SQL używa procedury obsługi wyjątku raise_application_error, zaś program w Transact-SQL nie musi używać żadnych mechanizmów poza wbudowanymi. Procedura obsługi wyjątku raise_application_error została dołączona w celu uniemożliwienia programowi w PL/SQL ewentualnego zwrócenia niejasnego komunikatu błędu unhandled exception („nieobsługiwany wyjątek”). Zamiast tego, w przypadku wystąpienia nieoczekiwanego problemu, zawsze zwraca komunikat o błędzie Oracle (SQLERRM).
W przypadku awarii programu w Transact-SQL, zawsze zwraca on szczegółowy komunikat o błędzie programowi klienckiemu. Dlatego też - jeżeli nie jest wymagane jakieś specjalne obsłużenie błędu - wyrażenie RAISERROR nie jest potrzebne.
Oracle |
SQL Server firmy Microsoft |
CREATE OR REPLACE FUNCTION |
CREATE PROCEDURE |
Migracja Oracle na MSSQL cz. XX
Data publikacji: 2003-08-22 1:43|
Odsłon: 4717|
Dodał: Jacek Kolonko
Implementowanie Kursorów
Oracle zawsze wymaga, żeby wraz z wyrażeniami SELECT były używane kursory, niezależnie od liczby wierszy żądanych od bazy danych. W SQL Server firmy Microsoft wyrażenie SELECT, które nie jest zamknięte w kursorze, zwraca klientowi wiersz w charakterze domyślnego zbioru wyników. Jest to efektywny sposób zwracania danych aplikacji klienckiej.
SQL Server obsługuje dwa interfejsy dla funkcji kursora. W przypadku użycia kursorów w procedurach przechowywanych lub plikach wsadowych Transact-SQL, możliwe jest używanie wyrażeń SQL do deklarowania, otwierania oraz pobierania z kursorów, a także pozycjonowane uaktualnienia oraz wykasowania. W przypadku użycia kursorów z programu DB-Library, ODBC lub OLE DB biblioteki klienckie SQL Server w przezroczysty sposób wywołują wbudowane funkcje serwera w celu bardziej efektywnego obsługiwania kursorów.
Podczas przenoszenia procedury PL/SQL z Oracle najpierw należy określić, czy kursory są konieczne do wykonania tego samego zadania w Transact-SQL. Jeśli kursor zwraca tylko zbiór wierszy do aplikacji klienckiej, wystarczy użyć bezkursorowego wyrażenia SELECT języka Transact-SQL w celu zwrócenia standardowego zbioru wynikowego. Jeśli kursor jest używany do załadowania danych do lokalnych zmiennych procedury pojedynczymi wierszami, konieczne w Transact-SQL jest użycie kursora.
Składnia kursora
Poniższa tabela pokazuje składnię używania kursora.
Operacja |
Oracle |
SQL Server firmy Microsoft |
Deklarowanie kursora |
CURSOR nazwa_kursora [(parametr(y)_kursora)] |
DECLARE nazwa_kursora CURSOR |
Otwieranie kursora |
OPEN nazwa_kursora [(parametr(y)_kursora)]; |
OPEN nazwa_kursora |
Pobieranie z kursora |
FETCH nazwa_kursora INTO variable(s) |
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] |
Uaktualnienie pobranego wiersza |
UPDATE table_name |
UPDATE nazwa_tabeli |
Usunięcie pobranego wiersza |
DELETE FROM nazwa_tabeli |
DELETE FROM nazwa_tabeli |
Zamknięcie kursora |
CLOSE nazwa_kursora; |
CLOSE nazwa_kursora |
Usunięcie struktur danych kursora |
Niedostępna |
DEALLOCATE nazwa_kursora |
Deklarowanie kursora
Mimo że wyrażenie DECLARE CURSOR z Transact-SQL nie obsługuje użycia argumentów kursora, obsługuje jednak zmienne lokalne. Wartości tych lokalnych zmiennych są używane w kursorze podczas jego otwierania. SQL Server firmy Microsoft udostępnia wiele dodatkowych możliwości w wyrażeniu DECLARE CURSOR.
Opcja INSENSITIVE jest używana do zdefiniowania kursora tworzącego tymczasową kopię danych, które mają być używane przez ten kursor. Na wszystkie żądania wobec kursora odpowiada ta tymczasowa tabela , w wyniku czego zmiany dokonane w tabeli podstawowej nie są odzwierciedlane w danych zwracanych kursor. Dane, z których korzysta kursor tego typu, nie mogą zostać zmienione.
Aplikacje mogą stworzyć określony typ kursora, a następnie uruchomić wyrażenie Transact-SQL nie obsługiwane przez taki kursor. SQL Server zwróci błąd sygnalizujący, że typ kursora się zmienił, lub - pod wpływem określonych czynników - domyślnie przekonwertuje kursor. Aby uzyskać kompletną listę czynników wyzwalających domyślną konwersję kursora przez SQL Server 2000 z jednego typu na inny, należy zajrzeć do SQL Server Books Online.
Opcja SCROLL powoduje, że kolejne rekordy można pobierać w dowolny sposób - odwołując się do następnego/poprzedniego rekordu, lub rekordu o bezwzględnym/względnym numerze. Kursor tego typu używa modelu kursora zbioru kluczy, w którym zatwierdzone wykasowania uraz uaktualnienia naniesione na tabele bazowe (z których kożysta kursor) są odzwierciedlane w kolejnych pobraniach. Tak się dzieje tylko wtedy, gdy kursor nie został zadeklarowany z opcją INSENSITIVE.
Jeśli natomiast wybrano opcję READ ONLY, uaktualnienia są blokowane w każdym wierszu w ramach kursora i ta opcja jest ważniejsza od domyślnej zdolności kursora do uaktualniania.
Wyrażenie UPDATE [OF lista_kolumn] jest używane do zdefiniowania możliwych do uaktualnienia kolumn w ramach kursora. Jeśli podano [OF lista_kolumn], wówczas tylko wymienione kolumny dopuszczają zmiany. Jeśli nie podano listy, wszystkie kolumny mogą być uaktualnianie, chyba że kursor został zdefiniowany jako READ ONLY.
Ważne jest to, że zasięg nazwy dla kursora Serwera SQL obejmuje całe połączenie (sesję). Różni się on od zasięgu lokalnej zmiennej. Drugi kursor o takiej samej nazwie jak istniejący na tym samym połączeniu użytkownika nie może zostać zadeklarowany aż do zwolnienia pierwszego kursora.
Otwieranie kursora
Transact-SQL, w przeciwieństwie do PL/SQL, nie obsługuje przekazywania argumentów do kursora, gdy jest on otwarty. Kiedy kursor Transact-SQL zostaje otwarty, ustalony zostaje porządek oraz członkostwo wynikowego zbioru. Uaktualnienie oraz kasowanie, jakie zaszło w podstawowych tabelach kursora na skutek działania innych użytkowników są odwzorowywane w pobraniach wykonanych w stosunku do wszystkich kursorów zdefiniowanych bez opcji INSENSITIVE. W przypadku kursora INSENSITIVE tworzona jest tymczasowa tabela.
Pobieranie danych
Kursory w Oracle mogą przesuwać się tylko do przodu - nie ma możliwości przewijania względnego ani wstecz. Kursory SQL Server mogą przesuwać się do przodu oraz do tyłu dzięki opcjom pobierania opisanym w poniższej tabeli. Te opcje pobierania mogą być używane tylko wtedy, gdy kursor został zadeklarowany z opcją SCROLL.
Opcja przesuwania |
Opis |
NEXT |
Zwraca pierwszy wiersz ze zbioru wynikowego, jeśli jest to pierwsze pobranie od kursora; w przeciwnym wypadku przesuwa kursor o jeden wiersz w zbiorze wynikowym. NEXT jest podstawową metodą przechodzenia przez zbiór wynikowy. NEXT jest domyślnym pobraniem z kursora. |
PRIOR |
Zwraca poprzedni wiersz ze zbioru wynikowego. |
FIRST |
Przesuwa kursor do pierwszego wiersza w zbiorze wynikowym i zwraca ten pierwszy wiersz. |
LAST |
Przesuwa kursor do ostatniego wiersza w zbiorze wynikowym i zwraca ten ostatni wiersz. |
ABSOLUTE n |
Zwraca n-ty wiersz ze zbioru wynikowego. Jeśli n jest wartością ujemną, zwracany wiersz jest n-tym wierszem, licząc wstecz od ostatniego wiersza zbioru wynikowego. |
RELATIVE n |
Zwraca n-ty wiersz po aktualnie pobranym wierszu. Jeśli n jest wartością ujemną, zwracany wiersz jest n-tym wierszem, licząc wstecz od względnej pozycji kursora. |
Wyrażenie FETCH z Transact-SQL nie potrzebuje klauzuli INTO. Jeśli zmienne zwracane nie zostaną określone, wiersz jest automatycznie zwracany klientowi jako jednowierszowy zbiór wynikowy. Jeśli jednak procedura musi dostarczyć wiersze do klienta, znacznie wydajniejsze będzie użycie bezkursorowego wyrażenia SELECT.
Funkcja @@FETCH_STATUS jest uaktualniana po każdym wyrażeniu FETCH. Używa się jej podobnie jak zmiennych CURSOR_NAME%FOUND oraz CURSOR_NAME%NOTFOUND używanych w PL/SQL. Funkcja @@FETCH_STATUS ma swoją wartość ustawianą na 0 po każdym pomyślnym pobraniu. Jeśli pobranie próbuje odczytu poza końcem kursora, zwracana jest wartość -1. Jeśli żądany wiersz został usunięty z tabeli zaraz po otworzeniu kursora, funkcja @@FETCH_STATUS zwraca -2. Wartość równa -2 zwykle występuje tylko w kursorze zadeklarowanym przy użyciu opcji SCROLL i tę zmienną trzeba sprawdzać po każdym pobraniu, by upewnić się, czy dane są właściwe.
SQL Server nie obsługuje składni pętli FOR kursora z Oracle.
Klauzula CURRENT OF
Składnia oraz działanie klauzuli CURRENT OF w przypadku uaktualnień oraz wykasowań są takie same zarówno w PL/SQL, jak i w Transact-SQL - wykonywane jest pozycjonowane UPDATE lub DELETE w stosunku do bieżącego wiersza w określonym kursorze.
Zamykanie kursora
Wyrażenie CLOSE CURSOR z Transact-SQL zamyka kursor, ale pozostawia strukturę jego danych dostępną do ponownego otwarcia. Wyrażenie CLOSE CURSOR z PL/SQL zamyka kursor i zwalnia wszystkie struktury danych.
Aby usunąć struktury danych kursora w Transact-SQL, trzeba użyć wyrażenia DEALLOCATE CURSOR. Wyrażenie DEALLOCATE CURSOR tym różni się od CLOSE CURSOR, że w tym przypadku zamknięty kursor może zostać ponownie otworzony. Wyrażenie DEALLOCATE CURSOR zwalnia wszystkie struktury danych związane z kursorem i usuwa jego definicję.
Przykład kursora
Poniższy przykład pokazuje równoważne wyrażenia kursora w PL/SQL oraz Transact-SQL.
Oracle |
SQL Server firmy Microsoft |
DECLARE |
DECLARE
BEGIN |
Więcej informacji na temat kursorów można znaleźć w dziale "Cursors" w SQL Server Books Online.
Migracja Oracle na MSSQL cz. XXI
Data publikacji: 2003-09-06 17:44|
Odsłon: 2154|
Dodał: Jacek Kolonko
Optymalizacja wyrażeń Transact-SQL
Niniejszy dział zawiera informacje o kilku narzędziach SQL Server, które można wykorzystać do optymalizacji wyrażeń w Transact-SQL.
Microsoft SQL Server 2000 jest serwerem bazy danych, który w dużym stopniu sam się automatycznie konfiguruje oraz optymalizuje, co oszczędza administratorowi bazy danych ogromną część pracy związanej z konfigurowaniem serwera. W większości przypadków SQL Server działa najlepiej wtedy, gdy pozostawiono domyślne ustawienia parametrów autooptymalizacji i administrator pozwala Serverowi SQL na samodzielne zajęcie się optymalizacją. Najnowsze informacje na temat optymalizacji bazy danych SQL Server można znaleźć pod adresem http://msdn.microsoft.com/sqlserver.
Analizator Zapytań SQL
Aby lepiej poznać sposób przetwarzania wyrażeń przez optymalizator, można użyć graficznego wyświetlania planu (ang. graphical showplan) Query Analyzer.
Profiler SQL
To narzędzie graficzne rejestruje ciągły zapis aktywności serwera w czasie rzeczywistym. Program profilujący SQL monitoruje wiele różnych zdarzeń serwera oraz kategorie zdarzeń, filtruje te zdarzenia uwzględniając kryteria użytkownika i wypisuje wygenerowany ślad na ekran, do pliku lub wysyła go do innego serwera z działającym SQL Server.
Programu profilującego można używać do:
Monitorowania wydajności SQL Server.
Wyszukiwania błędów w procedurach przechowywanych oraz wyrażeniach Transact-SQL.
Identyfikowania wolno działających zapytań.
Rozwiązywania problemów w SQL Server poprzez rejestrowanie wszystkich zdarzeń prowadzących do konkretnego problemu, a następnie powtórzenie tych zdarzeń na serwerze testowym w celu skopiowania i wyizolowania problemu.
Testowania procedur przechowywanych oraz wyrażeń SQL w fazie tworzenia programu poprzez wykonywanie wyrażeń krok po kroku, po jednej linii, w celu sprawdzenia, czy kod działa zgodnie z oczekiwaniami.
„Nagrywania” zdarzeń w systemie produkcyjnym i „odtwarzania” tych nagranych zdarzeń w systemie testowym, odtwarzając tym samym zdarzenia zaszłe w środowisku produkcyjnym do testowania oraz w celu usunięcia błędów. Odtwarzanie nagranych zdarzeń w osobnym systemie pozwala użytkownikom na korzystanie z systemu produkcyjnego bez żadnych zakłóceń.
Program Profilujący SQL udostępnia graficzny interfejs użytkownika do zbioru rozszerzonych procedur przechowywanych. Można także używać tych rozszerzonych procedur przechowywanych bezpośrednio. Możliwe jest zatem stworzenie własnej aplikacji używającej rozszerzonych procedur przechowywanych SQL Profiler do monitorowania SQL Server.
Wyrażenie SET
Wyrażenie SET może ustawić opcje SQL Server dotyczące przetwarzania zapytań na czas sesji roboczej użytkownika lub na czas działania triggera albo procedury przechowywanej.
Wyrażenie SET FORCEPLAN ON wymusza na optymalizatorze przetwarzanie złączeń w takiej samej kolejności, w jakiej występują tabele w klauzuli FROM, podobnie jak w przypadku podpowiedzi ORDERED używanej w optymalizatorze z Oracle.
Wyrażenia SET SHOWPLAN_ALL oraz SET SHOWPLAN_TEXT zwracają tylko informację o planie wykonywania wyrażenia lub zapytania, natomiast nie uruchamiają zapytania ani wyrażenia. Do uruchomienia zapytania lub wyrażenia należy ustawić odpowiednie wyrażenie showplan na OFF, co spowoduje wykonanie tego zapytania lub wyrażenia. Opcja SHOWPLAN daje wyniki podobne do użycia narzędzia EXPLAIN PLAN z Oracle.
W przypadku SET STATISTICS PROFILE ON każde uruchomione zapytanie zwraca zwykły zbiór wynikowy oraz dodatkowy zbiór wynikowy zawierający profil wykonywania zapytania. Istnieją również inne opcje, między innymi SET STATISTICS IO oraz SET STATISTICS TIME.
Przetwarzanie wyrażeń Transact-SQL składa się z dwóch faz - kompilacji oraz wykonania. Opcja NOEXEC kompiluje wszystkie zapytania, ale ich nie uruchamia. Po ustawieniu NOEXEC na ON żadne kolejne wyrażenia nie są uruchamiane (włącznie z innymi wyrażeniami SET) aż do momentu przestawienia NOEXEC na OFF.
SET SHOWPLAN ON
SET NOEXEC ON
go
SELECT * FROM DEPT_ADMIN.DEPT,
STUDENT_ADMIN.STUDENT
WHERE MAJOR = DEPT
go
STEP 1
The type of query is SETON
STEP 1
The type of query is SETON
STEP 1
The type of query is SELECT
FROM TABLE
DEPT_ADMIN.DEPT
Nested iteration
Table Scan
FROM TABLE
STUDENT_ADMIN.STUDENT
Nested iteration
Table Scan
Optymalizacja Zapytań
Aby mieć wpływ na działanie oraz wydajność opartego na kosztach optymalizatora, Oracle wymaga użycia podpowiedzi. Oparty również na koszcie optymalizator SQL Server nie wymaga podpowiedzi dla wspomożenia procesu oceniania zapytań, są one jednak dostępne, gdyż w niektórych sytuacjach ich użycie może być uzasadnione.
Podpowiedź INDEX = {nazwa_indeksu | identyfikator_indeksu} określa nazwę lub identyfikator indeksu, który ma zostać użyty dla danej tabeli. Identyfikator_indeksu równy 0 wymusza skanowanie tabeli, podczas gdy identyfikator_indeksu równy 1 wymusza użycie indeksu zgrupowanego, jeśli taki istnieje. Przypomina to podpowiedzi dotyczące indeksów używane w Oracle.
Podpowiedź SQL Server FASTFIRSTROW nakazuje optymalizatorowi użycie niezgrupowanego indeksu, jeśli porządek jego kolumny odpowiada klauzuli ORDER BY. Ta podpowiedź działa w podobny sposób, jak podpowiedź FIRST_ROWS w Oracle.
Migracja Oracle na MSSQL cz. XXII
Data publikacji: 2003-09-24 15:24|
Odsłon: 4908|
Dodał: Jacek Kolonko
ODBC, XML, migracja aplikacji
Używanie XML
Microsoft SQL Server 2000 wprowadza nowe funkcje obsługujące mechanizmy XML. Dzięki XML można:
Dokonywać wyborów (SELECT), wstawień (INSERT) oraz uaktualnień (UPDATE) bazy danych SQL Server.
Użyć zapytań Xpath wobec XDR (schematy Zredukowanych Danych XML - ang. XML Data Reduced schemas).
Formatować wyniki wyrażeń Transact-SQL w XML przy użyciu FOR XML.
Więcej informacji o wsparciu dla XML SQL Server można znaleźć w dziale "Przegląd wsparcia Internetu oraz XML" (ang. XML and Internet Support Overview) w SQL Server Books Online. Można także przejrzeć Bibliotekę MSDN® w poszukiwaniu artykułów "Duwamish Online SQL Server XML Catalog Browsing" oraz "SQL Server XML and Web Application Architecture" na stronie http://msdn.microsoft.com/xml/.
Używanie ODBC
Niniejszy dział zawiera informacje o sposobach używania ODBC przez Oracle oraz SQL Server, a także informacje o tworzeniu oraz migracji aplikacji z ODBC.
Zalecana strategia konwersji
Podczas konwersji kodu aplikacji z Oracle do SQL Server należy postępować w opisany poniżej sposób:
Rozważyć konwersję aplikacji na ODBC, jeśli została napisana za pomocą Oracle Pro*C lub Interfejsu Wywołania Oracle (OCI - ang. Oracle Call Interface).
Zrozumieć domyślne zbiory wynikowe SQL Server oraz opcje kursorów i wybrać strategię pobierania najefektywniejszą dla danej aplikacji.
Przemapować w razie potrzeby typy danych ODBC SQL z Oracle na ODBC SQL SQL Server .
Użyć rozszerzeń ODBC Rozszerzonego SQL w celu utworzenia ogólnych wyrażeń SQL.
Określić, czy konieczne jest zastosowanie trybu ręcznego zatwierdzania w opartych na SQL Server aplikacjach.
Sprawdzić wydajność aplikacji i w razie potrzeby zmodyfikować program.
Archietkura ODBC
Architektura ODBC
Microsoft udostępnia zarówno 16-bitową, jak i 32-bitową wersję swojego sterownika ODBC dla SQL Server . 32-bitowy sterownik ODBC SQL Server ma wielowątkowe zabezpieczenia. Ten sterownik serializuje dostęp przez wiele wątków do współdzielonych uchwytów (ang. handle) do wyrażeń (hstmt), uchwytów do połączeń (hdbc) oraz uchwytów środowiskowych (henv). Za zachowanie operacji w wyrażeniach oraz przestrzeganiu połączeń we właściwej kolejności odpowiedzialny jest jednak program w ODBC, nawet jeśli używa wielu wątków.
Ponieważ sterownik ODBC dla Oracle może być dostarczony przez jednego z wielu możliwych dostawców, istnieje też wiele możliwych scenariuszy dotyczących architektury oraz działania. Aby więc upewnić się, czy sterownik ODBC spełnia wymagania aplikacji, należy skontaktować się z jego dostawcą.
W większości przypadków most, sterownik ODBC dla Oracle używa SQL*Net do łączenia się z RDBMS Oracle. SQL*Net może jednak nie zostać użyty w przypadku łączenia się z Personal Oracle.
Poniższa ilustracja pokazuje architekturę aplikacja/sterownik dla 32-bitowego środowiska.
Określenie thunking oznacza przechwycenie wywołania funkcji, wykonanie specjalnego przetwarzania do przetłumaczenia kodu z 16-bitowego na kod 32-bitowy i przekazanie kontroli funkcji docelowej. Opcjonalnie Biblioteka Kursorów ODBC znajduje się pomiędzy zarządcą sterowników a sterownikiem ODBC. Ta biblioteka udostępnia przewijalne usługi kursorowe nad sterownikami obsługującymi kursory tylko-do-przodu.
Kursory tylko-do-przodu
Oracle oraz Server Server odmiennie traktują zbiory wynikowe oraz kursory. Zrozumienie tych różnic jest ważne dla pomyślnego przeniesienia aplikacji klienckiej z Oracle na SQL Server i ich póĽniejszej optymalnej wydajności.
W Oracle każdy zbiór wynikowy z polecenia SELECT pobierany w aplikacji klienckiej jest zawsze traktowany jako kursor tylko-do-przodu, jeśli użyto ODBC, OCI lub Osadzonego SQL jako narzędzia do utworzenia danej aplikacji.
Domyślnie w Oracle każde polecenie FETCH wydane przez program kliencki (dla przykładu: SQLFetch w ODBC) powoduje okrężną wędrówkę przez serwer, aby zwrócić tylko jeden wiersz. Jeśli aplikacja kliencka chce pobrać więcej niż jeden wiesz za jednym razem, musi skonfigurować w swoim programie tablicę i wykonać pobranie tablicy.
Dla kursora tylko-do-odczytu na serwerze nie są utrzymywane żadne blokady pomiędzy pobraniami z powodu wielowersyjnego modelu współbieżności w Oracle. Kiedy program określa uaktualnialny kursor za pomocą klauzuli FOR UPDATE, wszystkie żądane wiersze w poleceniu SELECT są blokowane w momencie otwarcia wyrażenia. Te blokady na poziomie wierszy pozostają do czasu, aż program wyśle żądanie COMMIT lub ROLLBACK.
W SQL Server wyrażenie SELECT nie zawsze związane jest z kursorem na serwerze. Domyślnie SQL Server po prostu przesyła strumieniem wszystkie wiersze zbioru wynikowego z wyrażenia SELECT z powrotem do klienta. To przesyłanie rozpoczyna się w momencie uruchomienia wyrażenia SELECT. Strumienie zbiorów wynikowych mogą być także zwracane przez wyrażenia SELECT wewnątrz procedur przechowywanych. Dodatkowo pojedyncza procedura przechowywana lub wsad poleceń może wysłać strumieniem powrotnym wiele zbiorów wyników w odpowiedzi na pojedyncze wyrażenie EXECUTE.
Klient SQL Server jest odpowiedzialny za pobieranie tych domyślnych zbiorów wynikowych od momentu, gdy stają się one dostępne. W przypadku domyślnych zbiorów wynikowych pobrania po stronie klienta nie powodują okrężnych "podróży" do serwera. Zamiast więc pobrania z domyślnych zbiorów wynikowych pobierają dane z lokalnych buforów sieciowych do zmiennych programu. Ten model domyślnego zbioru wynikowego tworzy efektywny mechanizm zwracania wielu wierszy danych do klienta przy tylko jednokrotnym przejściu przez tę sieć. Minimalizacja przejść dookoła przez sieć jest zwykle najistotniejszym czynnikiem wpływającym na wydajność aplikacji klient/serwer.
W porównaniu z kursorami Oracle, domyślne zbiory wynikowe nakładają kilka dodatkowych obowiązków na aplikację kliencką SQL Server, która musi natychmiast pobierać wiersze zbiorów wynikowych zwracanych przez wyrażenie EXECUTE. Jeśli aplikacja ma przedstawiać wiersze innym częściom programu stopniowo, musi buforować wiersze w wewnętrznej tabeli. Jeżeli nie uda się jej pobrać wszystkich wierszy zbiorów wynikowych, połączenie z SQL Server pozostaje zajęte.
W takiej sytuacji żadne inne zadanie (takie jak wyrażenia UPDATE) nie będą mogły być uruchomione na tym połączeniu do czasu, aż wszystkie wiersze zbioru wynikowego zostaną pobrane lub klient anuluje żądanie. Co więcej, serwer będzie nadal utrzymywał blokady współdzielenia na stronach danych tabeli aż do ukończenia pobierania. Właśnie z powodu tego utrzymywania blokad współdzielenia aż do zakończenia pobrania konieczne jest pobranie wszystkich wierszy tak szybko, jak to tylko możliwe. Ta technika stanowi najistotniejszą różnicę między SQL Server a Oracle, gdzie powszechnie używane jest w aplikacjach pobieranie przyrostowe.
Kursory SQL Server
SQL Server firmy Microsoft udostępnia serwery kursora w celu zaspokojenia potrzeby przyrostowego pobierania przez sieć zbiorów wynikowych. Aplikacja może żądać kursorów serwera po prostu wywołując SQLSetStmtOption do ustawienia opcji SQL_CURSOR_TYPE.
W momencie wywołania wyrażenia SELECT w charakterze kursora serwera, tylko identyfikator kursora jest zwracany przez wyrażenie EXECUTE. Dalsze żądania pobrań przekazują z powrotem do serwera identyfikator kursora wraz z parametrem określającym liczbę wierszy do jednorazowego pobrania. Serwer zwraca wtedy żądaną liczbę wierszy.
Pomiędzy żądaniami pobrania połączenie pozostaje wolne i możliwe jest wydawanie innych poleceń, włącznie z innymi żądaniami kursora OPEN oraz FETCH. W terminologii ODBC oznacza to, że kursory serwera pozwalają sterownikowi SQL Server na obsługę wielu aktywnych wyrażeń na pojedynczym połączeniu.
Co więcej, kursory serwera zwykle nie trzymają blokad pomiędzy żądaniami pobrania, tak więc możliwe są przerwy pomiędzy pobraniami w oczekiwaniu na polecenia użytkownika, co nie ma wpływu na innych użytkowników. Kursory serwera mogą być uaktualnianie na miejscu za pomocą opcji optymistycznego wykrywania konfliktów lub pesymistycznej współbieżności blokującej przewijanie.
Mimo że funkcje te bardziej ułatwiają pisanie programów z kursorami serwera programistom przyzwyczajonym do Oracle niż w przypadku używania domyślnych zbiorów wynikowych, ma to swoją cenę. W porównaniu z domyślnymi zbiorami wynikowymi:
Kursory serwera są bardziej kosztowne w sensie zasobów serwera, ponieważ wykorzystywana jest tymczasowa pamięć do przechowywania na serwerze informacji o stanie kursora.
Kursory serwera są bardziej kosztowne pod względem pobierania danego zbioru wynikowego danych, ponieważ wyrażenie EXECUTE oraz każde żądanie pobrania w kursorze serwera wymaga osobnej komunikacji do serwera i z powrotem.
Kursory serwera są mniej elastyczne pod względem obsługiwanych wsadów oraz procedur przechowywanych. Jest to spowodowane tym, że kursor serwera może wykonywać tylko jedno wyrażenie SELECT jednocześnie, podczas gdy domyślne zbiory wynikowe mogą być używane do wsadów oraz procedur przechowywanych zwracających wiele zbiorów wynikowych; mogą też zawierać wyrażenia inne niż tylko SELECT.
Dla tych powodów zalecane jest ograniczanie wykorzystywania kursorów serwera do tych części aplikacji, które potrzebują ich funkcji.
Kursory przewijalne
RDBMS Oracle obsługuje tylko kursory przewijające do przodu. Każdy wiersz jest pobierany do aplikacji w takiej kolejności, jaką określono w zapytaniu. Oracle nie obsługuje żądań cofnięcia się do wcześniej pobranego wiersza. Jedynym sposobem na cofnięcie się jest zamknięcie kursora i jego ponowne otworzenie. Niestety, powoduje to cofnięcie do pierwszego wiersza w aktywnym zbiorze zapytań.
Ponieważ SQL Server obsługuje przewijalne kursory, można ustawić pozycję kursora SQL Server na dowolnym wierszu. Możliwe jest przewijanie zarówno do przodu, jak i do tyłu. W przypadku wielu aplikacji z interfejsem użytkownika możliwość takiego przewijania jest bardzo użyteczną funkcją. W przypadku przewijalnych kursorów aplikacja może pobrać najpierw tyle wierszy, ile zmieści się na ekranie, a potem pobierać tylko dodatkowe wiersze na żądanie użytkownika.
Chociaż Oracle nie obsługuje kursorów przewijalnych, ograniczenie to można obejść używając kilku opcji ODBC. Na przykład niektóre sterowniki ODBC dla Oracle, takie jak ten dostarczany z wizualnym systemem developerskim Microsoft Developer Studio®, oferują oparte na kliencie przewijalne kursory w samym sterowniku.
Alternatywnie Biblioteka Kursorów ODBC obsługuje kursory przewijalne blokowo w przypadku każdego sterownika ODBC spełniającego warunki pierwszego poziomu (ang. Level One) zgodności. Obie te opcje kursorów klienckich obsługują przewijanie z wykorzystaniem RDBMS dla pobierania wprzód, buforując przy tym dane zbiorów wynikowych w pamięci lub na dysku. Kiedy dane są potrzebne, sterownik pobiera je w zależności od potrzeb z RDBMS lub ze swojego lokalnego bufora.
Oparte na kliencie kursory obsługują także pozycjonowane wyrażenia UPDATE oraz DELETE dla zbiorów wynikowych wygenerowanych przez wyrażenia SELECT. Biblioteka kursorów konstruuje wyrażenie UPDATE lub DELETE z klauzulą WHERE określającą zbuforowaną wartość dla każdej kolumny w wierszu.
Jeśli przewijalne kursory są niezbędne i jednocześnie przydatne jest zachowanie takiego samego kodu Ľródłowego zarówno dla implementacji dla Oracle, jak i dla SQL Server, Biblioteka Kursorów ODBC jest przydatną opcją. Więcej informacji na temat Biblioteki Kursorów ODBC można znaleĽć w dokumentacji do ODBC.
Strategie używania kursorów serwera oraz domyślnych zbiorów wynikowych SQL Server
Przy tych wszystkich opcjach pobierania danych dostępnych w SQL Server czasami trudno jest zdecydować się, co użyć i kiedy. Oto kilka przydatnych wskazówek:
Domyślne zbiory wynikowe są zawsze najszybszym sposobem pobrania przez klienta całego zbioru danych z SQL Server. Należy w aplikacji szukać możliwości wykorzystania tej przewagi. Przykładowo wsadowe generowanie raportów zwykle przetwarza cały zbiór wynikowy aż do końca - bez interakcji z użytkownikiem oraz bez uaktualnień w trakcie przetwarzania.
Jeśli program potrzebuje kursorów z możliwością uaktualniania, należy użyć kursorów serwera. Domyślnych zbiorów wynikowych nie da się uaktualniać, jeśli użyto pozycjonowanych wyrażeń UPDATE lub DELETE. Dodatkowo kursory serwera są lepsze, jeśli chodzi o uaktualnianie, od opartych na kliencie kursorach, które muszą symulować pozycjonowane wyrażenia UPDATE oraz DELETE poprzez konstruowanie równoważnych przeszukanych wyrażeń UPDATE oraz DELETE.
Jeśli program potrzebuje przewijalnych kursorów tylko-do-odczytu, to zarówno Biblioteka Kursorów ODBC, jak i kursory serwera są dobrym wyborem. Biblioteka Kursorów ODBC zapewnia zgodne zachowanie pomiędzy implementacją dla SQL Server oraz dla Oracle, natomiast serwery kursora dają większą elastyczność w kwestii ilości danych pobieranych jednorazowo poprzez sieć.
W przypadku zastosowania domyślnych zbiorów wynikowych lub Biblioteki Kursorów ODBC zbudowanych na domyślnych zbiorach wynikowych należy upewnić się, że zbiór wynikowy zostanie pobrany aż do końca tak szybko, jak to możliwe, w celu uniknięcia utrzymywania blokad współdzielenia na serwerze.
W przypadku użycia kursorów serwera należy użyć SQLExtendedFetch do pobierania wierszy w blokach, a nie pojedynczo. Jest to odpowiednik pobierania tabelami w aplikacjach Oracle. Każde żądanie pobrania na kursorze serwera wymaga przejścia dookoła przez sieć - od aplikacji do RDBMS.
Robienie zakupów stanowi dobrą analogię. Załóżmy, że kupujemy kilka toreb towarów; ładujemy jedną torbę do samochodu, jedziemy do domu, zostawiamy torbę i wracamy po następną. Jest to mało prawdopodobny scenariusz, ale tak się właśnie dzieje w SQL Server w przypadku pobierania pojedynczych wierszy z kursora serwera.
Jeśli program potrzebuje kursorów tylko-do-odczytu przewijanych tylko do przodu, ale polega na wielu otwartych kursorach na tym samym połączeniu, należy użyć domyślnych zbiorów wynikowych, jeśli wiadomo, że możliwe będzie pobranie całego zbioru wynikowego do zmiennych programu. Jeśli nie jest to pewne, należy użyć kursora serwera.
Ta strategia nie jest tak trudna, jak się wydaje. Większość programistów wie, kiedy generuje pojedyncze wyrażenie SELECT, które może zwrócić maksymalnie jeden wiersz. W przypadku pojedynczych pobrań użycie domyślnego zbioru wyników jest bardziej wydajne niż użycie kursora serwera.
Więcej informacji na ten temat można znaleĽć w dokumentacji SQL Server Books Online.
Wiele aktywnych wyrażeń (hstmt) na połączenie
Sterownik ODBC używa zwykle uchwytu (ang. handle) do wyrażenia (hstmt) do śledzenia każdego aktywnego wyrażenia SQL w programie. Uchwyt do wyrażenia jest zawsze związany z uchwytem połączenia do RDBMS (hdbc). Zarządca sterownika ODBC używa uchwytu połączenia do wysyłania żądanych wyrażeń SQL do określonego RDBMS. Większość sterowników ODBC dla Oracle dopuszcza wiele uchwytów do wyrażeń na połączenie. Jednakże sterownik ODBC SQL Server dopuszcza tylko jeden uchwyt do aktywnego wyrażenia na połączenie, jeśli używane są domyślne zbiory wynikowe. Funkcja SQLGetInfo sterownika SQL Server zwraca wartość 1, jeśli otrzyma zapytanie z opcją SQL_ACTIVE_STATEMENTS. Kiedy opcje wyrażeń ustawione są tak, że wykorzystują one kursory serwera, dopuszczalnych jest wiele aktywnych wyrażeń na uchwyt do połączenia.
Więcej informacji na temat ustawiania opcji wyrażeń związanych z żądaniem kursorów serwera można znaleĽć w dokumentacji SQL Server Books Online.
Mapowanie typów danych
Sterownik ODBC SQL Server udostępnia bogatszy asortyment mapowań typów danych niż większość dostępnych sterowników ODBC dla Oracle.
Typ danych SQL Server firmy Microsoft |
Typ danych ODBC SQL |
binary |
SQL_BINARY |
bit |
SQL_BIT |
char, character |
SQL_CHAR |
datetime |
SQL_TIMESTAMP |
decimal, dec |
SQL_DECIMAL |
float, double precision, float(n) dla n = 8-15 |
SQL_FLOAT |
image |
SQL_LONGVARBINARY |
int, integer |
SQL_INTEGER |
money |
SQL_DECIMAL |
nchar |
SQL_WCHAR |
ntext |
SQL_WLONGVARCHAR |
numeric |
SQL_NUMERIC |
nvarchar |
SQL_WVARCHAR |
real, float(n) dla n = 1-7 |
SQL_REAL |
smalldatetime |
SQL_TIMESTAMP |
smallint |
SQL_SMALLINT |
smallmoney |
SQL_DECIMAL |
sysname |
SQL_VARCHAR |
text |
SQL_LONGVARCHAR |
timestamp |
SQL_BINARY |
tinyint |
SQL_TINYINT |
uniqueidentifier |
SQL_GUID |
varbinary |
SQL_VARBINARY |
varchar |
SQL_VARCHAR |
Typ danych timestamp jest konwertowany na typ danych SQL_BINARY. Dzieje się tak, ponieważ wartości w kolumnie timestamp nie są danymi typu datetime, ale raczej typu binary(8) i używane są do określania kolejności aktywności SQL Server w wierszu.
Mapowanie typów danych Oracle w przypadku sterownika ODBC z SQL Server dla Oracle są pokazane w poniższej tabeli.
Typ danych Oracle |
Typ danych ODBC SQL |
CHAR |
SQL_CHAR |
DATE |
SQL_TIMESTAMP |
LONG |
SQL_LONGVARCHAR |
LONG RAW |
SQL_LONGVARBINARY |
NUMBER |
SQL_FLOAT |
NUMBER(P) |
SQL_DECIMAL |
NUMBER(P,S) |
SQL_DECIMAL |
RAW |
SQL_BINARY |
VARCHAR2 |
SQL_VARCHAR |
Sterowniki ODBC dla Oracle dostarczane przez innych dostawców mogą używać innego mapowania typów danych.
Rozszerzony SQL ODBC
Standard Rozszerzonego SQL ODBC udostępnia ODBC rozszerzenia SQL obsługujące zaawansowane niestandardowe funkcje SQL oferowane zarówno w Oracle, jak i SQL Server . Ten standard pozwala sterownikowi ODBC na konwersję ogólnych wyrażeń SQL na składnię "rodzimą" dla Oracle bądĽ SQL Server .
Ten standard odnosi się do zewnętrznych złączeń, takich jak znaki wyjścia z predykatu (ang. predicate escape characters), funkcje skalarne, wartości data/czas/znacznik czasu oraz programów przechowywanych. Do identyfikowania tych rozszerzeń używana jest następująca składnia:
--(*vendor(Microsoft), product(ODBC) extension *)--
LUB
{extension}
Konwersja odbywa się automatycznie, w czasie działania i nie wymaga modyfikacji w kodzie. W większości aplikacji jest to najlepszy sposób obsługi róznych typów RDBMS - bo to sterownik odpowiada za właściwą transalcję.
Zewnętrzne złączenia
Oracle oraz SQL Server nie mają kompatybilnej składni zewnętrznych złączeń. Problem ten można rozwiązać przy użyciu składni zewnętrznych złączeń rozszerzonego SQL ODBC. Składnia SQL Server jest taka sama, jak składnia Rozszerzonego SQL ODBC/SQL-92. Jedyną różnicą jest kontener {oj }.
Rozszerzony SQL ODBC oraz SQL-92 |
Oracle |
SQL Server firmy Microsoft |
SELECT STUDENT.SSN, FNAME, LNAME, CCODE, GRADE |
SELECT STUDENT.SSN, FNAME, LNAME, |
SELECT STUDENT.SSN, FNAME, LNAME, |
Wartości daty, czasu oraz znacznika czasu
ODBC udostępnia trzy klauzule wyjścia dla wartości daty, czasu oraz znacznika czasu.
Kategoria |
Skrócona składnia |
Format |
Data |
{d 'wartość'} |
"rrrr-mm-dd" |
Czas |
{t ' wartość '} |
"gg:mm:ss" |
Znacznik czasu |
{Ts ' wartość '} |
"rrrr-mm-dd gg:mm:ss[.f.]" |
Format dat ma większy wpływ na aplikacje w Oracle, niż na aplikacje oparte na SQL Server. Oracle oczekuje formatu daty "DD-MIES-RR". W każdym innym przypadku w celu dokonania konwersji formatu używane są funkcje TO_CHAR oraz TO_DATE z podanym modelem formatu.
SQL Server automatycznie dokonuje konwersji większości popularnych formatów danych, a ponadto udostępnia funkcję CONVERT, której można użyć, jeśli automatyczna konwersja nie jest możliwa do przeprowadzenia.
Jak pokazano w tabeli, rozszerzony SQL ODBC działa z obiema bazami danych. SQL Server nie potrzebuje funkcji konwersji, niemniej jednak skrócona składnia ODBC może ogólnie być używana zarówno w Oracle, jak i SQL Server.
Rozszerzony SQL ODBC |
Oracle |
SQL Server firmy Microsoft |
SELECT SSN, FNAME, LNAME, BIRTH_DATE |
SELECT SSN, FNAME, LNAME, |
SELECT SSN, FNAME, LNAME, |
Wywoływanie procedur przechowywanych
Skrócona składnia ODBC wywoływania procedur obsługuje zarówno elementy w SQL Server Microsoft oraz pakiety, funkcje i procedury przechowywane Oracle. Opcjonalny operator "?=" przechwytuje zwracaną wartość tak w przypadku funkcji Oracle, jak i procedury SQL Server. Składnia parametrów używana jest do przekazywania oraz zwracania wartości do/z wywołanego programu. W większości przypadków taka sama składnia może być użyta zarówno w przypadku aplikacji opartych na Oracle, jak i aplikacji opartych na SQL Server .
W poniższym przykładzie funkcja SHOW_RELUCTANT_STUDENTS jest częścią pakietu P1 Oracle. Funkcja ta musi istnieć w pakiecie; zwraca wiele wierszy z kursora PL/SQL. W przypadku wywoływania funkcji lub procedury istniejącej w pakiecie przez nazwą programu należy umieścić nazwę pakietu.
Funkcja SHOW_RELUCTANT_STUDENTS z pakietu P1 używa kursora do pobrania wielu wierszy danych. Każdy wiersz musi zostać zażądany poprzez wywołanie tejże funkcji. Jeśli nie ma już więcej wierszy do pobrania, funkcja zwraca wartość 0, sygnalizując, że nie ma już więcej wierszy od odebrania. Wynikowa wydajność tego przykładowego pakietu oraz jego funkcji może nie być satysfakcjonująca. W tym przykładzie procedura SQL Server jest bardziej wydajna.
Ogólny rozszerzony SQL ODBC |
Oracle |
SQL Server firmy Microsoft |
{?=} call nazwa_procedury[(parameter(y))]}
SQLExecDirect(hstmt1,(SQLCHAR *)"{? = call właściciel.procedura(?)}", |
SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call |
SQLExecDirect(hstmt1, (SQLCHAR*)"{? = call |
Rodzima translacja SQL
Z powodu różnorodności sterowników ODBC zarówno dla Oracle, jak i dla SQL Server nie zawsze uzyskany zostanie ten sam łańcuch konwersji dla rozszerzonych funkcji SQL. Aby rozwiązać kwestię usuwania błędów z aplikacji, można pomyśleć o użyciu funkcji SQLNativeSql. Ta funkcja zwraca łańcuchy tekstowe SQL tak, jak został on przetłumaczony przez sterownik.
Poniżej przedstawiono możliwe wyniki dla poniższego łańcucha wejściowego SQL zawierającego skalarną funkcję CONVERT. Kolumna SSN jest zdefiniowana jako typ CHAR(9) i jest konwertowana na wartość liczbową.
Oryginalne wyrażenie |
Skonwertowane wyrażenie Oracle |
Skonwertowane wyrażenie SQL Server |
SELECT (fn CONVERT |
SELECT TO_NUMBER(SSN) |
SELECT CONVERT(INT,SSN) |
Tryb ręcznego zatwierdzania
Oracle automatycznie wchodzi w tryb transakcyjny w momencie zmodyfikowania danych przez użytkownika. Potem musi nastąpić jawne użycie COMMIT w celu zapisania zmian w bazie danych. Jeśli użytkownik chce anulować zmiany, może użyć w tym celu polecenia ROLLBACK.
Domyślnie SQL Server automatycznie zatwierdza każdą pojawiającą się zmianę. Jest to tak zwany tryb automatycznego zatwierdzania w ODBC. Jeśli jest to niepożądane, można użyć wyrażenia BEGIN TRANSACTION w celu zasygnalizowania początku bloku wyrażeń składających się na transakcję. Po pojawieniu się takiego wyrażenia występuje po nim jawne wyrażenie zatwierdzające (COMMIT TRANSACTION) lub wycofujące (ROLLBACK TRANSACTION) transakcję.
W celu zapewnienia zgodności z aplikacją w Oracle, zalecane jest użycie funkcji SQLConnectOption do wprowadzenia aplikacji opartej na SQL Server w trybie transakcji bezwarunkowych. W tym celu opcja SQL_AUTOCOMMIT musi być ustawiona na SQL_AUTOCOMMIT_OFF. Poniższy wyjątek z kodu demonstruje tę koncepcję:
SQLSetConnectOption(hdbc1, SQL_AUTOCOMMIT,-sql_AUTOCOMMIT_OFF);
Opcja SQL_AUTOCOMMIT_OFF instruuje sterownik, żeby ten używał transakcji bezwarunkowych. Domyślna opcja SQL_AUTOCOMMIT_ON nakazuje sterownikowi używanie trybu automatycznego zatwierdzania, w którym każde wyrażenie jest zatwierdzane natychmiast po jego wykonaniu. Przejście z trybu ręcznego zatwierdzania na tryb automatyczny powoduje zatwierdzenie wszystkich transakcji w danym połączeniu.
Jeśli ustawiona jest opcja SQL_AUTOCOMMIT_OFF, aplikacja musi zatwierdzać lub anulować transakcje jawnie za pomocą funkcji SQLTransact. Ta funkcja wysyła żądanie potwierdzenia lub wycofania operacji dla wszystkich aktywnych operacji dla wszystkich uchwytów związanych z uchwytem połączenia. Może także zażądać potwierdzenia lub wycofania operacji dla wszystkich połączeń związanych z uchwytem do środowiska.
SQLTransact(henv1, hdbc1, SQL_ROLLBACK);
(SQLTransact(henv1, hdbc1, SQL_COMMIT);
Kiedy tryb automatycznego zatwierdzania jest wyłączony, sterownik wysyła do serwera wyrażenie SET IMPLICIT_TRANSACTIONS ON. Począwszy od wersji 6.5 SQL Server, wyrażenia DDL są obsługiwane w tym trybie.
Aby zatwierdzić lub wycofać transakcję w trybie ręcznego zatwierdzania, aplikacja musi wywołać SQLTransact. SQL Server wysyła wyrażenie COMMIT TRANSACTION w celu zatwierdzenia transakcji lub wyrażenie ROLLBACK TRANSACTION w celu jej anulowania.
Należy pamiętać, że tryb ręcznego zatwierdzania może poważnie wpłynąć na wydajność opartych na SQL Server aplikacji. Każde żądanie zatwierdzenia wymaga osobnej "wędrówki" do serwera w celu wysłania łańcucha COMMIT TRANSACTION.
W przypadku pojedynczych atomowych transakcji (pojedyncze INSERT, UPDATE lub DELETE z następującym natychmiast po nich COMMIT) należy użyć trybu automatycznego zatwierdzania.
Losowe sortowanie wyników kwerendy
Data publikacji: 2002-06-30 10:53|
Odsłon: 283|
Dodał: Jacek Kolonko
SQL Server ma funkcję do zwracania liczb losowych (RAND(ziarno)). Ziarno określa liczbę, które tak naprawdę jednoznacznie określa zwracaną liczbę pseudolosową. Jeżeli ten parametr nie zostanie podany, SQL Server zwraca
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
select RAND(),rand(3)
select RAND(0)
select RAND(1)
select RAND(2)
Poniżej przedstawiony jest wynik działania tej funkcji:
0.51854834815116846
(1 row(s) affected)
0.9957130855330244 0.71362925915543995
(1 row(s) affected)
0.94359739042414437
(1 row(s) affected)
0.71359199321292355
(1 row(s) affected)
0.7136106261841817
(1 row(s) affected)
Wytłuszczono te wartości pseudolosowe, które są takie same, wraz z kolejnymi wywołaniami skryptu. Tak naprawdę, aby otrzymać liczbę pseudolosową, trzeba użyć albo samej funkcji RAND(), albo dobrać ziarno zależne od zewnętrznych czynników. W podanym powyżej przykładzie wykorzystywany jest czas (odpowiednio przekształcony). Można też użyć zmienne SQL Server, takie jak @@IDLE czy @@CPU_BUSY - ich wartość zachowuje się dosyć "losowo".
Problem polega na tym, że nawet podając
select * from Customers order by rand()
Otrzymamy za każdym razem tą samą kolejność rekordów - funkcja RAND zostanie wywołana tylko raz, a następnie zostanie wykonana kwerenda (ze znaną wartością funkcji RAND).
Jednym prostym sposobem uzyskania losowego porządku sortowania jest wykorzystanie funkcji NEWID(). Tworzy ona nową wartość typu uniqueidentifier, która powstaje na bazie numeru MAC karty sieciowej, czasu itp - podobnie jak unikalny numer GUID w COM. Zwracany identyfikator ma postać na przykład: 732264CB-AF84-4E97-AF57-6453BBE87D61.
Tak więc jeżeli zostanie wykonane następujące polecenie:
select * from Customers order by newid()
to zwrócone rekordy będą posortowane "losowo".
Analogicznie, aby wybrać jeden "losowy" rekord można napisać:
select * from Customers order by newid()
Omawiany przykład wykorzystuje przykładową bazę danych Northwind instalowaną razem z Microsoft SQL Server 2000.
Prawa dostępu i dynamiczne zapytania SQL
Data publikacji: 2002-06-30 11:01|
Odsłon: 390|
Dodał: Jacek Kolonko
Aby wykonać dynamiczne zapytanie SQL na jakimś obiekcie bazodanowym, użytkownik musi mieć pełne prawa do danego obiektu.
Proszę przeanalizować poniższą procedurę przechowywaną:
CREATE PROCEDURE GeneralSelect @TableName SYSNAME
AS
EXEC ('SELECT * FROM ' + @TableName)
GO
Zwykle wywołania do tego typu procedury będą miały postać:
EXEC GeneralSelect 'nazwa_tabeli'
Jednak, przy użyciu powyższej procedury można wykonać następujący kod:
EXEC GeneralSelect 'nazwa_tabeli DROP TABLE nazwa_tabeli'
co spowoduje, że po zwróceniu rekordów, tabela zostanie skasowana. Tak więc wystarczyłoby, aby użytkownik miał prawa EXECUTE do procedury wbudowanej GeneralSelect, by mógł skasować tabelę. SQL Server zabezpiecza przed taką sytuacją wymagając, by użytkownik wykorzystujący dynamiczne zapytania SQL miał odpowiednie prawa do obiektów których dotyczy zapytanie (tu do tabeli nazwa_tabeli).
Manipulacja indeksami - dynamicznie dodawanie i usuwanie z zapamiętywaniem historii
Data publikacji: 2002-08-21 17:56|
Odsłon: 656|
Dodał: Jacek Kolonko
Manipulacja indeksami - dynamicznie dodawanie i usuwanie z zapamiętaniem historii
Często zdarza się, że przed ładowaniem danych do tablicy, czy też przy skomplikowanych operacjach operacjach indeksy są często zmieniane, bądź też usuwane. Enterprise Manager pozwala łatwo tworzyć indeksy, a następnie generować odpowiednie skrypty TSQL, jednak warto mieć wygodne procedury, które ułatwią zapisanie historii zmian indeksów, czy pozwolą w dowolnym momencie "cofnąć się" do wybranej konfiguracji.
Poniżej przedstawiony jest przykład 5 procedur przeznaczonych do manipulacji indeksami (autorstwa Phillip D. Snipes i Dale Butcher, z drobnymi zmianami).
Dostępne są następujące procedury:
sp_LogIndexes (@table varchar(55) = null, @clustereduq varchar(1) = 'N', @WhenDescrip varchar(50) = 'Before Import') Procedura sp_LogIndexes zapisuje ustawienia indeksów wybranej tabeli (przekazanej jako parametr @table) w tbl_IndexList (tabela w razie potrzeby jest tworzona w aktualnej bazie). Parametr @clustereduq określa, czy uwzględniać unikatowe indeksy klastrowe, a @WhenDescrip to dodatkowy opis (np. dlaczego rejestrowane są indeksy).
sp_SavenDropIndexes (@table varchar(55), @Clustereduq varchar(1) = 'N', @whenDescrip varchar(50) = 'Before Import') Procedura wywołuje sp_LogIndexes aby zapisać informacje o aktualnych indeksach. Następnie tworzy dynamiczny sql z poleceniem usunięcia (drop) każdego z indeksów wybranej tabeli. Parametry:
@table - nazwa tabeli, z której usuwane są indeksy.
@Clustereduq - Y/N, czy należy usuwać unikalne indeksy klastrowe
@whenDescrip - akceptuje do 50 znaków opisu (np - dlaczego indeks jest usuwany)
Przykład użycia: exec sp_SavenDropIndexes 'Authors', 'Y', 'Before Rebuild'
@table - nazwa tabeli do wyszukania tbl_IndexListHistory table.'
@backcount - liczba analizowanych okresów "wstecz"'
@backperiod - kod okresu - akceptuje D (dni), W (tygodnie), M (miesiące), Y (lata); domyślnie D
Przykład: Aby przeszukać historię indeksów i znaleźć te, które były utworzone na tabeli Authors w ciągu ostatnich 2 tygodni należy napisać: exec sp_SearchIndexHist 'Authors', 2, 'W'
sp_compare_IndexHist_to_existing (@table varchar(55) = null, @HistDate datetime, @WhenDescrip varchar(50) = 'Before Import', @OSql varchar(1) = 'N' ) Procedura sp_compare_IndexHist_to_existing porównuje aktualnie założone indeksy z tymi, które znajdują się w tbl_IndexListHistory. Przydatna do analizy zmian indeksów w czasie. Parametry:
@table - nazwa tabeli której indeksy są porównywane z historią tbl_IndexListHistory
@HistDate - określa, z którego dnia mają być brane indeksy do porównywania. Format daty zalezy od ustawień połączenia; powinien zawsze działać 'rrrrmmdd'
@WhenDescrip - akceptuje do 50 znaków; określa opis danego zestawu" indeksów, z którym ma być porównywany stan bieżący (parametr opcjonalny; trzeba dodać warunki w kodzie, by ten warunek był uwzględniany). Odpowiednie komentarze zostały oznaczone (*)
@OSql - akceptuje Y/N; określa czy wyprowadzać SQL, potrzebny do utworzenia indeksów
Poniżej przedstawiony jest sposób wywołania procedury sp_LogIndexes dla wszystkich tabel w bieżącej bazie danych.
DECLARE @RC int
DECLARE @table varchar(55)
DECLARE @clustereduq varchar(1)
DECLARE @WhenDescrip varchar(50)
set @clustereduq='Y'
set @WhenDescrip ='Mój opis'
/* Kursor służy do przeglądania wszystkich obiektów w bieżącej bazie danych
, o typie 'U', czyli tabel zdefiniowanych przez użytkownika. Można też
wykorzystać OBJECTPROPERTY */
DECLARE alltables CURSOR FOR select name from sysobjects where xtype='U'
OPEN alltables
FETCH NEXT FROM alltables INTO @table
select @table
WHILE @@FETCH_STATUS=0
BEGIN
select @table
EXEC @RC = [pubs].[dbo].[sp_LogIndexes] @table, @clustereduq, @WhenDescrip
FETCH NEXT FROM alltables INTO @table
END
CLOSE alltables
DEALLOCATE alltables
Uwaga! Wszystkie przykłady zakładają, że wykonywane są operacje na bazie pubs (tam też warto sprawdzić działanie tych procedur). Uwaga! Aby okreslić tabelę, należy przekazać tylko nazwę tabeli (bez właściciela). Np. zamiast dbo.authors należy podać samo authors)
Domyślnie skrypty tworzą tabele pomocnicze w tej samej bazie w której są tworzone lub modyfikowane indeksy.
Uwaga! Redakcja przetestowała te procedury. Jednak ani autor ani redakcja www.mssqlserver.org.pl nie ponosi odpowiedzialności za ew. straty wynikające ze stosowania tych procedur.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_LogIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_LogIndexes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_RebuildSavedIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[sp_RebuildSavedIndexes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_SavenDropIndexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SavenDropIndexes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_SearchIndexHist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SearchIndexHist]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_compare_IndexHist_to_existing]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_compare_IndexHist_to_existing]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE procedure sp_LogIndexes (@table varchar(55) = null, @clustereduq
varchar(1) = 'N', @WhenDescrip varchar(50) = 'Before Import') as
/******************************
Jest to część kolekcji 5 porcedur przechowywanych przeznaczonych do manipulacji
indexami (Smart Index Manipulation System).
Procedura sp_LogIndexes zapisuje ustawienia indeksów wybranej tabeli
(przekazanej jako parametr @table) w tbl_IndexList (tabela w razie potrzeby jest
tworzona w aktualnej bazie)
Polecenie drop oparte jest na kodzie, znalezionym w Internecie; Autor :
Eddy Djaja, Publix Super Markets, Inc.
Phillip D. Snipes
7/22/02
Dale Butcher
7/11/02
Dodatkowe komentarze + drobne zmiany: www.mssqlserver.org.pl
*******************************/
set nocount on
/* proces */
--Wyczyść tabelę
--Zainicjuj zmienne do generacji kwerendy
declare @empty varchar(1)
select @empty = ''
declare @des1 varchar(35), -- 35 pasujących spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35),
@objectid Int,
@indid smallint,
@groupid smallint,
@groupname sysname,
@status Int,
@keys nvarchar(2078),
@objname nvarchar(776),
@description nvarchar(210),
@misc nvarchar(210),
@index_name varchar(70),
@table_there1 char(5),
@table_there2 char(5),
@Now Datetime,
@i Int,
@thiskey sysname,
@sql varchar(100),
@trgsql varchar(1555)
Select @Now = getdate()
--Czy tabela tbl_IndexList istnieje
Set @table_there1 = 'False'
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_IndexList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Set @table_there1 = 'True'
If @table_there1 = 'False'
BEGIN
--Jeżeli nie, to utwórz
BEGIN TRAN T1
CREATE TABLE [dbo].[tbl_IndexList] (
[TableName] [varchar] (255) NULL ,
[index_name] [sysname] NULL ,
[stats] [int] NULL ,
[groupname] [sysname] NULL ,
[index_keys] [nvarchar] (2078) NULL ,
[indid] [smallint] NULL ,
[indexdescription] [nvarchar] (210) NULL ,
[misc] [varchar] (50) NULL ,
[WhenDescription] [varchar] (50) NULL ,
[SnapshotTime] [datetime] NOT NULL
)
COMMIT TRAN T1
END
--Czy tabela tbl_IndexListHistory istnieje
Set @table_there2 = 'False'
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_IndexListHistory]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
Set @table_there2 = 'True'
If @table_there2 = 'False'
BEGIN
--Jeżeli nie, to utwórz
BEGIN TRAN T2
CREATE TABLE [dbo].[tbl_IndexListHistory] (
[TableName] [varchar] (255) NULL ,
[index_name] [sysname] NULL ,
[stats] [int] NULL ,
[groupname] [sysname] NULL ,
[index_keys] [nvarchar] (2078) NULL ,
[indid] [smallint] NULL ,
[indexdescription] [nvarchar] (210) NULL ,
[misc] [varchar] (50) NULL ,
[WhenDescription] [varchar] (50) NULL ,
[SnapshotTime] [datetime] NOT NULL
)
COMMIT TRAN T2
END
--Wstawianie do tabeli
--Procedura obsługuje do 21 indeksowanych kolumn (można ją rozszerzyć, jak zachodzi
-- taka potrzeba)
--Badanie można też oprzeć o widok INFORMATION_SCHEMA.KEY_COLUMN_USAGE
If @clustereduq = 'N'
Begin
/* Znajdź wartości indeksów (klastrowy, unikalny itp) */
Select @des1 = Name from master.dbo.spt_values where Type = 'I' and number = 1
Select @des2 = Name from master.dbo.spt_values where Type = 'I' and number = 2
Select @des4 = Name from master.dbo.spt_values where Type = 'I' and number = 4
Select @des32 = Name from master.dbo.spt_values where Type = 'I' and number = 32
Select @des64 = Name from master.dbo.spt_values where Type = 'I' and number = 64
Select @des2048 = Name from master.dbo.spt_values where Type = 'I' and
number = 2048
Select @des4096 = Name from master.dbo.spt_values where Type = 'I' and
number = 4096
Select @des8388608 = Name from master.dbo.spt_values where Type = 'I' and
number = 8388608
Select @des16777216 = Name from master.dbo.spt_values where Type = 'I' and
number= 16777216
/* Znajdź daną tabelę */
Declare object_cursor CURSOR For
Select id
from sysobjects
where name = @table
Open object_cursor
FETCH Next FROM object_cursor INTO @objectid
/* Znajdź tabelę z indeksami */
While @@FETCH_STATUS = 0
Begin
Declare index_cursor insensitive cursor For
Select indid, groupid, Name, status from sysindexes
where id = @objectid And indid > 0 And indid < 255
order by indid
Open index_cursor
FETCH index_cursor into @indid, @groupid, @index_name, @status
/* Teraz, nalezy sprawdzić każdy indeks, zbatdać jego klucze i zapisać wynik
do tabeli */
While @@fetch_status >= 0
Begin
Select @objname = object_name(@objectid)
/* Najpierw - analiza wszystkich elementów*/
Select @keys = index_col(@objname, @indid, 1),
@i = 2, @thiskey = index_col(@objname, @indid, 2)
While (@thiskey Is Not Null )
Begin
Select @keys = @keys + ', ' + @thiskey, @i = @i + 1
Select @thiskey = index_col(@objname, @indid, @i)
End
Select @groupname = groupname from sysfilegroups where groupid = @groupid
Select @description = convert(varchar(210),
--bit 16 zgaszony, 1, 2, 16777216 zapalone, grupa
+ Case when (@status & 4)<>0 Then ' '+@des4 else @empty end
+ Case when (@status & 64)<>0 Then ' '+@des64 else
case when (@status & 32)<>0
then ' '+@des32 else @empty end end
+ Case when (@status & 2048)<>0 Then ' '+@des2048 else @empty
end
+ Case when (@status & 4096)<>0 Then ' '+@des4096 else @empty
end
+ Case when (@status & 8388608)<>0 Then ' '+@des8388608 else
@empty end
+ Case when (@status & 16777216)<>0 Then ' '+@des16777216 else
@empty end
+ Case when (@status & 16)<>0 Then ' clustered' else '
nonclustered' end)
Select @misc = convert(varchar(210),
--bit 16 zgaszony, 1, 2, 16777216 zapalone, grupa
Case when (@status & 1)<>0 Then ' '+@des1 else @empty end)
Select @description = RTrim(LTrim(@description))
/* Wstawianie wiersza z informacją o indeksie */
If RTrim(@description) = 'nonclustered' OR rtrim(@description) =
'unique nonclustered' OR rtrim(@description) = 'unique clustered' OR
rtrim(@description) = 'clustered'
Begin
insert into tbl_IndexList (tablename, index_name, stats, groupname,
index_keys, indid, indexdescription, whendescription, snapshottime)
values (@objname, @index_name, @status, @groupname, @keys, @indid,
@description, @WhenDescrip, @Now)
If @misc <> ''
Begin
update tbl_IndexList
Set misc = 'WITH IGNORE_DUP_KEY'
WHERE index_name = @index_name
End
End
/* Pobierz informacje o następnym indeksie */
fetch index_cursor into @indid, @groupid, @index_name, @status
End
deallocate index_cursor
FETCH Next FROM object_cursor INTO @objectid
End
DEALLOCATE object_cursor
/*Jeżeli @clustereduq = 'N' (domyślnie), usuwane są z tbl_IndexList
unikalne indeksy klastrowe*/
Delete from tbl_IndexList where TableName = @table and IndexDescription =
'unique clustered'
End
If @clustereduq = 'Y'
Begin
/* Znajdź indeksy (klastrowe, unikalne itp.) */
Select @des1 = Name from master.dbo.spt_values where Type = 'I' and number = 1
Select @des2 = Name from master.dbo.spt_values where Type = 'I' and number = 2
Select @des4 = Name from master.dbo.spt_values where Type = 'I' and number = 4
Select @des32 = Name from master.dbo.spt_values where Type = 'I' and number = 32
Select @des64 = Name from master.dbo.spt_values where Type = 'I' and number = 64
Select @des2048 = Name from master.dbo.spt_values where Type = 'I' and number =
2048
Select @des4096 = Name from master.dbo.spt_values where Type = 'I' and number =
4096
Select @des8388608 = Name from master.dbo.spt_values where Type = 'I' and number =
8388608
Select @des16777216 = Name from master.dbo.spt_values where Type = 'I' and number
= 16777216
/* Znajdź tabelę */
Declare object_cursor CURSOR For
Select id
from sysobjects
where name = @table
Open object_cursor
FETCH Next FROM object_cursor INTO @objectid
/* Znajdź indeksy */
While @@FETCH_STATUS = 0
Begin
Declare index_cursor insensitive cursor For
Select indid, groupid, Name, status from sysindexes
where id = @objectid And indid > 0 And indid < 255
order by indid
Open index_cursor
FETCH index_cursor into @indid, @groupid, @index_name, @status
/* Najpierw - analiza wszystkich elementów*/
While @@fetch_status >= 0
Begin
Select @objname = object_name(@objectid)
/* Analiza definicji indeksu */
Select @keys = index_col(@objname, @indid, 1),
@i = 2, @thiskey = index_col(@objname, @indid, 2)
While (@thiskey Is Not Null )
Begin
Select @keys = @keys + ', ' + @thiskey, @i = @i + 1
Select @thiskey = index_col(@objname, @indid, @i)
End
Select @groupname = groupname from sysfilegroups where groupid = @groupid
Select @description = convert(varchar(210),
--bit 16 zgaszony, 1, 2, 16777216 zapalone, grupa
+ Case when (@status & 4)<>0 Then ' '+@des4 else @empty end
+ Case when (@status & 64)<>0 Then ' '+@des64 else
case when (@status & 32)<>0 then ' '+@des32 else @empty
end end
+ Case when (@status & 2048)<>0 Then ' '+@des2048 else @empty
end
+ Case when (@status & 4096)<>0 Then ' '+@des4096 else @empty
end
+ Case when (@status & 8388608)<>0 Then ' '+@des8388608 else
@empty end
+ Case when (@status & 16777216)<>0 Then ' '+@des16777216 else
@empty end
+ Case when (@status & 16)<>0 Then ' clustered' else '
nonclustered' end)
Select @misc = convert(varchar(210),
--bit 16 zgaszony, 1, 2, 16777216 zapalone, grupa
Case when (@status & 1)<>0 Then ' '+@des1 else @empty end)
Select @description = RTrim(LTrim(@description))
/* Wstaw wiersz z informacją o indeksie */
If RTrim(@description) = 'nonclustered' OR rtrim(@description) =
'unique nonclustered' OR rtrim(@description) = 'unique clustered'
OR rtrim(@description) = 'clustered'
Begin
insert into tbl_IndexList (tablename, index_name, stats, groupname,
index_keys, indid, indexdescription, whendescription, snapshottime)
values (@objname, @index_name, @status, @groupname, @keys, @indid,
@description, @WhenDescrip, @Now)
If @misc <> ''
Begin
update tbl_IndexList
Set misc = 'WITH IGNORE_DUP_KEY'
WHERE index_name = @index_name
End
End
/* Następny element */
fetch index_cursor into @indid, @groupid, @index_name, @status
End
deallocate index_cursor
FETCH Next FROM object_cursor INTO @objectid
End
DEALLOCATE object_cursor
End
/*Jeżeli tabela nie była utworzona, to trzeba jeszcze zdefiniować trigger. Aby to
wykonać z poziomu procedury przechowywanej, trzeba skorzystać z dynamicznego SQL.
Trigger ma za zadanie przenosić informacje o indeksach do tabeli z historią
(tbl_indexlisthistory)
*/
If @table_there1 = 'False'
Begin
Set @trgsql = 'create trigger trg_del_indexhistory
on tbl_indexlist
for delete
as
begin
insert into tbl_indexlisthistory(tablename, index_name, stats, groupname, index_keys,
indid, indexdescription, misc, whendescription, snapshottime)
select tablename, index_name, stats, groupname, index_keys, indid, indexdescription,
misc, whendescription, snapshottime
from deleted
end'
BEGIN TRAN TR1
Exec (@trgsql)
COMMIT TRAN TR1
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE proc sp_RebuildSavedIndexes (@table varchar(55))
as
/******************************
Procedura przechodzi przy użyciu kursora po tbl_IndexList i tworzy dynamiczne
polecenie SQL które generuje dany indeks
Część kodu pochodzi od Eddy Djaja, Publix Super Markets, Inc.
z 12/07/1999.
W ostatnim kroku usuwane są informacje o indeksach danej tabeli z tbl_IndexList
(które zostały odzyskane). Ponieważ na tabeli tbl_IndexList znajduje się trigger on
delete, to informacje są przenoszone do tabeli historycznej tbl_IndexListHistory
(pomysł Dale Buther)
Phillip D. Snipes
7/25/02
Dale Butcher
7/21/02
*******************************/
/*********************
Wyśeitl instrukcję, gdy nie przekazano nazwy tabeli
**********************/
if @table = ''
Begin
Print '#################################################'
Print char(13)+'Należy przekazać nazwę tabeli w której będą odtworzone indeksy
(zgodnie z ustawieniami)'
Print char(13)+'exec sp_RebuildSavedIndexes Authors'
Print char(13)+'#################################################'
Return
End
Declare @objectid Int,
@indid smallint,
@groupid smallint,
@groupname sysname,
@status Int,
@keys nvarchar(2078),
@objname nvarchar(776),
@description nvarchar(210),
@misc nvarchar(210),
@index_name varchar(70),
@createindexsql varchar(512),
@errorstr varchar(105)
Set NOCOUNT On
set @createindexsql = ''
set @errorstr = ''
--Czy tabela istnieje w liście indexów?
if exists (select 1 from tbl_IndexList where tablename = @table)
BEGIN
--Tak, to przeczytaj indeksy
Declare create_object_cursor insensitive cursor
For Select tablename, indexdescription, index_name, index_keys, groupname, misc
From tbl_IndexList
where tablename = @table
Open create_object_cursor
fetch Next from create_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
While @@fetch_status = 0
Begin
-- Zbuduj polecenie do tworzenia indeksów
Select @createindexsql = 'Create ' +
@description +
' index ' +
@index_name +
' on dbo.' +
@objname +
'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
Select @createindexsql
Begin
--Select @createindexsql - tu można dodatkowo wyświetlić indeks
--Utwórz indeks
exec (@createindexsql)
End
fetch Next from create_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
End
Close create_object_cursor
deallocate create_object_cursor
-- usuń bieżącą informację o indeksach (trigger przeniesie ją do tabeli
-- historycznej
delete from tbl_indexlist where TableName = @table
END
If @createindexsql = ''
Begin
Set @errorstr = 'Błąd - nie ma zapisanych indeksów dla tabeli: ' + @table + '.'
Select @errorstr
End
Set NOCOUNT OFF
return (@@error)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc sp_SavenDropIndexes (@table varchar(55), @Clustereduq varchar(1) = 'N',
@whenDescrip varchar(50) = 'Before Import')
as
/******************************
Procedura wywołuje sp_LogIndexes do zapisania informacji o aktualnych indeksach.
Następnie tworzy dynamiczny sql z poleceniem drop
(i go wykonuje). Opiera się na kodzie Eddy Djaja, Publix Super Markets, Inc. z
12/07/1999
Phillip D. Snipes
7/25/02
Dale Butcher
7/21/02
*******************************/
/*********************
Wyświetl pomoc, gdy nie zostały podane parametry
**********************/
if @table = ''
Begin
Print '#################################################'
Print char(13)+'@table - nazwa tabeli, z której usuwane są indeksy.'
Print char(13)+'@Clustereduq - Y/N, czy należy usuwać unikalne indeksy klastrowe'
Print char(13)+'@whenDescrip - akceptuje do 50 znaków opisu (np - dlaczego'
Print char(13)+'indeks jest usuwany)'
Print char(13)+'Przykład: '
Print char(13)+'exec sp_SavenDropIndexes ''Authors'', ''Y'', ''Before Rebuild'''
Print char(13)+'#################################################'
Return
End
Declare @objectid Int,
@indid smallint,
@groupid smallint,
@groupname sysname,
@status Int,
@keys nvarchar(2078),
@objname nvarchar(776),
@description nvarchar(210),
@misc nvarchar(210),
@index_name varchar(70),
@dropindexsql varchar(512)
set @dropindexsql = ''
--Zapisz indeksy
Exec sp_LogIndexes @table, @Clustereduq, @whenDescrip
--Jeżeli choć jeden został zapisany
if exists (select 1 from tbl_IndexList where tablename = @table)
begin
--Kursor przechodzi po kolejnych indeksach
Declare drop_object_cursor insensitive cursor
For Select tablename, indexdescription, index_name, index_keys, groupname, misc
From tbl_IndexList
where tablename = @table
Open drop_object_cursor
fetch Next from drop_object_cursor into @objname, @description,
@index_name, @keys, @groupname, @misc
While @@fetch_status = 0
Begin
--Polecenie usunięcia indeksu
Select @dropindexsql = 'drop index ' + @objname + '.' + @index_name
Begin
--Select @dropindexsql - można dodatkowo wyświetlić
exec (@dropindexsql)
End
fetch Next from drop_object_cursor into @objname, @description,
@index_name, @keys, @groupname, @misc
End
Close drop_object_cursor
deallocate drop_object_cursor
End
return (@@error)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure sp_SearchIndexHist (@table varchar(55) = null, @backcount int
= '1', @backperiod varchar(1) = 'D')
as
/******************************
Procedura służy do wyszukiwania informacji w tabeli z "historycznymi" indeksami
(tbl_IndexListHistory),
w zadanym okresie czasu.
Phillip D. Snipes
7/29/02
*******************************/
/*********************
Wyświetl instrukcje, gdy nie ma przekazanego parametru
**********************/
if @table = null
Begin
Print '#################################################'
Print char(13)+'@table - nazwa tabeli do wyszukania tbl_IndexListHistory table.'
Print char(13)+'@backcount - liczba analizowanych okresów "wstecz"'
Print char(13)+'@backperiod - kod okresu - akceptuje D (dni), W (tygodnie), M'
Print char(13)+'(miesiące), Y (lata); domyślnie D'
Print char(13)+'Przykład: Aby przeszukać historię indeksów i znaleźć te, które'
Print char(13)+'były utworzone na tabeli Authors w ciągu ostatnich 2 tygodnim,'
Print char(13)+'należy:'
Print char(13)+'exec sp_SearchIndexHist ''Authors'', 2, ''W'''
Print char(13)+'#################################################'
Return
End
Declare @Now datetime
Declare @BeginDate datetime
Declare @dspbgdate varchar(12)
Declare @Rows int
Declare @Output1 varchar(125)
Declare @Output2 varchar(125)
Set @Now = GetDate()
--Ustalenie daty początkowej
If @backperiod = 'D'
Begin
Set @BeginDate = (@Now - @backcount )
End
If @backperiod = 'W'
Begin
Set @backcount = @backcount * 7
Set @BeginDate = (@Now - @backcount )
End
If @backperiod = 'M'
Begin
Set @backcount = @backcount * 30
Set @BeginDate = (@Now - @backcount )
End
If @backperiod = 'Y'
Begin
Set @backcount = @backcount * 365
Set @BeginDate = (@Now - @backcount )
End
Set @dspbgdate = CONVERT(varchar, @BeginDate, 100)
Set @Output1 = 'Wyniki znajdujące sie w tbl_IndexListHistory dla ' + @table + '
pozycje od ' + @dspbgdate + ' do przodu. Najnowsze na początku.'
Set NOCOUNT On
Select @Output1
Set @Rows = (Select Count(*) from tbl_IndexListHistory where TableName = @table and
SnapshotTime >= @BeginDate)
If @Rows = 0
Begin
Set @Output2 = 'Nie ma żadnych wyników.'
Select @Output2
End
Set NOCOUNT Off
If @Rows > 0
Begin
Select TableName, index_name as IndexName, index_keys as Fields, groupname as
FileGroup, WhenDescription, CONVERT(varchar, SnapshotTime, 101) as BeginDate,
SnapshotTime as ExactTime
From tbl_IndexListHistory
Where TableName = @table and SnapshotTime >= @BeginDate
Order By SnapShotTime Desc
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure sp_compare_IndexHist_to_existing (@table varchar(55) = null,
@HistDate datetime, @WhenDescrip varchar(50) = 'Before Import',
@OSql varchar(1) = 'N' )
as
set nocount on
/******************************
Jest to część kolekcji 5 porcedur przechowywanych przeznaczonych do manipulacji
indexami (Smart Index Manipulation System).
Procedura sp_compare_IndexHist_to_existing porównuje aktualnie założone indeksy z
tymi, które znajdują się w tbl_IndexListHistory (wypełnianej np. przez trigger na sp_LogIndexes). Przydatna do analizy zmian indeksów w czasie.
Phillip D. Snipes
7/29/02
Zmiany (głównie w obsłudze daty,komentarzach i sposobie porównywania):
redakcja www.mssqlserver.org.pl
*******************************/
--Wyświetl instrukcję użycia, gdy nie podano kryteriów badania. Tu wystarczy
--sprawdzenie czy przekazano coś w @HistDate
if @HistDate = null
Begin
Print '#################################################'
Print char(13)+'Porównywanie bieżących indeksów z informacjami historycznymi'
Print char(13)+'@table - nazwa tabeli której indeksy są porównywane z historią ' Print char(13)+'tbl_IndexListHistory' Print char(13)+'@HistDate - określa, z którego dnia mają być brane indeksy do ' Print char(13)+'porównywania. Format daty zalezy od ustawień połączenia; ' Print char(13)+'powinien zawsze działać ''rrrrmmdd'' (parametr opcjonalny)' Print char(13)+'@WhenDescrip - akceptuje do 50 znaków; określa opis danego ' Print char(13)+'"zestawu" indeksów, z którym ma być porównywany stan bieżący' Print char(13)+'(parametr opcjonalny; trzeba dodać warunki w kodzie, by ten' Print char(13)+'warunek był uwzględniany)' Print char(13)+'@OSql - akceptuje Y/N; określa czy wyprowadzać SQL, potrzebny ' Print char(13)+'do odtworzenia indeksów zgodnie z tabelą historyczną (parametr' Print char(13)+'opcjonalny)' Print char(13)+'Przykład: Aby porównać indeksy na tabeli Authors z 17/7/2002, ' Print char(13)+'opisane jako During the Import i wyprowadzić polecenia SQL,' Print char(13)+'nalezy wykonać:' Print char(13)+'exec sp_compare_IndexHist_to_existing ''Authors'', ' Print char(13)+'''20020717'',''During the Import'', ''Y''' Print char(13)+'#################################################' Return
End
Declare @OutputE varchar(255)
Declare @Existing int
Declare @OutputB varchar(255)
Declare @Before int
Declare @Now datetime
Declare @Now1 int
Declare @OutputN varchar(255)
declare @empty varchar(1)
select @empty = ''
Set @Now = (Getdate())
declare @des1 varchar(35), -- 35 pasujących spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35),
@objectid Int,
@indid smallint,
@groupid smallint,
@groupname sysname,
@status Int,
@keys nvarchar(2078),
@objname nvarchar(776),
@description nvarchar(210),
@misc nvarchar(210),
@index_name varchar(70),
@table_there1 char(5),
@table_there2 char(5),
@i Int,
@thiskey sysname,
@sql varchar(100)
CREATE TABLE #ExistingIndexes (
[TableName] [varchar] (255) NULL ,
[index_name] [sysname] NULL ,
[stats] [int] NULL ,
[groupname] [sysname] NULL ,
[index_keys] [nvarchar] (2078) NULL ,
[indid] [smallint] NULL ,
[indexdescription] [nvarchar] (210) NULL ,
[misc] [varchar] (50) NULL ,
[WhenDescription] [varchar] (50) NULL ,
[SnapshotTime] [datetime] NOT NULL
)
Begin
/* Znajdź "wartość" indeksu ( klastrowy, unikalny itp. ) */
Select @des1 = Name from master.dbo.spt_values where Type = 'I' and number = 1
Select @des2 = Name from master.dbo.spt_values where Type = 'I' and number = 2
Select @des4 = Name from master.dbo.spt_values where Type = 'I' and number = 4
Select @des32 = Name from master.dbo.spt_values where Type = 'I' and number = 32
Select @des64 = Name from master.dbo.spt_values where Type = 'I' and number = 64
Select @des2048 = Name from master.dbo.spt_values where Type = 'I' and
number = 2048
Select @des4096 = Name from master.dbo.spt_values where Type = 'I' and
number = 4096
Select @des8388608 = Name from master.dbo.spt_values where Type = 'I' and
number = 8388608
Select @des16777216 = Name from master.dbo.spt_values where Type = 'I' and
number = 16777216
/* Znajdź tabelę */
Declare object_cursor CURSOR For
Select id
from sysobjects
where name = @table
Open object_cursor
FETCH Next FROM object_cursor INTO @objectid
/* Znajdź indeksy */
While @@FETCH_STATUS = 0
Begin
Declare index_cursor insensitive cursor For
Select indid, groupid, Name, status from sysindexes
where id = @objectid And indid > 0 And indid < 255
order by indid
Open index_cursor
FETCH index_cursor into @indid, @groupid, @index_name, @status
/* Dla każdego indeksu sprawdź jesgo elementy i zapisz informacje */
While @@fetch_status >= 0
Begin
Select @objname = object_name(@objectid)
/* Najpierw klucze*/
Select @keys = index_col(@objname, @indid, 1), @i = 2, @thiskey =
index_col(@objname, @indid, 2)
While (@thiskey Is Not Null )
Begin
Select @keys = @keys + ', ' + @thiskey, @i = @i + 1
Select @thiskey = index_col(@objname, @indid, @i)
End
Select @groupname = groupname from sysfilegroups where groupid = @groupid
Select @description = convert(varchar(210),
--bit 16 zgaszony, 1, 2, 16777216 zapalony, grupa
+ Case when (@status & 4)<>0 Then ' '+@des4 else @empty end
+ Case when (@status & 64)<>0 Then ' '+@des64 else
case when (@status & 32)<>0 then ' '+@des32 else @empty end
end
+ Case when (@status & 2048)<>0 Then ' '+@des2048 else @empty
end
+ Case when (@status & 4096)<>0 Then ' '+@des4096 else @empty
end
+ Case when (@status & 8388608)<>0 Then ' '+@des8388608 else
@empty end
+ Case when (@status & 16777216)<>0 Then ' '+@des16777216 else
@empty end
+ Case when (@status & 16)<>0 Then ' clustered' else '
nonclustered' end)
Select @misc = convert(varchar(210),
Case when (@status & 1)<>0 Then ' '+@des1 else @empty end)
Select @description = RTrim(LTrim(@description))
/* Wstaw wiersz dla tego indeksu do tabeli tymczasowej*/
If RTrim(@description) = 'nonclustered' OR rtrim(@description) =
'unique nonclustered' OR rtrim(@description) = 'unique clustered'
OR rtrim(@description) = 'clustered'
Begin
insert into #ExistingIndexes (tablename, index_name, stats, groupname,
index_keys, indid, indexdescription, whendescription, snapshottime)
values (@objname, @index_name, @status, @groupname, @keys, @indid,
@description, @WhenDescrip, @Now)
If @misc <> ''
Begin
update #ExistingIndexes
Set misc = 'WITH IGNORE_DUP_KEY'
WHERE index_name = @index_name
End
End
/* Następny indeks*/
fetch index_cursor into @indid, @groupid, @index_name, @status
End
deallocate index_cursor
FETCH Next FROM object_cursor INTO @objectid
End
DEALLOCATE object_cursor
End
--Tymczasowa tabela z różnicami (te indeksy, które są nowo utworzone)
CREATE TABLE #ListA (
[TableName] [varchar] (255) NULL ,
[index_name] [sysname] NULL ,
[stats] [int] NULL ,
[groupname] [sysname] NULL ,
[index_keys] [nvarchar] (2078) NULL ,
[indid] [smallint] NULL ,
[indexdescription] [nvarchar] (210) NULL ,
[misc] [varchar] (50) NULL ,
[WhenDescription] [varchar] (50) NULL ,
[SnapshotTime] [datetime] NOT NULL
)
insert into #ListA
SELECT DISTINCT #ExistingIndexes.TableName, #ExistingIndexes.Index_Name,
#ExistingIndexes.stats, #ExistingIndexes.groupname,
#ExistingIndexes.index_keys,
#ExistingIndexes.indid,
#ExistingIndexes.indexdescription,
#ExistingIndexes.misc,
#ExistingIndexes.WhenDescription,
#ExistingIndexes.SnapShotTime
FROM #ExistingIndexes where TableName=@table and not exists (
select * from tbl_IndexListHistory where
Index_Name=#ExistingIndexes.Index_Name and TableName=@table
--AND (#ExistingIndexes.IndexDescription = IndexDescription) - to można też dodać,
--wtedy porównywane będą opisy indeksów (np zamiast nazw)
And DAY( tbl_IndexListHistory.Snapshottime) = DAY(@HistDate) and
MONTH( tbl_IndexListHistory.Snapshottime) = MONTH(@HistDate) and
YEAR( tbl_IndexListHistory.Snapshottime) = YEAR(@HistDate)
-- AND #ExistingIndexes.WhenDescription=@WhenDescrip *
)
Order By #ExistingIndexes.TableName
--Tymczasowa tabela z różnicami (te indeksy, które już nie istnieją)
CREATE TABLE #ListB (
[TableName] [varchar] (255) NULL ,
[index_name] [sysname] NULL ,
[stats] [int] NULL ,
[groupname] [sysname] NULL ,
[index_keys] [nvarchar] (2078) NULL ,
[indid] [smallint] NULL ,
[indexdescription] [nvarchar] (210) NULL ,
[misc] [varchar] (50) NULL ,
[WhenDescription] [varchar] (50) NULL ,
[SnapshotTime] [datetime] NOT NULL
)
insert into #ListB
SELECT DISTINCT tbl_IndexListHistory.TableName, tbl_IndexListHistory.Index_Name,
tbl_IndexListHistory.stats, tbl_IndexListHistory.groupname,
tbl_IndexListHistory.index_keys, tbl_IndexListHistory.indid,
tbl_IndexListHistory.IndexDescription,
tbl_IndexListHistory.misc,
tbl_IndexListHistory.WhenDescription,
tbl_IndexListHistory.SnapshotTime
FROM tbl_IndexListHistory where TableName=@table and
not exists (
select * from #ExistingIndexes where
Index_Name=tbl_IndexListHistory.Index_Name and TableName=@table
--AND (tbl_IndexListHistory.IndexDescription = IndexDescription) - to można też
--dodać, wtedy porównywane będą opisy
And DAY( tbl_IndexListHistory.Snapshottime) = DAY(@HistDate) and MONTH(
tbl_IndexListHistory.Snapshottime) = MONTH(@HistDate) and YEAR(
tbl_IndexListHistory.Snapshottime) = YEAR(@HistDate)
-- AND tbl_IndexListHistory.WhenDescription=@WhenDescrip *
)
Order By tbl_IndexListHistory.TableName
--Wyprowadzenie wyników
Select @Existing = (SELECT COUNT(*) FROM #ExistingIndexes)
IF @Existing > 0
BEGIN
Set @OutputE = 'Następujące indeksy obecnie istnieją na ' + @table + '.'
Select @OutputE
Select tablename, index_name, indexdescription, index_keys, indid, stats,
groupname, misc, WhenDescription, SnapShotTime from #ExistingIndexes Order By
IndexDescription
If @OSql = 'Y'
BEGIN
Set @OutputE = ''
Set @OutputE = 'Poniższe polecenie SQL usunie aktualnie istniejące indeksy z ' +
@table + '.'
Select @OutputE
Declare @dropindexsql varchar(512)
set @dropindexsql = ''
Declare drop_object_cursor insensitive cursor
For Select tablename, indexdescription, index_name, index_keys, groupname, misc
From #ExistingIndexes
where tablename = @table
Open drop_object_cursor
fetch Next from drop_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
While @@fetch_status = 0
Begin
Select @dropindexsql = 'drop index ' + @objname + '.' + @index_name
Begin
Select @dropindexsql
--exec (@dropindexsql) - to można dodać, jeżeli polecenie ma być od razu
--wykonane
End
fetch Next from drop_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
End
Close drop_object_cursor
deallocate drop_object_cursor
End
END
ELSE
BEGIN
Set @OutputE = 'Obecnie nie ma żadnego indeksu na ' + @table + '.'
Select @OutputE
END
Select @Before = (SELECT COUNT(*) FROM #ListA)
IF @Before > 0
BEGIN
Set @OutputB = 'Poniżej znajduje się lista nowo utworzonych indeksów na ' + @table
Select @OutputB
Select Distinct tablename, index_name, indexdescription, index_keys, indid, stats,
groupname, misc, WhenDescription from #ListA
If @OSql = 'Y'
BEGIN
Set @OutputN = ''
Set @OutputN = 'Poniższe polecenia sql utworzy ponownie te indeksy na ' + @table
+ '.'
Select @OutputN
Declare @createindexsqlA varchar(512)
Declare create_object_cursorA insensitive cursor
For Select Distinct tablename, indexdescription, index_name, index_keys,
groupname, misc
From #ListA
where tablename = @table
Open create_object_cursorA
fetch Next from create_object_cursorA into @objname, @description, @index_name,
@keys, @groupname, @misc
While @@fetch_status = 0
Begin
Select @createindexsqlA = 'Create ' +
@description +
' index ' +
@index_name +
' on dbo.' +
@objname +
'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
Begin
Select @createindexsqlA
--exec (@createindexsql) - ew. można od razu je wykonać
End
fetch Next from create_object_cursorA into @objname, @description, @index_name,
@keys, @groupname, @misc
End
Close create_object_cursorA
deallocate create_object_cursorA
END
END
Select @Now1 = (SELECT COUNT(*) FROM #ListB)
IF @Now1 > 0
BEGIN
Set @OutputN = 'Poniższa lista indeksów na tabeli ' + @table + ' jest nowo
utworzona'
Select @OutputN
Select Distinct tablename, index_name, indexdescription, index_keys, indid, stats,
groupname, misc, WhenDescription from #ListB
If @OSql = 'Y'
BEGIN
Set @OutputN = ''
Set @OutputN = 'Polecenia SQL do utworzenia tych indeksów na ' + @table + ':'
Select @OutputN
Declare @createindexsql varchar(512)
Declare create_object_cursor insensitive cursor
For Select Distinct tablename, indexdescription, index_name, index_keys,
groupname, misc
From #ListB
where tablename = @table
Open create_object_cursor
fetch Next from create_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
While @@fetch_status = 0
Begin
Select @createindexsql = 'Create ' +
@description +
' index ' +
@index_name +
' on dbo.' +
@objname +
'(' + @keys + ')' +isnull( @misc,'') + ' on [' + @groupname + ']'
--Select @createindexsql
Begin
Select @createindexsql
--exec (@createindexsql)
End
fetch Next from create_object_cursor into @objname, @description, @index_name,
@keys, @groupname, @misc
End
Close create_object_cursor
deallocate create_object_cursor
END
END
IF @Before = 0 And @Now1 = 0
BEGIN
Set @OutputN = ''
Set @OutputN = 'Indeksy na ' + @table + ' i te, zapisane w tabeli historycznej są
identyczne.'
Select @OutputN
END
Drop Table #ExistingIndexes
Drop Table #ListA
Drop Table #ListB
Set NOCOUNT Off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Wyświetlenie informacji o tabelach, w których istnieje dana kolumna
Wyświetlenie informacji o tabelach, w których istnieje dana kolumna.
Projektując bazę danych kolumny połączone relacjami nazywane są zwykle w podobny sposób. Dzięki widokom INFORMATION_SCHEMA można bez trudu odczytać tą informację, i utworzyć uniwersalną procedurę przechowywaną która zwraca listę tabel i pewne (wybrane) cechy poszczególnych kolumn. Jako parametr należy przekazać nazwę poszukiwanej kolumny (można w nazwie stosować pełną składnie LIKE).
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(
N'[dbo].[GetTablesForColumn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetTablesForColumn]
GO
CREATE PROCEDURE dbo. GetTablesForColumn(
@ColumnName nvarchar(100))
AS
declare @tmp varchar(1000)
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,IS_NULLABLE,
CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.columns
where COLUMN_NAME like rtrim(ltrim(@ColumnName))
GROUP BY COLUMN_NAME,TABLE_NAME,COLUMN_DEFAULT,IS_NULLABLE,
CHARACTER_MAXIMUM_LENGTH,DATA_TYPE ORDER BY COLUMN_NAME,TABLE_NAME,
COLUMN_DEFAULT,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH,DATA_TYPE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
W przypadku bazy pubs, można wydać polecenie:
EXEC [GetTablesForColumn] 'job%'
133
Wyszukiwarka
Podobne podstrony:
7496
7496
7496
7496
praca-magisterska-wa-c-7496, Dokumenty(2)
7496
7496
7496
7496
praca magisterska 7496
7496
08 Recepty i lekiid 7496 pptx
więcej podobnych podstron