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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use COLLATE database_default?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate collation conflicts and resolutions by COLLATE DATABASE_DEFAULT. SQL Server sample databases AdventureWorks & AdventurWorks2008 are used in the demos.

-- Cannot resolve collation conflict for - SQL Server collation conflict

Typical string operations collate database default usages:
     equal match, concatenation & IN match

 

... WHERE ( or ON) a.StringColAlpha = b.StringColOmega COLLATE DATABASE_DEFAULT

 

SELECT NewString = a.StringColAlpha + b.StringColOmega COLLATE DATABASE_DEFAULT ...

... WHERE ap.Color COLLATE DATABASE_DEFAULT IN (SELECT dp.Color FROM  Product dp)

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

-- REPLACE string operation with collation conflict

SELECT REPLACE(o.TYPE + o.NAME, ' ','~')

FROM   sys.objects o

/*

Msg 446, Level 16, State 9, Line 2

Cannot resolve collation conflict for replace operation.

*/

 

-- SQL collate fixes the collation conflict issue between the TYPE & NAME columns

SELECT REPLACE(o.TYPE + o.NAME COLLATE DATABASE_DEFAULT,  ' ','~')

FROM   sys.objects o

-- (658 row(s) affected)

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

-- EQUAL TO operation with collation conflict - Cannot Resolve Collation Conflict

SELECT NAME

FROM   sys.objects

WHERE NAME NOT IN (SELECT TYPE

                    FROM   sys.objects)

/* 

Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and 

"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

*/

-- SQL COLLATE DATABASE_DEFAULT fixes issue

SELECT NAME

FROM   sys.objects

WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (SELECT TYPE

                                             FROM   sys.objects)

-- (658 row(s) affected)

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

-- CONCAT() function is new in SQL Server 2012

SELECT CONCAT('Production.', objname) AS TableName, value AS [Description]

FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', default, NULL, NULL);

GO

/* Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"

and "Latin1_General_CI_AI" in the concat operation.

*/

-- The following will work  

SELECT CONCAT('Production.', objname COLLATE DATABASE_DEFAULT) AS TableName, value AS [Description]

FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', default, NULL, NULL);

GO

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

-- UNICODE / NCHAR functions - Use them instead of ASCII / CHAR for unicode

-- The Hungarian letter is UNICODE (2 bytes required)

SELECT UNICODE(N'Ő'), ASCII (N'Ő'), CONVERT(BINARY(2), N'Ő')

-- 336      79    0x5001

SELECT UNICODE('O'), ASCII ('O'), CONVERT(BINARY(2), 'O')

-- 79 79    0x4F00

SELECT CONVERT(CHAR(1), N'Ő') -- O - information is lost in conversion

SELECT CONVERT(NCHAR(1), N'Ő') -- Ő - information is kept in conversion
----------

-- Find server and database default collations

SELECT SERVERPROPERTY('COLLATION')

      ,DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')

-- SQL_Latin1_General_CP1_CI_AS     SQL_Latin1_General_CP1_CI_AS

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

-- Finding the code page of a collation

select name, CodePage = COLLATIONPROPERTY(name, 'CodePage'),

       description

from sys.fn_HelpCollations()

where name = 'SQL_Latin1_General_CP1_CI_AS'

go

/* name     CodePage    description

SQL_Latin1_General_CP1_CI_AS  1252  Latin1-General, case-insensitive,

accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,

SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data */
----------

-- 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 ....*/

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

-- A column must be the same collation
SELECT
City = 'New York City' COLLATE SQL_Latin1_General_CP1253_CI_AI
UNION

SELECT
City = 'Los Angeles' COLLATE SQL_Latin1_General_CP1253_CI_AS
/* Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1253_CI_AS" and "SQL_Latin1_General_CP1253_CI_AI" in the UNION operation.  */
------------

/******

* Alter Database Collation - Change Database Collation

* NOTE: only the default collation is changed - other collations remain in DB.

* Tables must be rebuilt (create new table, move data) one by one for new collation.

* ALTER TABLE can be used to change the collation of a column.

******/

ALTER DATABASE YourDBName COLLATE SQL_Latin1_General_CP1_CI_AS

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

-- SQL Server change collation of a column to Case Sensitive (CS)

USE AdventureWorks2008;

ALTER TABLE Production.Product ALTER COLUMN Color nvarchar(15)

                               COLLATE SQL_Latin1_General_CP1_CS_AS

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

/******

Collation conflict can occur when tempdb collation different from current database.

******/

 

-- T-SQL check collation of a column - SQL Server sample database AdventureWorks2008

USE AdventureWorks2008;

SELECT ColumnName=name, Collation=collation_name

FROM sys.columns

WHERE name='Color'

  AND OBJECT_ID IN ( SELECT OBJECT_ID

                     FROM sys.objects

                     WHERE type = 'U'

                     AND name = 'Product'

                     AND SCHEMA_NAME(schema_id)='Production')

-- Color    SQL_Latin1_General_CP1_CI_AS

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

-- SQL Server list all columns with different collation from database default collation

USE AdventureWorks2008;

CREATE TABLE TestOnly(

  text varchar(128) COLLATE SQL_Latin1_General_CP1253_CI_AI     );

GO

 

SELECT

    ObjectType = o.type,

    SchemaName = SCHEMA_NAME(schema_id),

    Objectname = o.name,

    ColumnName = c.name,

    CollationName = c.collation_name

FROM sys.objects o

INNER JOIN sys.columns c ON c.object_id = o.object_id

WHERE c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')

AND o.type IN('U', 'V')

/*

ObjectType  SchemaName  Objectname  ColumnName  CollationName

U           dbo         TestOnly    text        SQL_Latin1_General_CP1253_CI_AI */

DROP TABLE TestOnly

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

-- Implicit conversion collation conflict in string concatenation

create table #Standard(text varchar(16))

create table #Mohawk  (text varchar(16) collate Mohawk_100_CI_AS_KS_WS)

 

insert into #Standard values('SQLUSA')

insert into #Mohawk values('Kanien''kehá:ka')

 

select s.text +' '+ m.text from #Standard s cross join #Mohawk m

go

/* Msg 457, Level 16, State 1, Line 7

Implicit conversion of varchar value to varchar cannot be performed because the

collation of the value is unresolved due to a collation conflict.

*/

select s.text +' '+ m.text COLLATE DATABASE_DEFAULT

from #Standard s cross join #Mohawk m

go

/* SQLUSA Kanien'kehá:ka */

drop table #Standard

drop table #Mohawk

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

-- SQL Server collation conflict on SELECT COUNT DISTINCT

USE tempdb;

SELECT ProductID, Color=Color COLLATE SQL_Latin1_General_CP1253_CI_AI

INTO Product

FROM AdventureWorks.Production.Product

GO

SELECT ColorCount=COUNT(DISTINCT Color)

-- SELECT ColorCount=COUNT(DISTINCT Color COLLATE DATABASE_DEFAULT)

FROM ( SELECT Color=Color 

       FROM AdventureWorks2008.Production.Product

       UNION ALL

       SELECT Color FROM Product ) x

/* Msg 446, Level 16, State 11, Line 1

Cannot resolve collation conflict for DISTINCT operation. */

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

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

-- Find special characters in text - data cleansing

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

SELECT FirstName, LastName

FROM AdventureWorks2008.Person.Person

WHERE LastName LIKE '%[^0-9a-zA-Z'' -]%' COLLATE SQL_Latin1_General_Cp850_BIN

ORDER BY LastName

 

SELECT FirstName, LastName

FROM AdventureWorks.Person.Contact

WHERE LastName LIKE '%[^0-9a-zA-Z'' -]%' COLLATE SQL_Latin1_General_Cp850_BIN

ORDER BY LastName

/*    FirstName   LastName

      Francisco   Javier Castrejón

      Adrienne    Jiménez  ....*/

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

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

-- COLLATE & the EXCEPT operator

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

USE tempdb;

SELECT * INTO Prod1 FROM AdventureWorks2008.Production.Product

SELECT TOP (200) * INTO Prod2 FROM AdventureWorks2008.Production.Product

 

ALTER TABLE Prod2 ALTER COLUMN Name nvarchar(50)

                  COLLATE SQL_Latin1_General_CP1251_CI_AS

 

SELECT ProductID, Name, ProductNumber, ListPrice FROM Prod1

EXCEPT

SELECT ProductID, Name COLLATE DATABASE_DEFAULT, ProductNumber, ListPrice FROM Prod2

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

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

SQL collation conflict - Create demo table in tempdb - collation database default

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

USE tempdb

GO

SET NOCOUNT ON;

-- Create a table with some columns of different collation

CREATE TABLE [dbo].[Product](

[ProductID] [int] NOT NULL,

[Name] [nvarchar](125) NOT NULL,

[ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,

[MakeFlag] [bit] NOT NULL CONSTRAINT [DF_Product_MakeFlag] DEFAULT ((1)),

[FinishedGoodsFlag] [bit] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag] DEFAULT ((1)),

[Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,

[SafetyStockLevel] [smallint] NOT NULL,

[ReorderPoint] [smallint] NOT NULL,

[StandardCost] [money] NOT NULL,

[ListPrice] [money] NOT NULL,

[Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Weight] [decimal](8, 2) NULL,

[DaysToManufacture] [int] NOT NULL,

[ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ProductSubcategoryID] [int] NULL,

[ProductModelID] [int] NULL,

[SellStartDate] [datetime] NOT NULL,

[SellEndDate] [datetime] NULL,

[DiscontinuedDate] [datetime] NULL,

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Product_rowguid] DEFAULT (newid()),

[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate] DEFAULT (getdate()),

CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED

(

[ProductID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]

) ON [PRIMARY]

GO

  

-- SQL Server database populate table - T-SQL insert select

-- SQL Server sample database AdventureWorks 

INSERT Product

SELECT * FROM AdventureWorks.Production.Product

GO

 

-- The following IN set operation fails due to collation conflict
-- SQL collate sql_latin1_general_cp1_ci_as - transact sql collate

SELECT TOP 100 ap.*

FROM   AdventureWorks.Production.Product ap

WHERE  ap.Color IN (SELECT dp.Color

                    FROM   Product dp)

GO

 

/* Msg 468, Level 16, State 9, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI"

 and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

*/

 

-- Inserting COLLATE DATABASE_DEFAULT before IN results in successful query

-- Microsoft SQL Server T-SQL TOP function

SELECT TOP 100 ap.*

FROM   AdventureWorks.Production.Product ap

WHERE  ap.Color COLLATE DATABASE_DEFAULT IN (SELECT dp.Color

                                             FROM   Product dp)

GO

/* The advantage of using COLLATE DATABASE_DEFAULT is that it works without

using specific hard-to-remember collation name.

 

String append and string compare of different collations require the use of

COLLATE clause. */

   

-- The following JOIN fails due to collation conflict

SELECT TOP 15 ap.*

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color = dp.Color

GO

-- Msg 468, Level 16, State 9, Line 1

-- Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

 

-- This JOIN succeeds with specific collation

SELECT TOP 15 ap.*

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color = dp.Color COLLATE SQL_Latin1_General_CP1_CI_AS

GO

 

-- The following JOIN also succeeds

SELECT TOP 15 ap.*

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color COLLATE SQL_Latin1_General_CP1_CI_AS = dp.Color

GO

 

-- MSSQL collation database property check

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')

GO

-- SQL_Latin1_General_CP1_CI_AS

 

-- This JOIN with COLLATE database default works with no need for specific collation

SELECT TOP 15 ap.*

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color = dp.Color COLLATE database_default

GO

 

-- This JOIN also works with collate database default on the left hand side of =

SELECT TOP 15 ap.*

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color COLLATE database_default = dp.Color

GO

 

-- This query fails in the SELECT string concatenation

SELECT TOP 20 [Colors] = 'Color: ' + ap.Color +' '+ dp.color

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color COLLATE database_default = dp.Color

GO

 

-- Msg 451, Level 16, State 1, Line 1

-- Cannot resolve collation conflict for column 1 in SELECT statement.

 

 

-- This query works by applying collate database default after the second column

SELECT TOP 20 [Colors] = 'Color: ' + ap.Color +' '+ dp.color COLLATE database_default

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color COLLATE database_default = dp.Color

GO

 

-- This query also works using collate database default after the first column

SELECT TOP 20 [Colors] = 'Color: ' + ap.Color COLLATE database_default +' '+ dp.color

FROM AdventureWorks.Production.Product ap

JOIN dbo.Product dp

ON ap.Color COLLATE database_default = dp.Color

GO

-- This query fails in the SELECT string concatenation

-- It also fails in the WHERE filter

SELECT TOP 20 ProductName=dp.Name,

[Colors] = 'Color: ' + ap.Color +' '+ dp.color

FROM AdventureWorks.Production.Product ap

INNER JOIN dbo.Product dp

ON dp.ProductID = ap.ProductID

WHERE ap.Color  = dp.Color

GO

 

-- The following queries succeed with double use of COLLATE DATABASE_DEFAULT

SELECT TOP 20 ProductName=dp.Name,

[Colors] = 'Color: ' + ap.Color +' '+ dp.color COLLATE database_default

FROM AdventureWorks.Production.Product ap

INNER JOIN dbo.Product dp

ON dp.ProductID = ap.ProductID

WHERE ap.Color COLLATE database_default = dp.Color

GO

-- SQL collate select - SQL collate where  

SELECT TOP 20 ProductName=dp.Name,

[Colors] = 'Color: ' + ap.Color COLLATE database_default +' '+ dp.color

FROM AdventureWorks.Production.Product ap

INNER JOIN dbo.Product dp

ON dp.ProductID = ap.ProductID

WHERE ap.Color  = dp.Color COLLATE database_default

GO

-- SQL collation table - SQL collation column

SELECT ColumnName = name, collation_name

FROM AdventureWorks.sys.columns

WHERE OBJECT_ID IN (SELECT OBJECT_ID

  FROM AdventureWorks.sys.objects

  WHERE type = 'U'

  AND name = 'Address')

AND collation_name is not null

/* Results

 

ColumnName        collation_name

AddressLine1      SQL_Latin1_General_CP1_CI_AS

AddressLine2      SQL_Latin1_General_CP1_CI_AS

City              SQL_Latin1_General_CP1_CI_AS

PostalCode        SQL_Latin1_General_CP1_CI_AS

*/

 

-- Collation information - SQL database collation

USE AdventureWorks;

SELECT databasepropertyex(db_name(),'collation') AS Collation

GO

-- Result: SQL_Latin1_General_CP1_CI_AS

 

-- Find column collation - SQL table column collations - SQL information_schema views

SELECT TableSchema = TABLE_SCHEMA,

       TableName = TABLE_NAME,

       TableColumn = COLUMN_NAME,

       ColumnCollation = COLLATION_NAME

FROM     INFORMATION_SCHEMA.COLUMNS

WHERE    COLLATION_NAME IS NOT NULL

ORDER BY TableSchema,

         TableName

GO

-- SQL collation case sensitive: "CS" suffix in name
/* Partial results 

 

TableSchema  TableName  TableColumn       ColumnCollation

Sales       CreditCard  CardType          SQL_Latin1_General_CS_AS 

Sales       CreditCard  CardNumber        SQL_Latin1_General_CS_AS 

Sales       Currency    CurrencyCode      SQL_Latin1_General_CS_AS 

*/

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

-- ALL SQL related collations

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

USE MASTER;

 

-- List all sql related collations

SELECT name, description = LEFT(description, 30)

FROM   sys.fn_HelpCollations()

WHERE  name LIKE '%SQL%'

GO

/* Partial results

 

name  description

SQL_Latin1_General_CP1253_CI_AI     Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 124 on Code Page 1253 for non-Unicode Data

SQL_Latin1_General_CP1253_CI_AS     Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 114 on Code Page 1253 for non-Unicode Data

SQL_Latin1_General_CP1253_CS_AS     Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 113 on Code Page 1253 for non-Unicode Data

*/

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

 

-- COLLATION conflict between operands in the REPLACE function

CREATE VIEW vCollate

AS

SELECT Today = REPLACE(CONVERT(VARCHAR,getdate(),120)

                           COLLATE SQL_Latin1_General_CP1_CS_AS,

                       '-' COLLATE SQL_Latin1_General_CP1_CI_AS,

                       '/')

GO

/*

Msg 468, Level 16, State 9, Procedure vcollate, Line 7

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and

"SQL_Latin1_General_CP1_CS_AS" in the replace operation.

 

*/

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

-- Related article link:

 

   SQL Server Collation Fundamentals

 

 

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