Unicode and Collation in SQL

Collation at four levels:

1. Server. 
2. Database. Every database has a collation (default is the server collation). the particular collation needs to be present on the operating system as well.
3. Column. Overrides database level collation.
4. Expression. Can be used to override any other collation, uses the COLLATE keyword.

--Querying the server collation:

SELECT CONVERT(char, SERVERPROPERTY('collation'))

--Querying the DB collation:

SELECT DATABASEPROPERTYEX(db_name(), 'Collation') SQLCollation

--Find Collation of a Table Column:

SELECT name, collation_name FROM sys.columns

WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'History')

AND name = 'AssignmentName'

Non-Unicode to Unicode Data conversion:

• Use unicode (n*) data type (nvarchar, nchar,  ntext) for multi-language support.
• Defining Parameters in Stored Procedures with a Unicode data type.
• Using the N Prefix - @name = N'Chain'.

Points to remember before Non-Unicode to Unicode Data conversion:

• The storage size of the columns grows to twice size.
• indexes, constraints and triggers on the columns have to be removed and recreated.
• If your tables are large it takes its time.

• Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales
• Collation settings doesn't affect the data stored in columns of unicode data type, so - as long as you remember about N' prefix - collation is important only according to sort order.
• the collation impacts data comparision tests.
• In SQL Server 2000, you use the ntext data type instead of nvarchar(max).
• The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar. In those cases, replace to nchar(max), nvarchar(max).
• The Unicode types (distinguished by the "n" in front of the type name) uses twice as many bits (two, in fact) to store the data. So - it also need to be concerned about how much more storage you'll need with Unicode data

More useful selects:

-- Get a list of all the collations supported by SQL Server:

SELECT Name, Description FROM fn_helpcollations()

-- get not unicode columns

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME, DATA_TYPE ,ORDINAL_POSITION, COLUMN_DEFAULT , IS_NULLABLE

from information_schema.columns

where DATA_TYPE in ('varchar', 'char', 'text')

order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

Collation Types

http://msdn.microsoft.com/en-us/library/ms175194.aspx

1. Windows Collations, http://msdn.microsoft.com/en-us/library/ms188046.aspx

2. SQL Server Collations, http://msdn.microsoft.com/en-us/library/ms144260.aspx

Get a property of a specified collation:

COLLATIONPROPERTY(collation_name,property ):

SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage') --result: 1252 

http://msdn.microsoft.com/en-us/library/ms190305.aspx

Links:

Articles:

Unicode and SQL Server, good introduction to Unicode and collation

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=275

Implementation of Unicode in SQL Server

http://www.codeproject.com/KB/reporting-services/UnicodeInSQLServer.aspx

MSDN:

International Considerations for Databases and Database Engine Applications:

http://msdn.microsoft.com/en-us/library/ms190245.aspx

Server-Side Programming with Unicode:

http://msdn.microsoft.com/en-us/library/ms191313.aspx

SQL Server Collation Names:

http://msdn.microsoft.com/en-us/library/ms180175.aspx

Forums:

Any thoughts on collation settings for an International database?

http://www.eggheadcafe.com/software/aspnet/29255888/unicode-database-collation.aspx