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.

/* ============================================================================================ */
/* ================================ 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

-- Cert-encrypted data types all return varbinary(128)
IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = N'EncryptedByCert') BEGIN
	CREATE TYPE dbo.EncryptedByCert FROM varbinary(128);
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

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

-- ========== 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

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

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

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

	CONSTRAINT CardEncryptByCert_PK PRIMARY KEY CLUSTERED (CardID)
) ON [PerfTest_FG2];
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')) <> 3 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
	CardNumber2			bigint					NOT NULL,
	CardNumber3			bigint					NOT NULL,
	CardNumber4			bigint					NOT NULL,
	SecurityCode1		EncryptedSmallint		NOT NULL,				-- Encrypt
	SecurityCode2		smallint				NOT NULL,
	SecurityCode3		smallint				NOT NULL,
	SecurityCode4		smallint				NOT NULL,
	SecureString1		EncryptedSecureString	NOT NULL,				-- Encrypt
	SecureString2		varchar(36)				NOT NULL,
	SecureString3		varchar(36)				NOT NULL,
	SecureString4		varchar(36)				NOT NULL,

	CONSTRAINT CardEncryptByKey_PK PRIMARY KEY CLUSTERED (CardID)
) ON [PerfTest_FG3];
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 EncryptSmallintByCert'
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'EncryptSmallintByCert' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.EncryptSmallintByCert;
END;
GO
CREATE FUNCTION dbo.EncryptSmallintByCert (
	@CertID			CertID,
	@SmallintParam	smallint
)
RETURNS EncryptedByCert
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN EncryptByCert(@CertID, CONVERT(varchar(6), @SmallintParam));
END;
GO

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

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

-- =====================================================
PRINT N'CardEncryptByCertCreate';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardEncryptByCertCreate' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardEncryptByCertCreate;
GO
CREATE PROCEDURE dbo.CardEncryptByCertCreate (
	@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),
	@CertID				CertID
) AS
-- Create a new CardEncryptByCert record
BEGIN
	SET NOCOUNT ON;

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

	INSERT INTO dbo.CardEncryptByCert (
		-- CardID
		CardNumber1,
		CardNumber2,
		CardNumber3,
		CardNumber4,
		SecurityCode1,
		SecurityCode2,
		SecurityCode3,
		SecurityCode4,
		SecureString1,
		SecureString2,
		SecureString3,
		SecureString4
	)
	VALUES (
		dbo.EncryptBigintByCert(@CertID,		@CardNumber1),
		@CardNumber2,
		@CardNumber3,
		@CardNumber4,
		dbo.EncryptSmallintByCert(@CertID,		@SecurityCode1),
		@SecurityCode2,
		@SecurityCode3,
		@SecurityCode4,
		dbo.EncryptSecureStringByCert(@CertID,	@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 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 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'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,
		CardNumber2,
		CardNumber3,
		CardNumber4,
		SecurityCode1,
		SecurityCode2,
		SecurityCode3,
		SecurityCode4,
		SecureString1,
		SecureString2,
		SecureString3,
		SecureString4
	)
	SELECT
--		CONVERT(varbinary(68), 1234), --- here
		dbo.EncryptBigint(@KeyGUID,			@CardNumber1),
		@CardNumber2,
		@CardNumber3,
		@CardNumber4,
		dbo.EncryptSmallint(@KeyGUID,		@SecurityCode1),
		@SecurityCode2,
		@SecurityCode3,
		@SecurityCode4,
		dbo.EncryptSecureString(@KeyGUID,	@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', 'ByCert', 'ByKey'
	@CertID				CertID,
	@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 = 'ByCert' BEGIN
			EXEC dbo.CardEncryptByCertCreate
				@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,
				@CertID				= @CertID;
		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
) AS
-- Read a CardNoEncrypt record by CardID
-- Don't return anything; this is just exercising the SELECT
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber1	bigint;
	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode1	smallint;
	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;

-- DEBUG
--	SELECT @CardID AS '@CardID', * FROM dbo.CardNoEncrypt WHERE CardID = @CardID;
--	SELECT
--		CardNumber1,
--		SecurityCode1,
--		SecureString1
--	FROM dbo.CardNoEncrypt
--	WHERE CardID = @CardID;

	RETURN(0);
END;
GO

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

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

-- ======================================================
PRINT N'Function DecryptAsciiByCert';
-- ======================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'DecryptAsciiByCert' AND ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo') BEGIN
	DROP FUNCTION dbo.DecryptAsciiByCert;
END;
GO
CREATE FUNCTION dbo.DecryptAsciiByCert (
	@Cert				CertID,
	@EncryptedString	EncryptedByCert
)
RETURNS varchar(117)
WITH EXECUTE AS CALLER
AS
BEGIN
	RETURN CONVERT(varchar(117), DecryptByCert(@cert, @EncryptedString));
END;
GO

-- =====================================================
PRINT N'CardEncryptByCertReadByID';
-- =====================================================
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'CardEncryptByCertReadByID' AND ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo')
	DROP PROCEDURE dbo.CardEncryptByCertReadByID;
GO
CREATE PROCEDURE dbo.CardEncryptByCertReadByID (
	@Cert		CertID,
	@CardID		int
) AS
-- Read a CardEncryptByCertView record by CardID
-- Don't return anything; this is just exercising the SELECT
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber1	bigint;
	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode1	smallint;
	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.DecryptBigintByCert(@Cert,	CardNumber1),
		@cardNumber2	= CardNumber2,
		@cardNumber3	= CardNumber3,
		@cardNumber4	= CardNumber4,
		@securityCode1	= dbo.DecryptSmallintByCert(@Cert,	SecurityCode1),
		@securityCode2	= SecurityCode2,
		@securityCode3	= SecurityCode3,
		@securityCode4	= SecurityCode4,
		@secureString1	= dbo.DecryptAsciiByCert(@Cert,		SecureString1),
		@secureString2	= SecureString2,
		@secureString3	= SecureString3,
		@secureString4	= SecureString4
	FROM dbo.CardEncryptByCert
	WHERE CardID = @CardID;

-- DEBUG
--	SELECT @CardID AS '@CardID', * FROM dbo.CardEncryptByCert WHERE CardID = @CardID;
--	SELECT
--		dbo.DecryptBigintByCert(@Cert,		CardNumber1)	AS ByCertCdNum1,
--		dbo.DecryptSmallintByCert(@Cert,	SecurityCode1)	AS ByCertSecCd1,
--		dbo.DecryptAsciiByCert(@Cert,		SecureString1)	AS ByCertString1
--	FROM dbo.CardEncryptByCert
--	WHERE CardID = @CardID;

	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
) AS
-- Read a CardEncryptByKey record by CardID
-- Don't return anything; this is just exercising the SELECT
BEGIN
	SET NOCOUNT ON;

	DECLARE @cardNumber1	bigint;
	DECLARE @cardNumber2	bigint;
	DECLARE @cardNumber3	bigint;
	DECLARE @cardNumber4	bigint;
	DECLARE @securityCode1	smallint;
	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;

-- DEBUG
--	SELECT @CardID AS '@CardID', * FROM dbo.CardEncryptByKey WHERE CardID = @CardID;
--	SELECT
--		dbo.DecryptBigint(CardNumber1)			AS ByKeyCdNum1,
--		dbo.DecryptSmallint(SecurityCode1)		AS ByKeySecCd1,
--		dbo.DecryptSecureString(SecureString1)	AS ByKeyString1
--	FROM dbo.CardEncryptByKey
--	WHERE CardID = @CardID;

	RETURN(0);
END;
GO

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

	DECLARE @cardID			int;
	DECLARE @ct				int;
	DECLARE @d1				datetime;

	OPEN SYMMETRIC KEY PerfTestKey DECRYPTION BY CERTIFICATE PerfTestCert;

	SELECT @d1 = GETDATE();

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

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

--		PRINT '@cardID: ' + CONVERT(varchar(11), @cardID);	-- DEBUG

		IF @EncryptionType = 'None' BEGIN
			EXEC dbo.CardNoEncryptReadByID
				@CardID	= @cardID;
		END;
		ELSE IF @EncryptionType = 'ByCert' BEGIN
			EXEC dbo.CardEncryptByCertReadByID
				@Cert	= @CertID,
				@CardID	= @cardID;
		END;
		ELSE IF @EncryptionType = 'ByKey' BEGIN
			EXEC dbo.CardEncryptByKeyReadByID
				@CardID	= @cardID;
		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), @ReadMax)
			+ ', Encryption: ' + @EncryptionType
			+ ', MsDuration: ' + CONVERT(varchar(11), @MsDuration);
	END;
END;
GO

PRINT '<< DONE >>'
GO

--
---- Simple validation tests
--DECLARE @esi	EncryptedSmallint;
--DECLARE @ebi	EncryptedBigint;
--DECLARE @es1	EncryptedSecureString;
--DECLARE @ecsi	EncryptedByCert;
--DECLARE @ecbi	EncryptedByCert;
--
--DECLARE @cert	CertID;
--DECLARE @key	KeyGUID;
--
--SELECT @cert = Cert_ID('PerfTestCert');
--SELECT @cert AS '@cert';
--
--SELECT @key = Key_GUID('PerfTestKey');
--SELECT @key AS '@key';
--
--OPEN SYMMETRIC KEY PerfTestKey DECRYPTION BY CERTIFICATE PerfTestCert;
--
---- Key
--SELECT @esi = dbo.EncryptSmallint(@key, 1234);
--SELECT @ebi = dbo.EncryptBigint(@key, 12340000000);
--
--SELECT
--	@esi AS '@esi',
--	@ebi AS '@ebi';
--
--SELECT EncryptByKey(@Key, CONVERT(varchar(6), 1234));
--SELECT EncryptByKey(@Key, CONVERT(varchar(20), 12340000000));
--
--SELECT EncryptByKey(Key_GUID('PerfTestKey'), CONVERT(varchar(6), 1234));
--SELECT EncryptByKey(Key_GUID('PerfTestKey'), CONVERT(varchar(20), 12340000000));
--
--SELECT dbo.DecryptSmallint(@esi) AS 'DecryptedSmallint';
--SELECT dbo.DecryptBigint(@ebi) AS 'DecryptedBigint';
--
---- Cert
--SELECT @ecsi = dbo.EncryptSmallintByCert(@cert, 1234);
--
--SELECT @ecsi AS '@ecsi', LEN(@ecsi) AS 'Length - ByCert';
--
--SELECT dbo.DecryptSmallintByCert(@cert, @ecsi) AS 'DecryptedSmallint ByCert';
--
--SELECT @ecbi = dbo.EncryptBigintByCert(@cert, 1234567890000);
--
--SELECT @ecbi AS '@ecbi', LEN(@ecbi) AS 'Length - ByCert';
--
--SELECT dbo.DecryptBigintByCert(@cert, @ecbi) AS 'DecryptedBigint ByCert';

--SELECT * FROM dbo.CardNoEncrypt;
--SELECT * FROM dbo.CardEncryptByCertView;
--
--OPEN SYMMETRIC KEY PerfTestKey DECRYPTION BY CERTIFICATE PerfTestCert;
--
--SELECT
--	CardID,
--	dbo.DecryptBigint(CardNumber1)			AS 'CardNumber1',
--	CardNumber2,
--	CardNumber3,
--	CardNumber4,
--	dbo.DecryptSmallint(SecurityCode1)		AS 'SecurityCode1',
--	SecurityCode2,
--	SecurityCode3,
--	SecurityCode4,
--	dbo.DecryptSecureString(SecureString1)	AS 'SecureString1',
--	SecureString2,
--	SecureString3,
--	SecureString4
--FROM dbo.CardEncryptByKey;