-- Test EncryptByKey with increasing-length strings
-- ASCII   strings between length 0 and 7,943 work.
-- UNICODE strings between length 0 and 3,971 work.
SET NOCOUNT ON;
USE AdventureWorks;
GO

-- Create the database master key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = '##MS_DatabaseMasterKey##'
) BEGIN
	PRINT 'Create master key';
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'thuyuy73r_!r-kuwru4huy&Yar=-athE';
END;
GO

-- Create the database certificate
IF NOT EXISTS (
	SELECT 1
	FROM sys.certificates
	WHERE name = N'TestCert1'
) BEGIN
	PRINT 'Create certificate';
	CREATE CERTIFICATE TestCert1 WITH SUBJECT = N'TestCert1';
END;
GO

-- Create the symmetric key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = 'TestKey1'
) BEGIN
	PRINT 'Create key';
	CREATE SYMMETRIC KEY TestKey1
	WITH ALGORITHM = AES_256
	ENCRYPTION BY CERTIFICATE TestCert1;
END;
GO

OPEN SYMMETRIC KEY TestKey1 DECRYPTION BY CERTIFICATE TestCert1;
GO

DECLARE @ct					INT;
DECLARE @firstError			BIT;
DECLARE @inputASCII			VARCHAR(MAX);
DECLARE @inputUNICODE		NVARCHAR(MAX);
DECLARE @max				INT;
DECLARE @varbinaryASCII		VARBINARY(MAX);
DECLARE @varbinaryUNICODE	VARBINARY(MAX);

SELECT
	@firstError		= 0,
	@max			= 8000,
	@inputASCII		= '',
	@inputUNICODE	= N'';

SELECT @ct = 0;
WHILE @ct < @max BEGIN
	SELECT
		@ct				= @ct + 1,
		@inputASCII		= REPLICATE('a',  @ct),
		@inputUNICODE	= REPLICATE(N'a', @ct);

	SELECT
		@varbinaryASCII		= EncryptByKey (Key_GUID (N'TestKey1'), @inputASCII),
		@varbinaryUNICODE	= EncryptByKey (Key_GUID (N'TestKey1'), @inputUNICODE);

	SELECT
		LEN(@inputASCII)		AS 'Input Length',
		LEN(@varbinaryASCII)	AS 'ASCII',
		LEN(@varbinaryUNICODE)	AS 'UNICODE'

	IF @firstError = 0 AND LEN(@varbinaryUNICODE) IS NULL BEGIN
		SET @firstError = 1;
		PRINT '(UNICODE) Function returned NULL at input length = ' + CONVERT(varchar(10), LEN(@inputASCII));
	END;
	IF LEN(@varbinaryASCII) IS NULL BEGIN
		PRINT '(ASCII)   Function returned NULL at input length = ' + CONVERT(varchar(10), LEN(@inputASCII));
		RETURN;
	END;
END;
GO

-- ============================================================
-- How much space is required for integer datatypes?
-- ============================================================
DECLARE @varbinaryResult	VARBINARY(MAX);
DECLARE @varbinaryLength	INT;
DECLARE @b					BIT;
DECLARE @ti					TINYINT;
DECLARE @si					SMALLINT;
DECLARE @i					INT;
DECLARE @bi					BIGINT;

SELECT
	@b	= 1,					-- bit
	@ti = 255,					-- Max tinyint
	@si	= -32768,				-- Min smallint
	@i	= -2147483648,			-- Min int
	@bi	= -9223372036854775808;	-- Min bigint

SELECT @varbinaryResult = EncryptByKey (Key_GUID (N'TestKey1'), CONVERT(char(1), @b));
SELECT LEN(@varbinaryResult) AS 'Max varbinary Length - bit';

SELECT @varbinaryResult = EncryptByKey (Key_GUID (N'TestKey1'), CONVERT(varchar(3), @ti));
SELECT LEN(@varbinaryResult) AS 'Max varbinary Length - tinyint';

SELECT @varbinaryResult = EncryptByKey (Key_GUID (N'TestKey1'), CONVERT(varchar(6), @si));
SELECT LEN(@varbinaryResult) AS 'Max varbinary Length - smallint';

SELECT @varbinaryResult = EncryptByKey (Key_GUID (N'TestKey1'), CONVERT(varchar(11), @i));
SELECT LEN(@varbinaryResult) AS 'Max varbinary Length - int';

SELECT @varbinaryResult = EncryptByKey (Key_GUID (N'TestKey1'), CONVERT(varchar(20), @bi));
SELECT LEN(@varbinaryResult) AS 'Max varbinary Length - bigint';

/* Result
	bit:		52
	tinyint:	52
	smallint:	52
	int:		68
	bigint:		68
*/
GO