SQL SERVER – Change Collation of Database Column – T-SQL Script

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

SQL SERVER - Change Collation of Database Column - T-SQL Script colcollation

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

Reference : Pinal Dave (http://www.SQLAuthority.com)

SQL Collation, SQL Scripts, SQL Server Security, SQL Utility
Previous Post
SQLAuthority News – Download – SQL Server 2005 Books Online (December 2008)
Next Post
SQLAuthority News – SQL Server Related Downloads from Microsoft

Related Posts

50 Comments. Leave new

  • James,

    I’m glad you got the issue sorted out. Incidentally, the code I posted above was only to reveal my email address. Nothing fancy, but it gets thru the spam filters, and I didn’t want any screen-scrapers to add me to their spam targets.

    For all others, I will see if I can find the time to patch up the code and make it a bit more generic. Then I’ll write it up and pass it on to Pinal for posting, so that people can grab it a little easier. I don’t have a blog, so I’ll drop the scripts here.

    Reply
  • Brian,

    Really looking forward to your post. I think it will be great additional for readers for this blog. I will post it with due credit.

    Kind Regards,
    Pinal

    Reply
  • Brian/Pinal – I’m really looking forward to that post, it will be most helpful. Thank you very much!

    Reply
  • Definitely something worthy, in the mean time Brian if possible can you email me a copy of the script to, urgently needed

    Reply
  • Hello again.
    Apologies for this taking as much time as it did to prepare, but I now have all of the documentation ready. Note, this is not a click-bang-done task. It’s a series of scripts that generate DDL which must be executed against the target machines. It uses DMO via OLE, so make sure your instances are not disabling the sp_oaCreate, sp_oaMethod, etc SP’s.

    Unfortunately, the documentation is only from my POV, and has NOT been beta tested against the masses.

    I am emailing the article to Pinal Dave along with the scripts, but I have no control over when he has the time to review and publish.

    For those of you that have urgent needs… you’ve got to realize, your email address is NOT posted, nor is mine. The back-end of Pinal’s blog filters out email address in posts. No way for me to know where to send. So, if time is of the essence, be a little diligent and refer to my posts on March 2nd and September 12th. My email address is obfuscated in the CONVERT() statement. Just copy and paste it in a Q/A window.

    Reply
  • Finally…
    Sorry it’s taken so long for me to get back around to this. But, good news… I finally did. I’ve an article that details the work, and you can also download all of the scripts.

    There are a couple of edits you’ll probably need to make to the individual scripts, for example, the collation you intend to use. The scripts have Latin1_General_CI_AS hard-coded.

    Read all the accompanying documentation. Hopefully it will answer any questions you have about how to implement these.

    And of course, if you have additional questions, my email is posted in the article.

    Here it is… Enjoy.

    Brian

    https://blog.sqlauthority.com/2009/10/19/sql-server-change-collation-of-database-column-t-sql-script-consolidating-collations-extention-script/

    Reply
  • Hi,

    Is it possible to set a collation sequence on a schema so all tables within that schema are case-sensitive, whereas rest of database is case-insensitive?

    Cheers
    Gordon

    Reply
  • Hi
    please i got a problem, i got 2 apps MS Dynamics CRM and MS Dynamics GP
    but CRM is in Modern_Spanish_CI_AI
    and GP in SQL_Latin1_General_CP1_CI_AS
    i want to do a query like this
    SELECT * FROM TABLE_IN_GP WHERE ID NOT IN ( SELECT ID FROM LINKEDSERVER.CRM_DB.dbo.TABLE_IN_CRM )

    but i got this error
    Cannot resolve the collation conflict between “Modern_Spanish_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

    i even put at the end COLLATE SQL_Latin1_General_CP1_CI_AS

    but i still got the same error
    it’s not posible to change the collations of the DBs

    could you help me please

    thanks a lot

    Reply
  • FAC,
    Is the COLLATE statement inside or outside the parentheses? It should be inside.
    If that doesn’t fix it, try troubleshooting by minimizing your select criteria. Try just a single column from the GP table.

    Reply
    • Hi Brian
      thanks for your response

      i modified the query like this

      SELECT ITEMNMBR
      FROM IV00101
      WHERE
      ITEMNMBR NOT IN ( select productnumber from CRMLINKED.CRMDB.dbo.ProductBase where productnumber COLLATE SQL_Latin1_General_CP1_CI_AS )

      but still got the error
      Cannot resolve the collation conflict between “Modern_Spanish_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

      any other ideas please

      thanks a lot

      Reply
  • FAC:
    In the above query, are the fields ITEMNMBR and productnumber of a numeric type? The COLLATE clause will only work with character data types: char, nchar, varchar, nvarchar, text, ntext

    If they are string based types, try the following:

    SELECT ITEMNMBR
    FROM IV00101 i
    LEFT JOIN CRMLINKED.CRMDB.dbo.ProductBase p
    ON p.productnumber COLLATE SQL_Latin1_General_CP1_CI_AS = i.ITEMNMBR
    WHERE p.productnumber IS NULL

    Reply
  • Thanks, very very useful and It Works !

    Reply
  • Thanks Pinal

    Reply
  • If i change collation for entire database,it changed but table level collation not effected.Please suggest me to get the collation change in all tables in that database.
    I tried collation change using

    ‘alter database testCollationChange
    collate SQL_Latin1_General_CP1_CI_AS ‘

    Is anyother script?

    Reply
  • Kumar,

    This is a very complex process that, depending on the size of your database, could be impossible to do manually. Here’s what needs to be done:

    1. Identify all the columns which have incorrect collation values.
    2. Identify all objects bound to above columns, including indexes, keys, checks and UDF’s,
    3. Script out all of the above object DDL.
    4. Drop all of the objects.
    5. Update all the columns with the correct collation
    6. Recreate all of the dropped objects using the DDL generated in step 3

    Fortunately, I’ve written a set of scripts to do all this and have them documented and published here on the SQLAuthority site.

    The following page should provide you with everything you need.
    https://blog.sqlauthority.com/2009/10/19/sql-server-change-collation-of-database-column-t-sql-script-consolidating-collations-extention-script/

    Hope this helps.

    Reply
  • great, thanks and thanks google for showing this on top

    Reply
  • AnilKumar Jonnala
    July 27, 2011 1:52 pm

    Hi Brain ,

    Data stored in Sql Server 2005
    in a table the Column Consists Data in iscii Format how
    Can i Show that data in specifeid Language

    The Data was inserted using CDAC iplugin the Language Used is Telugu
    Waiting..;
    For Reply

    Reply
  • AnilKumar Jonnala
    July 27, 2011 1:58 pm

    Hi Brain ,

    Data stored in Sql Server 2005
    in a table the Column Consists Data in iscii Format how
    Can i Show that data in specifeid Language

    The Data was inserted using CDAC iplugin the Language Used is Telugu
    Waiting..;
    For Reply
    Data is visible in this format

    Reply
    • AnilKumar:
      Collations do not translate data from one language to another. They specify sort-order definitions. If the plugin you mention is capable of language translations, then you will need to consult its documentation.

      Sorry I cannot help you further.

      Reply
  • Nice Articles.

    Reply
  • Thank! Good articles

    Reply
  • I want to have all languages in my tables in a database. Currently when i insert the arabic characters, it is getting inserted as “?”. Let me know what collation to be used for all languages , for all tables and its syntax.

    Reply

Leave a Reply