29 kwietnia 2011

Tabele tymczasowe i "Cannot resolve the collation conflict between Collaction1 and Collation2 in the equal to operation."

Tabele tymczasowe i collation


Tabele tymczasowe są tworzone w bazie danych tempdb. Kolumny tych tabel domyślnie używają collation tempdb (czyli collation instacji MS SQL Server). Może to doprowadzić do problemów jeżeli baza danych używa innego collation, niż collation serwera.


Stwórzmy bazę danych z jednym z popularnych collation dla języka polskiego Polish_CI_AS.



Konflikt collation


Stwórzmy tabele tymczasowe bez specyfikowania collation dla kolumn.



Wykonajmy proste zapytanie z JOIN-em po tabeli tymczasowej (#TestCollaction2) i tabeli TestCollaction z bazy DatabaseWithPolishCollation.



Otrzymujemy błąd:

Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Polish_CI_AS" in the equal to operation.

Niestety bez poważnego wysiłku nie można zmienić collaction instancji MS SQL Server. Pozostaje ustawienie collation dla poszczególnych kolumn tabeli tymczasowej, lub posłużenie się zmiennymi tabelarycznymi.

Rozwiązanie 1 - collation dla każdej z kolumn


Zastąpimy deklaracje #TestCollaction2 następującym kodem:



Zapytanie powinno wykonać się bez problemu po ponownym połączeniu do serwera.

Rozwiązanie 2 - zmienna tabelaryczna zamiast tabeli tymczasowej


Kolumny zmiennych tabelarycznych (DECLARE @table TABLE) domyślnie mają collation bazy danych w kontekście której są tworzone. W opisywanym przypadku będzie to DatabaseWithPolishCollation.

4 komentarze:

  1. Niezależnie od collation bazy można tworzyć tabele tymczasowe dopisując obok każdego pola tekstowego: COLLATE database_default. Druga, dobrze sprawująca się metoda to tworzenie tabel tymczasowych poleceniem SELECT INTO:
    SELECT * INTO #Tabela FROM Tabela WHERE 1=0
    Obie metody gwarantują zgodność collation w tabeli tymczasowej z collation bazy, w ramach której akurat pracujemy lub z collation kolumn z tabeli, której kopię tworzymy polceniem SELECT INTO.

    OdpowiedzUsuń
  2. Dzięki za informację, COLLATE database_default rzeczywiście wygląda bardziej elegancko.

    Aczkolwiek przy metodzie SELECT/INTO musisz mieć już istniejącą tabelą z której schematu chciałbyś skorzystać, więc nie zawsze można z tej opcji użyć.

    OdpowiedzUsuń
  3. Przy SELECT INTO nic nie musisz robić. Collation w nowo powstałej tabeli zostanie takie, jakie było w tabeli źródłowej.

    OdpowiedzUsuń
  4. Rozwiązanie 1 jest najlepsze z punktu widzenia bazy danych ale to nie jest zawsze możliwe, ponieważ czasem projekty są robione dla klienta z jeszcze nie zainstalowanym SQL Serwerem.
    Rozwiązanie 2 jest bardziej łatwe do oprogramowania lecz w niektórych wypadkach to spowoduje spad wydajności ( jest dużo artykułów na temat "Table Variable vs Temporary Table")
    Rozwiazanie z "COLLATE database_default" jest jeszcze łatwe do napisania ... ale robi kwerendę jako nie SARG`able. Szczegóły opisałem tu:
    http://stackoverflow.com/questions/6145687/affecting-performance-with-sql-collation/6148659#6148659
    (testowano na 2008R2)

    OdpowiedzUsuń

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.