DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to change the collation of a column?

The following Microsoft SQL Server T-SQL scripts demonstrate how to change and use the collation of a database table column:

-- SQL Server Change Column Collation - QUICK SYNTAX

SELECT * INTO Product FROM AdventureWorks2008.Production.Product

ALTER TABLE Product ALTER COLUMN Name nvarchar(50)

COLLATE SQL_Latin1_General_CP1_CS_AS null

------------

 

-- SQL Server Find Column Collation - QUICK SYNTAX

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE          TABLE_SCHEMA = 'Production'

                        AND TABLE_NAME = 'Product'

                        AND COLUMN_NAME = 'Name';

 /* TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME         COLLATION_NAME

Production       Product            Name  SQL_Latin1_General_CP1_CI_AS   */

------------

 

-- T-SQL CASE SENSITIVE Search applying COLLATE

SELECT * FROM AdventureWorks2008.Production.Product

WHERE ProductNumber like ('[A-Z][A-Z]-[A-Z]%') COLLATE Latin1_General_BIN

------------

 

-- Create table with SELECT INTO for testing changing ProductName collation

-- Price is increased with $1.00

-- SQL Server default collation: SQL_Latin1_General_CP1_CI_AS

USE tempdb;

SELECT      ProductID,

            ProductName = Name COLLATE SQL_Latin1_General_CP1_CI_AI,

            ListPrice = ListPrice + 1.00

INTO Product

FROM AdventureWorks2008.Production.Product

GO

-- (504 row(s) affected)

 

SELECT COUNT(*)

FROM Product p

INNER JOIN AdventureWorks2008.Production.Product aw8

on p.ProductName = aw8.Name

GO

/*

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between

"SQL_Latin1_General_CP1_CI_AS" and

"SQL_Latin1_General_CP1_CI_AI" in the equal to operation.

*/

-- Changing query for different collation JOIN

SELECT COUNT(*)

FROM Product p

INNER JOIN AdventureWorks2008.Production.Product aw8

on p.ProductName = aw8.Name COLLATE DATABASE_DEFAULT

GO

-- 504

-- Changing ProductName to original collation

ALTER TABLE Product alter column ProductName varchar(40)

COLLATE SQL_Latin1_General_CP1_CI_AS null

GO

SELECT COUNT(*)

FROM Product p

INNER JOIN AdventureWorks2008.Production.Product aw8

on p.ProductName = aw8.Name

GO

-- 504

 

DROP TABLE tempdb.dbo.Product
------------

 

-- List all column collations for all tables in a database

USE AdventureWorks2008

GO

SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +

 '.' + QUOTENAME(c.name)   AS ColumnName,

c.collation_name AS Collation

FROM sys.schemas s

   INNER JOIN sys.tables t

      ON t.schema_id = s.schema_id

   INNER JOIN sys.columns c

      ON c.object_id = t.object_id

WHERE collation_name is not null

ORDER BY ColumnName

/* ColumnName     Collation

[dbo].[SalesStaff].[MiddleName]     SQL_Latin1_General_CP1_CI_AS

[dbo].[SalesStaff].[PhoneNumber]    SQL_Latin1_General_CP1_CI_AS

[dbo].[SalesStaff].[PhoneNumberType]      SQL_Latin1_General_CP1_CI_AS ....*/

------------

 

 

Related articles:

 

http://sqlusa.com/bestpractices2005/collatedatabasedefault/

 

Changing Collations

 

Update Collation of all fields in database on the fly

 

Easy way to change collation of all database objects in SQL Server

 

Column Collation Changer

 

A Seven-Step Process for Changing a Database's Collation

 

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE