Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Collation Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 4:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 21, 2016 4:01 AM
Points: 2,211, Visits: 2,756
How can I change the collation for all columns of a table?


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1392366
Posted Tuesday, December 4, 2012 4:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 4, 2013 6:26 AM
Points: 99, Visits: 402
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
Posted Tuesday, December 4, 2012 4:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 21, 2016 4:01 AM
Points: 2,211, Visits: 2,756
when i tried to change collation for a column I got this error--
Expression type tinyint is invalid for COLLATE clause.



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1392373
Posted Tuesday, December 4, 2012 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 1, 2016 2:56 AM
Points: 5,969, Visits: 6,067
@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
When a question, really isn't a question - Jeff Smith
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
Posted Tuesday, December 4, 2012 4:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 21, 2016 4:01 AM
Points: 2,211, Visits: 2,756
@anthony,
how can we change the collation of a table?



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1392381
Posted Tuesday, December 4, 2012 5:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 1, 2016 2:56 AM
Points: 5,969, Visits: 6,067
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
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 #1392384
Posted Tuesday, December 4, 2012 6:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 21, 2016 4:01 AM
Points: 2,211, Visits: 2,756
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..


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1392433
Posted Tuesday, December 4, 2012 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 1, 2016 2:56 AM
Points: 5,969, Visits: 6,067
Yes that is correct



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
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 #1392440
Posted Tuesday, December 4, 2012 6:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 14,469, Visits: 38,083
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1392452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse