Database

Changing Collations

You can change the collation of a column by using the ALTER TABLE statement:

CREATE TABLE MyTable (
   PrimaryKey   int PRIMARY KEY,
   CharCol      varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable 
   ALTER COLUMN CharCol varchar(10)
   COLLATE Latin1_General_CI_AS NOT NULL
GO

• A computed column.
• An index.
• Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
• A CHECK constraint.
• A FOREIGN KEY constraint.

ALTER DATABASE MyDatabase COLLATE French_CI_AS

• The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
• Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
• All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
• The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.

• Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.
• Export all of your data using a tool such as bulk copy.
• Drop all of the user databases.
• Rebuild the master database specifying the new collation.
• Create all of the databases and all of the objects in them.
• Import all of your data.

SELECT SERVERPROPERTY('Collation')

SELECT DATABASEPROPERTYEX('Northwind', 'Collation')

SELECT TOP 1 SQL_VARIANT_PROPERTY(Column, 'Collation') FROM Table

SqlString = "SELECT * FROM Accounts ";
SqlString += "WHERE (Nick = '"; 
SqlString += tNick;
SqlString += "')";

SqlString = "SELECT * FROM Accounts WHERE (Nick = 'tNick');