Create Collation safe TempTables

collation_safe_temptables_07.sql

CREATE TYPE NameType FROM varchar(50)
CREATE TYPE LocationType FROM varchar(50)

CREATE TABLE MoreRockStars (id int, name NameType, location LocationType)
INSERT INTO MoreRockStars VALUES (1, 'Jeff Buckley', 'Heaven')
INSERT INTO MoreRockStars VALUES (2, 'Neil Young', 'USA')

DECLARE @Name     NameType, 
        @Location LocationType

SELECT @Name as name, @Location as information into #RockStarInfo3 
INSERT INTO #RockStarInfo3 VALUES ('Jeff Buckley', 'Drowned sadly')
INSERT INTO #RockStarInfo3 VALUES ('Neil Young', 'Keeps on rocking')

SELECT * 
FROM MoreRockstars s
  LEFT JOIN #RockStarInfo3 i
  ON s.name = i.name