• Articles
• admin

wanneer u een toepassing ontwikkelt of een code schrijft in het sql-databasesysteem, is het cruciaal om te begrijpen hoe gegevens gesorteerd en vergeleken worden. U kunt uw gegevens in een specifieke taal opslaan, of u wilt mogelijk dat SQL Server hoofdlettergevoelige en hoofdletterongevoelige gegevens afzonderlijk behandelt. Microsoft heeft een SQL Server-instelling met de naam Collation verstrekt om dergelijke vereisten te beheren en aan te pakken.

Wat is collatie in SQL Server?

we kunnen de collatie op verschillende niveaus instellen in SQL Server zoals hieronder aangegeven.

• serverniveau
• databaseniveau
• Kolomniveau
• expressieniveau

de serverniveau-collatie kan soms SQL-Serverinstantieniveau-collatie worden genoemd.

de collatie op databaseniveau zal worden overgenomen van de instelling voor collatie op serverniveau als u geen specifieke collatie kiest tijdens het aanmaken van de database. U kunt later ook de collatie op databaseniveau wijzigen. Merk op dat het wijzigen van database collation zal worden toegepast voor alleen aankomende of nieuwe objecten die zullen worden gemaakt na de collation wijziging.

nieuwe collatie wijzigt geen bestaande gegevens die zijn opgeslagen in tabellen die gesorteerd zijn met het laatste collatietype. Het applicatieteam heeft verdere planning nodig om deze opgeslagen gegevensconversie te verwerken vanwege de nieuwe instelling voor collatie.

er zijn verschillende manieren om dit te doen. Een daarvan is om de gegevens van de bestaande tabel te kopiëren naar een nieuwe tabel die is gemaakt met de nieuwe collatie en dan de oude tabel te vervangen door de nieuwe. U kunt ook uw tabelgegevens naar een nieuwe database verplaatsen door een nieuwe collatie te hebben en de oude database te vervangen door de nieuwe.

opmerking: het wijzigen van de collatie is een complexe taak die u moet vermijden, tenzij u een verplichte business case hebt.

hoe Database Collation in SQL Server te vinden en te wijzigen?

laten we verder gaan en de collatie van SQL Server instantie en alle databases gehost op die instantie controleren. U kunt de collatie controleren door het venster Eigenschappen voor database-of instance-niveau te openen met SQL Server Management Studio of door simpelweg het onderstaande T-SQL-statement uit te voeren. De Collation voor elke database wordt opgeslagen in het systeem object sys.databases-we zullen het openen om deze informatie te krijgen.

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

ik heb het bovenstaande T-SQL statement uitgevoerd en de onderstaande uitvoer gekregen. We kunnen zien dat alle databases en serverniveau collation dezelfde instellingen hebben als SQL_Latin1_General_CP1_CI_AS. Het betekent dat database collaties zijn overgenomen door de server-niveau collatie tijdens het maken ervan, en de standaard waarde is niet gewijzigd.

Nu, Laat me je laten zien hoe je de database collation controleren met behulp van GUI in SQL Server Management Studio.

maak eerst verbinding met uw SQL Server-instantie met behulp van SQL Server Management Studio. Vouw het knooppunt instantie uit, gevolgd door de databasemap. Klik met de rechtermuisknop op de doeldatabase en kies Eigenschappen:

u krijgt de onderstaande Database Eigenschappen venster.

klik nu op het tabblad Opties in het linkerpaneel. U krijgt meerdere eigenschappen instellingen in het rechterpaneel. Collation is de eerste eigenschap van deze pagina – je kunt zien dat het hetzelfde is als in het bovenstaande T-SQL script.

op dezelfde manier kunt u op het knooppunt SQL Server-exemplaar klikken en met de rechtermuisknop op de eigenschappen op instance-niveau klikken om de collatie op serverniveau te zien.

Als u deze collatie wilt wijzigen in een nieuwe collatie, hoeft u alleen maar op de collatie dropdown te klikken en de optie te kiezen die u nodig hebt. Zorg ervoor dat u een volledige back-up van uw database hebt gedaan voordat u het doet.

ik heb voor deze database een soortgelijke vergelijking met hoofdlettergevoelige SQL_Latin1_General_CP1_CS_AS gekozen en op OK geklikt om het toe te passen. Opmerking: Zorg ervoor dat er tijdens deze procedure niemand is verbonden met de doeldatabase, anders moet u de modus naar single user one schakelen en deze configuratie wijzigen.

u kunt deze database collatie ook wijzigen met behulp van het T-SQL statement. Gebruik hiervoor de COLLATE clausule van het ALTER DATABASE statement.

eerst hebben we de database geschakeld naar de single-user modus, daarna hebben we de collatie gewijzigd en ten slotte hebben we de database verplaatst naar de multi-user modus.

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

Toon alle ondersteunde Collaties in SQL Server

deze sectie laat u zien hoe u alle beschikbare collaties in SQL Server kunt vinden. Laat me je eerst laten zien hoe je de lijst van alle ondersteunde collaties voor de SQL Server instantie krijgt.

SQL Server heeft een systeemfunctie genaamd fn_helpcollations () die u kunt gebruiken om alle collaties op te halen.

voer het onderstaande commando uit om de lijst weer te geven.

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

we kunnen alle 5508 ondersteunde collaties zien in de output sectie. Als u niet zeker weet welke collatie u moet kiezen, kunt u de WHERE-clausule in het onderstaande script gebruiken om alle mogelijke collaties die in de database kunnen worden ingesteld, uit te filteren.

laten we zeggen dat u uw gegevens in de Amerikaanse Engelse taal moet opslaan en dat de SQL Server deze in een hoofdlettergevoelig formaat moet verwerken. U kunt het onderstaande commando gebruiken om de lijst van mogelijke en ondersteunde collaties voor uw query op te halen:

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

de uitvoer toont slechts 10 collaties die voldoen aan uw vraag. U kunt het bovenstaande script gebruiken om verschillende collaties uit te filteren.

Impact van het veranderen van Database Collation op Query Output

In deze sectie zal ik u het verschil laten zien tussen de twee uitgangen van dezelfde query wanneer ze worden uitgevoerd met verschillende collaties.

eerst zal ik een database aanmaken met de naam MSSQL met een collatie (SQL_Latin1_General_CP1_CS_AS). Dan zal ik dezelfde query twee keer uitvoeren om de uitvoer te krijgen. Later zal ik de collatie veranderen in SQL_Latin1_General_CP1_CI_AS en opnieuw dezelfde queries uitvoeren om hun uitvoer te krijgen. U kunt beide uitgangen vergelijken en de impact van het wijzigen van een database collation begrijpen. Laten we beginnen met het aanmaken van een database.

start het nieuwe venster voor het aanmaken van een database Zoals weergegeven in de onderstaande afbeelding. U kunt deze database ook aanmaken met T-SQL. Daarna, kunt u de database naam en de gegevensbestanden te zien. Klik nu op het tweede tabblad in het linkerpaneel om over te schakelen naar het venster Eigenschappen collation.

u kunt zien dat de collation naam voor deze database de standaard is. Dit betekent dat deze database collation zal erven van het serverniveau collation type. Klik op de vervolgkeuzelijst collatie om uw nieuwe collatie te kiezen.

ik heb de onderstaande sql_latin1_general_cp1_cs_as geselecteerd voor deze database – niet de standaard. Klik op OK om verder te gaan met het aanmaken van de database.

nu, controleer de database collation voor een nieuw aangemaakte database. We kunnen zien dat het SQL_Latin1_General_CP1_CS_AS zoals we hebben geselecteerd in de vorige stap.

in SQL_LATIN1_GENERAL_CP1_CS_AS staat CS voor de hoofdlettergevoelige modus, en CI staat voor de hoofdletterongevoelige modus. Nu kunt u de onderstaande T-SQL-code of een code uitvoeren om de uitvoer te krijgen.

ik heb hetzelfde commando tweemaal uitgevoerd. Het eerste script filtert de kolomnamen met een waarde SYS in hoofdletters, terwijl het tweede script dezelfde kolom filtert met dezelfde waarde sys in kleine letters. De output sectie laat zien dat het eerste script geen uitvoer heeft getoond, terwijl het tweede script de uitvoer heeft weergegeven vanwege zijn hoofdlettergevoelig gedrag.

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

nu zullen we de collatie van deze database veranderen in de hoofdletterongevoelige collation SQL_Latin1_General_CP1_CI_AS door de onderstaande T-SQL statements uit te voeren. U kunt het ook wijzigen via de GUI door het venster Database-Eigenschappen in SQL Server Management Studio te openen.

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

ik heb het bovenstaande script in één opname uitgevoerd, en de database collation is succesvol gewijzigd in een nieuwe collation met hoofdletterongevoelige ondersteuning.

u kunt deze wijziging verifiëren door de onderstaande scripts uit te voeren om de collatie van de nieuw aangemaakte database MSSQL te controleren. We kunnen zien dat de nieuwe collation is ingesteld voor deze database in de onderstaande afbeelding.

we zullen opnieuw hetzelfde T-SQL statement uitvoeren voordat we de collatie veranderen om de impact van deze verandering te zien. Zoals we nu kunnen zien, zijn beide T-SQL statements in de output.

conclusie

ik hoop dat het duidelijk is dat de collatie in SQL Server cruciaal is. We hebben gedefinieerd welke impact het heeft als u wijzigingen aanbrengt in de collatie op elk niveau in SQL Server. Voer altijd de juiste planning uit en test eerst de wijzigingen in uw lagere levenscyclusomgeving.

stay tuned voor mijn volgende artikel, waar ik u een stap-voor-stap methode zal tonen om serverniveau collation te wijzigen.