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

来源：互联网发布：纪念币价格走势软件编辑：程序博客网时间：2024/05/05 14:38

SELECT (table1.name   + ' ' + table2.lastname) as ownersname

table1.name and table2.lastname have different collations.

There is a default collation for the server.

There is a default collation for the database (which defaults to the collation for the server)

The default collation for the database will be applied to any column you add to a table UNLESS you explicitly specify a collation at the column level.

/* Find Collation of SQL Server Database */SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')GO/* Find Collation of SQL Server Database Table Column *//* Address: Table Name   City:Column Name */USE AdventureWorksGOSELECT name, collation_nameFROM sys.columnsWHERE OBJECT_ID IN (SELECT OBJECT_IDFROM sys.objectsWHERE type = 'U'AND name = 'Address')AND name = 'City'/* Change the database collation */ALTER TABLE TestTableALTER COLUMN FirstCol VARCHAR(10)COLLATE SQL_Latin1_General_CP1_CS_AS NULLGO

How to generate script with collation?
(SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)