Remove the collation of a column in SQL Server

Question

How can I remove the collation of a column after it has been set?

The standard collation of a column in the SQL Server is "NULL". It means, the collation of the database is used.

The TSQL-Statement ALTER table ALTER column COLLATE database_default will explicit set the collation of the column to the one of the database, but it doesn't restore the original state of no collation (NULL collation).

Can we really drop the collation?

So what's the difference between `NULL` meaning database default, and the `database_default` then?? — marc_s, Dec 18 '12 at 10:19
The difference appears in queries of sys.columns and by changing the database collation itself. — Olivier Faucheux, Dec 18 '12 at 10:28

score 1 · Accepted Answer · answered Dec 18 '12 at 10:34

1

No, you can't drop the collation of a column or even of a database.

1: If you do not specify any collation, the columns will use the default collation of the database.

2: If no collation is specified by the database, then the server collation is used.

answered Dec 18 '12 at 10:34

András Ottó

7,605
1
28
38

1

Confirmed this, sort of sucks. HOWEVER if you use the designer in SQL Manager you can select the text column you want to reset, click the "..." button in the "Collation" property grid, in the popup window select "Restore Default". The SQL Manager will script all the DROP + CREATE commands to essentially reset it to the NULL state by recreating the column. IF that is acceptable to your specific case. – DarrenMB Sep 05 '14 at 15:42

score 0 · Answer 2 · edited Aug 08 '23 at 00:43

You can restore the COLLATION of a COLUMN back to the database default; you simply use database_default as the collation name you want:

ALTER TABLE someTableName ALTER COLUMN someColumnName [nvarchar](255) COLLATE database_default`

I am using this simple script to generate all the SQL statements. I have seen other scripts that create the statements and execute them, but they were for specific named collations.

SELECT   TABLE_NAME,
         COLUMN_NAME, 
         COLLATION_NAME, 
         CONCAT('ALTER TABLE ',TABLE_NAME ,' ALTER COLUMN ', COLUMN_NAME ,' [',DATA_TYPE,'](',CHARACTER_MAXIMUM_LENGTH,') 
COLLATE  database_default') AS theScripts
FROM     information_schema.columns 
WHERE    COLLATION_NAME IS NOT NULL 
  AND    collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
  AND    data_type LIKE '%char%'

`ALTER table ALTER column COLLATE database_default` was specifically mentioned in the question and doesn't do what the question asker wants (though why they want that I have no idea) — Martin Smith, Aug 04 '23 at 11:09

Remove the collation of a column in SQL Server

2 Answers2