Post reply

Collation Sequence

  • July 18, 2015 at 6:28 am

    #309953

    Hi All,

    I have two databases with different collation sequences, let them be called A (SQL_Latin1_General_CI_AS) and B (Latin1_General_CI_AS). Now I need to join between the two (including through temp tables with server collation being Latin1_General_CI_AS). In order to get rid of the errors when trying to do so I changed all my statements in the WHERE and ON clauses to

    A.table.column collate database_default = B.table.column

    If I were to change the collation of the character typed columns in database A to the one in database B, would it make any difference in terms of performance, or would it just be a useless exercise? Just asking because many of those columns are part of a primary or foreign key that would need to first be dropped and then recreated after changing the collation, and I'd prefer to save myself the effort of writing scripts to do so if the answer is NO.

    BTW, I tried to change the database's collation sequence, but that leaves the collation of the columns unchanged. Would have been a quick solution, but allas it doesn't do what I need it to do.

    Thanks in advance,

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • July 20, 2015 at 4:38 am

    #1813615

    Jan,

    Changing the database collation (as far as I know) only goes forward for new tables and possibly new columns.

    I'm not sure about the rest, really. But if you're going so far as to change the database collation, why not just do some dynamic sql to change all your current table collations? I'm assuming that there's no reason you can't, since you were trying to change the collation on the database level to begin with.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 20, 2015 at 5:28 am

    #1813619

    Thanks, Brandie. I found a script on this site (http://www.sqlservercentral.com/scripts/Collation/113863/) that actually takes care of converting all the character and text based columns to a different collation. Quite handy. I haven't had a chance to compare performance yet, but will come back once I have a definite conclusion.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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