SQL Server Collation Setting

Question

In the SQL server, the default collation is SQL_Latin1_General_CP1_CI_AS, previously, I used to specify the collation explicitly at the end of each query to SQL_Latin1_General_CP1_CI_AI to ignore the accents when comparing strings.

Now, I wanted to implement a generic solution, so that, I changed the database collation to SQL_Latin1_General_CP1_CI_AI but the queries are still not working unless I specify the collation explicitly! I searched a bit for this issue and I knew that the columns that were created previously using the old collation, have to be updated as well to the new collation or have to be dropped and recreated again!

Is there any idea to handle this issue without updating all columns one by one?

Here is a sample of the queries that I have With specifying the collation:

select * from Members where FirstName like '%cafe%' collate SQL_Latin1_General_CP1_CI_AI

Without specifying the collation:

select * from Members where FirstName like '%cafe%'

If your column is in a different collation then when the comparison occurs then that column's collation will be used. If your columns are in the wrong collation then changing their collation is the correct solution, however, that isn't as simple task as just changing them, as some dependant objects will need to be DROPed and reCREATEd. — Thom A
– Thom A ♦, Commented Aug 14, 2023 at 9:19
I just want the simplest way to ignore the accents when comparing the strings, that's it! If I couldn't find any simple way, I'll have to update all columns' collations — Ⲁⲅⲅⲉⲗⲟⲥ
– Ⲁⲅⲅⲉⲗⲟⲥ, Commented Aug 14, 2023 at 9:23
Changing the column's collation is what you want to do, as syntax using COLLATE isn't SARGable. — Thom A
– Thom A ♦, Commented Aug 14, 2023 at 9:26

Charlieface · Accepted Answer · 2023-08-15 10:12:26Z

0

You can generate a script to change the collation of all columns in all tables using the following code:

DECLARE @fromCollation sysname = 'collation_name_here', @toCollation sysname = 'collation_name_here';

DECLARE @sql nvarchar(max);

SELECT
  CONCAT(
    'ALTER TABLE ',
    QUOTENAME(s.name),
    '.',
    QUOTENAME(t.name),
    ' ALTER COLUMN ',
    QUOTENAME(c.name),
    ' ',
    typ.name,
    '(',
    c.max_length,
    ') COLLATE ',
    @toCollation,
    IIF(c.is_nullable = 1, ' NULL', ' NOT NULL')
  )
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types typ ON typ.user_type_id = c.user_type_id
WHERE c.collation_name = @fromCollation
  AND typ.name IN ('varchar', 'nvarchar', 'char', 'nchar');

If you have any non-clustered indexes, or any schema-bound functions or views, you would need to drop and re-create them.

edited Aug 15, 2023 at 10:12

answered Aug 14, 2023 at 16:19

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

siggemannen Over a year ago

That's usually far from enough to just change the column collations because indexes, inline functions etc might depend on the columns being changes.

Charlieface Over a year ago

For functions only if they have WITH SCHEMABINDING otherwise it works fine to just change it.

Ⲁⲅⲅⲉⲗⲟⲥ Over a year ago

@Charlieface, unfortunately, this solution doesn't fit as we have many indexes related to the columns we need to update!

Charlieface Over a year ago

Probably easeist to just use a tool to script all the indexes and constraints as drop/create

Paul de Kock · Accepted Answer · 2025-03-25 09:25:09Z

I know this is an old post, but for those who are curious, if one does not want to follow the normal route and rebuild your dataset to get all data and columns converted, here is a script to allow you to backup your indexes, keys and constraints and convert all tables and columns and restore your keys, indexes and constraints. Note, this should always only be done in testing environment before attempting any production runs.

USE [PUT DATABASE NAME HERE];
GO

DECLARE @DBName NVARCHAR(255) = '[PUT DATABASE NAME HERE]';
DECLARE @NewCollation NVARCHAR(255) = 'Latin1_General_CI_AS';

-- Create temporary tables to store constraints, indexes, and foreign keys
IF OBJECT_ID('tempdb..#Constraints') IS NOT NULL DROP TABLE #Constraints;
CREATE TABLE #Constraints (
    TableName NVARCHAR(128),  -- Use NVARCHAR(128) instead of sysname
    ConstraintName NVARCHAR(128),
    ConstraintSQL NVARCHAR(MAX)
);

IF OBJECT_ID('tempdb..#Indexes') IS NOT NULL DROP TABLE #Indexes;
CREATE TABLE #Indexes (
    TableName NVARCHAR(128),  -- Use NVARCHAR(128) instead of sysname
    IndexName NVARCHAR(128),
    IndexSQL NVARCHAR(MAX)
);

-- Create a temporary table to log errors
IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL DROP TABLE #ErrorLog;
CREATE TABLE #ErrorLog (
    Step NVARCHAR(100),
    ErrorMessage NVARCHAR(MAX),
    ErrorTime DATETIME DEFAULT GETDATE()
);

-- Change the database collation
BEGIN TRY
    EXEC('ALTER DATABASE ' + @DBName + ' COLLATE ' + @NewCollation);
END TRY
BEGIN CATCH
    INSERT INTO #ErrorLog (Step, ErrorMessage) VALUES ('Database Collation Change', ERROR_MESSAGE());
END CATCH

-- Capture primary keys and unique constraints (including the SQL to recreate them)
INSERT INTO #Constraints
SELECT 
    OBJECT_NAME(k.parent_object_id) AS TableName, 
    k.name AS ConstraintName,
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(k.parent_object_id)) + 
    ' ADD CONSTRAINT ' + QUOTENAME(k.name) + ' PRIMARY KEY (' + STUFF((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 = k.parent_object_id AND ic.index_id = k.unique_index_id
                  ORDER BY ic.key_ordinal
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
FROM sys.key_constraints k
WHERE k.type = 'PK'  -- Primary keys

-- Capture unique constraints (same logic for recreating them)
INSERT INTO #Constraints
SELECT 
    OBJECT_NAME(k.parent_object_id) AS TableName, 
    k.name AS ConstraintName,
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(k.parent_object_id)) + 
    ' ADD CONSTRAINT ' + QUOTENAME(k.name) + ' UNIQUE (' + STUFF((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 = k.parent_object_id AND ic.index_id = k.unique_index_id
                  ORDER BY ic.key_ordinal
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
FROM sys.key_constraints k
WHERE k.type = 'UQ'  -- Unique constraints

-- Capture all indexes (Clustered, Non-Clustered, Unique, Non-Unique)
INSERT INTO #Indexes
SELECT 
    OBJECT_NAME(i.object_id) AS TableName, 
    i.name AS IndexName,
    'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 
    i.type_desc + ' INDEX ' + QUOTENAME(i.name) + 
    ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) +
    ' (' + STUFF((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 = i.object_id AND ic.index_id = i.index_id
                  ORDER BY ic.key_ordinal
                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
FROM sys.indexes i
WHERE OBJECT_SCHEMA_NAME(i.object_id) = 'dbo' 
  AND i.type_desc IN ('NONCLUSTERED', 'CLUSTERED', 'XML')  -- Including all types of indexes
  AND i.is_primary_key = 0  -- Exclude primary keys
  AND i.is_unique_constraint = 0  -- Exclude unique constraints

-- Drop primary keys, unique constraints, and indexes (Clustered, Non-Clustered, Unique, Non-Unique)
DECLARE @DropConstraintsSQL NVARCHAR(MAX) = '';
DECLARE @DropIndexesSQL NVARCHAR(MAX) = '';

-- Drop primary keys and unique constraints
SELECT @DropConstraintsSQL = @DropConstraintsSQL + 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(k.parent_object_id)) + 
    ' DROP CONSTRAINT ' + QUOTENAME(k.name) + ';' + CHAR(10)
FROM sys.key_constraints k
WHERE k.type IN ('PK', 'UQ')  -- Primary keys and Unique constraints
  AND OBJECT_SCHEMA_NAME(k.parent_object_id) = 'dbo';

-- Drop all indexes (Clustered, Non-Clustered, Unique, Non-Unique)
SELECT @DropIndexesSQL = @DropIndexesSQL + 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';' + CHAR(10)
FROM sys.indexes i
WHERE OBJECT_SCHEMA_NAME(i.object_id) = 'dbo'
  AND i.type_desc IN ('NONCLUSTERED', 'CLUSTERED', 'XML')  -- Including all types of indexes
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0;

-- Execute drop constraints and indexes
BEGIN TRY
    EXEC(@DropConstraintsSQL);
    EXEC(@DropIndexesSQL);
END TRY
BEGIN CATCH
    INSERT INTO #ErrorLog (Step, ErrorMessage) VALUES ('Drop Constraints and Indexes', ERROR_MESSAGE());
END CATCH;

-- Alter column collations (for dbo schema)
DECLARE @collate NVARCHAR(100);
DECLARE @table NVARCHAR(255);
DECLARE @column_name NVARCHAR(255);
DECLARE @column_id INT;
DECLARE @data_type NVARCHAR(255);
DECLARE @max_length INT;
DECLARE @row_id INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @sql_column NVARCHAR(MAX);
DECLARE @is_nullable BIT;

SET @collate = @NewCollation; -- 'Latin1_General_CI_AS'; --

DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR
    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name AS column_name
        , t.name AS data_type
        , c.max_length
        , c.column_id
        , c.is_nullable
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@table)
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF (@max_length = -1) OR (@max_length > 4000) 
            SET @max_length = 4000;

        -- Preserve the nullability of the column
        IF (@data_type LIKE '%char%' OR @data_type LIKE '%text%')
        BEGIN
            -- Check if column is nullable or not and preserve it
            IF @is_nullable = 1
            BEGIN
                SET @sql = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + 
                           '(' + CAST(@max_length AS NVARCHAR(100)) + ') COLLATE ' + @collate + ' NULL';
            END
            ELSE
            BEGIN
                SET @sql = 'ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + 
                           '(' + CAST(@max_length AS NVARCHAR(100)) + ') COLLATE ' + @collate + ' NOT NULL';
            END

            PRINT @sql -- For debugging: Output the generated SQL
            BEGIN TRY
                EXEC sp_executesql @sql
            END TRY
            BEGIN CATCH
                PRINT 'ERROR: Some index or constraint relies on the column ' + @column_name + '. No conversion possible.'
                PRINT @sql
            END CATCH
        END

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable

    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

-- After changing column collations, recreate primary keys, unique constraints, and indexes
DECLARE @RecreateConstraintsSQL NVARCHAR(MAX) = '';
DECLARE @RecreateIndexesSQL NVARCHAR(MAX) = '';

-- Recreate primary keys and unique constraints
SELECT @RecreateConstraintsSQL = @RecreateConstraintsSQL + ConstraintSQL + ';' + CHAR(10)
FROM #Constraints;

-- Recreate all indexes (Clustered, Non-Clustered, Unique, Non-Unique)
SELECT @RecreateIndexesSQL = @RecreateIndexesSQL + IndexSQL + ';' + CHAR(10)
FROM #Indexes;

-- Execute recreate constraints and indexes
BEGIN TRY
    PRINT 'Recreating Constraints:';
    PRINT @RecreateConstraintsSQL; -- Debugging output to see the SQL
    EXEC(@RecreateConstraintsSQL);

    PRINT 'Recreating Indexes:';
    PRINT @RecreateIndexesSQL; -- Debugging output to see the SQL
    EXEC(@RecreateIndexesSQL);
END TRY
BEGIN CATCH
    INSERT INTO #ErrorLog (Step, ErrorMessage) VALUES ('Recreate Constraints and Indexes', ERROR_MESSAGE());
END CATCH;

-- Display error log
SELECT * FROM #ErrorLog;

-- Cleanup
DROP TABLE #ErrorLog;
DROP TABLE #Constraints;
DROP TABLE #Indexes;

Collectives™ on Stack Overflow

SQL Server Collation Setting

Is there any idea to handle this issue without updating all columns one by one?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Is there any idea to handle this issue without updating all columns one by one?

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related