Hash czy małpa a sprawa collation
Wczoraj miałem przyjemność poprowadzić na PLSSUG Warszawa prezentację dotyczącą collation na SQL Serverze. W czasie prezentacji wygłosiłem pewną, nie do końca uprawnioną tezę. Mianowicie stwierdziłem, że collation z którym zakładane są domyślnie kolumny tabeli tymczasowej ustawia się na collation bazy bieżącej w SQL Server 2005 i z collation bazy tempdb w SQL Server 2000. W rzeczywistości jest to prawdą tylko dla zmiennych tablicowych. Oto przykład:
-- ważne, żeby to nie było collation servera!
create database bad_db collate SQL_Polish_CP1250_CI_AS
GO
use bad_db
GO
declare @t table (a nvarchar(128))
select * from @t t join sysobjects o on t.a = o.name
GO
create table #t (a nvarchar(128))
select * from #t t join sysobjects o on t.a = o.name
GO
Przykład uruchamiamy na SQL Server 2000 i SQL Server 2005. Jak się okazuje, query ze zmienną tablicową wykonuje się prawidłowo tylko na 2005. Wszystkie inne zwracają błąd konfliktu collation. Zatem #tabele tymczasowe w kontekście collation nadal są złe na SQL Server 2005. Co więcej, test przeprowadzony na ostatnim dostępnym mi buildzie SQL Server 2008 daje identyczny rezultat, co 2005.
Czy zatem, będąc świadomymi zła, jesteśmy w stanie mu zaradzić? Owszem jesteśmy, ale wymaga to zmiany przyzwyczajeń w pisaniu kodu. Otóż, aby być collation insensitive, musimy przy tworzeniu tabel tymczasowych, a na SQL 2000 również zmiennych tablicowych, dopisywać przy stringowych kolumnach klauzulę COLLATE database_default. Na naszym przykładzie wygląda to tak:
-- tak już nie musimy robić na SQL Server 2005 i wyższych
declare @t table (a nvarchar(128) collate database_default)
select * from @t t join sysobjects o on t.a = o.name
GO
-- tak niestety musimy nadal
create table #t (a nvarchar(128) collate database_default)
select * from #t t join sysobjects o on t.a = o.name
drop table #t
GO
Wnioski
Jeśli nie chcemy mieć potencjalnego konfliktu collation:
1. Dopisujemy collate database_default przy definicjach kolumn stringowych #tabel tymczasowych zawsze i zmiennych @tablicowych na SQL 2000.
2. Jeśli mamy SQL 2005 i nie chcemy wyrabiać sobie odruchu z punktu 1 - bardziej lubimy @tabele niż #tabele. Abstrahując od innych warunków ich zastosowania, rzecz jasna.