SQL SERVER T-SQL Programming

最新推荐文章于 2019-07-26 00:33:29 发布

原创最新推荐文章于 2019-07-26 00:33:29 发布 · 846 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server

sql server 同时被 2 个专栏收录

68 篇文章

订阅专栏

SQL Server 实战笔记

48 篇文章

订阅专栏

本文汇总了T-SQL编程中的实用技巧，包括日期时间处理、字符串操作、行列转换方法以及加密解密技术等内容，提供了丰富的示例帮助读者理解和应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

T-SQL编程技巧汇总。碰到有意思的编程技巧都记录下，方便以后查找

1 日期时间:

1.1 datetime类型前面4 个字节存储的是距离1900-01-01的天数；后面4个字节存储的是距离午夜以来，所有的秒数。

select datediff(day,0,getutcdate()) as days
select datediff(week,0,getutcdate()) as weeks

上面的sql查询出来的结果，第一个是距离1900-01-01相差的天数，第二个是距离1900-01-01以来相差的周数。0 在这里代表的意思就是1900-01-01，所以相差0天。

关于一周的定义，是从周天开始的，礼拜天算是一周的开始。一周的时间从上个礼拜天到这个礼拜六结束。

declare @loop int = 1
declare @begindate datetime = '1900-01-01'
while @loop <= 30
begin
select @begindate, datename(dw,@begindate) as day, datepart(week,@begindate)as weekNumber ,dateadd(week,1,@begindate) as aweekafter
set @begindate = @begindate + 1
set @loop = @loop +1
end

关于datename():
DATENAME ( datepart , date )

这里写图片描述

关于 datepart():
DATEPART(datepart,date)

这里写图片描述

这里区别在于weekday的描述，datename()以Monday,Tuesday等来表述，datepart()以1,2等来表述。

如何取每个月的第一天?

declare @begindate datetime = '2016-05-21'
select dateadd(dd,datepart(dd,@begindate)*-1 ,@begindate) + 1

时间刻度变量：

DECLARE @time time(0) = '16:30:23'
declare @date date
set @time = convert(time,getutcdate())
set @date = convert(date,getutcdate())
select @time ,@date

07:46:30 2016-05-23

时间差距，如何将当前的时间日期类型取出日期：

select convert(binary,getutcdate()) as binary_datetime
, convert(binary(8),getutcdate()) as binary_8_datetime
, getdate() as [current_date]
, datediff(day,0,getdate()) as days_after_19000101
, convert(bigint,convert(binary(8),getdate())) as x_wrong_format
, convert(int, substring(convert(varbinary(8),getdate()),1,4)) as days_after_19000101

这里写图片描述

最后一个表达式，取前面4个字节，也就是取了日期。因为时间日期型数据存储的是，前4个字节存储日期，后四位字节存储时间。

2 字符串:

Stuff(): 填充覆盖：根据填充字符长短，相应增加字符长度

STUFF ( character_expression , start , length , replaceWith_expression )
declare @stuffstring varchar(200) = 'hello world!'
select @stuffstring,stuff(@stuffstring,len(@stuffstring) , 10,', T-SQL')

结果：

hello world! hello world, T-SQL

Replace():替换覆盖

REPLACE ( string_expression , string_pattern , string_replacement )
declare @stuffstring varchar(200) = 'hello world!'
select @stuffstring,stuff(@stuffstring,len(@stuffstring) , 10,', T-SQL'),replace(@stuffstring,' ','T-SQL')

结果：

hello world! hello world, T-SQL helloT-SQLworld!

Stuff，按照字符位置来填充；Replace，按照指定字符匹配模式来替换

3 行列转换

–1 rows of records transformed to columns 行转列

if exists(select 1 from tempdb.sys.tables where upper(name) like upper('%tempScores%'))
drop table #tempScores
create table #tempScores ( studentName varchar(200), className varchar(200), classScore int )
insert into #tempScores (studentName,className,classScore)
select 'Alex' as studentName, 'English' as className, 80 as classScore
union
select 'Alex' as studentName, 'Math' as className, 90 as classScore
union
select 'Ken' as studentName, 'English' as className, 60 as classScore
union
select 'Ken' as studentName, 'Math' as className, 80 as classScore
union
select 'Ken' as studentName, 'Sport' as className, 100 as classScore
select * from #tempScores
select studentName, ps.English, ps.Math, ps.Sport
from #tempScores ts
pivot (
max(ts.classScore)
for className in(English, Math,Sport)
) ps

这里写图片描述

这里是行转列的例子。对于没有数值的那些行，转换过来之后显示null。Pivot在这里的作用，就是根据某一列的散列值做聚合。我们可以再进一步，如果不知道散列值有多少，那么怎么动态的区生成这个pivot的语句呢？

if exists(select 1 from tempdb.sys.tables where upper(name) like upper('%tempScores%'))
drop table #tempScores
declare @columnheader nvarchar(max) ;
declare @columnfilter nvarchar(max) ;
declare @sqlstatement nvarchar(max) ;
create table #tempScores ( studentName varchar(200), className varchar(200), classScore int )
insert into #tempScores (studentName,className,classScore)
select 'Alex' as studentName, 'English' as className, 80 as classScore
union
select 'Alex' as studentName, 'Math' as className, 90 as classScore
union
select 'Ken' as studentName, 'English' as className, 60 as classScore
union
select 'Ken' as studentName, 'Math' as className, 80 as classScore
union
select 'Ken' as studentName, 'Sport' as className, 100 as classScore
select * from #tempScores
select @columnheader = ( select distinct className +',' from #tempScores for xml path(''))
select @columnfilter = left(@columnheader ,len(@columnheader) - 1 )
select @columnheader = 'ps.'+replace(left(@columnheader,len(@columnheader)-1),',',',ps.')
select @columnheader , @columnfilter
select @sqlstatement = N'
select studentName, ' + @columnheader + N'
from #tempScores ts
pivot (
max(ts.classScore)
for className in(' + @columnfilter +N')
) ps '
exec sp_executesql @sqlstatement

–2 columns of records transformed to rows 列转行

if exists(select 1 from tempdb.sys.tables where upper(name) like upper('%tempScores%'))
drop table #tempScores
create table #tempScores (studentName varchar(200), English int, Math int, Sport int)
insert into #tempScores (studentName, English, Math, Sport)
select 'Alex' as studentName, 80 as English, 90 as Math, Null as Sport
union
select 'Ken' as studentName, 60 as English, 80 as Math, 100 as Sport
select * from #tempScores
select studentName, ps.ClassName, ps.Scores
from #tempScores
unpivot
(
Scores for ClassName in ( English, Math, Sport)
) ps

这里写图片描述

这里unpivot的作用就是定义一个带值的列，并且将原来几个列的值映射到这个列上去，原来的几个列的列名就变为一个列的散列值。最终的两个列都是可以自定义的，带值的列放unpivot的for子句之前。如果值是null的，在转换过程中就被过滤掉。所以要保留 null的列，必须将Null转变为一个有意思的值。
同样，这里也有动态列的情况：

use lenistest3
go
if exists(select 1 from tempdb.sys.tables where upper(name) like upper('%tempScores%'))
drop table #tempScores
declare @columnheader nvarchar(max) ;
declare @columnfilter nvarchar(max) ;
declare @sqlstatement nvarchar(max) ;
create table #tempScores (studentName varchar(200), English int, Math int, Sport int)
insert into #tempScores (studentName, English, Math, Sport)
select 'Alex' as studentName, 80 as English, 90 as Math, isnull(Null ,0) as Sport
union
select 'Ken' as studentName, 60 as English, 80 as Math, 100 as Sport
select * from #tempScores
select @columnfilter = (select name + ',' from tempdb.sys.columns where object_id in (select object_id from tempdb.sys.tables where upper(name) like upper('%tempScores%')) and name<>'studentName' for xml path(''))
select @columnfilter = left(@columnfilter,len(@columnfilter) - 1)
select @columnheader = N'ps.ClassName,ps.Scores'
select @columnfilter, @columnheader
select @sqlstatement = N'
select studentName, ' + @columnheader + '
from #tempScores
unpivot
(
Scores for ClassName in ( ' + @columnfilter +')
) ps '
exec sp_executesql @sqlstatement

4 加密与解密

举个简单的例子：
加密函数：

create function   dbo.encryptbypassphrasepwd_1(  
@password nvarchar(200))  
returns varbinary(max)  
as   
begin   
 declare @pwdencrypted varbinary(max) ;  
 set @pwdencrypted = ENCRYPTBYPASSPHRASE(N'123456',@password,1,CONVERT(varbinary,123)) ;  
 return @pwdencrypted;  
end

解密函数：

create function    dbo.decryptbypassphrasepwd_1(
@encryptpwd varbinary(max))
returns nvarchar(max)
as 
begin 
    declare @clear_text nvarchar(200) ;
    set @clear_text = convert(nvarchar, DECRYPTBYPASSPHRASE(N'123456',@encryptpwd,1,CONVERT(varbinary,123)));
    return @clear_text ;
end

加密过程：

declare @pwd varbinary(max) 
select @pwd = dbo.encryptbypassphrasepwd_1(  N'lewis123。')
select @pwd

解密过程：

declare @clear_text nvarchar(200) ;
declare @pwdencrypted varbinary(max) = 0x01000000897C94116F62ABB73BE41BD1758616C43314B7965BA8D046C268E2BB39E80CC468C4BA4CFDA0E6EE9204181139FAAAA23E39CA382A379B4C10DE6ABFF71AA763
select @clear_text = dbo.decryptbypassphrasepwd_1(  @pwdencrypted );
select @clear_text ;

这里要着重注意的是传入的varbinary(max)参数不要传入nvarchar(max)然后再转varbinary.

我们看看试着把函数改写成这样：

create function    dbo.decryptbypassphrasepwd_2(
@encryptpwd nvarchar(max))
returns nvarchar(max)
as 
begin 
    declare @clear_text nvarchar(200) ;
    declare @pwd varbinary(max) = convert(varbinary(max),@encryptpwd) ;
    set @clear_text = convert(nvarchar, DECRYPTBYPASSPHRASE(N'123456',@pwd,1,CONVERT(varbinary,123)));
    return @clear_text ;
end

然后解密:

declare @clear_text nvarchar(200) ;
declare @pwdencrypted nvarchar(max) = N'0x01000000897C94116F62ABB73BE41BD1758616C43314B7965BA8D046C268E2BB39E80CC468C4BA4CFDA0E6EE9204181139FAAAA23E39CA382A379B4C10DE6ABFF71AA763'
select @clear_text = dbo.decryptbypassphrasepwd_2(  @pwdencrypted );
select @clear_text ;

发现结果是NULL。但是如果我们就直接放入十六进制，比如

declare @clear_text nvarchar(200) ;
declare @pwdencrypted nvarchar(max) = 0x01000000897C94116F62ABB73BE41BD1758616C43314B7965BA8D046C268E2BB39E80CC468C4BA4CFDA0E6EE9204181139FAAAA23E39CA382A379B4C10DE6ABFF71AA763
select @clear_text = dbo.decryptbypassphrasepwd_2(  @pwdencrypted );
select @clear_text ;

却又是可以解密完成的。

给表字段加密：

create table dbo.test(testid bigint, test_text varchar(200),test_text_pwd varbinary(max)) 
go 
insert into dbo.test(testid,test_text) values(1,'1-2X37')
insert into dbo.test(testid,test_text) values(2,'2-2x37')

update dbo.test set test_text_pwd = dbo.encryptbypassphrasepwd_1(test_text)

给表字段解密：

select testid,test_text,dbo.decryptbypassphrasepwd_1(test_text_pwd) as clear_text from dbo.test

另外这里的加密解密函数不能改成存储过程，这样加密解密的结果始终是在变化的，不是持久化的。

Create  procedure dbo.encryptbypassphrasepwd
@password nvarchar(200)
as 
begin 
    declare @pwdencrypted varbinary(max) ;
    set @pwdencrypted = ENCRYPTBYPASSPHRASE(N'123456',@password,1,CONVERT(varbinary,123)) ;
    return @pwdencrypted;
end 

declare @pwd varbinary(max) 
exec @pwd = dbo.encryptbypassphrasepwd  N'lewis123.'
select @pwd

每次出来的加密值都是在变化的，也就是说不可持久化

Asymmetric encryption: 一个public key, 一个 private key
Symmetric encryption:一段加密隐码，加密解密都靠这个隐码

Symmetric encryption 例子, 加密一列数据:

USE AdventureWorks2012;
–If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = ‘23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj’
GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = ‘Customer Credit Card Numbers’;
GO
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
– Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
– Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
– Encrypt the value in column CardNumber using the
– symmetric key CreditCards_Key11.
– Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID(‘CreditCards_Key11’)
, CardNumber, 1, HashBytes(‘SHA1’, CONVERT( varbinary
, CreditCardID)));
GO
– Verify the encryption.
– First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
GO
– Now list the original card number, the encrypted card number,
– and the decrypted ciphertext. If the decryption worked,
– the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
AS ‘Encrypted card number’, CONVERT(nvarchar,
DecryptByKey(CardNumber_Encrypted, 1 ,
HashBytes(‘SHA1’, CONVERT(varbinary, CreditCardID))))
AS ‘Decrypted card number’ FROM Sales.CreditCard;
GO

来自microsoft : Encrypt a Column of Data

Column-level encryption (aka cell-level encryption) was introduced in SQL Server 2005 and is available in all editions of SQL Server, including the free SQL Server Express edition. To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can affect performance. Encryption of the database occurs at the page level, but when those pages are read to buffer pool, they’re decrypted. Data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate. The supported algorithms for column-level encryption are AES with 128,196,256 bit keys and 3DES. To learn more about column-level encryption, see the MSDN article “Encrypt a Column of Data.”
参考: http://sqlmag.com/database-security/sql-server-encryption-options

上面的这段引用来自网络，讲的大概意思是，加密一列数据，可以用AES（128，196，256位密钥）或者3DES（triple DES）算法.这里写代码片

我们从头过一遍这段加密列的脚本。

1 创建master key: master key采用的是symmetric encryption(对称加密). 作用是保护证书的私钥和当前数据库中的asymmetric key。如果没有 service master key保护，则在使用master key的时候需要open master key. Master Key采用AES和口令加密，在SQL SERVER 2008及2008 R2 中也可以使用3DES(TRIPLE DES)来加密。

语法: CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘23987hxJ#KL95234nl0zBe’;

创建一个 MASTER KEY ENCRPTION, 这里要注意的是 password需要符合 windows密码要求。
创建完之后，可以查看创建的结果：

select * from sys.symmetric_keys
select * from sys.asymmetric_keys
select
name
, is_master_key_encrypted_by_server
from sys.databases
where name = ‘lenistest5’

这里 IS_MASTER_KEY_ENCRYPTED_BY_SERVER提示 MASTER KEY是不是被 service master key加密了。
打开一个 master key用来加密盒解密，这是一个必要步骤，每一次加密解密之前，都要先打开key.

语法：OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘password’
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘23987hxJ#KL95234nl0zBe’;

这儿要注意的是，当一个数据库被恢复到一个新的instance上面的时候，这个数据库是不会自动拷贝一份Master Key(经过 service master key加密的)到本地数据库的，所以我们要打开这个master key. 当我们需要自动打开这个master key的时候，可以用alter master key设置。当然我们也可以drop掉这个master key : DROP MASTER KEY. 如果有其他密钥，证书受它保护地时候，drop会失败。

看到一篇很有意思的文章 :
Can’t understand the difference between hashing and encryption?
https://blogs.msdn.microsoft.com/sqlserverfaq/2009/03/30/cant-understand-the-difference-between-hashing-and-encryption/

文中讲到了hashing与encryption的区别:
Encryption有两个特点：一是每次加密完之后，密文都是不一样的；二是密文都可以被解密。
Hashing有三个特点：一是每次加密完(hashing)之后,密文都是一样的；二是密文不可以被解密；三是不同的明文经过同一个hash算法，得到一个同一的值。

可以看看hashbytes的用法：
HASHBYTES ( ‘’, { @input | ‘input’ } )
::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

DECLARE @HashThis nvarchar(4000);
SET @HashThis = CONVERT(nvarchar(4000),’dslfdkjLK85kldhnv$n000#knf’);
SELECT HASHBYTES(‘SHA1’, @HashThis);

输入可以是varchar,nvarchar,varbinary. 返回值一定是varbinary.

这里也有篇很有意思的文章，关于.net加密算法，总是针对一段相同的明文文本产生一段相同的密文。这也叫加密?
c# - How to obtain different cipher text for same plain text using AES
http://stackoverflow.com/questions/22126412/how-to-obtain-different-cipher-text-for-same-plain-text-using-aes

我们碰到一个有趣的需求，针对同一段明文，加密后需要输出同一段相同的密文，前提是在T-SQL里面实现。实现这种加密的时候，只能用RC4等加密算法，很不幸的是，这种加密算法在SQL Server 2008R2往上都将淘汰，为了使用这种算法，需要将数据库的competible level降为90或者110.

use lenistest5
go
–drop function dbo.EncryptByRC4
alter procedure dbo.EncryptByRC4(
@ID nvarchar(20)
,@ClearText nvarchar(max)
,@CipherText varbinary(max) output
)
as
begin
declare @symmetric_sql nvarchar(max) ;
declare @symmetric_header nvarchar(200) = ‘sym_key_’+replace(@ID,’-‘,”)
declare @symmetric_password nvarchar(max) = N’hoover’ + @ID +’.’ ;
declare @ciphertext varbinary(max) ;
if not exists(select 1 from sys.symmetric_keys where name = @symmetric_header )
begin
set @symmetric_sql = N’create symmetric key ‘+ @symmetric_header+ ’ with algorithm = RC4 encryption by password =N”’+
@symmetric_password + ”’
open symmetric key ‘+ @symmetric_header + ’ decryption by password =N”’
+ @symmetric_password + ”’
select @ciphertext = encryptbykey(key_guid(”’+@symmetric_header+”’),”’+convert(nvarchar(max),@ClearText)+”’)
close symmetric key ’ + @symmetric_header ;
–print @symmetric_sql
exec sp_executesql @stmt = @symmetric_sql ,@params =N’@ciphertext varbinary(max) output’,@ciphertext = @CipherText output ;
end
else
begin
set @symmetric_sql = N’
open symmetric key ‘+ @symmetric_header + ’ decryption by password =N”’
+ @symmetric_password + ”’
select @ciphertext = encryptbykey(key_guid(”’+@symmetric_header+”’),”’+convert(nvarchar(max),@ClearText)+”’)
close symmetric key ’ + @symmetric_header ;
–print @symmetric_sql
exec sp_executesql @stmt = @symmetric_sql ,@params =N’@ciphertext varbinary(max) output’,@ciphertext = @CipherText output ;
end
end
go
alter procedure dbo.DecryptByRC4(
@ID nvarchar(20)
,@ClearText nvarchar(max) output
,@CipherText varbinary(max)
)
as
begin
declare @symmetric_sql nvarchar(max) ;
declare @symmetric_header nvarchar(200) = ‘sym_key_’+replace(@ID,’-‘,”)
declare @symmetric_password nvarchar(max) = N’hoover’ + @ID +’.’ ;
declare @ciphertext varbinary(max) ;
if not exists(select 1 from sys.symmetric_keys where name = @symmetric_header )
begin
set @symmetric_sql = N’create symmetric key ‘+ @symmetric_header+ ’ with algorithm = RC4 encryption by password =N”’+
@symmetric_password + ”’
open symmetric key ‘+ @symmetric_header + ’ decryption by password =N”’
+ @symmetric_password + ”’
select @ClearText = convert(varchar,decryptbykey(@CipherText))
close symmetric key ’ + @symmetric_header ;
–print @symmetric_sql
exec sp_executesql @stmt = @symmetric_sql
,@params =N’@CipherText varbinary(max), @ClearText nvarchar(max) output’
,@CipherText = @CipherText
,@ClearText = @ClearText output ;
end
else
begin
set @symmetric_sql = N’
open symmetric key ‘+ @symmetric_header + ’ decryption by password =N”’
+ @symmetric_password + ”’
select @ClearText = convert(varchar,decryptbykey(@CipherText))
close symmetric key ’ + @symmetric_header ;
–print @symmetric_sql
exec sp_executesql @stmt = @symmetric_sql
,@params =N’@CipherText varbinary(max), @ClearText nvarchar(max) output’
,@CipherText = @CipherText
,@ClearText = @ClearText output ;
end
end

declare @ID nvarchar(20) = ‘2-x232’
declare @ClearText nvarchar(200) = N’lewis’
declare @ciphertext varbinary(max)

declare my_cur cursor for
select ‘2-x232’ as ID, N’lewis’ as ClearText
union all
select ‘2-x232’ as ID, N’lewis’ as ClearText
open my_cur
fetch next from my_cur into @ID,@ClearText

while @@FETCH_STATUS = 0
begin
exec dbo.EncryptByRC4 @ID, @ClearText,@ciphertext output
select @ID as ID, @ClearText as ClearText, @ciphertext as CipherText
fetch next from my_cur into @ID,@ClearText
set @ciphertext = null
select @ciphertext
end
close my_cur
deallocate my_cur

declare @ID nvarchar(20) = '20-x232'
declare @ClearText nvarchar(max) ;
declare @ciphertext varbinary(max) =0x003D25B2E76D8B44817B679A28441059010000001BDA96E0D14742E46062269ED3
exec dbo.DecryptByRC4 @ID = @ID, @ClearText = @ClearText output,@CipherText = @ciphertext
select @ClearText

这里面要注意的事情有很多，一是注意open key与close key的重要性。如果每一次加密解密都不close key,整个session都是处于解密状态，任何值都能解密出明文来。

上面的例子适合单条数据加密，当我们要对一列数据加密，就不管用了。原因是我们的加密过程是用了存储过程，而对一列数据加密，从性能上来讲，肯定是用function比用存储过程作历遍来的快。所以我们可以采取下面的方法，先打开一个symmetric key ,然后用一列的值作deriven来创建密文, 完了之后把门(key)关上。

/*
1. open then GateWay Symmetric Key
2. Encrypt the Clear Text ;
3. Decrypt the Cipher Text;
*/

create procedure dbo.openSymmetricKey
as
begin
if not exists( select 1 from sys.symmetric_keys where name ='symmetrickey_gateway')
begin
create symmetric key symmetrickey_gateway
with algorithm = RC4
encryption by password = N'hoover123456.' ;
open symmetric key symmetrickey_gateway
      decryption by password = N'hoover123456.' ;
      end
else
begin
open symmetric key symmetrickey_gateway
decryption by password = N'hoover123456.' ;
end
end
go

create function dbo.EncryptByID (
@ID nvarchar(20)
, @ClearText nvarchar(max)
) returns varbinary(max)
as
begin
declare @CipherText varbinary(max) ;
select @CipherText = encryptbykey(key_guid('symmetrickey_gateway'),@ClearText,1,convert(varbinary,@ID)) ;
return @CipherText ;
end
go

create function dbo.DecryptByID(
@ID nvarchar(20)
, @CipherText varbinary(max)
) returns nvarchar(max)
as
begin
declare @ClearText nvarchar(max) ;
select @ClearText = convert(nvarchar(max),decryptbykey(@CipherText,1,convert(varbinary,@ID)));
return @ClearText ;
end
go

-- 开始测试
if exists( select 1 from tempdb.sys.tables where upper(name) like '%SAMPLE_CUR%')
begin
drop table #sample_cur;
end
create table #sample_cur ( ID nvarchar(20), ClearText nvarchar(max), CipherText varbinary(max)) ;
insert into #sample_cur (ID,ClearText)
select N'1-2x34' as ID, N'lewis' as ClearText
union all
select N'2-3X45' as ID, N'Hoover' as ClearText
;
exec dbo.openSymmetricKey
update #sample_cur set CipherText = dbo.EncryptByID(ID,ClearText)
select ID
,ClearText
,CipherText
,dbo.DecryptByID(ID,CipherText) as DecryptedText
from #sample_cur ;

简单的把语法帖下，关于加密解密，语法要领还是挺多的, 关于创建 symmetric key就有证书加密，密码加密等，保护这些 symmetric key还需要考虑数据库的备份，symmetric key的保密方法等。

CREATE SYMMETRIC KEY key_name 
    [ AUTHORIZATION owner_name ]
    [ FROM PROVIDER provider_name ]
    WITH <key_options> [ , ... n ]
    |
    ENCRYPTION BY <encrypting_mechanism> [ , ... n ]
<key_options> ::=
    KEY_SOURCE = 'pass_phrase'
    |
    ALGORITHM = <algorithm>
    |
    IDENTITY_VALUE = 'identity_phrase'
    |
    PROVIDER_KEY_NAME = 'key_name_in_provider' 
    |
    CREATION_DISPOSITION = {CREATE_NEW | OPEN_EXISTING }
<algorithm> ::=
    DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128
    | DESX | AES_128 | AES_192 | AES_256 
<encrypting_mechanism> ::=
    CERTIFICATE certificate_name 
    |
    PASSWORD = 'password' 
    |
    SYMMETRIC KEY symmetric_key_name 
    |
    ASYMMETRIC KEY asym_key_name

我们的例子里，用了最简单的创建 symmetric key的方法：

create symmetric key sym_key
with algorithm = RC4
encryption by password = ‘hoover123456.’

我们用了 RC4 的算法，RC4 算法有两个缺陷，一是需要database COMPATIBILITY设置为90，100，110，语法是
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 130 | 120 | 110 | 100 | 90 }

二是，只在SQL SERVER 2008/R2里面能直接使用，之后的版本需要设置 compatibility来向前兼容。也就是说2008R2之后的版本都不再支持这个算法。因为这个算法出来的密文，针对相同的明文都加密成相同的密文。
encryption by password = ‘hoover123456.’
加密密码必须符合windows安全认证要求。
加密：
EncryptByKey ( key_GUID , { ‘cleartext’ | @cleartext }
[, { add_authenticator | @add_authenticator }
, { authenticator | @authenticator } ] )
select @CipherText = encryptbykey(key_guid(‘symmetrickey_gateway’),@ClearText,1,convert(varbinary,@ID)) ;
如果我们需要一段口令或者附加码来加密，那么可以增加 authenticator，authenticator的类型是sysname。经过口令或者附加码来加密，解密的时候同样需要制定这段口令或者附加码。
解密：
DecryptByKey ( { ‘ciphertext’ | @ciphertext }
[ , add_authenticator, { authenticator | @authenticator } ] )
select @ClearText = convert(nvarchar(max),decryptbykey(@CipherText,1,convert(varbinary,@ID)));
加密或者解密之前，都需要将symmetric key打开。

4 针对日期转换成字符串的处理，很多时候忘记转换过后的格式，所以我们用个脚本来看统一的模拟下每一个选项对应的输出格式:

set nocount on

declare @currenttime datetime = getutcdate() ;

declare @incrementsource int = 0 ;

declare @expression varchar(max) = '';

declare @sqlcommand nvarchar(max) ;

declare @sqlheader nvarchar(max) =N' begin try' +char(13)+char(10) ;

declare @sqltail nvarchar(max) = char(13) + char(10)+ N'end try ' +char(13) + char(10) +

'begin catch '+char(13) + char(10)+' set @error = 1' +char(13)+char(10)+'end catch';

declare @error int ;

if not exists(select 1 from tempdb.sys.tables where upper(name) like '%VALIDOPTIONS%')

create table #validoptions(optionid int) ;

if not exists(select 1 from tempdb.sys.tables where upper(name) like '%INVALIDOPTIONS%')

create table #invalidoptions(optionid int) ;

begin try

while @incrementsource <= 200

begin

set @error = 0 ;

set @sqlcommand = @sqlheader +

' select @expression + convert(varchar,@current,@int) '

+ @sqltail ;

--select @sqlcommand;

select @expression = ' convert(varchar,'''+convert(varchar,@currenttime)+''','+convert(varchar,@incrementsource)+'):';

--select @expression ;

exec sp_executesql @stat = @sqlcommand,@params = N'@expression varchar(max), @current datetime, @int int , @error int output',

@error = @error output,@int = @incrementsource,@current = @currenttime,@expression = @expression;

if @error = 1

begin

select ' convert(varchar,'''+convert(varchar,@currenttime)+''','+convert(varchar,@incrementsource)+') is not valid expression' ;

insert into #invalidoptions(optionid) values(@incrementsource) ;

end

else

insert into #validoptions(optionid) values(@incrementsource) ;

set @incrementsource = @incrementsource + 1 ;

end

end try

begin catch

select error_message() ;

end catch

select 'convert(varchar,@datetime,'+ convert(varchar,optionid)+'):' + convert(varchar,getdate(),optionid) from #validoptions ;

drop table #invalidoptions

drop table #validoptions ;

convert(varchar,@datetime,0):Jul 7 2016 10:23PM

convert(varchar,@datetime,1):07/07/16

convert(varchar,@datetime,2):16.07.07

convert(varchar,@datetime,3):07/07/16

convert(varchar,@datetime,4):07.07.16

convert(varchar,@datetime,5):07-07-16

convert(varchar,@datetime,6):07 Jul 16

convert(varchar,@datetime,7):Jul 07, 16

convert(varchar,@datetime,8):22:23:07

convert(varchar,@datetime,9):Jul 7 2016 10:23:07:277PM

convert(varchar,@datetime,10):07-07-16

convert(varchar,@datetime,11):16/07/07

convert(varchar,@datetime,12):160707

convert(varchar,@datetime,13):07 Jul 2016 22:23:07:277

convert(varchar,@datetime,14):22:23:07:277

convert(varchar,@datetime,20):2016-07-07 22:23:07

convert(varchar,@datetime,21):2016-07-07 22:23:07.277

convert(varchar,@datetime,22):07/07/16 10:23:07 PM

convert(varchar,@datetime,23):2016-07-07

convert(varchar,@datetime,24):22:23:07

convert(varchar,@datetime,25):2016-07-07 22:23:07.277

convert(varchar,@datetime,100):Jul 7 2016 10:23PM

convert(varchar,@datetime,101):07/07/2016

convert(varchar,@datetime,102):2016.07.07

convert(varchar,@datetime,103):07/07/2016

convert(varchar,@datetime,104):07.07.2016

convert(varchar,@datetime,105):07-07-2016

convert(varchar,@datetime,106):07 Jul 2016

convert(varchar,@datetime,107):Jul 07, 2016

convert(varchar,@datetime,108):22:23:07

convert(varchar,@datetime,109):Jul 7 2016 10:23:07:277PM

convert(varchar,@datetime,110):07-07-2016

convert(varchar,@datetime,111):2016/07/07

convert(varchar,@datetime,112):20160707

convert(varchar,@datetime,113):07 Jul 2016 22:23:07:277

convert(varchar,@datetime,114):22:23:07:277

convert(varchar,@datetime,120):2016-07-07 22:23:07

convert(varchar,@datetime,121):2016-07-07 22:23:07.277

convert(varchar,@datetime,126):2016-07-07T22:23:07.277

convert(varchar,@datetime,127):2016-07-07T22:23:07.277

convert(varchar,@datetime,130): 2 ???? 1437 10:23:07:277PM

convert(varchar,@datetime,131): 2/10/1437 10:23:07:277PM