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 作为该列的默认值。
