Collation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Collation

Collation

Rate Topic

Display Mode

Topic Options

Author

Message

kapil_kk

Posted Tuesday, December 04, 2012 4:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

How can I change the collation for all columns of a table?

Post #1392366

memymasta

Posted Tuesday, December 04, 2012 4:42 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:15 AM
Points: 99, Visits: 399

Collations cannot be changed specifically for columns, they effect the whole DB.

But you can temporarily change them like this:

SELECT top 10  firstname COLLATE SQL_Latin1_General_CP1_CI_AS FROM users

Post #1392371

kapil_kk

Posted Tuesday, December 04, 2012 4:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

when i tried to change collation for a column I got this error--
Expression type tinyint is invalid for COLLATE clause.

Post #1392373

anthony.green

Posted Tuesday, December 04, 2012 4:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

@kapil_kk

numeric columns dont have a collation.

@memymasta

collation can be changed at the server, database or column level depending what you want to do

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392379

kapil_kk

Posted Tuesday, December 04, 2012 4:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

@anthony,
how can we change the collation of a table?

Post #1392381

anthony.green

Posted Tuesday, December 04, 2012 5:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

My bad, you cant change the table collation, that should of been the database level, to alter a table you can change column collations

Drop all indexes on any columns which are string data types

Change all existing columns using ALTER TABLE .... ALTER COLUMN ... COLLATE ...

Re-create all indexes.

Post #1392384

kapil_kk

Posted Tuesday, December 04, 2012 6:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

if 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

Post #1392433

anthony.green

Posted Tuesday, December 04, 2012 6:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Yes that is correct

Post #1392440

Lowell

Posted Tuesday, December 04, 2012 6:52 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 11:35 AM
Points: 11,646, Visits: 27,750

it 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.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1392452

« Prev Topic | Next Topic »

Permissions