• Articles
• admin

Quando si sviluppa un’applicazione o si scrive un codice nel sistema di database SQL, è fondamentale capire come i dati verranno ordinati e confrontati. È possibile memorizzare i dati in una lingua specifica oppure è possibile che SQL Server tratti separatamente i dati sensibili alle maiuscole e minuscole. Microsoft ha fornito un’impostazione SQL Server chiamata Collation per controllare e risolvere tali requisiti.

Che cos’è la collazione in SQL Server?

Possiamo impostare le regole di confronto a vari livelli in SQL Server come indicato di seguito.

• Server Level
• Database Level
• Column Level
• Expression Level

Le regole di confronto a livello di server possono talvolta essere chiamate regole di confronto a livello di istanza di SQL Server.

Le regole di confronto a livello di database verranno ereditate dall’impostazione Regole di confronto a livello di server se non si scelgono regole di confronto specifiche durante la creazione del database. È inoltre possibile modificare le regole di confronto a livello di database in un secondo momento. Si noti che la modifica delle regole di confronto del database verrà applicata solo per gli oggetti imminenti o nuovi che verranno creati dopo la modifica delle regole di confronto.

Le nuove regole di confronto non modificano i dati esistenti memorizzati nelle tabelle ordinate con l’ultimo tipo di regole di confronto. Il team dell’applicazione ha bisogno di un’ulteriore pianificazione per gestire questa conversione dei dati memorizzati a causa della nuova impostazione delle regole di confronto.

Ci sono diversi modi per farlo. Uno è copiare i dati dalla tabella esistente in una nuova tabella creata con le nuove regole di confronto e quindi sostituire la vecchia tabella con quella nuova. È inoltre possibile spostare i dati della tabella in un nuovo database disponendo di una nuova collazione e sostituire il vecchio database con quello nuovo.

NOTA: cambiare le regole di confronto è un compito complesso e dovresti evitarlo a meno che tu non abbia un business case obbligatorio.

Come trovare e modificare le regole di confronto del database in SQL Server?

Andiamo avanti e controllare le regole di confronto di istanza di SQL Server e tutti i database ospitati su tale istanza. È possibile controllare le regole di confronto accedendo alla finestra delle proprietà a livello di database o istanza utilizzando SQL Server Management Studio o semplicemente eseguendo la seguente istruzione T-SQL. Le Regole di confronto per ogni database sono memorizzate nell’oggetto di sistema sys.database-vi accederemo per ottenere queste informazioni.

--Check Database CollationSELECT name, collation_name FROM sys.databases GO --Check Server or Instance level CollationSELECT SERVERPROPERTY('Collation') As 

Ho eseguito l’istruzione T-SQL sopra e ho ottenuto l’output sottostante. Possiamo vedere che tutti i database e le regole di confronto a livello di server hanno le stesse impostazioni di SQL_Latin1_General_CP1_CI_AS. Significa che le regole di confronto del database sono state ereditate dalle regole di confronto a livello di server durante la loro creazione e il valore predefinito non è stato modificato.

Ora, lascia che ti mostri come controllare le regole di confronto del database usando la GUI in SQL Server Management Studio.

Innanzitutto, connettiti all’istanza di SQL Server utilizzando SQL Server Management Studio. Espandere il nodo istanza seguito dalla cartella Database. Fare clic con il pulsante destro del mouse sul database di destinazione e scegliere Proprietà:

Si otterrà la finestra delle proprietà del database qui sotto.

Ora fai clic sulla scheda Opzioni dal pannello di sinistra. Otterrete più impostazioni delle proprietà nel pannello di destra. Collation è la prima proprietà di questa pagina: puoi vedere che è la stessa dello script T – SQL precedente.

Allo stesso modo, è possibile fare clic sul nodo istanza di SQL Server e fare clic con il pulsante destro del mouse sulle proprietà a livello di istanza per visualizzare le regole di confronto a livello di server.

Se si desidera modificare questa regole di confronto in una nuova regole di confronto, è sufficiente fare clic sul menu a discesa Regole di confronto e scegliere l’opzione desiderata. Assicurarsi di aver fatto un backup completo del database prima di farlo.

Ho scelto una collazione simile con SQL_Latin1_General_CP1_CS_AS case-sensitive per questo database e ho fatto clic su OK per applicarlo. Nota: Assicurati che nessuno sia connesso al database di destinazione durante questa procedura, altrimenti dovrai cambiare la modalità in Single user one e modificare questa configurazione.

È inoltre possibile modificare le regole di confronto del database utilizzando l’istruzione T-SQL. Per questo, utilizzare la clausola COLLATE dell’istruzione ALTER DATABASE.

In primo luogo, abbiamo commutato il database in modalità utente singolo, quindi modificato le regole di confronto e, infine, spostato il database in modalità multiutente.

--Change Database Collation using T-SQL USE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USER

Elenca tutte le regole di confronto supportate in SQL Server

Questa sezione mostra come trovare tutte le regole di confronto disponibili in SQL Server. Innanzitutto, lascia che ti mostri come ottenere l’elenco di tutte le regole di confronto supportate per l’istanza di SQL Server.

SQL Server ha una funzione di sistema chiamata fn_helpcollations() che è possibile utilizzare per recuperare tutte le regole di confronto.

Eseguire il comando sottostante per visualizzare l’elenco.

--Display the list of all collations SELECT name, description FROM fn_helpcollations()

Possiamo vedere tutte le 5508 collazioni supportate nella sezione output. Se non si è sicuri di quale confronto scegliere, è possibile utilizzare la clausola WHERE nello script sottostante per filtrare tutte le possibili regole di confronto che possono essere impostate sul database.

Supponiamo che sia necessario memorizzare i dati nella lingua inglese degli Stati Uniti e che il server SQL lo gestisca in un formato sensibile alle maiuscole e minuscole. È possibile utilizzare il comando seguente per recuperare l’elenco delle collazioni possibili e supportate per la query:

--Display the list of all collations with WHERE clause SELECT Name, Description FROM fn_helpcollations() WHERE Name like 'SQL_Latin1%' AND Description LIKE '%case-sensitive%'

L’output mostra solo 10 collazioni che soddisfano la tua query. È possibile utilizzare lo script di cui sopra per filtrare le varie regole di confronto.

Impatto della modifica delle regole di confronto del database sull’output della query

In questa sezione, ti mostrerò la differenza tra i due output della stessa query quando vengono eseguiti con regole di confronto diverse.

Innanzitutto, creerò un database denominato MSSQL con una collazione (SQL_Latin1_General_CP1_CS_AS). Quindi eseguirò la stessa query due volte per ottenere l’output. Successivamente, cambierò le regole di confronto in SQL_Latin1_General_CP1_CI_AS e eseguirò nuovamente le stesse query per ottenere il loro output. È possibile confrontare entrambi gli output e comprendere l’impatto della modifica di un confronto di database. Quindi, iniziamo con la creazione del database.

Avvia la nuova finestra di creazione del database come mostrato nell’immagine sottostante. È inoltre possibile creare questo database utilizzando T-SQL. Successivamente, è possibile visualizzare il nome del database e i relativi file di dati. Ora, fare clic sulla seconda scheda sul pannello di sinistra per passare alla finestra delle proprietà collation.

È possibile vedere che il nome di confronto per questo database è predefinito. Significa che questo database erediterà le regole di confronto dal tipo di regole di confronto a livello di server. Fai clic sul menu a discesa Regole di confronto per scegliere la nuova regole di confronto.

Ho selezionato il seguente confronto SQL_Latin1_General_CP1_CS_AS per questo database, non quello predefinito. Fare clic su OK per procedere con la creazione del database.

Ora, controlla le regole di confronto del database per un database appena creato. Possiamo vedere che è SQL_Latin1_General_CP1_CS_AS come abbiamo selezionato nel passaggio precedente.

In SQL_Latin1_General_CP1_CS_AS, CS sta per la modalità sensibile al maiuscolo / minuscolo e CI sta per la modalità senza maiuscolo / minuscolo. Ora puoi eseguire il seguente codice T-SQL o qualsiasi codice per ottenere l’output.

Ho eseguito lo stesso comando due volte. Il primo script filtra i nomi delle colonne con un valore SYS in lettere maiuscole, mentre il secondo script filtrerà la stessa colonna con lo stesso valore sys in lettere minuscole. La sezione output dimostra che il primo script non ha mostrato alcun output, mentre il secondo script ha visualizzato l’output a causa del suo comportamento case-sensitive.

Select * from sysusersWhere name='SYS'GoSelect * from sysusersWhere name='sys'GO

Ora, cambieremo le regole di confronto di questo database con le regole di confronto senza distinzione tra maiuscole e minuscole SQL_Latin1_General_CP1_CI_AS eseguendo le seguenti istruzioni T-SQL. È inoltre possibile modificarlo tramite GUI accedendo alla finestra Proprietà del database in SQL Server Management Studio.

--Change database collation to SQL_Latin1_General_CP1_CI_ASUSE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USER

Ho eseguito lo script di cui sopra in un solo colpo, e le regole di confronto del database è stato modificato con successo in una nuova regole di confronto con il supporto case-insensitive.

È possibile verificare questa modifica eseguendo gli script seguenti per verificare le regole di confronto del database MSSQL appena creato. Possiamo vedere che le nuove regole di confronto sono impostate per questo database nell’immagine qui sotto.

Eseguiremo nuovamente la stessa istruzione T-SQL prima di modificare le regole di confronto per vedere l’impatto di questa modifica. Come possiamo vedere ora, entrambe le istruzioni T-SQL sono nell’output.

Conclusione

Spero che sia evidente che le regole di confronto in SQL Server sono cruciali. Abbiamo definito quale impatto lascia se si apportano modifiche alle regole di confronto a qualsiasi livello in SQL Server. Fai sempre una pianificazione corretta e prova prima le modifiche nel tuo ambiente a ciclo di vita inferiore.

Restate sintonizzati per il mio prossimo articolo, dove vi mostrerò un metodo passo-passo per cambiare regole di confronto a livello di server.