

USE [DataEncryptDemo]
go

-- In order to use the synmmetric key, you need to open it first
OPEN SYMMETRIC KEY [SecretTable_SecretData_Key] 
    DECRYPTION BY CERTIFICATE [cert_SecretTable_SecretData_Key]
go

-- Now insert some secret data into the table
DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')
IF( @KeyGuid is not null )
BEGIN
	INSERT INTO [dbo].[CreditCards] VALUES ( 1, encryptbykey( @KeyGuid, N'4388-1234-1234-1234'))
	INSERT INTO [dbo].[CreditCards] VALUES ( 2, encryptbykey( @KeyGuid, N'4549-5678-5678-5678'))
END
ELSE
BEGIN
	PRINT 'Failed to obtain the symmetric key GUID'
END

-- INSERTED ENCRYPTED DATA

-- Now we can see the data in the table encrypted...
SELECT * FROM [dbo].[CreditCards]

-- SELECT DATA WITH ORDINARY SELECT

-- and unencrypted
SELECT CardId, 
       convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number' 
FROM   [dbo].[CreditCards]
go

-- SELECT DATA WITH DECRYPTION FUNCTION


-- A good recommendation is to close the key after you have finish to encrypt data
CLOSE SYMMETRIC KEY [SecretTable_SecretData_Key]
go

-- KEY CLOSED

-- Without the key open, the unencrypt function returns NULL
SELECT CardId, 
       convert( NVARCHAR(100), decryptbykey( CardNumber )) as 'Card Number' 
FROM   [dbo].[CreditCards]
go