FilipDeVos

Execute as user = 'SimpleLogin'
exec p_a
revert;
GO

FilipDeVos

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'
CREATE USER SchemaCertUser FROM CERTIFICATE SchemaCert
GRANT ALTER ANY SCHEMA TO SchemaCertUser
ADD SIGNATURE TO p_a BY CERTIFICATE SchemaCert
GO

FilipDeVos

CREATE TABLE #RockAndRoll(
    ID           int     primary key 
  , SongTitle    nvarchar(20)
  , Genre        nvarchar(200)
  , Sales        int
)

FilipDeVos

CREATE DATABASE strangeCollationDb COLLATE Latin1_General_CI_AI_KS_WS
GO
USE strangeCollationDb 

FilipDeVos

CREATE TABLE RockStars (id int, name varchar(50), location varchar(400))
INSERT INTO RockStars VALUES (1, 'Mick Jagger', 'USA')
INSERT INTO RockStars VALUES (2, 'Jim Morrison', 'Heaven or Hell')

CREATE TABLE #RockStarInfo (name varchar(50), information varchar(400))
INSERT INTO #RockStarInfo VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo VALUES ('Jim Morrison', 'Passed away')

FilipDeVos

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo i
  ON s.name = i.name

FilipDeVos

SELECT db_name() as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   = 'RockStars' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'
 
SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo%' 
  AND TABLE_SCHEMA = current_user

FilipDeVos

SELECT cast(null as varchar(50)) as name, cast(null as varchar(400)) as information
INTO #RockStarInfo2 
WHERE 1=2
INSERT INTO #RockStarInfo2 VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo2 VALUES ('Jim Morrison', 'Passed away')

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo2 i
  ON s.name = i.name

FilipDeVos

SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo2%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

FilipDeVos

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