InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 13:22:44 GMT20http://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxit also gets a lot more difficult if you have any constraints on any of those 50 columns...unique constraints, indexes, defaults, etc could prevent the ALTER command from completing.... all those constraints have to be dropped first, then change the collation, and then recreate those indexes and constraints after you change the collation.Tue, 04 Dec 2012 06:52:49 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxYes that is correctTue, 04 Dec 2012 06:42:02 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxif i have 50 columns in a table of datatype varchar then do i need to manually change the collation using ALTER for each column..:w00t:Tue, 04 Dec 2012 06:36:28 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxMy bad, you cant change the table collation, that should of been the database level, to alter a table you can change column collationsDrop all indexes on any columns which are string data typesChange all existing columns using ALTER TABLE .... ALTER COLUMN ... COLLATE ...Re-create all indexes.Tue, 04 Dec 2012 05:02:38 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspx@anthony,how can we change the collation of a table?Tue, 04 Dec 2012 04:54:34 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspx@kapil_kknumeric columns dont have a collation.@memymastacollation can be changed at the server, database or column level depending what you want to doTue, 04 Dec 2012 04:53:24 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxwhen i tried to change collation for a column I got this error--Expression type tinyint is invalid for COLLATE clause.Tue, 04 Dec 2012 04:46:31 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxCollations cannot be changed specifically for columns, they effect the whole DB.But you can temporarily change them like this:[code="sql"]SELECT top 10 firstname COLLATE SQL_Latin1_General_CP1_CI_AS FROM users [/code]Tue, 04 Dec 2012 04:42:47 GMTmemymastahttp://www.sqlservercentral.com/Forums/Topic1392366-392-1.aspxHow can I change the collation for all columns of a table?Tue, 04 Dec 2012 04:30:53 GMTkapil_kk