Una Collation in SQL Server rappresenta un insieme di regole che determinano il modo in cui i dati vengono ordinati e comparati dal DBMS.
I caratteri all’interno di un database vengono ordinati utilizzando regole che definiscono la loro corretta sequenza con opzioni per specificare, ad esempio, la differenziazione tra maiuscole e minuscole, tra i diversi accenti e tra le diverse dimensioni (in termini di byte).
Le regole di confronto possono essere impostate a diversi livelli in SQL Server. Di seguito sono riportati i tre livelli:
- Livello di SQL Server
- Livello di database
- Livello di colonna
In fase d’ installazione di SQL Server è possibile scegliere le regole di confronto predefinite per l’istanza creata (Latin1_General_CI_AI). I nuovi database verranno configurati con le regole di confronto predefinite di SQL Server.
Gli utenti possono modificare la collation in Sql Server a livello di database ma non a livello di installazione.
Sommario
Collation in Sql Server a livello istanza
Le regole di confronto di un’istanza di SQL Server sono le regole di confronto predefinite per i database di sistema. Include master, tempdb e model. Se le regole di confronto del database di un utente sono configurate in modo diverso rispetto a una delle regole di confronto dell’istanza di SQL Server, un confronto tra le tabelle in tale database e in tempdb, quando si tratta di confronto testuale dei dati, restituirà un errore.
Per trovare l’utilizzo delle regole di confronto a livello di istanza di SQL Server, il T-SQL illustrato di seguito:
SELECT SERVERPROPERTY('collation') AS ServerCollation;
Regole di confronto del database
Le regole di confronto predefinite del database vengono ereditate dalla collation in Sql Server impostata in fase d’installazione e utilizzate per il ripristino del database. Le regole di confronto vengono copiate nel backup quando viene creato il backup. Per trovare le regole di confronto del database correnti, utilizzare il script illustrato di seguito:
SELECT name, collation_name DbCollation
FROM sys.databases
Se la collation di un database viene modificata da un utente, le regole di confronto delle colonne di tabella esistenti definite dall’utente non verranno modificate. Tuttavia, le nuove tabelle saranno caratterizzate dalle regole di confronto del database modificate.
Da tenere in considerazione che se nel database sono presenti tabelle con varie regole di confronto, possono verificarsi errori durante la creazione di un join tra di esse.
Per applicare nuove regole di confronto alle tabelle esistenti di un database, l’utente può impostare nuove regole di confronto sulle colonne TEXT delle tabelle. Questa attività bloccherà le tabelle perché la colonna verrà modificata con le nuove regole di confronto. Di conseguenza, l’indice di colonna esistente verrà eliminato e ricreato automaticamente.
Regole di confronto a livello di colonna
La collation in Sql Server definita a livello di colonna eredita dalle regole di confronto configurate per il database. Se le regole di confronto del database vengono modificate, le colonne esistenti manterranno le regole di confronto originali, ma verranno create nuove colonne con le regole di confronto del database correnti.
Questo T-SQL può aiutare a trovare le regole di confronto per una particolare colonna:
SELECT OBJECT_NAME(OBJECT_ID), name ColumnName, collation_name AS ColumnCollation
FROM sys.columns
WHERE collation_name IS NOT NULL
AND OBJECT_NAME(OBJECT_ID) = '?'
AND name = '?'L’immagine seguente mostra il tipo di regole di confronto come proprietà di colonna quando si naviga in Esplora oggetti e viene visualizzato dall’opzione Proprietà del menu di scelta rapida:
Conflitti di regole di confronto
SQL Server supporta più regole di confronto, ma non è in grado di gestire correttamente le mancate corrispondenze delle regole di confronto. Se un utente tenta di confrontare campi con regole di confronto diverse, SQL Server restituirà un errore con i nomi delle regole di confronto, come illustrato di seguito:
Msg 468, livello 16, stato 9, riga 9
Impossibile risolvere il conflitto di regole di confronto tra "Latin1_General_CI_AI" e "SQL_Latin1_General_CP1_CI_AS" nell'operazione simile.Per evitare conflitti, gli utenti possono aggiungere un’impostazione di regole di confronto predefinita nell’istruzione di query T-SQL:
COLLATE DATABASE_DEFAULT
WHERE Column1 COLLATE DATABASE_DEFAULT = Column2Ciò potrebbe causare un problema durante la programmazione per SQL Server. Gli utenti possono risolvere il problema utilizzando una clausola di confronto delle colonne di SQL Server con COLLATE come descritto in precedenza.
In una situazione in cui SQL Server è stato installato di recente e viene identificata un’impostazione di regole di confronto inappropriata, può causare un problema con l’utilizzo di tempdb.
Come accennato in precedenza, tempdb viene creato con le regole di confronto predefinite a livello di server quando si riavvia il servizio SQL Server.
Cambiare collation in Sql Server
Cambiare la collation di un database non è, come accennato in precedenza, un’operazione semplice e probabilmente sarà oggetto di un articolo di approfondimento futuro. Per il momento evidenzio che esistono due approcci per effettuare questa operazione.
Il primo è quello di creare una copia del database di origine impostando ovviamente la collation desiderata e successivamente trasferire i dati tra i due database. Il secondo è quello di cambiare l’impostazione della collation del database tramite la seguente istruzione
ALTER DATABASE nome_database COLLATE nome_nuova_collatione successivamente andare a modificare la collation di ogni colonna delle tabelle del database tramite la sintassi
ALTER TABLE nome_tabella ALTER COLUMN nome_colonna COLLATE nome_nuova_collationquesto perchè l’ALTER DATABASE non opera sulle tabelle.
Alcuni Script per avere info sulle collation
Sapere quale collation è installata:
SELECT SERVERPROPERTY('COLLATION')Avere un elenco delle collation disponibili:
SELECT Name, Description FROM fn_helpcollations()Sapere quale database ha una collation differente da quella predefinita:
SELECT
NAME AS DATABASE_NAME
, DATABASEPROPERTYEX(NAME,’COLLATION’) AS DBCOLLATION
, SERVERPROPERTY(‘COLLATION’) AS SERVERCOLLATION
FROM SYS.DATABASES
WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,’COLLATION’)) <> SERVERPROPERTY(‘COLLATION’)
Visualizzare la collation di ogni colonna delle tabelle del database:
SELECT
C.TABLE_CATALOG AS DATABASE_NAME
,C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,DATA_TYPE
,SERVERPROPERTY(‘COLLATION’) AS SERVER_COLLATION
,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,’COLLATION’)) AS DATABASE_COLLATION
,C.COLLATION_NAME AS COLUMN_COLLATION
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.DATABASES D
ON DB_ID(C.TABLE_CATALOG) = DB_ID(D.NAME)
WHERE DATA_TYPE IN (‘VARCHAR’ ,’CHAR’,’NVARCHAR’,’NCHAR’,’TEXT’,’NTEXT’)
Cambiare la collation di una colonna:
ALTER TABLE nome_tabella
ALTER COLUMN nome_colonna tipo_di_dati
COLLATE nome_nuova_collationQueste sono solo alcune delle istruzioni che riguardano l’uso delle collation e chiaramente per approfondire le caratteristiche delle diverse collation e comprendere al meglio quella che fa al caso vostro vi consiglio di consultare la documentazione ufficiale Microsoft.
Conclusione
Abbiamo visto insieme le collation in Sql Server e alcuni script fondamentali per estrarre informazioni utili e come cambiare le regole di confronto.
Se hai dubbi scrivici nei commenti.
