22. SQL -- SQL Server 2005 和SQL Server 2008新特性

• SQL SERVER 2005 与SQL SERVER 2008 新特性

SQLSERVER 2005 新特性

Enhanced Data Types：存储最大8K-2G

Partitioned Table（分区表）：数据拆分管理

背景：一个公司，有目前现存数据以及历史数据，各在一台SER 上，服务器访问方式：

SQL 2000

TABLE 1：在一张table 中，对数据进行分区

Enhanced Indexing Features

SER 1 SER 2

现存数据 TABLE 1 TABLE 1 历史数据

分区视图 对2 个table 进行链接

CLIENT

CLIENT 访问的是分区视图

Use the OUTPUT Keyword

Storing XML in the Database

The Common Language Runtime

The Database Engine

SQL Server 2005 CLR Integration

SQL Server Projects in Visual Studio 2005

Notification Services

Notification Services Architecture

An Adventure Works Notification Services Application

Service Broker

Reporting Services

Analysis Services

Snapshot Isolation Level

SQLiMail

SQL 2008 新特性

1)、设置状态栏颜色

2)、活动监视 DB 实例 → 右键 → 活动监视

3)、对象资源管理器详细信息 视图 →对象资源管理器详细信息

4)、数据库对象搜索 视图 → 对象资源管理器详细信息 → DB → 插索

5)、智能感知 在输入语句时，会智能感知函数、对象等信息

6)、T-SQL 调试

7)、多服务器查询

8)、加密

加密方式：透明数据加密 备份加密 HASH 加密 用密加密数据

创建对称密钥加密据

用证书加密数据

A、透明数据加密: 整个数据库可以通过SQL 引擎加密。该方式加密了所有数据库的数据和数据库的日志文件。通过使用这个包罗万象的方式，所有的索引和表都是加密的。不必更改你的应用程序。

加密步骤：

(1)、查看master 数据库是否被加密SELECT name,is_master_key_encrypted_by_server FROM sys.databases;

(2)、在主数据库中创建一个主密钥

--创建master 数据库下的主数据库密钥

CREATE MASTER KEY ENCRYPTION BYPASSWORD = N‘passw0rd‘;

/*

RESTORE SERVICE MASTER KEYFROMFILE = ‘c:\SqlBackup\SMK.bak‘

DECRYPTION BY PASSWORD =‘passw0rd‘

go

--重新生成数据库主密钥

ALTER MASTER KEY[FORCE]REGENERATE WITH ENCRYPTION BY

PASSWORD = ‘password‘

--删除数据库主密钥

GO

DROP MASTER KEY

*/

 (3)、查看master 数据库下的密钥信息

SELECT * FROM sys.symmetric_keys;

(4)、创建证书用来保护数据库加密密钥(DEK)

CREATE CERTIFICATEmaster_server_cert WITH SUBJECT = N‘Master Protect DEK

Certificate‘;

(5)、创建测试数据库

IF DB_ID(‘db_encryption_test‘) ISNOT NULL

DROP DATABASE db_encryption_test

go

CREATE DATABASEdb_encryption_test;

--创建由master_server_cert 保护的DEK 数据库加密密钥（对称密钥）

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATEmaster_server_cert;

GO

--系统提示你，立刻备份证书;这里备份证书。

USE master;

BACKUP CERTIFICATEmaster_server_cert TO FILE = ‘c:\master_server_cert.cer‘

--不要保存在根目录，不然报错

WITH PRIVATE KEY (

FILE =‘c:\master_server_cert.pvk‘ , --不要保存在根目录，不然报错

ENCRYPTION BY PASSWORD =‘passw0rd‘ );

--相应的，我们也备份一下数据库主密钥(master)

USE master;

--如果没有启用主密钥的自动解密功能

--OPEN MASTER KEY DECRYPTION BYPASSWORD = ‘passw0rd‘;

BACKUP MASTER KEY TO FILE =‘c:\master.cer‘ --不要保存在根目录，不然报错

ENCRYPTION BY PASSWORD =‘passw0rd‘;

GO

--生产环境下，设置成单用户在运行加密

ALTER DATABASE db_encryption_testSET SINGLE_USER WITH ROLLBACK

IMMEDIATE;

GO

--备份成功以后，开启TDE 加密

ALTER DATABASE db_encryption_testSET ENCRYPTION ON;

GO

--设置多用户访问

ALTER DATABASE db_encryption_testSET MULTI_USER WITH ROLLBACK

IMMEDIATE;

GO

--查看db_encryption_test 数据库是否被加密 encryption_state:3 TDE加密了,2 正在加密

SELECTDB_NAME(database_id),encryption_state FROM sys.dm_database_encryption_keys;

/*

发现tempdb 也被加密了。MSDN 解释是：如果实例中有一个数据库启用了TDE加密，那么tempdb 也被加密

*/

--接下来,找另外一台机器或者实例来测试,如果数据文件被盗走了,防止附加的测试.

USE master;

EXEC sp_detach_dbN‘db_encryption_test‘;

GO

B、备份加密: SQL Server 2008 加密备份的方式可以防止数据泄漏和被窜改。另外，备份的恢复可以限于特点的用户。

C、HASH 加密：SQL SERVER 允许5 种不同的哈希算法SHA、SHA1、MD2、MD4、MD5，哈希算法对大小写敏感

declare @hash varchar(100)

select @hash=‘encryption text‘

--SHA

select HASHBYTES(‘SHA‘,@hash)

--SHA1

select HASHBYTES (‘SHA1‘, @hash)

--MD2

select HASHBYTES (‘MD2‘, @hash)

--MD4

select HASHBYTES (‘MD4‘, @hash)

--MD5

select HASHBYTES (‘MD5‘, @hash)

D、用密码加密数据:

declare @encryptedtextvarbinary(80)

select @encryptedtext=ENCRYPTBYPASSPHRASE(‘passw0rd‘,‘encryptiontext‘)

select@encryptedtext,CAST(DECRYPTBYPASSPHRASE(‘password‘,@encryptedtext)

as varchar(max))

select@encryptedtext,CAST(DECRYPTBYPASSPHRASE(‘passw0rd‘,@encryptedtext)

as varchar(max))

E、创建对称密钥来加密数据:

use AdventureWorks --打开数据库

go

--1、建立对称密钥匙

CREATE SYMMETRIC KEYtestsymmterickey with algorithm =RC4

encryption by password=‘passw0rd‘

select * from sys.symmetric_keys

--2、打开对称密钥

open symmetric key testsymmterickey

decryption by password=‘passw0rd‘

--3、查看用对称密钥匙加密的数据

declare @encryptedtextvarbinary(80)

select

@encryptedtext=ENCRYPTBYKEY(KEY_GUID(‘testsymmterickey‘),‘encryptedtext‘)

select@encryptedtext,CAST(DECRYPTBYKEY(@encryptedtext) as varchar(30))

--4、关闭对称密钥

close symmetric keytestsymmterickey

F、用证书加密数据

--1、创建测试表及增加登录用户

create table certest (id intidentity(1,1) primary key,

salesrep varchar(30) not null,

saleslead varbinary(500) notnull)

go

create user salesrep1 withoutlogin

go

create user salesrep2 withoutlogin

go

grant select,insert ondbo.certest to salesrep1

grant select,insert ondbo.certest to salesrep2

go

--2、为每个用户创建一个证书

--CREATE MASTER KEY ENCRYPTION BYPASSWORD = ‘passw0rd‘

-- BACKUP MASTER KEY TO FILE =‘C:\sqlcert\masterkeya‘ ENCRYPTION BY

PASSWORD = ‘passw0rd‘

create certificate salesrep1certauthorization salesrep1

with subject=‘salesrep 1certificate‘

go

create certificate salesrep2certauthorization salesrep2

with subject=‘salesrep 2certificate‘

go

select * from sys.certificates

--3、为每个用户写入数据

execute as user=‘salesrep1‘

go

insert into dbo.certest

values(‘salesrep1‘,ENCRYPTBYCERT(CERT_ID(‘salesrep1cert‘),‘fabrikam‘))

revert

go

execute as user=‘salesrep2‘

go

insert into dbo.certest

values(‘salesrep2‘,ENCRYPTBYCERT(CERT_ID(‘salesrep2cert‘),‘contoso‘))

revert

go

--4、查看表的内容及每个用户的数据

select * from dbo.certest

go

execute as user=‘salesrep1‘

go

select*,CAST(DECRYPTBYCERT(cert_id(‘salesrep1cert‘),saleslead) as varchar(max))

from dbo.certest

revert

go

execute as user=‘salesrep2‘

go

select*,CAST(DECRYPTBYCERT(cert_id(‘salesrep2cert‘),saleslead) as varchar(max))

from dbo.certest

revert

go

9)、数据压缩

压缩方式：

A、行压缩：

a- 减少元数据头记录数据。

b- 所有的数值类型(integer,decimal,float)或基于数值类型的类型都会被压缩(datetime,money)。比如，100 被存在一个int 型字段中，占4 位，但是从0~255只需要1 位，压缩后，就节省了3byte 空间。c- Char 和NChar 都被存放于可变长度的类型中。原因同上。比如，CSDN 被存在Char(10)中，但是它只需要Char(4)空间，所以压缩后就释放了Char(6)个空间。

d- 所有类型的NULL 和0 都不占字节了。

B、页压缩：

a- 进行行压缩。

b- 前标压缩：每一页中的所有列，在行标头下面，每行都存储着一个行定义值，压缩后，所有行的定义值都被替换成行头值的引用。

c- 字典压缩：字典压缩和前标压缩类似。前标压缩中，一个与其他普通值不同的值被定义到每一列上。但是字典压缩中，每一页中所有列的普通值被存在下一行的行头前面。然后，这些值被替换成新行的引用值。

备份压缩：

a- 备份压缩可以大大减小数据库备份文件的大小，由于降低了备份时的I/O，所以进行备份压缩通常可以提高备份的速度。

b- 备份压缩是用CPU 换空间，虽然减少了存储空间大小，但是在备份和还原时将消耗更多的CPU。DB → 任务 → 备份 → 选择压缩备份

10)、安全性增强

A、 配置安全

B、 身份验证

Kerberos / NTLM

强制密码策略

端点身份验证

登录凭证加密

单点登录

C、 授权颗粒度更高

D、 数据加密

E、 行为审计

11)、元数据有关的存储

12)、新增系统函数

13)、策略管理：基于策略的管理是一种用于管理一个或多个SQL Server 2008 实例的系统。使用策略管理可以检查数据库对象的属性甚至限制数据库对象的修改。

14)、智能化管理

按需检查

按计划检查，记录违反策略情况

即时检查，阻止违反策略的操作

即时检查，记录违反策略情况

15)、新增T-SQL 语法：

INSERT 语句一次插入多条记录

INSERT INTO t1 VALUES (‘JohnDoe‘, ‘425-333-5321‘),

(‘Jane Doe‘, ‘206-123-4567‘),(‘John Smith‘, ‘650-434-7869‘);

新增操作符：+=、-=、*=、/= ……

UPDATE t1 SET c1+=1 WHERE c2=‘a‘

变量定义同时初始化

DECLAER @v int = 5;

DECLARE @v1 varchar(10) = xxxxx‘;

16)、Merge 语法

在SQLServer2008 中可以使用MERGE在一条语句中执行INSERT、UPDATE和DELETE操作，MERGE 语法包括四个主要子句：

MERGE 子句用于指定作为INSERT、UPDATE 或DELETE 操作目标的表或视图。

USING 子句用于指定要与目标联接的数据源。

ON 子句用于指定决定目标与源的匹配位置的联接条件。

WHEN 子句用于根据ON 子句的结果指定要执行的操作。

17)、Grouping Sets

使用GROUPING SETS 的GROUP BY子句可以生成一个等效于由多个简单GROUP BY子句的UNION ALL 生成的结果集

-- Use UNION ALL ondual SELECT statements

SELECT customerType,Null asTerritoryID,MAX(ModifiedDate)

FROM Sales.Customer GROUP BYcustomerType

UNION ALL

SELECT Null ascustomerType,TerritoryID,MAX(ModifiedDate)

FROM Sales.Customer GROUP BYTerritoryID

order by TerritoryID

-- Use GROUPING SETSon single SELECT statement

SELECTcustomerType,TerritoryID,MAX(ModifiedDate)

FROM Sales.Customer

GROUP BY GROUPING SETS((customerType), (TerritoryID))

order by customerType

18)、表值参数:

表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。

19)、日期和时间类型

20)、Hierarchyid

用于创建具有层次结构的表，或引用位于另一个位置的数据层次结构。

系统提供了多个方法用于操作hierarchyid 数据类型。支持深度优先遍历和广度优先遍历：

21)、空间地理数据类型

22)、稀疏列

稀疏列是对 null 值采用优化的存储方式的普通列。

当至少能够节省 20% 到 40% 的空间时，才应考虑使用稀疏列。

一般情况下表最多可以创建1024 个列。使用稀疏列时可以在表中创建更多的稀疏列。使用稀疏列存储：

NULL 值: 0 bytes. 非NULL: +(2-4)b。

在稀疏列中检索非NULL 数据性能有一定下降。

23)、筛选索引：筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。

24)、FILESTREAM 存储：使用FILESTREAM 允许以独立文件的形式存放大对象数据

（varbinary(max)），而不是以往一样将所有数据都保存到数据文件中。

本文出自 “Ricky's Blog” 博客，请务必保留此出处http://57388.blog.51cto.com/47388/1626274