|
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
------------
-- 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
|
