Press "Enter" to skip to content

Collation che Passione…

Si fa per dire…

Le collation di SQL Server sono una cosa splendida per chi ha il problema di trattare dati scritti in lingue diverse con segni alfabetici diversi che devono convivere all’interno di un server dati e, dalla versione 2000 in su, addirittura su una stessa tabella.

Però, le stesse collation possono divenire un problema insidioso anche per chi tratta solo dati in italiano.

Il motivo per cui tutto ciò può accadere è legato al fatto che le collation sono state modificate nel passaggio fra SQL 7.0 e SQL 2000, per compatibilità, le collation di SQL 7.0 sono tuttora supportate e mantenute, ma è necessario avere le seguenti attenzioni:

  1. Verificare che la collation del database sia coerente con quella di compatibilità.
  2. Quando si dovesse aggiungere campi con comandi ALTER TABLE assicurarsi che questi comandi non abbiano al loro interno il comando COLLATE con una collation diversa da quella usata dalle tabelle del vostro database.

Sono certa che non è molto chiaro, ma con un esempio probabilmente è molto più semplice.

Le 2 collation incriminate sono così chiamate:

SQL_LATIN1_General_CP1_CI_AS (La collation compatibile con SQL 7.0)

Latin1_General_CI_AS (La collation di default da 2000 in poi)

Creiamo appositamente una tabella con un conflitto

USE [paperinik]
GO
/****** Object:  Table [dbo].[TbTestCollation]
    Script Date: 10/24/2007 13:55:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TbTestCollation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TestCollation1] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    [TestCollation2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TbTestCollation] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Come può accadere che una tabella di un nostro database si trovi ad avere un campo con collation errata? Un esempio semplice, il DB di origine della vostra applicazione è stato scriptato su un database SQL 7.0, magari semplicemente perché il produttore desidera essere certo che possa funzionare sulle versioni che vanno da SQL 7.0 in su. Però, l’utility usata per generare lo script ha generato anche i comandi COLLATE per tutte le stringhe.

Voi avete installato SQL Server 2000 oppure SQL Server 2005 e avete lasciato la collation di default per il database, che è Latin1_General_CI_AS, quando create il database non vi preoccupate di modificare la collation, poi lo script di generazione dati genera tutti i campi con la collation di SQL 7.0 e tutto funziona.

Per un malaugurato caso, ad un certo punto della vita dell’applicazione, i programmatori smettono di scriptare gli aggiornamenti alle tabelle con il comando COLLATE, “Perché così viene utilizzata la collation di default e tutto funziona ovunque.”, ma sul vostro database vi troverete nella situazione qui sopra, i vecchi campi con collation SQL 7.0 i nuovi campi con collation di default, di qui il patatrac…

Nella nostra tabella di test, inseriamo stringhe uguali sui due campi, eseguiamo la seguente query:

SELECT * FROM TBTESTCOLLATION WHERE TESTCOLLATION1 = TESTCOLLATION2

Otteniamo il seguente errore:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between 
 "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" 
in the equal to operation.

Come possiamo risolvere il problema? nel nostro caso, se abbiamo un solo campo possiamo passare tutto alla collation di default del database con l’esecuzione del seguente comando:

ALTER TABLE [TbTestCollation] ALTER COLUMN [TestCollation2]
            nvarchar(50)COLLATE Latin1_General_CI_AS  NULL

Ricordando però che non è possibile eseguirlo nei seguenti casi:

  • Se il campo è una computed column
  • Se il campo ha un indice
  • Se su di esso sono predisposte delle Distribution statistics, sia generate automaticamente che da CREATE STATISTICS
  • Se il campo ha un CHECK constraint
  • Se il campo è riferito da un FOREIGN KEY constraint

In questi casi è necessario prima rimuovere i vincoli poi eseguire l’ALTER COLUMN e infine ripristinare i vincoli.

Se per caso non sapessimo quante colonne con collation sbagliata ci sono sulle nostre tabelle, possiamo utilizzare una query sulle viste di sistema del database:

SELECT Table_Catalog, Table_Schema, Table_name, Column_Name, 
Collation_Name from INFORMATION_SCHEMA.COLUMNS

Listate le colonne con collation errata, trasformiamo tutte le colonne usando ovviamente la collation con il più alto numero di campi, modificando le tabelle errate.

Se il nostro problema è che il 99% del nostro database ha la collation SQL_Latin1_General_CP1_CI_AS, allora dobbiamo prima cambiare collation al database, in modo che futuri script senza comando COLLATE non provochino danni perché la collation di default è quella giusta. Poi dobbiamo eseguire comunque l’ALTER TABLE per sistemare le colonne errate.

Il comando per aggiornare la collation di default del database è:

ALTER DATABASE COLLATE SQL_Laitn1_General_CP1_CI_AS

gatto[3]

Fatto questo, potete inviare al centro assistenza del vostro fornitore l’ immagine
a lato in una e-mail di ringraziamento: