Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

TSQL to change collation of database, whats new in SQL 2008 then? - SQL Server Knowledge Sharing Network (@sqlmaster)

TSQL to change collation of database, whats new in SQL 2008 then?

How can you change collation of a database?

How do you check the existing collation setup on a database & tables?

By using the below TSQL:

--For database 

select databasepropertyex(db_name(),'collation') as collation_name
go

--Tables 

select column_name, collation_name from INFORMATION_SCHEMA.COLUMNS
where table_name = 'TableName'

The generic answer you get is use ALTER DATABASE <DBName> COLLATE CollationName and so on. TO this affect only the inserted data after this statement execution will affect the data and not with existing data, in order to achieve that is to generate the database creation script using ENterprise Manager or Management Studio by replacing the existing collation  with the new name. Then export the tables database using BCP or DTS then drop the database (don't forget to take full database backup before), use the changed script to create the database and then use BCP or BULK INSERT to import the data. ALso this KBA325335 talks much about details into it.

Also you may be aware that you can have multiple collation set of columns within a table and I have referred the following TSQL script to change the COLLATE that is different to existing collation, the script it as follows:

declare  @toCollation sysname
     
SET    @toCollation = 'SQL_Latin1_General_CP1_CI_AS' --  Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
 and COLLATION_NAME <> @toCollation

Also be aware that it will work on default data types and not on user-defined database, in this case you have to drop and re-define them again, also ensure to drop the associated indexes & constraints that are stick to installation default such as case-sensitive.

So what's new within SQL Server 2008 version then, usual support for new collations that are associated with Windows 2008 and Windows VIsta. There are 84 new collations within the existing Windows collations within SQL Server 2005, with this they have added East Asian collations to support supplementary characters.

Also BOL defines that support for supplementary character string comparison based on linguistic sorting with new collations in SQL Server. The deprecated collations are Korean, Hindi, Macedonian, and Lithuanian_Classic Windows collations and deprecation of the SQL_ALTDiction_CP1253_CS_AS SQL collation. These collations were supported SQL Server 2005, and are supported in SQL Server 2008, but will not be displayed in the Setup collations list, and will not appear when the system function ::fn_helpcollations() is used to query the list of supported collations in SQL Server 2008.

 


Published Wednesday, March 12, 2008 8:10 AM by ssqa.net

Comments

# http://sqlserver-qa.net/blogs/t-sql/archive/2008/03/12/3770.aspx

Friday, March 14, 2008 12:46 PM by TrackBack