What is the meaning of: COLLATE SQL_Latin1_General_CP1_CI_AS

Just to avoid confusion between Windows Code Page and SQL Server CP
For ex.,
The SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS. For SQL Server 2000 default code page is Latin1_General_CI_AS.

Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause.

CP1 - Codepage 1

CI - Case Insensitive

AS - Accent Sensitive.

Lets say you are running a stored procedure (SP) that performed a cross database joins on our SQL Server 2000 box. This particular procedure was processing records from a table in one database (database A), that was originally created on our SQL Server 2000 box, while also processing records from a table in another database (database B), that was also on our SQL Server 2000 box, but was created by restoring a SQL Server 7.0 database backup. The SQL Server 7 machine had a different character set and sort order than our SQL Server 2000 database. The SELECT statement the SP was running was joining records from the table in database A with the table in database B on a varchar key field. Can you imagine what happened? The SP failed with a "collation error".

SQL Server 2000 finally solved the problem of taking database backups from servers with different character set / sort order and restoring them, without getting a collation error. Before SQL Server 2000, backups from one server could only be restored on a different server if the same character set and sort order where used by both servers. With SQL Server 2000 you are able to set the collating sequence at the server, database and even the column level. Now that you can have multiple collating sequences on a single database server, there are a few things you should understand about mixing collations.

When you have character data stored with different collation sequences, you need to be aware that comparing columns becomes more involved. Columns with the same collating sequences can be compared or joined without any additional considerations. Although when you compare columns that have different collating sequences it require some special coding considerations.

Related Posts: