LISTING 1: Example of Implicit Data Type Conversion

USE tempdb
GO

CREATE TABLE Customers (
ncharCustomerId nchar(10) NOT NULL,
charCustomerId char(10) NOT NULL,
varcharCustomerId varchar(10) NOT NULL,
CompanyName nvarchar(80),
Filler char(100) NOT NULL
)

CREATE TABLE Orders (
OrderId int NOT NULL,
ncharCustomerId nchar(10) NOT NULL,
charCustomerId char(10) NOT NULL,
varcharCustomerId varchar(10) NOT NULL,
Filler char(100) NOT NULL
)


INSERT INTO tempdb..Customers SELECT CustomerId, CustomerId, CustomerId, CompanyName, ' ' FROM northwind..customers


INSERT INTO tempdb..Orders SELECT OrderId, CustomerId, CustomerId, CustomerId, ' ' FROM northwind..orders


CREATE NONCLUSTERED INDEX Customers_nchar ON Customers (ncharCustomerId)
CREATE NONCLUSTERED INDEX Customers_char ON Customers (charCustomerId)
CREATE NONCLUSTERED INDEX Customers_varchar ON Customers (varcharCustomerId)

CREATE NONCLUSTERED INDEX Orders_nchar ON Orders (ncharCustomerId)
CREATE NONCLUSTERED INDEX Orders_char ON Orders (charCustomerId)
CREATE NONCLUSTERED INDEX Orders_varchar ON Orders (varcharCustomerId)

--Run the following query with the Show Execution Plan option selected:

SELECT
	customers.CompanyName,
	orders.OrderId
FROM
	customers, orders
WHERE
	customers.ncharCustomerId = orders.ncharCustomerId
	AND Customers.ncharCustomerId = 'ALFKI'

/*As you would expect, SQL Server uses the Orders_nchar index on the Orders table to efficiently process the join. Now run this query:*/

SELECT
	customers.CompanyName,
	orders.OrderId
FROM
	customers, orders
WHERE
	customers.ncharCustomerId = orders.charCustomerId
	AND Customers.ncharCustomerId = 'ALFKI'