Post reply

Collation

  • December 4, 2012 at 4:30 am

    #285719

    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/

  • December 4, 2012 at 4:42 am

    #1565223

    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

  • December 4, 2012 at 4:46 am

    #1565225

    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/

  • December 4, 2012 at 4:53 am

    #1565230

    @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

  • December 4, 2012 at 4:54 am

    #1565232

    @anthony-2,

    how can we change the collation of a table?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 5:02 am

    #1565233

    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.

  • December 4, 2012 at 6:36 am

    #1565277

    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:

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • December 4, 2012 at 6:42 am

    #1565284

    Yes that is correct

  • December 4, 2012 at 6:52 am

    #1565295

    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!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply