14 różnic między standardowym SQL a Transact-SQL - DZone Database

W moim ostatnim artykule z grubsza opisałem, czym różni się standardowy SQL od T-SQL i kto powinien uczyć się którego. Teraz chciałbym się skupić na różnicach w składni i zilustrować te różnice przykładami. Jeśli myślisz, że T-SQL jest rozszerzeniem implementującym wszystkie funkcje ze standardowego SQL, to nie masz racji. Jednak w SQL Serverze znajdziesz prawie wszystkie cechy standardu SQL. W tym artykule znajdziesz przykłady niektórych różnic w składni między standardowym SQL a Transact-SQL.

#1 Nazwy obiektów bazy danych

W systemach relacyjnych baz danych nadajemy nazwy tabelom, widokom i kolumnom, ale czasami musimy użyć tej samej nazwy jako słowa kluczowego lub użyć znaków specjalnych. W standardowym SQL tego typu nazwy można umieszczać w cudzysłowach („”), ale w T-SQL można je również umieszczać w nawiasach (). Spójrz na te przykłady dla nazwy tabeli w T-SQL:

CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100));CREATE TABLE dbo.test. ( Id INT, Name VARCHAR(100));

Tylko pierwszy delimiter (cudzysłów) dla nazwy specjalnej jest również częścią standardu SQL.

What Is Different in a SELECT Statement?

Standard SQL nie posiada składni dla zapytania zwracającego wartości lub wartości pochodzące z wyrażeń bez odwoływania się do jakichkolwiek kolumn tabeli, ale MS SQL Server pozwala na tego typu wyrażenia. W jaki sposób? Można użyć samej instrukcji SELECT z wyrażeniem lub z innymi wartościami nie pochodzącymi z kolumn tabeli. W T-SQL wygląda to tak jak w poniższym przykładzie:

SELECT 12/6 ;

W tym wyrażeniu nie potrzebujemy tabeli do obliczenia 12 podzielnej przez 6, dlatego można pominąć instrukcję FROM oraz nazwę tabeli.

#3 Ograniczanie liczby rekordów w zbiorze wyników

W standardzie SQL można ograniczyć liczbę rekordów w wynikach, stosując składnię zilustrowaną poniżej:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL implementuje tę składnię w inny sposób. Poniższy przykład przedstawia składnię MS SQL Server:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

Jak można zauważyć, używana jest tu klauzula ORDER BY. Innym sposobem wyboru wierszy, ale bez ORDER BY, jest użycie klauzuli TOP w języku T-SQL:

SELECT TOP 10 * FROM tab;

#4 Automatyczne generowanie wartości

Standard SQL umożliwia tworzenie kolumn z automatycznie generowanymi wartościami. Składnia pozwalająca to zrobić jest przedstawiona poniżej:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

W języku T-SQL również możemy automatycznie generować wartości, ale w taki sposób:

CREATE TABLE tab (id INTEGER IDENTITY);

#5 Funkcje matematyczne

Kilka popularnych funkcji matematycznych jest częścią standardu SQL. Jedną z tych funkcji matematycznych jest CEIL(x), której nie znajdziemy w T-SQL. Zamiast tego, T-SQL udostępnia następujące niestandardowe funkcje: SIGN(x), ROUND(x,) do zaokrąglania wartości dziesiętnej x do podanej liczby pozycji po przecinku, TRUNC(x) do obcinania do podanej liczby miejsc po przecinku, LOG(x) do zwracania logarytmu naturalnego dla wartości x, oraz RANDOM() do generowania liczb losowych. Najwyższa lub najniższa liczba na liście w standardzie SQL jest zwracana przez funkcje MAX(lista) i MIN(lista), ale w Transact-SQL używamy funkcji GREATEST(lista) i LEAST(lista).

T-SQL function ROUND:SELECT ROUND(col) FROM tab;

#6 Funkcje agregujące

Znajdujemy kolejną różnicę w składni funkcji agregujących. Funkcje COUNT, SUM i AVG wszystkie przyjmują argument związany z liczbą. T-SQL pozwala na użycie DISTINCT przed tymi wartościami argumentów, dzięki czemu wiersze są liczone tylko wtedy, gdy ich wartości są różne od innych wierszy. Standard SQL nie pozwala na użycie DISTINCT w tych funkcjach.

Standard SQL:SELECT COUNT(col) FROM tab;T-SQL:SELECT COUNT(col) FROM tab;SELECT COUNT(DISTINCT col) FROM tab;

Ale w T-SQL nie znajdziemy funkcji kowariancji populacji: COVAR_POP(x,y), która jest zdefiniowana w standardzie SQL.

#7 Retrieving Parts of Dates and Times

Większość systemów relacyjnych baz danych dostarcza wiele funkcji do operowania na datach i czasach.

W standardowym SQL, funkcja EXTRACT(YEAR FROM x) i podobne funkcje do wybierania części dat różnią się od funkcji T-SQL takich jak YEAR(x) lub DATEPART(year, x).

Istnieje również różnica w uzyskiwaniu bieżącej daty i czasu. Standardowy SQL pozwala na uzyskanie bieżącej daty za pomocą funkcji CURRENT_DATE, jednak w MS SQL Server nie ma podobnej funkcji, dlatego musimy użyć funkcji GETDATE jako argumentu w funkcji CAST, aby przekonwertować na typ danych DATE.

#8 Operowanie na łańcuchach

Używanie funkcji do operowania na łańcuchach również różni się między standardowym SQL a T-SQL. Główna różnica występuje w usuwaniu spacji końcowych i końcowych z łańcucha znaków. W standardowym SQL-u jest to funkcja TRIM, natomiast w T-SQL-u jest kilka funkcji pokrewnych: TRIM (usuwającą spacje ciągnące i wiodące), LTRIM (usuwającą spacje wiodące) oraz RTRIM (usuwającą spacje ciągnące).

Inną bardzo często używaną funkcją łańcuchową jest SUBSTRING.

Standardowa składnia SQL dla funkcji SUBSTRING wygląda jak:

SUBSTRING(str FROM start )

ale w T-SQL składnia tej funkcji wygląda jak:

SUBSTRING(str, start, length)

Czasami istnieją powody, aby dodać wartości pochodzące z innych kolumn i/lub dodatkowych łańcuchów. Standardowy SQL umożliwia to za pomocą następującej składni:

Jak widać, składnia ta wykorzystuje operator || do dodawania jednego łańcucha do drugiego.

Ale równoważnym operatorem w T-SQL jest znak plusa. Spójrz na ten przykład:

SELECT col1 + col2 FROM tab;

W SQL Server mamy również możliwość użycia funkcji CONCAT konkatenującej listę ciągów znaków:

SELECT CONCAT(col1, str1, col2, ...) FROM tab;

Możemy również powtórzyć jeden znak kilka razy. Standardowy SQL definiuje do tego celu funkcję REPEAT(str, n). Transact-SQL udostępnia funkcję REPLICATE. Na przykład:

SELECT REPLICATE(str, x);

gdzie x określa, ile razy powtórzyć łańcuch lub znak.

#9 Operator nierówności

Podczas filtrowania rekordów w instrukcji SELECT czasami musimy użyć operatora nierówności. Standardowy SQL definiuje <> jako ten operator, natomiast T-SQL pozwala na użycie zarówno operatora standardowego, jak i operatora !=:

SELECT col3 FROM tab WHERE col1 != col2;

#10 Funkcja ISNULL

W T-SQL mamy możliwość zastępowania wartości NULL pochodzących z kolumny za pomocą funkcji ISNULL. Jest to funkcja specyficzna dla języka T-SQL i nie występuje w standardzie SQL.

SELECT ISNULL(col1) FROM tab;

Które części składni DML są inne?

W języku T-SQL podstawowa składnia zapytań DELETE, UPDATE i INSERT jest taka sama jak w standardzie SQL, ale różnice pojawiają się w bardziej zaawansowanych zapytaniach. Przyjrzyjmy się im.

#11 OUTPUT Keyword

Słowo kluczowe OUTPUT występuje w instrukcjach DELETE, UPDATE i INSERT. Nie jest zdefiniowane w standardowym SQL.

Używając T-SQL, możemy zobaczyć dodatkowe informacje zwracane przez zapytanie. Zwraca zarówno stare jak i nowe wartości w UPDATE lub wartości dodane przy użyciu INSERT lub usunięte przy użyciu DELETE. Aby zobaczyć te informacje, musimy użyć prefiksów w INSERT, UPDATE i DELETE.

UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col;

Widzimy wynik zmiany rekordów z poprzednimi i nowymi wartościami w zaktualizowanej kolumnie. Standard SQL nie obsługuje tej funkcji.

#12 Składnia dla INSERT INTO … SELECT

Inną strukturą zapytania INSERT jest INSERT INTO … SELECT. T-SQL pozwala na wstawianie danych z innej tabeli do tabeli docelowej. Spójrz na to zapytanie:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

Nie jest to standardowa funkcja, ale cecha charakterystyczna dla SQL Server.

#13 Klauzula FROM w DELETE i UPDATE

SQL Server udostępnia rozszerzoną składnię klauzul UPDATE i DELETE with FROM. Możesz użyć DELETE with FROM aby użyć wierszy z jednej tabeli do usunięcia odpowiadających im wierszy z innej tabeli poprzez odwołanie się do klucza głównego i obcego. Podobnie, możesz użyć UPDATE with FROM do aktualizacji wierszy z jednej tabeli poprzez odwołanie się do wierszy innej tabeli przy użyciu wspólnych wartości (klucz główny w jednej tabeli i klucz obcy w drugiej, np. ta sama nazwa miasta). Oto przykład:

DELETE FROM BookFROM AuthorWHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE BookSET Book.Price=Book.Price*0.2FROM AuthorWHERE Book.AuthorId=Author.Id AND Author.Id=12;

Standard SQL nie przewiduje takiej składni.

#14 INSERT, UPDATE, and DELETE With JOIN

Możesz również użyć INSERT, UPDATE, and DELETE używając JOIN, aby połączyć się z inną tabelą. Przykładem tego jest:

DELETE ItemOrder FROM ItemOrderJOIN Item ON ItemOrder.ItemId=Item.IdWHERE YEAR(Item.DeliveredDate) <= 2017;

Tej funkcji nie ma w standardzie SQL.

Podsumowanie

Ten artykuł nie obejmuje wszystkich zagadnień dotyczących różnic w składni pomiędzy standardem SQL a T-SQL wykorzystującym system MS SQL Server. Przewodnik ten pozwala jednak wskazać pewne podstawowe cechy charakterystyczne tylko dla Transact-SQL oraz to, jaka składnia standardu SQL nie jest implementowana przez MS SQL Server.