SQL Server

Na tegorocznej konferencji Microsoft Technology Summit (MTS) przedstawiłem wraz z Markiem Adamczukiem prezentację pt. "Najlepsze praktyki dla programisty Transact-SQL". Po tej sesji sporo osób prosiło mnie o materiały, a głównie demonstracje. Dobra informacja jest taka, że materiały te można już pobrać ze strony konferencji. Najwięcej dyskusji po prezentacji dotyczyło fragmentu poświęconego collation i pracy z danymi tekstowymi pochodzącymi z różnych języków. Dlatego, po rozmowie z Markiem, postanowiłem napisać niniejszy artykuł, w którym omówię wybrane najlepsze praktyki posługując się kodem zaprezentowanym na MTS.

Nvarchar – N ma znaczenie

Typy danych nchar i nvarchar umożliwiają przechowywanie w bazie danych tekstów zawierających znaki charakterystyczne dla różnych języków. Przykładowo, mamy bazę w collation "rosyjskim", a w jednej z kolumn w tabeli w tej bazie chcemy przechować tekst w języku polskim. Wówczas typ danych nvarchar doskonale nadaje się do przechowywania danych (co ciekawe, kolumna tego typu ma nadal przypisane collation "rosyjskie"). Co jest jednak istotne w takim przypadku? Ważne jest, by przy używaniu literałów tekstowych dawać systemowi SQL Server znać, że podany tekst ma być traktowany jako Unicode. Do tego służy prefiks N umieszczany bezpośrednio przed literałem (wielkość litery ma znaczenie). Poniższy przykład dobrze obrazuje tę zasadę. Bez prefiksu N polskie znaki w napisie, który otrzymuje collation bazy danych, po prostu znikają (w niektórych przypadkach zamiast znaków charakterystycznych dla różnych alfabetów pojawiają się znaki zapytania lub inne "krzaczki"). Po dodaniu prefiksu N polskie znaki są wstawiane w kolumnę typu nvarchar bez problemu, mimo że collation bazy i kolumny jest "rosyjskie".

--Tworzymy "rosyjską" bazę
USE master;
GO
IF DB_ID('RussianDB') IS NOT NULL BEGIN
  ALTER DATABASE RussianDB SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE;
  DROP DATABASE RussianDB;
END;
GO
CREATE DATABASE RussianDB COLLATE Cyrillic_General_CI_AS;
GO
 
USE RussianDB;
GO
 
--Tabela z kolumną nvarchar
CREATE TABLE dbo.Tab (c nvarchar(20) NULL);
GO
 
--Wstawiamy dane
INSERT INTO dbo.Tab (c) SELECT 'ąęśćłóżźń';
GO
 
--Test
SELECT * FROM dbo.Tab;
GO
 
--A teraz zróbmy to, jak trzeba
INSERT INTO dbo.Tab (c) SELECT N'ąęśćłóżźń';
GO
 
--Test
SELECT * FROM dbo.Tab;
GO

Długa czy krótka kolacja?

W SQL Server mamy bogactwo możliwych do wyboru collation. Zawsze, gdy tworzymy nową bazę danych, stajemy przed wyborem, jakie collation nadać tworzonej bazie? Pierwsza myśl – niech baza ma takie collation, jak bazy systemowe (czyli takie collation, jakie wybraliśmy przy instalacji SQL Servera). A jeśli ma mieć inne collation? Co wybierać – collation SQL-owe (prefiks SQL_ – zwane dalej "długim collation") czy raczej collation rodem z systemu Windows (np. Polish_CI_AS – zwane dalej "krótkim collation")? Istnieje kilka przesłanek przemawiających na korzyść collation "Windowsowych". Pierwsza przesłanka – niejawne rzutowania w przypadku, gdy do kolumny o "długim collation" spróbujemy wstawić dane typu nvarchar. Wówczas niejawna konwersja kolumny na nvarchar spowoduje niemożliwość wykorzystania ewentualnego indeksu (i odbędzie się skanowanie tabeli). W przypadku kolumny z "krótkim collation" problem znika – dane Unicode są rzutowane do typu varchar i indeks może zostać wykorzystany.

--Tabela z "długim" collation
CREATE TABLE #t1 (
  c1 varchar(128)
    COLLATE SQL_Polish_CP1250_CS_AS NOT NULL
  );
CREATE CLUSTERED INDEX IX_1 ON #t1 (c1);
GO
 
--Tabela z "krótkim" collation
CREATE TABLE #t2 (
   c2 varchar(128) COLLATE Polish_CI_AS NOT NULL
   );
CREATE CLUSTERED INDEX IX_2 ON #t2 (c2);
GO
 
--Dane
INSERT INTO #t1 (c1)
SELECT name FROM sys.all_objects;
GO
INSERT INTO #t2 (c2)
SELECT name FROM sys.all_objects;
GO
 
--Test
SELECT * FROM #t1 WHERE c1 = N'objects';
SELECT * FROM #t2 WHERE c2 = N'objects';
GO

Innym zachowaniem "długich collation", które może być powodem do zaniechania ich wykorzystywania, jest sortowanie tekstów, w których pojawiają się myślniki. W zasadzie komentarz jest zbędny – wystarczy rzut oka na wyniki zapytania z sortowaniem, by stwierdzić, że wynik dla "długiego collation" jest dość nieoczekiwany.

--Zmieńmy zawartość
TRUNCATE TABLE #t1;
TRUNCATE TABLE #t2;
INSERT INTO #t1 (c1)
VALUES ('a'), ('a-'), ('a-a');
INSERT INTO #t2 (c2)
VALUES ('a'), ('a-'), ('a-a');
GO
 
--Problem sortowania i myślników
SELECT * FROM #t1 ORDER BY c1;
SELECT * FROM #t2 ORDER BY c2;
GO

Collation a tabele tymczasowe

Jeśli już zdecydujemy się na wybór dla bazy danych collation innego niż collation serwera, musimy liczyć się z tym, że nasza nowo utworzona baza będzie miała inne collation niż baza tempdb. Co za tym idzie? Gdy będziemy używali tabel tymczasowych w kodzie proceduralnym w naszej bazie, możemy napotkać na konflikty collation w sytuacjach, gdy będą wykonywane operacje porównywania czy sortowania tekstu.

USE RussianDB;
GO
 
IF OBJECT_ID('dbo.T11', 'U') IS NOT NULL
  DROP TABLE dbo.T11;
GO
CREATE TABLE dbo.T11 (
  c11 varchar(128) NOT NULL
);
GO
INSERT INTO dbo.T11 (c11)
VALUES ('MTS');
GO
 
IF OBJECT_ID('tempdb.dbo.#T11', 'U') IS NOT NULL
  DROP TABLE #T11;
GO
CREATE TABLE #T11 (
  c11 varchar(128) NOT NULL
);
GO
 
-- Test - konflikt collation
SELECT * FROM dbo.T11
UNION
SELECT * FROM #T11;
GO

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "Polish_CI_AS" and "Cyrillic_General_CI_AS" in the UNION operation.

Rozwiązanie połowiczne – użyć składni SELECT INTO do stworzenia tabeli tymczasowej jako kopii istniejącej tabeli. Wówczas collation kolumn tekstowych są identyczne z tymi z tabeli źródłowej.

IF OBJECT_ID('tempdb.dbo.#T11', 'U') IS NOT NULL
  DROP TABLE #T11;
GO
SELECT TOP 0 * INTO #T11 FROM dbo.T11;
GO
 
-- Test: ciut lepiej, bo działa
SELECT * FROM dbo.T11
UNION
SELECT * FROM #T11;
GO

Nie zawsze jednak mamy tabelę wzorcową, na której możemy oprzeć tabelę tymczasową. Dlatego najlepszym rozwiązaniem wydaje się być użycie klauzuli COLLATE DATABASE_DEFAULT. Wówczas kolumna tekstowa otrzymuje collation identyczne z tym, jakie zostało przypisane bazie danych, w kontekście której uruchamiamy kod.

IF OBJECT_ID('tempdb.dbo.#T11', 'U') IS NOT NULL
  DROP TABLE #T11;
GO
CREATE TABLE #T11 (
  c11 varchar(128)
  COLLATE DATABASE_DEFAULT
  NOT NULL
);
GO
 
-- Test: jest dobrze :)
SELECT * FROM dbo.T11
UNION
SELECT * FROM #T11;
GO

Podsumowanie

Powyższe przykłady ilustrują pewne wybrane przeze mnie i Marka problemy dotyczące danych tekstowych w SQL Server. Oczywiście, podobnych problemów jest więcej (np. jak zmienić collation istniejącej bazy?), ale wydawało nam się, że przedstawione dema pozwolą na ominięcie najczęściej występujących pułapek. No i druga sprawa – mieliśmy tylko 60 minut na prezentację :-) W tym miejscu pragnę podziękować wszystkim, którzy byli na naszej sesji na MTS i wypełnili ankietę dotyczącą naszej prezentacji. Jest mi też niezmiernie miło, że nasz przekaz spotkał się z bardzo pozytywnym odbiorem. Gdyby była potrzeba opisania w kolejnym artykule jakiegoś fragmentu kodu z naszych demonstracji, dajcie znać, a chętnie opublikuję kolejny fragment tekstu na blogu.