《SQL SERVER 2005技术内幕--存储引擎》学习笔记

1 2008-8-7

4.8 数据库的真面目

l 执行校验检查

n 在SQL SERVER 2005中，DBCC被认为是数据库控制台命令(Database Console Command)，在之前的版本中，DBCC被认为是数据库一致性检查器（Database Consistency Checker）；

n DBCC CHECKDB是验证数据库中的Service Broker数据唯一办法；

n DBCC CHECKDB运行时使用的数据库快照技术只有当该被检查的数据库位于NTFS分区上才可以使用；

n DBCC命令会申请大量的空间，所以为了避免空间不够，可以事先使用ESTIMATEONLY选项运行DBCC CHECKDB命令，该估计值是一种最坏的结果，一般不会达到其估计值如：

u DBCC CHECKDB (“Northwind”) WITH ESTIMATEONLY

n 对于升级上来的数据库，应该执行一次带有DATA_PURITY选项的DBCC CHECKDB命令；

l DBCC Repair选项

n REPAIR_ALLOW_DATA_LOSS选项可能会造成数据丢失；

n REPAIR_REBUILD则会执行较小的相对较快的修复行为，不存在数据丢失的行为；

n DBCC Repair选项只能保证物理上的一致，不能保证逻辑上的一致，因此Repair只是用作最后的手段。

n 如果我们打算执行REPAIR_ALLOW_DATA_LOSS选项，那么之前应该先备份数据库；

n 可以对修复操作使用事务，并且在修复完成后，提交之前，我们可以查看修复的效果，如果对修复的效果不满意，则可以回滚。

l 进度报告

n 通过sys.dm_exec_requests 动态管理视图来查看

n DBCC CHECKDB, DBCC CHECKTABLE DBCC CHECKFILEGROUP 具有进度报告功能，因为他们一般都需要较长的时间；

n 一些选项（特别是SQL选项）有着对应的SET选项可以为某个特别的连接而打开或关闭；

4.9 设置数据库选项

默认情况下，所有为ON或 OFF的选项的值都是OFF，除非在model数据库中该选项的值是ON的。

所有的选项可以分为7大类：状态选项、游标选项、自动选项、参数化选项、SQL选项、数据库恢复选项和外部访问选项。

2 2008-8-9

4.9.1 状态选项

l 用户访问状态：有三个选项，他们是相互排斥的。

n SINGLE_USER, RESTRICTED_USER, MUTLI_USER

n 在RESTRICTED_USER模式下的数据库只接受合格的用户连接——这些用户属于dbcreator或sysadmin角色，或者是那个数据库的db_owner角色的成员。

n 默认时处于MULTI_USER模式下；

n 举例：ALTER DATABASE Northwind SET SINGLE_USER

n 确认一个数据库使用哪种用户访问状态的方式为：SELECT USER_ACCESS_DECT FROM sys.databases WHERE NAME = ‘Northwind’;

l 状态信息（state_desc）：

n OFFLINE, ONLINE, EMERGENCY；

n 除了上面的三种状态以外，数据库还有其他的状态，不过用户不能设置。如RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT；

n 在EMERGENCY模式下，数据库在内部来说是被设置为只读的，并且日志被禁用了。

3 2008-8-12

l 紧急模式修复；

n 我们可以通过将恢复中断（RESTORING_PENDING）状态的数据库设置为紧急状态来使其数据能够被读取。

l 可更新性：只读|读写

n 在只读模式下，我们不能执行插入、删除、更细操作。

n 在只读模式下，数据库重起时不会在该数据库上运行自动恢复；

l 结束选项：

n 我们可以指定SQL server等待情况发生变化，或者产生一条错误信息，或者结束不合格的用户的连接；

4.9.2游标选项

4.9.3 自动选项

l 所有的自动选项都是BOOL选项；

l AUTO_CLOSE，如果为ON，当一个数据库的最后一个用户退出时，该数据库会被正常关闭，并因此释放所有的资源；当一个用户尝试重新使用该数据库时，它会重新打开该数据库。

l AUTO_SHRINK：释放资质文件空间以便他们能够被收缩的唯一方法时备份日志文件并将恢复模式设定为SIMPLE。不是很明白?

4.9.4 SQL选项

l 对SQL server来讲，这些选项默认都是OFF，但是很多工具，比如Sql Server Management Studio和许多变成接口，ODBC都回启动某些会话级别的选项，这些选项会覆盖数据库选项，这使得看起来ON行为好像是默认行为一样。

l ARITHABORT，该选项被设置为ON时，查询在执行期间出现被0除或者算术溢出错误，该查询会被终止，当这个选项为OFF时，该查询会返回NULL值作为操作结果。

4.9.5 数据库恢复选项

l RECOVERY：有三个选项，FULL, BULK_LOGGED, SIMPLE

l PAGE_VERIFY：该选项能够发现由于磁盘I/O路径错误而损坏的数据库页面。TORN_PAGE_DETECTION, CHECKSUM。

l SQL SERVER 将会对任何校验和、损坏页和其他I/O进行四次重试操作，如果在这些尝试中有一次是成功的，那么就会向错误日志中写入一条记录，并且触发该读取操作的命令将会继续执行。如果所有的尝试都失败，那么该命令将会失败，并且触发一个824错误信息。

l 在SQL SERVER 2005中，CHECKSUM是默认值，在SQL SERVER 2000中，TORN_PAGE_DETECTION是默认值。

4 2008-8-13

4.10 数据库快照

l 数据库快照是SQL server2005新增的特性，它允许我们未任何数据库创建一个在某个时间点上的只读副本。我们可以对同一个数据库在不同的时间点创建多个快照；

l 快照只存储发生改变的页面，这和SVN创建标签和分支的方法一样；

4.10.1 创建数据库快照

l 创建快照没有图形界面，只能使用T-SQL语句；

l CREATE DATABASE Northwind_snapShot ON( NAME = N’Northwind’, FILENAME = “D:"SQL_DATA"Northwind_snapshot.mdf” ) AS SNAPSHOT OF Northwind；

l 当一个进程从快照中读取数据时，无论处于何种隔离级别，都不需要使用任何锁；

l 快照只能在NTFS格式的卷上创建；

4.10.2 数据库快照所使用的空间

数据库快照的最大值应该和快照创建时源数据库的大小基本相同。

4.10.3 管理快照

l 如果一个源数据库中存在快照，那么就无法删除、分离或还原该源数据库。如果把一个数据库快照切换到离线状态，那么快照就会被自动删除。

l 我们可以见源数据库回复（Revert）到快照创建时的状态：RESTORE DATABASE Northwind FROM SNAPSHOT= Northwind_snapshot；当存在多个快照时，不能执行回复操作。所以要先删除除了要快照以外的所有快照；

l Master, model, tempdb不能创建快照；

4.11 tempdb数据库

l Tempdb在SQLserver每次启动时都会被重新创建，他会从model中继承大多数的数据库选项，但是tempdb不会从model中继承其恢复模式，因为tempdb总是被设置为SIMPLE恢复模式；

l Tempdb是无法删除的；

4.11.1 tempdb中的对象

l 用户对象：

n 似有临时表以#开头，全局临时表以##开头；

n 在默认情况下我们没有权限将当前用户切换到tempdb，并在那里创建一张表，不过我们可以将这种权限添加到model中，然后tempdb就从model中继承该权限；

l 内部对象：工作表、工作文件和排序单元；

n 内部单元被存储在内存中，所以通过目录视图无法查看；

4.11.2 Tempdb中的优化

l SQL SERVER 只有一个tempdb数据库，一个表现很差的程序会影响到所有的其他用户和应用程序；

l 一般不要去修改tempdb的选项，有一些数据库选项并不适用于tempdb数据库，特别时autoshrink选项，该选项在tempdb中是被忽略的；

l 收缩tempdb的最佳方法是SLTER该数据库，更改其文件大下；

l 我们应该根据预先的测试和规划来确定tempdb的大小，以便tempdb在开始时就拥有它所需要的空间，并在在应用程序运行期间无需再增长；

5 2008-8-15

4.12 数据库安全

SQL SERVER 的数据库安全包含两个部分：身份验证和授权；

l 安全实体：安全实体是一个可以被授予权限的实体。安全实体包括数据库、架构和对象；

l 主体：在SQL SERVER 2000中作为一个用户。一个主题就是一个能够访问安全实体对象的实体。

4.12.1 数据库访问

SQL SERVER中的身份验证分为两级，首先要进行服务器级别的认证；

l SQL SERVER 2005有两种验证用户登陆帐户的方法：

n Winows身份验证，优势在于它允许sql server利用操作系统的安全特性，如密码加密，密码过期，以及对密码的最少和最多的长度限制；当运行在win2003下时，还能够利用windosw的密码策略；

n Sql server身份验证；

l 关于密码验证的更多细节可以参考ALTER LOGIN命令；

l 在SQL SERVER 2005中，如果在安装时选择了windows验证，则sql server默认的sa帐户会被禁止；

l 所有的登陆账户名，或者来自windows，或者来自sqlserver身份验证，都能从目录视图sys.server_principal中查看到，这个视图只有权限达到一定级别的用户才能够看到；

4.12.2 管理数据库安全性

数据库的所有者可以是登录账户名，登录账户名所拥有的唯一资源就是数据库，数据库内的所有对象都被数据库用户所拥有。

一个登录账户名可以和一个数据库用户名相同，但是他们是完全不同的概念，一个登录帐户在不同的数据库中可以表现为不同的用户名。

4.12.3 数据库与架构（schema）

架构被定义为有单个用户所有的一组数据库对象，并构成单独的命名空间。我们可以把架构看做是对象的容器。

4.12.4 分离主体和架构

在sql server 2000中，用户和架构的结合是如此紧密，以至于许多用户认为用户和架构其实是相同的概念。

在sql server2000中，如果我们创建一个名为sue的用户，sqlserver就会创建一个名为sue的架构作为用户sue的默认架构。权限被赋予用户，但是对象属于架构。

在sqlserver2005中打破了这种用户和架构之间的联系，一级和二级主体都可以拥有架构。

在sqlserver2000中，我们使用sp_adduser, sp_grantdbaccess来向数据库中添加用户和授权，在sqserver2005中，我们应该优先使用DDL CREATE USER 和 CREATE SCHEMA，当我们创建一个用户时，我们可以选定一个默认架构，但是默认情况下架构就是dbo架构。

4.12.5 默认架构

有以下几个默认架构：dbo， INFORMATION_SCHEMA和guest；另外还有一个sys架构，这是访问所有系统表和视图的方法。

如果没有给一个用户指定默认架构，那么其默认架构就是dbo。这样有一个坏处，就是在创建架对象时，sqlserver将会尝试在dbo架构中而不是该用户所拥有的架构中来创建对象。所以，为了避免混乱，在sqlserver2005中，我们应该在所有的对象访问和对象管理时指定架构名；

6 2008-8-18

4.13 复制和移动一个数据库

复制数据库是创建一个备用开发环境或试验环境的常用方法。

两种常用的方法：分离和附加，备份和还原；

4.13.1 分离和附件一个数据库

在分离数据库时必须没有用户正在使用数据库，如果发现无法终止已经存在的连接，则可以使用ALTER DATABASE 命令，并利用一个能够终止已经存在的连接的选项来把数据库切换到SINGLE_USER模式。

Exec sp_detach_db <dbname></dbname>

Exec sp_attach_db <dbname><span style="font-family: 宋体;">有</span>16<span style="font-family: 宋体;">个文件的限制，</span></dbname>

CREATE DATABASE… FOR ATTACH 没有文件限制。

分离和附件数据库是快速收缩一个已经长的太大的日志文件的方法。

4.13.2 备份和还原数据库

我们可以使用备份和还原来将一个数据库移动到一个新的位置，这种方法的好处是他们完全在线的操作，所以数据库根本不需要离线。

7 2008-8-19

第六章 表

一张表就是一个关于特定实体的数据的集合。

在2005中，表一般被称为基本表以强调数据存储的位置。

6.1 系统对象

Sql server 维护着一组表，用于存储所有对象、数据类型、约束和配置选项，以及sql server能访问的资源。这组表有时被称为系统目录。

系统目录中包含系统范围信息的系统基本表只存在于master数据库中，另一些包含特定数据库对象及其资源信息的表则存在于每个数据库中。

系统基本表在默认情况下总是不可见的。

6.1.1 兼容性视图

为了兼容sql server2000中的系统表数据，在2005中提供了兼容性视图。他们被创建在隐藏的资源数据库中。

这些兼容性视图与他们在2000中有着相同的名称，相同的列。但是当我们查询这些视图时，不一定能够得到与2000相应表进行查询完全一致的结果。

在未来版本的sql server中，所有这些兼容性视图都将被删除。

6.1.2 目录视图

2005提供了一组目录视图，作为访问系统元数据的接口。所有的目录视图都处于sys架构之下，因此必须使用架构名来访问这些对象。

在sys.databases 中，每个可能的数据库属性都有自己相应的列，而不是集中在一个需要解码的位图状态列中，这样将更加易于使用。在2000中，运行sp_dbhelp可以对这些位进行解码，但是很难对其查询结果进行过滤。

目录视图都是以继承模型构建的，从而避免了重复。

基本视图包含了列的子集和行的超子集，而生成视图则包含了列的超子集和行的子集。

联机丛书：Maping sql server 2000 tables to sql server 2005 system views

查看目录视图的方式： SELECT object_definition (object_id(‘sys.tables’))

6.1.3 其他元数据

l 信息架构视图

大多数从目录视图能够得到的信息都可以通过信息架构视图得到。INFORMATION_SCHEMA是ANSI_92标准，如果我们想写一个可移植的程序来访问元数据的话，则应该使用此类对象。

l 系统函数

大多数sql server的系统函数都是属性函数。属性函数的返回值是计量值而不是表值，因此可以被用作SELECT语句的返回值及其填充表中列的值。

例如下面的语句可以看到northwind的恢复模型：

SELECT DATABASEPROPERTY(‘Northwind’, ‘Recovery’)

而利用sys.database视图可以看到所有数据库的恢复模型：

SELECT name, recovery_model_desc FROM sys.databases。

其中_desc后缀表示有好名称。

除属性函数外，系统函数还包含一些仅仅用作访问目录视图捷径的函数，例如要找到Northwind数据库的ID，我们可以利用:

SELECT DB_ID(‘Northwind’)

l 系统存储过程

通过系统存储过程，我们基本上只要接受其返回的行就可以了，而使用目录视图，我们可以有更强的控制力。

8 2008-8-21

6.2 创建表

本质上来说，SQL语句就是源代码，所以我们应该用源代码工具来对其进行版本控制，比如SVN。

对于SQL SEVER而言，制表符、回车、空格所产生的效果是相同的，比如下面两条语句对于SQL SERVER来说效果时相同的。

CREATE TABLE dbo.customer

(

Name char(30),

Phone char(12),

Emp_id char(4)

)

CREATE TABLE dbo.customer( Name char(30), Phone char(12), Em_id char(4))

6.2.1 命名表和列

表总是被创建在某个数据库的某个架构之下的。一个数据库可以包含多个有用相同名称的表，只要他们位于不同的架构之下。

一个表的完整名称包含一下三个部分：database.schema.tablename;

如果在引用一张表的时候没有指定架构名，则查找的顺序为：当前用户的默认架构；如果没有，则查找dbo架构；如果还没有，则查找失败。

你应该养成在sql server2005中总是指定架构名的习惯，这样不但能够消除歧义，而且还能带来一些性能上的提升。

在某些情况下，我们可以采用四段式的名字，第一段就是数据库的实例名。

9 2008-8-22

6.2.2 保留关键字

这里要注意的是，有些SQL-92中的保留关键字，目前在SQL SERVER中还不是关键字，但是不排除以后他将会被作为关键字加入，所以为了防止以后的冲突，应该避免使用SQL-92中的所有关键字。

6.2.3 分隔标识符

如果我们使用了分隔标识符，我们不仅可以使用保留关键字作为标识符，还可以使用任何其他的符号作为对象名——不论他们是否符合标识符规则。有以下两种分隔标识符：

l 方括号分隔；

l 双引号分隔；必须用 SET QUOTED_IDENTIFIER ON 打开特定的选项后才可以使用。

ODBC和OLE DB的驱动程序在建立连接时，默认都会自动打开这个开关。我们可以通过如下的语句来判断当前的连接是否开启了该选项：

SELECT QUOTED_IDENTIFIER FROM sys.dm_exec_sessions WHERE session_id = @@spid

作者不推荐使用保留关键子，因为许多SQL SERVER的第三方工具对引用标识符处理的不是很好。

我们应该简单地制定一些命名惯例，而不是使用分隔标识符，这样我们就可以保护保留关键字，比如可以用表名的某些字母加上下划线来作为列名的前缀。

10 2008-8-27

6.2.4 命名惯例

不建议使用匈牙利命名管理来作为SQL SERVER中的表的命名惯例；

6.2.5 数据类型

在选择数据类型时，应该避免浪费空间，同时应该注意为可能插入的数据留下足够的空间。

l 选择数据类型

每个列要使用哪个数据类型主要取决于该列要存储的数据的本质，以及可能会对列数据进行的操作。

SQL SERVER2005有5中基本的数据类型：数字（numeric）、字符（charactar）、日期和时间（date and time）、大对象（large object, LOB）及其他。SQL SERVER 2005还支持一种名为sql_variant的可变数据类型，存储在sql_variant列中的数据可以是任何数据类型。

l Numeric数据类型

要关注的主要是要存储的数据的范围和精度要求。

Numeric可以分为精确和近似两类。精确数字值可以确保精确地存储数字，近似数字值可以表示的范围更大，但是不保证数字存储的精确性。

对于money和smallmoney，右边的四位是小数点后面的，而对于整数，小数点后面没有未。

Decimal和numeric数据类型对精度和数据范围都有很高的要求。下面这个语句就申明了一个decimal类型的变量，

aNum decimal(8,4);

l 日期和时间数据类型

n 有两种，他们的默认日期都是1900年1月1号，00:00:00

u Datetime，前面4个字节表示日期，后面4个字节表示时间。

u smalldatetime；

该类型的内部存储区域被分为时间和日期两个部分，日期部分以1900年1月1号为准，算出来的是在此日期之前或者之后的天数。

对于datetime类型，时间部分存储的是午夜0点之后的时钟周期数，每个时钟周期是1/300秒，为3.33毫秒。

对于smalldatetime而言，时间部分存储的是午夜之后的分钟数。

l 字符数据类型

n 有4种

u 单字节：变长和固定长, varchar, char；最大长度为8000

u Unicode：变长和固定长 nvarchar, nchar；最大长度为4000

另外，我们还可以定义MAX长度的字符串，被定义为 varchar(MAX) 长度的字符串在长度小于等于8000时，将作为普通的变长列处理，当实际长度超过8000时将被视作large object值。

11 2008-8-28

要决定是用变长还是定长的数据类型时一个很困难的问题，他的答案并不直观和明显，一个普遍的规则是：变长的数据类型适合数据长度差异明显、并且数据变动不频繁的列。

使用变长数据类型可以节省巨大的存储开销，有时会带来一些微小的性能损失，其他时候能够提高性能。

与变长列有关的一个潜在的性能问题就是在一个几乎满地页面上增加某一行的大小时，特别是当这个表上刚好又有聚焦索引的时候。这时会导致非常大的开销，为什么很大，我还不是很清楚。

变长列导致每一行的长度大大减小，这种减小一方面带来了空间上的节约，更重要的是，它使得一个页面上能够存放的行数更多，我们能在一个页面上放的行数越多，I/O及其缓存命中的效率就越高。

在一个页面上存放的记录行数的计算规则是：页面大小/行的大小，余数要舍弃。

l 其他数据类型

n Binary, varbinary，这些数值通过十六进制表现形式（0x作为前缀）来进行输入和显示；最大长度是8000；

n Bit，可以存储0或1，并且只消耗单个位的存储空间，尽管如此，如果在一张表上只有单个比特列，这个列也将占用一整个字节。

n Large object，包括text，ntext，image，最多可以存储2^31-1个字节。

n Cursor

n Rowversion，这是其正式名称timestamp的同义词。任何rowversion列的值在整个数据库中都是唯一的，并且每张表只能有一个rowversion类型的列。

n Sql_variant，可以用于存储除了下面集中类型之外的所有数据类型，text, ntext, image, xml。

n Table，可以用于存储某个函数返回的结果，还可以用来本地变量的数据类型；

n Xml，xml有其自身的方法来获取和操作。

n Uniqueidentifier，又被叫做GUID或UUID，可以使用NEWID或NEWSEQUENTIALID这两个系统函数来生成UUID的值。对该类型的值的操作只能是比较=, , =, 还可以检验NULL值。

Sqlserver使用GUID的原因是为了在合并复制中使用。不是很明白。

NEWSEQUENTIALID和NEWID的主要区别在于NEWSEQUENTIALID是创建一个比以前在这台及其上用此函数创建的GUID要大的GUID，并且可以为我们的GUID值引入一个序列。

Uniqueidentifier的值不可能被穷举。

Uniqueidentifier列可以有一个叫做ROWGUIDCOL的特殊属性，一张表只能有一个uniqueidentifier列可以有该属性。

一张表可以有多个uniqueidentifier列。

在查询中可以使用ROWGUIDCOL关键字来应用ROWGUIDCOL属性的uniqueidentifier，这与IDENTITYCOL关键字来引用表示列类似。

如果要自动生成uniqueidentifier，则要把NEWID作为该列的默认值。