• Articles
• admin

când dezvoltați o aplicație sau scrieți un cod în sistemul de baze de date SQL, este esențial să înțelegeți modul în care datele vor fi sortate și comparate. Puteți stoca datele într-o anumită limbă, sau poate doriți SQL Server pentru a trata datele sensibile la majuscule și minuscule separat. Microsoft a furnizat o setare SQL Server numită colaționare pentru a controla și a aborda astfel de cerințe.

ce este colaționarea în SQL Server?

putem seta colaționarea la diferite niveluri în SQL Server așa cum este prezentat mai jos.

• nivelul serverului
• nivelul bazei de date
• nivelul coloanei
• nivelul expresiei

colaționarea nivelului serverului poate fi uneori numită colaționare la nivel de instanță SQL Server.

colaționarea la nivel de bază de date va fi moștenită din setarea de colaționare la nivel de server dacă nu alegeți nicio colaționare specifică în timpul creării bazei de date. De asemenea, puteți modifica colaționarea la nivel de bază de date mai târziu. Rețineți că modificarea bazei de date colaționare se va aplica numai pentru obiecte viitoare sau noi, care vor fi create după schimbarea colaționare.

Noua colaționare nu va modifica datele existente stocate în tabele care au fost sortate cu ultimul tip de colaționare. Echipa de aplicații are nevoie de o planificare suplimentară pentru a gestiona această conversie a datelor stocate datorită noii setări de colaționare.

există mai multe moduri de a face acest lucru. Unul este să copiați datele din tabelul existent într-un tabel nou creat cu noua colaționare și apoi să înlocuiți tabelul vechi cu cel nou. De asemenea, puteți muta datele tabelului într-o bază de date nouă, având o nouă colaționare și înlocuiți baza de date veche cu cea nouă.

notă: schimbarea colaționării este o sarcină complexă și ar trebui să o evitați dacă nu aveți un caz de afaceri obligatoriu.

cum să găsiți și să modificați colaționarea bazei de date în SQL Server?

să mergem mai departe și să verificăm colaționarea instanței SQL Server și a tuturor bazelor de date găzduite pe acea instanță. Puteți verifica colaționare prin accesarea bazei de date sau fereastra de proprietăți la nivel de instanță folosind SQL Server Management Studio sau prin simpla executare de mai jos instrucțiunea T-SQL. Colaționarea pentru fiecare bază de date este stocată în sistem obiect sys.baze de date-o vom accesa pentru a obține aceste informații.

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

am executat instrucțiunea T-SQL de mai sus și am obținut ieșirea de mai jos. Putem vedea că toate bazele de date și colaționarea la nivel de server au aceleași setări ca SQL_Latin1_General_CP1_CI_AS. Aceasta înseamnă colaționările bazei de date au fost moștenite de colaționarea la nivel de server în timp ce le creați, iar valoarea implicită nu a fost modificată.

acum, permiteți-mi să vă arăt cum să verificați colaționarea bazei de date folosind GUI în SQL Server Management Studio.

mai întâi, conectați-vă la instanța SQL Server utilizând SQL Server Management Studio. Extindeți nodul instanță urmat de folderul baze de date. Faceți clic dreapta pe baza de date țintă și alegeți Proprietăți:

veți primi fereastra de proprietăți a bazei de date de mai jos.

Acum faceți clic pe fila Opțiuni din panoul din stânga. Veți obține mai multe setări de proprietăți în panoul din partea dreaptă. Colaționarea este prima proprietate a acestei pagini – puteți vedea că este la fel ca în scriptul T-SQL de mai sus.

în mod similar, aveți posibilitatea să faceți clic pe nodul de instanță SQL Server și faceți clic dreapta pe proprietățile la nivel de instanță pentru a vedea colaționarea la nivel de server.

dacă doriți să modificați această colaționare într-o nouă colaționare, trebuie doar să faceți clic pe meniul derulant colaționare și să alegeți opțiunea de care aveți nevoie. Asigurați-vă că ați făcut o copie de rezervă completă a bazei de date înainte de a o face.

am ales o colaționare similară cu case-sensitive sql_latin1_general_cp1_cs_as pentru această bază de date și a făcut clic pe OK să-l aplice. Notă: Asigurați-vă că nimeni nu este conectat la baza de date țintă în timpul acestei proceduri, în caz contrar, va trebui să comutați modul în unul singur utilizator și să modificați această configurație.

de asemenea, puteți modifica această colaționare a bazei de date utilizând instrucțiunea T-SQL. Pentru aceasta, utilizați clauza COLLATE din instrucțiunea ALTER DATABASE.

mai întâi, am schimbat baza de date în modul cu un singur utilizator, apoi am schimbat colaționarea și, în cele din urmă, am mutat baza de date în modul multi-utilizator.

--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

lista toate Colaționările acceptate în SQL Server

această secțiune vă va arăta cum să găsiți toate colaționările disponibile în SQL Server. Mai întâi, permiteți-mi să vă arăt cum să obțineți lista tuturor colațiilor acceptate pentru instanța SQL Server.

SQL Server are o funcție de sistem numită fn_helpcollations() pe care o puteți utiliza pentru a prelua toate colaționările.

rulați comanda de mai jos pentru a afișa lista.

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

putem vedea toate 5508 colaționări acceptate în secțiunea de ieșire. Dacă nu sunteți sigur ce colaționare să alegeți, puteți utiliza clauza WHERE din scriptul de mai jos pentru a filtra toate colaționările posibile care pot fi setate la baza de date.

să presupunem că trebuie să stocați datele în limba engleză americană și doriți ca SQL Server să le gestioneze într-un format sensibil la majuscule. Puteți utiliza comanda de mai jos pentru a prelua lista de colaționări posibile și acceptate pentru interogarea dvs:

--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%'

rezultatul arată doar 10 colaționări care satisfac interogarea. Puteți utiliza scriptul de mai sus pentru a filtra diverse colații.

impactul modificării colaționării bazei de date asupra ieșirii interogării

în această secțiune, vă voi arăta diferența dintre cele două ieșiri ale aceleiași interogări atunci când sunt executate cu colaționări diferite.

în primul rând, voi crea o bază de date numită MSSQL cu o colaționare (SQL_Latin1_General_CP1_CS_AS). Apoi voi rula aceeași interogare de două ori pentru a obține ieșirea. Mai târziu, voi schimba colaționarea la SQL_Latin1_General_CP1_CI_AS și voi rula din nou aceleași interogări pentru a obține ieșirea lor. Puteți compara ambele ieșiri și puteți înțelege impactul modificării unei colaționări a bazei de date. Deci, să începem cu crearea bazei de date.

lansați noua fereastră de creare a bazei de date așa cum se arată în imaginea de mai jos. De asemenea, puteți crea această bază de date folosind T-SQL. După aceea, puteți vedea numele bazei de date și fișierele sale de date. Acum, faceți clic pe a doua filă din panoul din stânga pentru a comuta la fereastra proprietății de colaționare.

puteți vedea că numele de colaționare pentru această bază de date este implicit. Înseamnă că această bază de date va moșteni colaționarea de la tipul de colaționare la nivel de server. Faceți clic pe meniul vertical colaționare pentru a alege noua colaționare.

am selectat colaționarea de mai jos sql_latin1_general_cp1_cs_as pentru această bază de date – nu cea implicită. Faceți clic pe OK pentru a continua cu crearea bazei de date.

acum, verificați colaționarea bazei de date pentru o bază de date nou creată. Putem vedea că este SQL_Latin1_General_CP1_CS_AS așa cum am selectat în pasul anterior.

în SQL_LATIN1_GENERAL_CP1_CS_AS, CS reprezintă modul sensibil la majuscule, iar CI reprezintă modul insensibil la majuscule. Acum Puteți rula fie codul T-SQL de mai jos, fie orice cod pentru a obține ieșirea.

am executat aceeași comandă de două ori. Primul script filtrează numele coloanelor cu o valoare SYS cu majuscule, în timp ce al doilea script va filtra aceeași coloană cu aceeași valoare sys cu litere mici. Secțiunea de ieșire demonstrează că primul script nu a arătat nicio ieșire, în timp ce al doilea script a afișat ieșirea din cauza comportamentului său sensibil la majuscule.

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

acum, vom schimba colaționarea acestei baze de date în colaționarea insensibilă la majuscule sql_latin1_general_cp1_ci_as executând instrucțiunile T-SQL de mai jos. De asemenea, îl puteți schimba prin GUI accesând fereastra de proprietăți a bazei de date din 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

am executat scriptul de mai sus într-o singură fotografie, iar colaționarea bazei de date a fost schimbată cu succes într-o nouă colaționare cu suport insensibil la majuscule.

puteți verifica această modificare rulând scripturile de mai jos pentru a verifica colaționarea bazei de date nou create MSSQL. Putem vedea că noua colaționare este setată pentru această bază de date în imaginea de mai jos.

vom rula din nou aceeași instrucțiune T-SQL înainte de a schimba colaționarea pentru a vedea impactul acestei modificări. După cum putem vedea acum, ambele instrucțiuni T-SQL sunt în ieșire.

concluzie

sper că este evident că colaționarea în SQL Server este crucială. Am definit ce impact lasă dacă faceți modificări în colaționare la orice nivel în SQL Server. Faceți întotdeauna o planificare adecvată și testați mai întâi modificările din mediul ciclului de viață inferior.

rămâneți la curent cu următorul meu articol, unde vă voi arăta o metodă pas cu pas pentru a schimba colaționarea la nivel de server.