MS SQL - amending collation

Question

Hi,

I have some queries on changing the collation from "SQL_Latin1_General_CP1_CI_AS" to "SQL_Latin1_General_CP1_CS_AS" i.e. case insensitive to case sensitive.

I tried :

exporting the data from the original DB
create a BACPAC of the original DB
change the collation
import the data from the original DB to this new DB

It did not work. I was unable to change the collation in the BACPAC DB.

How can I go about creating a new DB while preserving all the settings, configuration, etc of the original DB and just change the collation and thereafter import the data back?

Is that even possible? I am not very good in MS SQL.

Thanks & regards
James

Answer

Thanks for the information. It is quite a small database, so copying data over is certainly possible. I was starting to prepare instructions, but then I got nervous that there may be features that we would fail to handle.

So here are new instructions.

Step one: Make sure that you have restored a clean backup of the database.

Step two is to run this:

SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + 'ADD ' +   
          CASE WHEN cc.is_system_named = 1 THEN ''  
               ELSE ' CONSTRAINT ' + quotename(cc.name)  
          END + ' CHECK (' + cc.definition + ')'  
   FROM   sys.tables t  
   JOIN   sys.schemas s ON t.schema_id = s.schema_id  
   JOIN   sys.check_constraints cc ON cc.parent_object_id = t.object_id  
   ORDER  BY s.name, t.name, cc.name


DECLARE @cur CURSOR, 
        @object_id int,
        @index_id  int,
        @ixcmd nvarchar(MAX)

DECLARE @cmds TABLE (cmd nvarchar(MAX))

SET @cur = CURSOR STATIC FOR
   SELECT i.index_id, t.object_id 
   FROM   sys.indexes i
   JOIN   sys.tables t ON i.object_id = t.object_id
   WHERE  i.filter_definition IS NOT NULL

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK
  

   -- Get the mandatory part of the index definition. That is type and columns.
   SELECT @ixcmd = 'CREATE ' +
                   CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                   CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                   CASE WHEN i.type = 3 THEN 'XML '
                        WHEN i.type = 4 THEN 'SPATIAL '
                        WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                        ELSE ''
                    END + 'INDEX ' + quotename(i.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
   FROM   sys.indexes i
   JOIN   sys.tables t ON t.object_id = i.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) +
                        CASE ic.is_descending_key
                             WHEN 1 THEN ' DESC'
                             ELSE ''
                        END + ','
                 FROM   sys.index_columns ic
                 JOIN   sys.columns c ON ic.object_id = c.object_id
                                     AND ic.column_id = c.column_id
                 WHERE  ic.object_id = @object_id
                   AND  ic.index_id  = @index_id
                   AND  (i.type > 2 OR
                         ic.key_ordinal > 0)
                 ORDER  BY ic.key_ordinal
                 FOR XML PATH(''), TYPE) AS ic(collist)
   WHERE   i.object_id = @object_id
     AND   i.index_id  = @index_id
   
   -- Add any included columns.
   IF EXISTS (SELECT *
              FROM   sys.index_columns
              WHERE  object_id = @object_id
                AND  index_id  = @index_id
                AND  is_included_column = 1)
   BEGIN
      SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
                      substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
      FROM  (SELECT quotename(c.name) + ','
             FROM   sys.index_columns ic
             JOIN   sys.columns c ON ic.object_id = c.object_id
                                 AND ic.column_id = c.column_id
             WHERE  ic.object_id = @object_id
               AND  ic.index_id  = @index_id
               AND  ic.is_included_column = 1
             ORDER  BY ic.index_column_id
             FOR XML PATH(''), TYPE) AS ic(incllist)
   END

   -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
   -- so that we know that with have WITH section for the rest.
   SELECT @ixcmd = @ixcmd +
                   CASE WHEN filter_definition IS NOT NULL
                          THEN ' WHERE ' + filter_definition + ' '
                          ELSE ''
                   END 
   FROM    sys.indexes
   WHERE   object_id = @object_id
     AND   index_id  = @index_id

   INSERT @cmds (cmd) VALUES(@ixcmd)
END


SET @cur = CURSOR STATIC FOR
   SELECT st.stats_id, t.object_id 
   FROM   sys.stats st
   JOIN   sys.tables t ON st.object_id = t.object_id
   WHERE  st.filter_definition IS NOT NULL
     AND  NOT EXISTS (SELECT *
                      FROM  sys.indexes i
                      WHERE i.object_id = st.object_id
                        AND i.name = st.name)

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK

   -- Get the statistics definition. FOr statistics we don't consider
   -- abaobjectstorageproperties, but assume that AbaPerls owns it all.
   SELECT @ixcmd = 'CREATE STATISTICS ' + quotename(st.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(sc.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(sc.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
                   CASE WHEN st.filter_definition IS NOT NULL
                          THEN ' WHERE ' + st.filter_definition + ' '
                          ELSE ''
                   END +
                   CASE WHEN st.no_recompute = 1
                        THEN ' WITH NORECOMPUTE'
                        ELSE ''
                   END
   FROM   sys.stats st
   JOIN   sys.tables t ON t.object_id = st.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) + ','
                 FROM   sys.stats_columns sc
                 JOIN   sys.columns c ON sc.object_id = c.object_id
                                     AND sc.column_id = c.column_id
                 WHERE  sc.object_id = @object_id
                   AND  sc.stats_id  = @index_id
                 ORDER  BY sc.stats_column_id
                 FOR XML PATH(''), TYPE) AS sc(collist)
   WHERE   st.object_id = @object_id
     AND   st.stats_id  = @index_id
     
   INSERT @cmds (cmd) VALUES(@ixcmd)
END

SELECT * FROM @cmds
DECLARE @cur CURSOR, 
        @object_id int,
        @index_id  int,
        @ixcmd nvarchar(MAX)

SET @cur = CURSOR STATIC FOR
   SELECT i.index_id, t.object_id 
   FROM   sys.indexes i
   JOIN   sys.tables t ON i.object_id = t.object_id
   WHERE  i.filter_definition IS NOT NULL

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK
  

   -- Get the mandatory part of the index definition. That is type and columns.
   SELECT @ixcmd = 'CREATE ' +
                   CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                   CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                   CASE WHEN i.type = 3 THEN 'XML '
                        WHEN i.type = 4 THEN 'SPATIAL '
                        WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                        ELSE ''
                    END + 'INDEX ' + quotename(i.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
   FROM   sys.indexes i
   JOIN   sys.tables t ON t.object_id = i.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) +
                        CASE ic.is_descending_key
                             WHEN 1 THEN ' DESC'
                             ELSE ''
                        END + ','
                 FROM   sys.index_columns ic
                 JOIN   sys.columns c ON ic.object_id = c.object_id
                                     AND ic.column_id = c.column_id
                 WHERE  ic.object_id = @object_id
                   AND  ic.index_id  = @index_id
                   AND  (i.type > 2 OR
                         ic.key_ordinal > 0)
                 ORDER  BY ic.key_ordinal
                 FOR XML PATH(''), TYPE) AS ic(collist)
   WHERE   i.object_id = @object_id
     AND   i.index_id  = @index_id
   
   -- Add any included columns.
   IF EXISTS (SELECT *
              FROM   sys.index_columns
              WHERE  object_id = @object_id
                AND  index_id  = @index_id
                AND  is_included_column = 1)
   BEGIN
      SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
                      substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
      FROM  (SELECT quotename(c.name) + ','
             FROM   sys.index_columns ic
             JOIN   sys.columns c ON ic.object_id = c.object_id
                                 AND ic.column_id = c.column_id
             WHERE  ic.object_id = @object_id
               AND  ic.index_id  = @index_id
               AND  ic.is_included_column = 1
             ORDER  BY ic.index_column_id
             FOR XML PATH(''), TYPE) AS ic(incllist)
   END

   -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
   -- so that we know that with have WITH section for the rest.
   SELECT @ixcmd = @ixcmd +
                   CASE WHEN filter_definition IS NOT NULL
                          THEN ' WHERE ' + filter_definition + ' '
                          ELSE ''
                   END 
   FROM    sys.indexes
   WHERE   object_id = @object_id
     AND   index_id  = @index_id

   PRINT @ixcmd
END


SET @cur = CURSOR STATIC FOR
   SELECT st.stats_id, t.object_id 
   FROM   sys.stats st
   JOIN   sys.tables t ON st.object_id = t.object_id
   WHERE  st.filter_definition IS NOT NULL
     AND  NOT EXISTS (SELECT *
                      FROM  sys.indexes i
                      WHERE i.object_id = st.object_id
                        AND i.name = st.name)

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK

   -- Get the statistics definition. FOr statistics we don't consider
   -- abaobjectstorageproperties, but assume that AbaPerls owns it all.
   SELECT @ixcmd = 'CREATE STATISTICS ' + quotename(st.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(sc.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(sc.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
                   CASE WHEN st.filter_definition IS NOT NULL
                          THEN ' WHERE ' + st.filter_definition + ' '
                          ELSE ''
                   END +
                   CASE WHEN st.no_recompute = 1
                        THEN ' WITH NORECOMPUTE'
                        ELSE ''
                   END
   FROM   sys.stats st
   JOIN   sys.tables t ON t.object_id = st.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) + ','
                 FROM   sys.stats_columns sc
                 JOIN   sys.columns c ON sc.object_id = c.object_id
                                     AND sc.column_id = c.column_id
                 WHERE  sc.object_id = @object_id
                   AND  sc.stats_id  = @index_id
                 ORDER  BY sc.stats_column_id
                 FOR XML PATH(''), TYPE) AS sc(collist)
   WHERE   st.object_id = @object_id
     AND   st.stats_id  = @index_id

  
  
END

SELECT * FROM @cmds

Save the two result sets to file Restore-constraints-indexes.sql. On the top of that file, add these lines:

USE   
go

Step three:

 SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) +   
          'DROP CONSTRAINT ' + quotename(cc.name)  
   FROM   sys.tables t  
   JOIN   sys.schemas s ON t.schema_id = s.schema_id  
   JOIN   sys.check_constraints cc ON cc.parent_object_id = t.object_id  
   ORDER  BY s.name, t.name, cc.name

Copy result to a query window and run. This drops all check constraints and filtered indexes/statistics.

Now, make the collation change:

   ALTER DATABASE YourDB COLLATE SQL_Latin1_General_CP1_CS_AS

If this completes successfully, open Restore-constraints-indexes.sqll and run the script.

If you have come so far without errors, run this query again:

SELECT o.type, c.collation_name, COUNT(*)  
FROM   sys.objects o  
JOIN   sys.columns c ON o.object_id = c.object_id  
WHERE  c.collation_name IS NOT NULL  
GROUP  BY o.type, c.collation_name  
ORDER BY o.type, c.collation_name

If you get any error messages, abort the operation at that point and restore the database to the original state.

Share any messages you get, as well as the output as final query as screenshots. (To give us a higher fidelity in what we are seeing.)

Answer

This is getting just as much a professional problem as a technical one.

It is quite clear that with your background, you are facing an impossible task, and I really feel sorry for you. I think you need to discuss with your manager. Maybe someone on higher level in your organization can yell at the vendor. Maybe someone understands your company needs to take in an external resource to help.

My friend and MVP colleague Hugo Kornelis faced a similar task a couple of years back. He prodcued a script which shared in a blog post. That script was good enough to solve his problem. Over the years, people have made additions and corrections to handle more cases. The latest version is on https://github.com/basroovers/sysops-scripts/blob/master/sql/change_collation.sql. You can try this on a copy of the database, but I fear that it spew out tons of errors because of the data.

Answer

USE master;  
GO  
   
ALTER DATABASE testDB  
COLLATE SQL_Latin1_General_CP1_CS_AS ;  
GO

Answer

Changing the collation of a database can be a very challenging operation.

If the database is modest in size, you can right-click the database in Object Explorer and select Tasks->Generate Scripts. When you come to the page with output options, select Advanced button and change Script Schema Only to Script Schema and Data. Also make sure that you include all objects in the lower sessions, like indexes and triggers etc.

Once you the script, run a find/replace to change the collation throughout. Also change the database name to a new database. Then run the script, and the new database will have the new collation.

This should if the database is only 1-2 GB, but it's not an option for a 100 GB database.

Beware that there are many things than go wrong when you run the script. If you are changing from case-insensitive to case-sensitive, there are probably quite a few queries that will break, because you have been inconsistent with case usage. Also, if you have foreign keys string keys, the FK relations may break because 'a' can no longer refer 'A'. Likewise can check constraints fire when the data is case sensitive. So you may have to do a lot of data cleaning to get things going.

Answer

@Jingyang Li - thanks for your reply. I had tried those commands initially but it did not work. The system won't allow me to change it.

@Erland Sommarskog - thanks for your reply. As it is a production DB that needs the change, my worry is that things will break. Also, your suggestion of creating and running a DB creation script means that a new DB will be created. Does this mean that the existing DB needs to be renamed to "old" then rename the new new DB to the actual / "existing" DB name as applications are already running and pointing to this DB. Also do I need to take into consideration data export and import? You are right, it is certainly a painful process. :(

MS SQL - amending collation

6 answers