Collation Changes in SQL Server

I'm running SQL Server 7.0 on Windows Server 2000, and I'd like to change the SQL Server code page (collation) to 950. Is that possible?

Unfortunately, SQL Server 7.0 doesn't provide a mechanism for changing collations on the fly. To change the collation of an existing database, you need to rebuild the master database or reinstall SQL Server. You can find information about the rebuild process in SQL Server Books Online (BOL) in "How to rebuild the master database (Rebuild Master utility)." Remember, rebuilding the master database means recreating all logins. And in SQL Server 7.0, you can't reattach a database that has a different collation than the server. You'll need to use another mechanism, such as Data Transformation Services (DTS) or bulk copy program (BCP), to bring the data back in. The process isn't much different than starting from scratch with a complete reinstall.

Keep in mind that SQL Server 2000 has more flexibility for managing collations. You can change the default collation of a database after it's been installed and change or set collations at the column level for existing tables. If you've been thinking about upgrading to SQL Server 2000, collation flexibility might be the final reason to take the plunge. Note that changing the default collation of a database in SQL Server 2000 defines the collation of newly created objects but doesn't automatically change the collations of existing objects. You need to use the ALTER TABLE command to change the collation of an existing column. You can't change the collation of an entire table with one command, but you can change the collation for all columns within the table.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.