1

I'm trying to change collation on all our databases, and doing it on one of the databases (~15gb) took me 42 minutes in testing even though I put it on SSD disc. Do anyone know any way to speed it up? I don't want to have the production databases offline for hours unless I need to. I googled and searched around here without finding anything related to speeding up changing collation, I know its a one time thing that most companies probably do before their databases become big enough that its a performance problem, but still if someone have experience with speeding it up I'm very interested.

I'm using Redgate SQL Compare to generate statements that look like this:

ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Summary] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NOT NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Category] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [Contact] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL
ALTER TABLE [dbo].[tblCalendarEvent] ALTER COLUMN [RRule] [nvarchar] (255) COLLATE Finnish_Swedish_100_CI_AS NULL

Thanks!

  • You are changing from what collation to what ? What is your server collation and your database collation? – Kin Shah Nov 4 '14 at 14:28
  • 1
    I am pretty sure this is a size-of-data operation; meaning there is no magic to make it faster. You could consider creating a second table with the right collation in place, then you could possibly look at minimally logged operations to copy the data, which may help. But you'd still have to have the table offline in some way while the transfer is taking place. – Aaron Bertrand Nov 4 '14 at 14:42
  • @Aaron Bertrand yes it's related to size-of-data, I was able to run the same script in much shorter time when truncating / deleting data in the biggest tables. What I don't really understand is why changing collation would go in and modify each row, but I guess it is the alter column implemententation, probably something along the lines of create new column, copy all data there, remove old column. – Ztranger Nov 4 '14 at 19:41
  • Yes because collation affects all sorts of things (storage format and sorting, most importantly). – Aaron Bertrand Nov 4 '14 at 19:43
  • @Kin It's from Latin1_General_100_CI_AS to Finnish_Swedish_100_CI_AS. – Ztranger Nov 4 '14 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.