3.9 Wie ändere ich die Collation der Datenbank ?
Theorie vorab
Falls Sie eine Datenbank von einem SQL Server zu einem anderen SQL Server transportieren, kann es Probleme mit Collation geben. Die Collation ist die Sortiervorschrift für die Datenbank - wie Zeichen sortiert werden und ob Groß- und Kleinschreibung unterschieden wird. Effektiv sind alle Textspalten von der Collation abhängig, nummerische Datentypen sind nicht collationabhängig. Jede Textspalte könnte eine andere Collation haben (soll sie aber auf keinen Fall haben).
Die Collation ist eine recht komplexe Eigenschaft, da eine Rolle spielt
Welche Standardcollation der SQL Server hat
Welche Standardcollation die temp Datenbank besitzt
Welche Standardcollation die master Datenbank besitzt (da jede neue Datenbank eine Kopie der Master Datenbank ist)
Welche Standardcollation die Datenbank besitzt
Welche Collation die jeweilige Textspalte der Tabelle besitzt
Wie man temporäre Tabellen erzeugt - das sollte immer mit COLLATE DATABASE_DEFAULT passieren, damit die Tabellen in der temp Datenbank die gleich Sortierung haben wir in der Datenbank
Praxissituationen
Das Problem der Collationänderung tritt meist auf
beim verschieben einer Datenbank von Azure auf den lokalen SQL Server - hier wird per bacpac transportiert und die Datenbank automatisch erstellt
insbesondere, falls der lokale SQL Server eine CS (Case Sensitive) Collation und der Azure Server eine CI (Case Insensitive) Collation nutzt.
Option A - die Collation aller Textspalten ändern
Ändere die Standardcollation der Datenbank
lass einen Cursor über alle Textspalten laufen und ändere die Collation jeder Textspalten (Überlegungen dazu hier: https://stackoverflow.com/questions/26132895/changing-the-collation-of-a-sql-server-2012-database)
das kann durch Abhängigkeiten, Schlüsselbeziehungen, berechnete Spalten sehr schwer sein
da die Systemtabellen des SQL Servers die alte Collation behalten, kommt es zu Problemen bei Join mit Systemtabellen / views, wie z.B. sys.tables, sys.columns etc.
Option B - die Daten in eine neue Datenbank mit der richtigen Collation verschieben
Neue leere Datenbank mit der richtigen Collation einspielen
in dieser Datenbank nur die leeren OCT Tabellen anlegen (ohne default content etc.)
oder das komplette OCT Setup Script einspielen und allen Tabellen per TRUNCATE leeren
allen Tabellen aus der alten Datenbank in die neue überspielen per INSERT INTO Ziel.Tabelle SELECT * FROM Quelle.Tabelle - mit IDENTITY INSERT ON - die Keys müssen erhalten bleiben
alle Views / Prozeduren etc. können aus der alten Datenbank leicht per Rechte Maus auf Datenbank → Tasks → Scripts generieren übertragen werden
Option C - die Daten mit den anderen Datenbankobjekten per Script generieren verschieben
Neue leere Datenbank mit der richtigen Collation einspielen
alle Tabellen / Views / Prozeduren etc. können aus der alten Datenbank per Rechte Maus auf Datenbank → Tasks → Scripts generieren übertragen werden
das funktioniert ggf. für kleine Datenbanken, bei großen Datenbanken wird das Script riesig, dauert ewig auszuführen
Script über 300 MB kann nicht über SQL Managementstudio ausgeführt werden, nur über sqlcmd.exe