Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Change the COLLATION of a SQL Server Column


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > Query Optimization


Share your SQL Server knowledge and make some money too!


Problem

You've installed SQL Server with all the defaults, created your database and schema then loaded a bunch of data into your database. Now you realize that some or all of the columns in your database needed to have the COLLATION set to be case sensitive. This tip will take you through the steps required to change the column COLLATION of your tables from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.

Solution

Table Setup

Let's first setup a couple of tables in order to walkthrough these steps required to update the column collation. The following script will create two tables as well as some constraints, indexes and statistics in order to illustrate each of the steps that may be required depending on the design of your schema. For this example we won't add any data to our tables as it wouldn't have any effect on the update process since we are going from case insensitive to case sensitive. It's important to note that if you were performing the reverse update that is from case sensitive to case insensitive, you would also have an extra step of resolving any data issues as the update could result in duplicate data in columns with unique constraints. That said, here is the complete setup script listing.

CREATE TABLE dbo.MainTable
 (PKColumn int NOT NULL IDENTITY (1, 1),
  CharColumn char(10) NULL,
  NCharColumn nchar(10) NULL,
  VarcharColumn varchar(50) NULL,
  NVarcharColumn nvarchar(50) NULL,
  VarcharMaxColumn varchar(MAX) NULL,
  NVarcharMaxColumn nvarchar(MAX) NULL,
  ComputedColumn  AS CharColumn + VarcharColumn)
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT PK_MainTable PRIMARY KEY CLUSTERED (PKColumn)
GO

CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO

CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO

CREATE TABLE dbo.FKTable
 (CharColumn char(10) NOT NULL,
  DataColumn int NULL)
GO

ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO
Identify columns

Depending on your situation you may need to only change the collation of a few columns or possible every column in your database. You can use the following query which will list any columns in your database that have the default collation.

SELECT t.name "Table Name",
       c.name "Column Name",
       c.collation_name "Collation"
  FROM sys.tables t INNER JOIN
       sys.columns c ON c.object_id=t.object_id INNER JOIN
       sys.types s ON s.user_type_id=c.user_type_id
 WHERE c.collation_name LIKE 'SQL_Latin1_General_CP1_CI_AS'
   AND t.type like 'U'
   AND t.name not like 'spt%'
   AND t.name not like 'MSrep%'

For this tip let's assume that we are changing the collation of every column in our database. If you read the following link, Set or Change the Column Collation, from TechNet you'll notice that there are some restrictions on when you can change the collation of a column. Basically, if a column is referenced by any of the following objects you will not be able to change the collation without first removing these references.

  • A computed column
  • An index
  • Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
  • A CHECK constraint
  • A FOREIGN KEY constraint
Identify referencing objects

Let's now identify which objects need to be removed before we can make the collation update to our columns. The following queries, one for each category listed above, will identify objects that have a dependency on a column that we are going to be updating. Since we are updating all the columns in the database the only filter I have is "WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'". If your requirements are different you can expand on this as required. I've also include a sample output after each query for reference. One other item to note is with the foreign key section. We need to remove the referenced primary key index in order to make the collation update to that column as well so that the section includes two queries. One to identify the foreign key and the other for the primary key.

-- computed columns
SELECT OBJECT_NAME(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.computed_columns cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id AND 
                                  cc.column_id=sd.column_id AND 
                                  sd.object_id=sd.referenced_major_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND sd.class=1

Table Name Column Name Collation Definition
MainTable CharColumn SQL_Latin1_General_CP1_CI_AS ([CharColumn]+[VarcharColumn])
MainTable VarcharColumn SQL_Latin1_General_CP1_CI_AS ([CharColumn]+[VarcharColumn])

--foreign keys
SELECT f.name "Foreign Key Name",
       OBJECT_NAME(f.parent_object_id) "Table Name",
       COL_NAME(fc.parent_object_id,fc.parent_column_id) "Column Name",
       c1.collation_name "Collation",
       OBJECT_NAME (f.referenced_object_id) "Reference Table Name",
       COL_NAME(fc.referenced_object_id,fc.referenced_column_id) "Reference Column Name",
       c2.collation_name "Collation"
  FROM sys.foreign_keys AS f INNER JOIN
       sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN
       sys.columns c1 ON c1.object_id=fc.parent_object_id AND
                         c1.column_id=fc.parent_column_id INNER JOIN
       sys.columns c2 ON c2.object_id=fc.parent_object_id AND
                         c2.column_id=fc.parent_column_id
 WHERE c1.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
    OR c2.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

-- primary keys
SELECT i.name AS "Primary Key Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE i.is_primary_key=1
   AND c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

Foreign Key Name Table Name Column Name Collation
FK_MainTable_FKTable MainTable CharColumn SQL_Latin1_General_CP1_CI_AS
Reference Table Name Reference Column Name Collation
FKTable CharColumn SQL_Latin1_General_CP1_CI_AS

Primary Key Name Table Name Column Name Collation
PK_FKTable FKTable CharColumn SQL_Latin1_General_CP1_CI_AS

--indexes
SELECT i.name AS "Index Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND i.is_primary_key <> 1
   AND OBJECT_NAME(ic.object_id) NOT LIKE 'sys%'

Index Name Table Name Column Name Collation
IX_Table_NVarcharColumn MainTable NVarcharColumn SQL_Latin1_General_CP1_CI_AS

-- statistics
SELECT s.name "Statistics Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(c.object_id,c.column_id) "Column Name",
    c.collation_name "Collation"
  FROM sys.stats s INNER JOIN
       sys.stats_columns sc on s.stats_id=sc.stats_id AND 
                               s.object_id=sc.object_id INNER JOIN
       sys.columns c ON c.object_id=sc.object_id AND 
                        c.column_id=sc.column_id 
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND s.user_created=1
   AND OBJECT_NAME(c.object_id) NOT LIKE 'sys%'
   AND OBJECT_NAME(c.object_id) NOT LIKE 'MSrep%'

Statistics Name Table Name Column Name Collation
Stats_MainTable_VarcharMaxColumn MainTable VarcharMaxColumn SQL_Latin1_General_CP1_CI_AS

-- check constraints
SELECT OBJECT_NAME(cc.object_id) "Constraint Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.check_constraints cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND cc.type = 'C'
   AND sd.class=1

Constraint Name Table Name Column Name Collation
CK_Table_NVarCharMaxColumn MainTable NVarcharMaxColumn SQL_Latin1_General_CP1_CI_AS
Definition
([NVarCharMaxColumn]='Banana' OR [NVarCharMaxColumn]='Orange' OR [NVarCharMaxColumn]='Pear' OR [NVarCharMaxColumn]='Apple')

Drop referencing objects

Now that we've identified everything that we need to remove we can use any tool, including SSMS, to create drop scripts for these objects. Remember to at the same time create a script that will recreate these objects so we can run that after our column definition update is done. Here is the script to drop the objects identified above.

ALTER TABLE dbo.MainTable DROP COLUMN ComputedColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT FK_MainTable_FKTable
ALTER TABLE dbo.FKTable DROP CONSTRAINT PK_FKTable
DROP INDEX dbo.MainTable.IX_Table_NVarcharColumn
DROP STATISTICS dbo.MainTable.Stats_MainTable_VarcharMaxColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT CK_Table_NVarCharMaxColumn

Update the collation

After all that work we can now issue simple ALTER TABLE ... ALTER COLUMN ... statements to update the collation for each column in our database. Here is the script to perform this operation.

ALTER TABLE dbo.MainTable
 ALTER COLUMN NCharColumn nchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.FKTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharColumn varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN NVarcharColumn nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharMaxColumn varchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable 
 ALTER COLUMN NVarcharMaxColumn nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

Recreate referencing objects

Once the schema update completes we can run the script we generated above to recreate all of the referencing objects that we dropped earlier. Here is the script for our example.

ALTER TABLE dbo.MainTable ADD ComputedColumn AS CharColumn + VarcharColumn
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO
CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO
CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO
ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, February 01, 2017 - 11:36:45 PM - doctorgu Back To Top

I think COL_NAME(cc.object_id, cc.column_id) for computed columns also needed.

Because I need original column for generating drop and add statement.

 


Tuesday, February 24, 2015 - 8:38:18 PM - Ben Snaidero Back To Top

@Johnny

There are many tools you could use to generate the scripts to generate drop and recreate statements constraints and indexes, including just using TSQL.  I also mention in the article a link http://www.mssqltips.com/sqlservertip/1503/options-for-scripting-sql-server-database-objects/

Thanks for reading.


Tuesday, February 24, 2015 - 4:39:58 PM - Johnny Back To Top

Thanks !  Your check SQL scripts are very good.  However, I have few hundreds of Primary key constraints and indexes.  If I go through to generate DROP/CREATE scripts, it would be big task.  Is it possible to have scripts to generate DROP/CREATE scripts, save the results and run them ?


Learn more about SQL Server tools