Tuesday, January 27, 2015

Sử dụng COLLATE database_default database_default .

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

*/
------------ 

Sourse : sqlusa
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)