0

I'm looking for a procedure to transform a DB running on SQL Server 12.0.4100.1 from collation SQL_Latin1_General_CP1_CI_AS to collation SQL_Latin1_General_CP1_CS_AS

The database is about 330GB in size and in the end needs to be moved from a server with server collation SQL_Latin1_General_CP1_CI_AS to another server with collation SQL_Latin1_General_CP1_CS_AS. As the database actively uses tempdb, it is imperative that everything is transformed properly.

So far, my research shows these steps:

  1. ALTER DATABASE [dbname] COLLATE SQL_Latin1_General_CP1_CS_AS
  2. ALTER TABLE [tablename] COLLATE SQL_Latin1_General_CP1_CS_AS
  3. (Update based on answers) ALTER TABLE [tablename] ALTER COLUMN [columnname] COLLATE SQL_Latin1_General_CP1_CS_AS

But is this sufficient? Are any alterations necessary to the existing data, or is that comprised in the 2 previous steps?

TT.
  • 15,774
  • 6
  • 47
  • 88
Jan
  • 1
  • 1
  • 3
  • this looks right after that you can verify the collation setting with: `SELECT name, collation_name FROM sys.databases WHERE name = N'MyOptionsTest'; GO ` You can find the full answer andm ore info here: https://stackoverflow.com/a/9531221/7667467 – t16n Sep 13 '17 at 14:29
  • Changing DB collation value not sufficient, you have to alter every table columns collation that has collation attribute. – Serkan Arslan Sep 13 '17 at 14:32
  • 4
    Be exceedingly careful. You are going from a case-insensitive collation to a case-sensitive collation. This can break applications if they're used to case-insensitive data matching, and database objects like stored procedures and views if they have not carefully spelled out every object with the correct case. – Jeroen Mostert Sep 13 '17 at 14:53
  • 1
    Before changing the column collation, you'll need to drop referencing constraints and indexes. Rather than do this all in-place, consider creating a new database from scratch and copy the data. – Dan Guzman Sep 13 '17 at 14:57
  • If that server has no database at the moment, it's easier to rebuild its system databases. Server with CS collation will create you problems from not recognizing data types in upper case to just breaking the existing code by not founding objects with names written with the wrong case – sepupic Sep 13 '17 at 19:25

0 Answers0