-- 加密數據        
OPEN SYMMETRIC KEY PasswordSymmetric
    DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';

DECLARE @PlainText NVARCHAR(100) = N'敏感數據'; --需要加密
DECLARE @CipherText VARBINARY(256);

-- 加密
SELECT @CipherText = ENCRYPTBYKEY(KEY_GUID('PasswordSymmetric'), @PlainText);

-- 查看加密結果
SELECT @PlainText AS 明文, @CipherText AS 密文;

-- 解密
SELECT @PlainText AS 原始明文,@CipherText AS 加密數據,CONVERT(NVARCHAR(100),DECRYPTBYKEY(@CipherText)) AS 解密結果;

CLOSE SYMMETRIC KEY PasswordSymmetric;

-- 使用證書加密的對稱密鑰
OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE PasswordCert;

DECLARE @CreditCardNumber NVARCHAR(20) = '6222-5335-0012-3406';
DECLARE @EncryptedCard VARBINARY(256);

-- 加密信用卡號
SELECT @EncryptedCard = ENCRYPTBYKEY(KEY_GUID('SymmetricByCert'), @CreditCardNumber);

-- 解密信用卡號
SELECT 
    @CreditCardNumber AS 原始卡號,
    @EncryptedCard AS 加密卡號,
    CONVERT(NVARCHAR(20), DECRYPTBYKEY(@EncryptedCard)) AS 解密卡號;

CLOSE SYMMETRIC KEY SymmetricByCert;

-- 先打開主對稱密鑰
OPEN SYMMETRIC KEY PasswordSymmetric
    DECRYPTION BY PASSWORD = 'Pass#Tutorial#word';

-- 再打開被對稱密鑰加密的密鑰
OPEN SYMMETRIC KEY SymmetricBySy
    DECRYPTION BY SYMMETRIC KEY PasswordSymmetric;

DECLARE @Phone NVARCHAR(20) = '18900000000'
DECLARE @Email NVARCHAR(100) = 'user@example.com';
DECLARE @EncryptedPhone VARBINARY(256);
DECLARE @EncryptedEmail VARBINARY(256);

-- 加密移號碼
SELECT @EncryptedPhone = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Phone);
-- 加密郵箱
SELECT @EncryptedEmail = ENCRYPTBYKEY(KEY_GUID('SymmetricBySy'), @Email);

-- 解密移動號碼
SELECT 
    @Phone AS 原始移動號碼,
    @EncryptedPhone AS 加密移動號碼,
    CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedPhone)) AS 解密移動號碼;

-- 解密郵箱
SELECT 
    @Email AS 原始郵箱,
    @EncryptedEmail AS 加密郵箱,
    CONVERT(NVARCHAR(100), DECRYPTBYKEY(@EncryptedEmail)) AS 解密郵箱;

CLOSE SYMMETRIC KEY SymmetricBySy;
CLOSE SYMMETRIC KEY PasswordSymmetric;

-- 打開被非對稱密鑰加密的對稱密鑰
OPEN SYMMETRIC KEY SymmetricByAsy
    DECRYPTION BY ASYMMETRIC KEY PasswordAsymmetric WITH PASSWORD = 'Pass#Tutorial#word';

DECLARE @MedicalInsuranceCardNumber NVARCHAR(15) = '888-545-6004';
DECLARE @EncryptedMedicalInsuranceCardNumber VARBINARY(256);

-- 加密醫保卡號
SELECT @EncryptedMedicalInsuranceCardNumber = ENCRYPTBYKEY(KEY_GUID('SymmetricByAsy'), @MedicalInsuranceCardNumber);

-- 解密
SELECT 
    @MedicalInsuranceCardNumber AS 原始醫保卡號,
    @EncryptedMedicalInsuranceCardNumber AS 加密醫保卡號,
    CONVERT(NVARCHAR(15), DECRYPTBYKEY(@EncryptedMedicalInsuranceCardNumber)) AS 解密醫保卡號;

CLOSE SYMMETRIC KEY SymmetricByAsy;