Changing Code Page from SQL Server 7.0 to SQL Server 2000
Q
I've noticed that the SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS, but the SQL Server 2000 default code page is Latin1_General_CI_AS. This change makes a significant difference when I need to restore SQL Server 7.0 databases into SQL Server 2000. How can I get around this change?
A
SQL Server is moving away from the legacy, self-defined code page support and toward code pages matching the Microsoft® Windows code pages you choose when you set Windows locale IDs. For more information about code pages, see "How Character Data Is Stored" in SQL Server 2000 Books Online.
When you upgrade from SQL Server 7.0 to SQL Server 2000, you keep your existing sort order and code page by default. However, when you perform a clean installation of SQL Server 2000, you get the new sort order and code page. If you attach a database to a SQL Server 2000 instance, SQL Server keeps whatever the collation was on the database when you created it. In other words, you keep the SQL Server 7.0 code page if you attach a SQL Server 7.0 database to SQL Server 2000.
Because SQL Server is moving toward a new default code page, we recommend that, if you're upgrading by doing a clean installation and then attaching a database, you pick a collation at installation time that matches your SQL Server 7.0 collation. You can determine this collation by performing an upgrade, for example an upgrade of the pubs database, from your SQL Server 7.0 installation and then looking at the collation that results when you run a query using the SERVERPROPERTY (Collation) function.