USE PerfTest;
SET NOCOUNT ON;
GO

-- Encrypt some of the columns, but not all. This matches reality more closely where you would not choose to encrypt everything in a table.
-- For simplicity, I am only using SHA1 as the hashing algorithm.

/* ============================================================================================ */
/* ================================ UDTs ====================================================== */
/* ============================================================================================ */

---- CertID
--IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'CertID') BEGIN
--	CREATE TYPE dbo.CertID FROM int;
--END;
--GO

-- KeyGUID
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'KeyGUID') BEGIN
	CREATE TYPE dbo.KeyGUID FROM uniqueidentifier;
END;
GO

-- smallint
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'EncryptedSmallint') BEGIN
	CREATE TYPE dbo.EncryptedSmallint FROM varbinary(52);
END;
GO

-- bigint
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'EncryptedBigint') BEGIN
	CREATE TYPE dbo.EncryptedBigint FROM varbinary(68);
END;
GO

-- SecureString
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'EncryptedSecureString') BEGIN
	CREATE TYPE dbo.EncryptedSecureString FROM varbinary(84);
END;
GO

-- Salt (max length: 63 characters)
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'EncryptedSalt') BEGIN
	CREATE TYPE dbo.EncryptedSalt FROM varbinary(100);
END;
GO

-- HashResultMD
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'HashResultMD') BEGIN
	CREATE TYPE dbo.HashResultMD FROM varbinary(16);
END;
GO

-- HashResultSHA
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'HashResultSHA') BEGIN
	CREATE TYPE dbo.HashResultSHA FROM varbinary(20);
END;
GO

/* ============================================================================================ */
/* ================================ Test Schema =============================================== */
/* ============================================================================================ */

-- ========== Table SecretSalt
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'SecretSalt' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	DROP TABLE dbo.SecretSalt;
END;
GO

PRINT N'Create table SecretSalt'
CREATE TABLE dbo.SecretSalt (
	SaltID				tinyint			NOT NULL,
	Salt				EncryptedSalt	NOT NULL

	CONSTRAINT SecretSalt_PK PRIMARY KEY CLUSTERED (SaltID)
) ON [PerfTest_FG4];
GO

-- ========== Table CardNoEncrypt
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'CardNoEncrypt' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	DROP TABLE dbo.CardNoEncrypt;
END;
GO

PRINT N'Create table CardNoEncrypt'
CREATE TABLE dbo.CardNoEncrypt (
	CardID				int				NOT NULL	IDENTITY,
	CardNumber1			bigint			NOT NULL,
	CardNumber2			bigint			NOT NULL,
	CardNumber3			bigint			NOT NULL,
	CardNumber4			bigint			NOT NULL,
	SecurityCode1		smallint		NOT NULL,
	SecurityCode2		smallint		NOT NULL,
	SecurityCode3		smallint		NOT NULL,
	SecurityCode4		smallint		NOT NULL,
	SecureString1		varchar(36)		NOT NULL,
	SecureString2		varchar(36)		NOT NULL,
	SecureString3		varchar(36)		NOT NULL,
	SecureString4		varchar(36)		NOT NULL,

	CONSTRAINT CardNoEncrypt_PK PRIMARY KEY CLUSTERED (CardID)
) ON [PerfTest_FG1];
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'CardNoEncrypt_CardNumber1_SecurityCode1_UIDX') BEGIN
	CREATE UNIQUE NONCLUSTERED INDEX CardNoEncrypt_CardNumber1_SecurityCode1_UIDX ON dbo.CardNoEncrypt (
		CardNumber1,
		SecurityCode1
	) ON [PerfTest_FG2]
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'CardNoEncrypt_SecureString1_IDX') BEGIN
	CREATE NONCLUSTERED INDEX CardNoEncrypt_SecureString1_IDX ON dbo.CardNoEncrypt (
		SecureString1
	) ON [PerfTest_FG3]
END;
GO

-- ========== Table CardEncryptByKey
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'CardEncryptByKey' AND TABLE_SCHEMA = N'dbo' AND TABLE_TYPE = N'BASE TABLE') BEGIN
	DROP TABLE dbo.CardEncryptByKey;
END;
GO

-- Validate availability of UDTs
IF (SELECT COUNT(*) FROM sys.types WHERE name IN (N'EncryptedSmallint', N'EncryptedBigint', N'EncryptedSecureString', N'HashResultSHA')) <> 4 BEGIN
	RAISERROR(N'Required UDTs do not exist. Exiting.', 10, 1);
	RETURN;
END;
GO

PRINT N'Create table CardEncryptByKey'
CREATE TABLE dbo.CardEncryptByKey (
	CardID				int						NOT NULL	IDENTITY,
	CardNumber1			EncryptedBigint			NOT NULL,				-- Encrypt
	CardNumber1MAC		HashResultSHA			NOT NULL,				-- MAC
	CardNumber2			bigint					NOT NULL,
	CardNumber3			bigint					NOT NULL,
	CardNumber4			bigint					NOT NULL,
	SecurityCode1		EncryptedSmallint		NOT NULL,				-- Encrypt (no corresponding MAC)
	SecurityCode2		smallint				NOT NULL,
	SecurityCode3		smallint				NOT NULL,
	SecurityCode4		smallint				NOT NULL,
	SecureString1		EncryptedSecureString	NOT NULL,				-- Encrypt
	SecureString1MAC	HashResultSHA			NOT NULL,				-- MAC
	SecureString2		varchar(36)				NOT NULL,
	SecureString3		varchar(36)				NOT NULL,
	SecureString4		varchar(36)				NOT NULL,

	CONSTRAINT CardEncryptByKey_PK PRIMARY KEY CLUSTERED (CardID)
) ON [PerfTest_FG4];
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'CardEncryptByKey_CardNumber1MAC_IDX') BEGIN
	CREATE NONCLUSTERED INDEX CardEncryptByKey_CardNumber1MAC_IDX ON dbo.CardEncryptByKey (
		CardNumber1MAC
	) ON [PerfTest_FG2]
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'CardEncryptByKey_SecureString1MAC_IDX') BEGIN
	CREATE NONCLUSTERED INDEX CardEncryptByKey_SecureString1MAC_IDX ON dbo.CardEncryptByKey (
		SecureString1MAC
	) ON [PerfTest_FG3]
END;
GO

/* ============================================================================================ */
/* ================================ Stored Procedures ========================================= */
/* ============================================================================================ */

-- =====================================================
PRINT N'CardNoEncryptCreate';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardNoEncryptCreate' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardNoEncryptCreate;
GO
CREATE PROCEDURE dbo.CardNoEncryptCreate (
	@CardID				int							OUTPUT,
	@CardNumber1		bigint,
	@CardNumber2		bigint,
	@CardNumber3		bigint,
	@CardNumber4		bigint,
	@SecurityCode1		smallint,
	@SecurityCode2		smallint,
	@SecurityCode3		smallint,
	@SecurityCode4		smallint,
	@SecureString1		varchar(36),
	@SecureString2		varchar(36),
	@SecureString3		varchar(36),
	@SecureString4		varchar(36)
) AS
-- Create a new CardNoEncrypt record
BEGIN
	SET NOCOUNT ON;

	INSERT INTO dbo.CardNoEncrypt (
		-- CardID
		CardNumber1,
		CardNumber2,
		CardNumber3,
		CardNumber4,
		SecurityCode1,
		SecurityCode2,
		SecurityCode3,
		SecurityCode4,
		SecureString1,
		SecureString2,
		SecureString3,
		SecureString4
	)
	VALUES (
		@CardNumber1,
		@CardNumber2,
		@CardNumber3,
		@CardNumber4,
		@SecurityCode1,
		@SecurityCode2,
		@SecurityCode3,
		@SecurityCode4,
		@SecureString1,
		@SecureString2,
		@SecureString3,
		@SecureString4
	);

	RETURN(0);
END;
GO

-- ======================================================
PRINT N'Function EncryptSmallint'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'EncryptSmallint' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.EncryptSmallint;
END;
GO
CREATE FUNCTION dbo.EncryptSmallint (
	@KeyGUID		KeyGUID,
	@SmallintParam	smallint
)
RETURNS EncryptedSmallint
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN EncryptByKey(@KeyGUID, CONVERT(varchar(6), @SmallintParam));
END;
GO

---- ======================================================
--PRINT N'Function HashSmallintSHA'
---- ======================================================
--IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'HashSmallintSHA' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
--	DROP FUNCTION dbo.HashSmallintSHA;
--END;
--GO
--CREATE FUNCTION dbo.HashSmallintSHA (
--	@SmallintParam	smallint
--)
--RETURNS HashResultSHA
--WITH EXECUTE AS CALLER
--AS
--BEGIN
--	DECLARE @concat		varchar(69);		-- Max length of salt string is 63 characters
--
--	SELECT @concat = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(VARCHAR(6), @SmallintParam)
--	FROM dbo.SecretSalt
--	WHERE SaltID = 1
--
--	RETURN HashBytes('SHA1', @concat);
--END;
--GO

-- ======================================================
PRINT N'Function EncryptBigint'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'EncryptBigint' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.EncryptBigint;
END;
GO
CREATE FUNCTION dbo.EncryptBigint (
	@KeyGUID		KeyGUID,
	@BigintParam	bigint
)
RETURNS EncryptedBigint
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN EncryptByKey(@KeyGUID, CONVERT(varchar(20), @BigintParam));
END;
GO

-- ======================================================
PRINT N'Function HashBigintSHA'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'HashBigintSHA' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.HashBigintSHA;
END;
GO
CREATE FUNCTION dbo.HashBigintSHA (
	@BigintParam	bigint
)
RETURNS HashResultSHA
WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @concat		varchar(83);		-- Max length of salt string is 63 characters

	SELECT @concat = CONVERT(varchar(63), DecryptByKey(Salt)) + CONVERT(VARCHAR(20), @BigintParam)
	FROM dbo.SecretSalt
	WHERE SaltID = 1

	RETURN HashBytes('SHA1', @concat);
END;
GO

-- ======================================================
PRINT N'Function EncryptSecureString'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'EncryptSecureString' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.EncryptSecureString;
END;
GO
CREATE FUNCTION dbo.EncryptSecureString (
	@KeyGUID		KeyGUID,
	@StringParam	varchar(36)
)
RETURNS EncryptedSecureString
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN EncryptByKey(@KeyGUID, @StringParam);
END;
GO

-- ======================================================
PRINT N'Function HashSecureStringSHA'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'HashSecureStringSHA' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.HashSecureStringSHA;
END;
GO
CREATE FUNCTION dbo.HashSecureStringSHA (
	@StringParam	varchar(36)
)
RETURNS HashResultSHA
WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @concat		varchar(99);		-- Max length of salt string is 63 characters

	SELECT @concat = CONVERT(varchar(63), DecryptByKey(Salt)) + @StringParam
	FROM dbo.SecretSalt
	WHERE SaltID = 1

	RETURN HashBytes('SHA1', @concat);
END;
GO

-- =====================================================
PRINT N'CardEncryptByKeyCreate';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardEncryptByKeyCreate' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardEncryptByKeyCreate;
GO
CREATE PROCEDURE dbo.CardEncryptByKeyCreate (
	@CardID				int							OUTPUT,
	@CardNumber1		bigint,
	@CardNumber2		bigint,
	@CardNumber3		bigint,
	@CardNumber4		bigint,
	@SecurityCode1		smallint,
	@SecurityCode2		smallint,
	@SecurityCode3		smallint,
	@SecurityCode4		smallint,
	@SecureString1		varchar(36),
	@SecureString2		varchar(36),
	@SecureString3		varchar(36),
	@SecureString4		varchar(36),
	@KeyGUID			KeyGUID
) AS
-- Create a new CardEncryptByKey record
BEGIN
	SET NOCOUNT ON;

	IF @KeyGUID IS NULL BEGIN
		RAISERROR(N'Error. @KeyGUID parameter is NULL.', 10, 1);
	END;

	INSERT INTO dbo.CardEncryptByKey (
		-- CardID
		CardNumber1,
		CardNumber1MAC,
		CardNumber2,
		CardNumber3,
		CardNumber4,
		SecurityCode1,
		SecurityCode2,
		SecurityCode3,
		SecurityCode4,
		SecureString1,
		SecureString1MAC,
		SecureString2,
		SecureString3,
		SecureString4
	)
	SELECT
		dbo.EncryptBigint(@KeyGUID,			@CardNumber1),
		dbo.HashBigintSHA(@CardNumber1),
		@CardNumber2,
		@CardNumber3,
		@CardNumber4,
		dbo.EncryptSmallint(@KeyGUID,		@SecurityCode1),
		@SecurityCode2,
		@SecurityCode3,
		@SecurityCode4,
		dbo.EncryptSecureString(@KeyGUID,	@SecureString1),
		dbo.HashSecureStringSHA(@SecureString1),
		@SecureString2,
		@SecureString3,
		@SecureString4;

	RETURN(0);
END;
GO

-- =====================================================
PRINT N'CardCreateLoopN';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardCreateLoopN' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardCreateLoopN;
GO
CREATE PROCEDURE dbo.CardCreateLoopN (
	@Max				int,
	@EncryptionType		varchar(32),			-- Values: 'None', 'ByKey'
	@KeyGUID			KeyGUID,
	@Print				bit,
	@MsDuration			int				OUTPUT
) AS
-- Call Card...Create n times and report the duration
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardID			int;
	DECLARE @cardNumber1	bigint;
	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @ct				int;
	DECLARE @d1				datetime;
	DECLARE @secureString1	varchar(36);
	DECLARE @secureString2	varchar(36);
	DECLARE @secureString3	varchar(36);
	DECLARE @secureString4	varchar(36);
	DECLARE @securityCode1	smallint;
	DECLARE @securityCode2	smallint;
	DECLARE @securityCode3	smallint;
	DECLARE @securityCode4	smallint;

	SELECT @d1 = GETDATE();

	SELECT @ct = 0;
	WHILE @ct < @Max BEGIN
		SELECT
			@ct				= @ct + 1,
			@cardNumber1	= 1111222200000000 + @ct,
			@cardNumber2	= 2222333300000000 + @ct,
			@cardNumber3	= 3333444400000000 + @ct,
			@cardNumber4	= 4444555500000000 + @ct,
			@securityCode1	= @ct % 1000,
			@securityCode2	= @securityCode1 + 1,
			@securityCode3	= @securityCode1 + 2,
			@securityCode4	= @securityCode1 + 3,
			@secureString1	= CONVERT(varchar(36), NEWID()),
			@secureString2	= CONVERT(varchar(36), NEWID()),
			@secureString3	= CONVERT(varchar(36), NEWID()),
			@secureString4	= CONVERT(varchar(36), NEWID());

		IF @EncryptionType = 'None' BEGIN
			EXEC dbo.CardNoEncryptCreate
				@CardID				= @cardID					OUTPUT,
				@CardNumber1		= @cardNumber1,
				@CardNumber2		= @cardNumber2,
				@CardNumber3		= @cardNumber3,
				@CardNumber4		= @cardNumber4,
				@SecurityCode1		= @securityCode1,
				@SecurityCode2		= @securityCode2,
				@SecurityCode3		= @securityCode3,
				@SecurityCode4		= @securityCode4,
				@SecureString1		= @secureString1,
				@SecureString2		= @secureString2,
				@SecureString3		= @secureString3,
				@SecureString4		= @secureString4;
		END;
		ELSE IF @EncryptionType = 'ByKey' BEGIN
			EXEC dbo.CardEncryptByKeyCreate
				@CardID				= @cardID					OUTPUT,
				@CardNumber1		= @cardNumber1,
				@CardNumber2		= @cardNumber2,
				@CardNumber3		= @cardNumber3,
				@CardNumber4		= @cardNumber4,
				@SecurityCode1		= @securityCode1,
				@SecurityCode2		= @securityCode2,
				@SecurityCode3		= @securityCode3,
				@SecurityCode4		= @securityCode4,
				@SecureString1		= @secureString1,
				@SecureString2		= @secureString2,
				@SecureString3		= @secureString3,
				@SecureString4		= @secureString4,
				@KeyGUID			= @KeyGUID;
		END;
		ELSE BEGIN
			RAISERROR('Invalid value passed for @EncryptionType: %s', 10, 1, @EncryptionType);
			RETURN(-1);
		END;
	END;

	SELECT @MsDuration = DATEDIFF(ms, @d1, GETDATE());
	IF @Print = 1 BEGIN
		PRINT 'Max: ' + CONVERT(varchar(11), @Max)
			+ ', Encryption: ' + @EncryptionType
			+ ', MsDuration: ' + CONVERT(varchar(11), @MsDuration);
	END;
END;
GO

-- =====================================================
PRINT N'CardNoEncryptReadByID';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardNoEncryptReadByID' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardNoEncryptReadByID;
GO
CREATE PROCEDURE dbo.CardNoEncryptReadByID (
	@CardID			int,
	@CardNumber1	bigint				OUTPUT,
	@SecurityCode1	smallint			OUTPUT
) AS
-- Read a CardNoEncrypt record by CardID
-- Return CardNumber1 and SecurityCode1. Otherwise, this is just exercising the SELECT.
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode2	smallint;
	DECLARE @securityCode3	smallint;
	DECLARE @securityCode4	smallint;
	DECLARE @secureString1	varchar(36);
	DECLARE @secureString2	varchar(36);
	DECLARE @secureString3	varchar(36);
	DECLARE @secureString4	varchar(36);

	SELECT
		@CardNumber1	= CardNumber1,
		@cardNumber2	= CardNumber2,
		@cardNumber3	= CardNumber3,
		@cardNumber4	= CardNumber4,
		@SecurityCode1	= SecurityCode1,
		@securityCode2	= SecurityCode2,
		@securityCode3	= SecurityCode3,
		@securityCode4	= SecurityCode4,
		@secureString1	= SecureString1,
		@secureString2	= SecureString2,
		@secureString3	= SecureString3,
		@secureString4	= SecureString4
	FROM dbo.CardNoEncrypt
	WHERE CardID = @CardID;

	RETURN(0);
END;
GO

-- =====================================================
PRINT N'CardNoEncryptReadByCardNumber1AndSecurityCode1';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardNoEncryptReadByCardNumber1AndSecurityCode1' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardNoEncryptReadByCardNumber1AndSecurityCode1;
GO
CREATE PROCEDURE dbo.CardNoEncryptReadByCardNumber1AndSecurityCode1 (
	@CardNumber1		bigint,
	@SecurityCode1		smallint,
	@CardID				int					OUTPUT
) AS
-- Read a CardNoEncrypt record by CardNumber1 and SecurityCode1
-- Return CardID only.
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode2	smallint;
	DECLARE @securityCode3	smallint;
	DECLARE @securityCode4	smallint;
	DECLARE @secureString1	varchar(36);
	DECLARE @secureString2	varchar(36);
	DECLARE @secureString3	varchar(36);
	DECLARE @secureString4	varchar(36);

	SELECT
		@CardID			= CardID,
		@cardNumber1	= CardNumber1,
		@cardNumber2	= CardNumber2,
		@cardNumber3	= CardNumber3,
		@cardNumber4	= CardNumber4,
		@securityCode1	= SecurityCode1,
		@securityCode2	= SecurityCode2,
		@securityCode3	= SecurityCode3,
		@securityCode4	= SecurityCode4,
		@secureString1	= SecureString1,
		@secureString2	= SecureString2,
		@secureString3	= SecureString3,
		@secureString4	= SecureString4
	FROM dbo.CardNoEncrypt
	WHERE CardNumber1	= @CardNumber1
	  AND SecurityCode1	= @SecurityCode1

	RETURN(0);
END;
GO

-- ======================================================
PRINT N'Function DecryptSmallint';
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'DecryptSmallint' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.DecryptSmallint;
END;
GO
CREATE FUNCTION dbo.DecryptSmallint (
	@EncryptedSmallint	EncryptedSmallint
)
RETURNS smallint
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN CONVERT(smallint, CONVERT(varchar(6), DecryptByKey(@EncryptedSmallint)));
END;
GO

-- ======================================================
PRINT N'Function DecryptBigint';
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'DecryptBigint' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.DecryptBigint;
END;
GO
CREATE FUNCTION dbo.DecryptBigint (
	@EncryptedBigint	EncryptedBigint
)
RETURNS bigint
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN CONVERT(bigint, CONVERT(varchar(20), DecryptByKey(@EncryptedBigint)));
END;
GO

-- ======================================================
PRINT N'DecryptSecureString';
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'DecryptSecureString' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.DecryptSecureString;
END;
GO
CREATE FUNCTION dbo.DecryptSecureString (
	@EncryptedSecureString	EncryptedSecureString
)
RETURNS varchar(36)
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN DecryptByKey(@EncryptedSecureString);
END;
GO

-- =====================================================
PRINT N'CardEncryptByKeyReadByID';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardEncryptByKeyReadByID' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardEncryptByKeyReadByID;
GO
CREATE PROCEDURE dbo.CardEncryptByKeyReadByID (
	@CardID			int,
	@CardNumber1	bigint				OUTPUT,
	@SecurityCode1	smallint			OUTPUT
) AS
-- Read a CardEncryptByKey record by CardID
-- Return CardNumber1 and SecurityCode1. Otherwise, this is just exercising the SELECT.
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode2	smallint;
	DECLARE @securityCode3	smallint;
	DECLARE @securityCode4	smallint;
	DECLARE @secureString1	varchar(36);
	DECLARE @secureString2	varchar(36);
	DECLARE @secureString3	varchar(36);
	DECLARE @secureString4	varchar(36);

	SELECT
		@CardNumber1	= dbo.DecryptBigint(CardNumber1),
		@cardNumber2	= CardNumber2,
		@cardNumber3	= CardNumber3,
		@cardNumber4	= CardNumber4,
		@SecurityCode1	= dbo.DecryptSmallint(SecurityCode1),
		@securityCode2	= SecurityCode2,
		@securityCode3	= SecurityCode3,
		@securityCode4	= SecurityCode4,
		@secureString1	= dbo.DecryptSecureString(SecureString1),
		@secureString2	= SecureString2,
		@secureString3	= SecureString3,
		@secureString4	= SecureString4
	FROM dbo.CardEncryptByKey
	WHERE CardID = @CardID;

	RETURN(0);
END;
GO

-- =====================================================
PRINT N'CardEncryptByKeyReadByCardNumber1AndSecurityCode1';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardEncryptByKeyReadByCardNumber1AndSecurityCode1' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardEncryptByKeyReadByCardNumber1AndSecurityCode1;
GO
CREATE PROCEDURE dbo.CardEncryptByKeyReadByCardNumber1AndSecurityCode1 (
	@CardNumber1		bigint,
	@SecurityCode1		smallint,
	@CardID				int					OUTPUT
) AS
-- Read a CardEncryptByKey record by CardNumber1 and SecurityCode1
-- Return CardID only.
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode2	smallint;
	DECLARE @securityCode3	smallint;
	DECLARE @securityCode4	smallint;
	DECLARE @secureString1	varchar(36);
	DECLARE @secureString2	varchar(36);
	DECLARE @secureString3	varchar(36);
	DECLARE @secureString4	varchar(36);

	SELECT
		@CardID			= CardID,
		@cardNumber2	= CardNumber2,
		@cardNumber3	= CardNumber3,
		@cardNumber4	= CardNumber4,
		@securityCode2	= SecurityCode2,
		@securityCode3	= SecurityCode3,
		@securityCode4	= SecurityCode4,
		@secureString1	= dbo.DecryptSecureString(SecureString1),
		@secureString2	= SecureString2,
		@secureString3	= SecureString3,
		@secureString4	= SecureString4
	FROM dbo.CardEncryptByKey
	WHERE CardNumber1MAC				= dbo.HashBigintSHA(@CardNumber1)	-- Allows efficient look-up
	  AND DecryptByKey(CardNumber1)		= @CardNumber1						-- Validates correct CardNumber1
	  AND DecryptByKey(SecurityCode1)	= @SecurityCode1;					-- Validates correct SecurityCode1

	RETURN(0);
END;
GO

-- =====================================================
PRINT N'CardReadLoopN';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardReadLoopN' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardReadLoopN;
GO
CREATE PROCEDURE dbo.CardReadLoopN (
	@CardMax			int,
	@ReadMax			int,
	@EncryptionType		varchar(32),			-- Values: 'None', 'ByKey'
	@Print				bit,
	@MsDuration			int				OUTPUT
) AS
-- Call Card...ReadByID then Card...ReadByCardNumber1AndSecurityCode1 n times and report the duration
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardID1		int;
	DECLARE @cardID2		int;
	DECLARE @cardNumber1	bigint;
	DECLARE @ct				int;
	DECLARE @d1				datetime;
	DECLARE @securityCode1	smallint;

	OPEN SYMMETRIC KEY PerfTestKey DECRYPTION BY CERTIFICATE PerfTestCert;

	SELECT @d1 = GETDATE();

	SELECT @ct = 0;
	WHILE @ct < @ReadMax BEGIN
		SELECT
			@ct			= @ct + 1,
			@cardID1	= RAND() * @CardMax;

		-- It is possible for RAND() to return exactly zero
		IF @cardID1 = 0 BEGIN
			SET @cardID1 = 1;
		END;

		-- First, get CardNumber1 and SecurityCode1 for some random record
		SELECT
			@CardNumber1	= NULL,
			@SecurityCode1	= NULL;

		IF @EncryptionType = 'None' BEGIN
			EXEC dbo.CardNoEncryptReadByID
				@CardID			= @cardID1,
				@CardNumber1	= @cardNumber1			OUTPUT,
				@SecurityCode1	= @securityCode1		OUTPUT;
		END;
		ELSE IF @EncryptionType = 'ByKey' BEGIN
			EXEC dbo.CardEncryptByKeyReadByID
				@CardID			= @cardID1,
				@CardNumber1	= @cardNumber1			OUTPUT,
				@SecurityCode1	= @securityCode1		OUTPUT;
		END;
		ELSE BEGIN
			RAISERROR('Invalid value passed for @EncryptionType: %s', 10, 1, @EncryptionType);
			RETURN(-1);
		END;

		IF @CardNumber1 IS NULL OR @SecurityCode1 IS NULL BEGIN
			RAISERROR('Call to ReadByID returned NULL. Exiting.', 10, 1, @EncryptionType);
			RETURN(-2);
		END;

		-- Second, find a record based on CardNumber1 and SecurityCode1
		IF @EncryptionType = 'None' BEGIN
			EXEC dbo.CardNoEncryptReadByCardNumber1AndSecurityCode1
				@CardNumber1	= @cardNumber1,
				@SecurityCode1	= @securityCode1,
				@CardID			= @cardID2				OUTPUT;
		END;
		ELSE IF @EncryptionType = 'ByKey' BEGIN
			EXEC dbo.CardEncryptByKeyReadByCardNumber1AndSecurityCode1
				@CardNumber1	= @cardNumber1,
				@SecurityCode1	= @securityCode1,
				@CardID			= @cardID2				OUTPUT;
		END;
		ELSE BEGIN
			RAISERROR('Invalid value passed for @EncryptionType: %s', 10, 1, @EncryptionType);
			RETURN(-1);
		END;

		-- Third, validate that the IDs match between the two reads
		IF @cardID1 <> @cardID2 BEGIN
			RAISERROR(N'IDs returned do not match. Exiting.', 10, 1);
			RETURN;
		END;
	END;

	SELECT @MsDuration = DATEDIFF(ms, @d1, GETDATE());
	IF @Print = 1 BEGIN
		PRINT 'Max: ' + CONVERT(varchar(11), @ReadMax)
			+ ', Encryption: ' + @EncryptionType
			+ ', MsDuration: ' + CONVERT(varchar(11), @MsDuration);
	END;
END;
GO

PRINT '<< DONE >>'
GO