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 AdventureWorks
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
AND name = 'City'
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
How to generate script with collation?
(SSMS : Tools : Options : SQL Server object explorer : Scripting : Table and view options : Include collation = True)