第3章 数据库和表

3.1 数据库存储结构

数据库的存储结构分为逻辑存储结构和物理存储结构。
数据库的逻辑存储结构指的是数据库的性质信息等。SQL Server数据库是由表、视图和索引等各种不同的数据库对象所组成，它们分别存储数据库的特定信息，构成了数据库的逻辑存储结构。
数据库的物理存储结构则指的是磁盘上存储的数据库文件。
数据库文件由数据库文件和事务日志文件组成，保存在物理介质的NTFS分区或者FAT分区上，它预先分配了将要被数据库和事务日志所使用的物理存储空间。（？）
-----------------------

文件系统

是操作系统用于明确磁盘或分区上的文件的方法和数据结构；即在磁盘上组织文件的方法。也指用于存储文件的磁盘或分区，或文件系统种类。因此，可以说"我有2个文件系统"意思是他有2个分区，一个存文件，或他用 "扩展文件系统"，意思是文件系统的种类。操作系统中负责管理和存储文件信息的软件机构称为文件管理系统，简称文件系统。从系统角度来看，文件系统是对文件存储器空间进行组织和分配，负责文件存储并对存入的文件进行保护和检索的系统。具体地说，它负责为用户建立文件，存入、读出、修改、转储文件，控制文件的存取，当用户不再使用时撤销文件等。

NTFS分区和FAT分区
NTFS文件系统是NT内核系统所推荐使用的文件系统。NTFS具有FAT文件系统的所有基本功能，并且具有FAT或FAT32所没有的优点：文件的安全性更高、磁盘压缩性能更好和支持的硬盘容量更大。NTFS支持最大达2TB的大硬盘，比FAT大得多，而且随着磁盘容量的增加，NTFS的性能不像FAT那样随之降低。

----------------------

3.1.1 数据库文件和文件组

文件组
SQL文件组就是文件的逻辑集合。它的目的是为了方便数据的管理和分配.文件组可以把指定是文件组合在一起。
在首次创建数据库，或者以后将更多文件添加到数据库时，可以创建文件组。但是，一旦将文件添加到数据库，就不可能再将这些文件移到其它文件组。

SQL Server 2005数据库具有3种类型的文件：

• 主数据文件:包含数据库的启动信息，并用于存储数据。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。
• 次要数据文件:一般来说，如果数据大小没有超过主要文件的大小，就不会生成次要文件。次要数据文件的推荐文件扩展名是.ndf。
• 日志文件:包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件。日志文件的推荐文件扩展名是 .ldf。

注意：SQL Server 2005不强制使用.mdf、.ndf和.ldf 文件扩展名


数据库中所有文件的位置都记录在数据库的主文件和master数据库中。大多数情况下，数据库引擎使用master数据库中的文件位置信息。但是，在以下情况中，数据库引擎使用主文件中的文件位置信息来初始化master数据库中的文件位置项：

• 使用带有FOR ATTACH 或FOR ATTACH_REBUILD_LOG 选项的CREATE DATABASE语句来附加数据库时。关于CREATE DATABASE语句的详细使用，可以参看附录B（?）
• 从SQL Server 2000版或7.0版升级到SQL Server 2005时。
• 还原master数据库时。

为便于数据库文件的分配和管理，可以将数据库对象和文件一起分成文件组。有两种类型的文件组：

• 主文件组(primary)：包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。
• 用户定义文件组：通过在CREATE DATABASE(创建数据库)或ALTER DATABASE（修改数据库）语句中使用FILEGROUP（文件组）关键字指定的任何文件组。

一个文件不能是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联。这意味着它们的所有页都将从该文件组的文件中分配.

注 意：日志文件不包括在文件组内（显示不适用）。日志空间与数据空间分开管理。

SQL Server 2005文件可以从它们最初指定的大小开始，随数据的增加而自动增长。在定义文件时，可以指定一个特定的增量。每次填充文件时，其大小均按此增量来增长。如果文件组中有多个文件，则它们在所有文件被填满之前不会自动增长。填满后，这些文件会循环增长。（?）
每个文件还可以指定一个最大大小。如果没有指定最大大小，文件可以一直增长到用完磁盘上的所有可用空间。如果SQL Server作为数据库嵌入某应用程序，而该应用程序的用户无法迅速与系统管理员联系，则此功能就特别有用。用户可以使文件根据需要自动增长，以减轻监视数据库中的可用空间和手动分配额外空间的管理负担。

3.1.2 页和区

在创建数据库对象时，SQL Server会使用一些特定的数据结构给数据对象分配空间，即页和区。它们和数据库及其文件间的关系如图3.1所示。

1. 页

数据存储的基本单位是页（page）。为数据库中的数据文件分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。磁盘I/O操作在页级执行。也就是说，SQL Server读取或写入所有数据页。所有信息都存储在页上，页是数据库中使用的最小数据单元。每一个页存储8KB（8192字节）的信息，所有的页都包含一个132字节的页面头，这样就留下8060字节存储数据。

使用如下几种类型的页：

• 分配页面：用于控制数据库中给表和索引分配的页面。
• 数据和日志页面：用于存储数据库数据和事务日志数据。数据存储在每个页面的数据行中。SQL Server不允许跨页面存储。
• 索引页面：用于存储数据库中的索引数据。
• 分发页面：用于存储数据库中有关索引的信息。
• 文本/图像页面：用于存储大量的文本或者二进制的对象，例如，图像。

页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反，如图3.2所示。

行不能跨页，但是行的部分可以移出行所在的页，比如一行多列时，这一行的部分列在数据页A，部分列在数据页B。（?）

页的单个行中的最大数据量和开销是8060字节。但是，这不包括用文本/图像页类型存储的数据。包含varchar、nvarchar、varbinary或sql_variant列的表不受此限制的约束。当表中的行的总大小超过限制的8060字节时，将从最大长度的列开始动态将一个或多个可变长度列移动到ROW_OVERFLOW_DATA分配单元中的页。每当插入或更新操作将行的总大小增大到超过限制的8 060字节时，将会执行此操作。将列移动到ROW_OVERFLOW_DATA分配单元中的页后，将在IN_ROW_DATA分配单元中的原始页上维护24字节的指针。如果后续操作减小了行的大小，会动态将列移回到原始数据页。

------------------------------------

varchar和nvarchar和char的区别(以后补充)

sql_variant

一种存储 SQL Server 支持的各种数据类型（text、ntext、image、timestamp 和 sql_variant 除外）值的数据类型。  可以用在列、参数和变量中并返回用户定义函数的值。sql_variant 允许这些数据库对象支持其它数据类型的值。

---------------------------------------

2. 区

区（extent）是由8个连续的页面组成的数据结构，大小为64KB。当创建一个数据库对象时，SQL Server会自动以区为单位给它分配空间。每一个区只能包含一个数据库对象。
区是表和索引分配空间的单位，如果在一个新建的数据库中创建一个表和两个索引，并且表中只包含一笔记录，则总共占用3×64KB＝192KB的空间。
提 示：所有数据库都包含这些数据库结构，一个数据库是由文件组成，文件是由区组成，区是由页面组成。

为了使空间分配更有效，SQL Server不会将所有区分配给包含少量数据的表。SQL Server有两种类型的区：

• 统一区：由单个对象所有。区中的所有8页只能由所属对象使用。
• 混合区：最多可由8个对象共享。区中8页的每页可由不同的对象所有。

混合区和统一区如图3.3所示。

通常从混合区向新表或索引分配页。当表或索引增长到 8 页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。

3.1.3 事务日志

每个数据库都具有事务日志。
在创建数据库的时候，事务日志也会随着被创建。事务日志存储在一个单独的文件上。在修改写入数据库之前，事务日志会自动记录对数据库对象所做的修改。这是SQL Server的一个重要的容错特性，它可以有效地防止数据库的损坏，维护数据库的完整性。

1. 事务日志支持的操作

• 恢复个别的事务：如果应用程序发出ROLLBACK（回滚）语句，或者数据库引擎检测到错误（例如失去与客户端的通信），就使用日志记录回滚未完成的事务所做的修改。
• SQL Server启动时恢复所有未完成的事务：当运行SQL Server的服务器发生故障时，数据库还没有将某些修改从缓存写入数据文件，在数据文件内有未完成的事务所做的修改。当启动SQL Server实例时，它对每个数据库执行恢复操作。前滚日志中记录的、可能尚未写入数据文件的每个修改。在事务日志中找到的每个未完成的事务都将回滚，以确保数据库的完整性。（？略混乱）
• 将还原的数据库、文件、文件组或页前滚到故障点：在硬件丢失或磁盘故障影响到数据库文件后，可以将数据库还原到故障点。首先还原上一次的完整备份和差异备份，然后将事务日志备份后续序列还原到故障点。当还原每个日志备份时，数据库引擎 重新应用日志中记录的所有修改，以回滚所有事务。当最后的日志备份还原后，数据库引擎将使用日志信息回滚到该点未完成的所有事务。（？略混乱）
• 支持事务复制：日志读取器代理程序监视已为事务复制配置的每个数据库的事务日志，并将已设复制标记的事务从事务日志复制到分发数据库中。（？）
• 支持备用服务器解决方案：备用服务器解决方案、数据库镜像和日志传送高度依赖于事务日志。在日志传送方案中，主服务器将主数据库的活动事务日志发送到一个或多个目标服务器。每个辅助服务器将该日志还原为其本地的辅助数据库。在数据库镜像方案中，数据库（主体数据库）的每次更新都在独立的、完整的数据库（镜像数据库）副本中立即重新生成。主体服务器实例立即将每个日志记录发送到镜像服务器实例，镜像服务器实例将传入的日志记录应用于镜像数据库，从而将其继续前滚。有关详细信息，请参阅数据库镜像概述。（？）

-------------------------

前滚－恢复。还没有做，根据日志继续把它做完。
回滚－撤销。已经做了，根据日志取消刚才所做的。

-------------------------

SQL Server Database Engine的事务日志具有如下特征：

• 事务日志是作为数据库中的单独的文件或一组文件实现的。日志缓存与数据页缓存分开管理。
• 日志记录和页的格式不必遵守数据页的格式。
• 事务日志可以在几个文件上实现。通过设置日志的FILEGROWTH（文件增量）值可以将这些文件定义为自动扩展。这样可减少事务日志内空间不足的可能性
• 重用日志文件中空间的机制，速度快且对事务吞吐量影响最小。

2. 事务日志提供容错的机制
事务是指一次完成的操作的集合。为了维护数据库的完整性，它们必须彻底完成或者根本不执行。如果一个事务只是部分执行，则数据库将受到损坏。
使用数据库的事务日志来防止没有完成的事务破坏数据。具体步骤如下：

1. 1用户执行修改数据库对象的任务。
2. 2当这个事务开始时，在事务日志中会记录一个事务开始标志，并将与此操作相关的数据读入缓冲区。
3. 3在日志中记录每一个操作，然后在日志中记录一个提交事务的标志。每一个事务都会以这种方式记录在事务日志中，这些记录立即写到硬盘上。
4. 4在缓冲区中修改响应的数据。这些数据一直在缓冲区中，直到检查点进程发生（定期发生），才会写到硬盘上。同时，也会在事务日志中写入“所有已经完成的事务已经作用于数据库”，即在事务日志中写入一个检查点标志。这个标志用于在数据库恢复过程中确定哪个事务已经作用于数据库了。
5. 5如果服务器在已经完成了这个事务（这些事务的操作信息已经写入事务日志中）但还没有将缓冲区中的数据写入物理硬盘的情况下（检查点进程尚未触发）失效了或者在服务器恰好处理了部分事务的情况下数据库服务器失效了，那么在这两种情况下，数据库都不会被破坏。在服务器恢复正常后，SQL Server会开始一个恢复过程，检查数据库和事务日志，如果事务日志中的事务还没有在数据库中生效，则会在此时作用于数据库（前滚）；如果发现部分事务还没有完成，则将这个事务在数据库中的作用去掉（回滚）。这个过程是自动进行的。数据库完整性信息都由事务日志来完成，从而从本质上增强了SQL Server的容错性能

3.2 查看数据库

3.2.1 查看系统数据库

1. master 数据库

master数据库记录了SQL Server系统的所有系统级别信息。它记录所有的登录账户和系统配置设置。它还记录所有其他的数据库，其中包括数据库文件的位置。master数据库记录SQL Server的初始化信息，它始终有一个可用的最新master数据库备份。因此，如果 master数据库不可用，则SQL Server无法启动。
注 意：在2005中，系统对象不再存储在master数据库中，而是存储在Resource数据库中。Resource 数据库是只读数据库，它包含了SQL Server 2005中的所有系统对象。SQL Server系统对象（例如sys.objects）在物理上持续存在于Resource数据库中，但在逻辑上，它们出现在每个数据库的sys架构中。

-----------------------

常见的系统对象：

表： 由行和列构成的集合，用来存储数据
数据类型： 定义列或变量的数据类型，SQL Server提供了系统数据类型，并允许用户自定义数据类型
视图 ：由表或其他视图导出的虚拟表
索引 ：为数据快速检索提供支持且可以保证数据唯一性的辅助数据结构
约束 ：用于为表中的列定义完整性的规则
默认值： 为列提供的缺省值
存储过程： 存放于服务器的预先编译好的一组T-SQL语句
触发器： 特殊的存储过程，当用户表中数据改变时，该存储过程被自动执行

----------------------

2. tempdb 数据库

tempdb数据库是连接到SQL Server实例的所有用户都可用的全局资源，保存所有的临时表和临时存储过程。它还满足任何其他的临时存储要求，例如，存储SQL Server生成的工作表。所有连接到系统的用户的临时表和存储过程都存储在该数据库中。该数据库在SQL Server 每次启动时都重新创建，因此该数据库在系统启动时总是干净的。临时表和存储过程在连接断开时自动除去，而且当系统关闭后将没有任何连接处于活动状态，因此tempdb数据库中没有任何内容会从SQL Server的一个会话保存到另一个会话。
注 意：默认情况下，在SQL Server运行时，tempdb数据库会根据需要自动增长。不过，与其他数据库不同，每次启动数据库引擎时，它会重置为其初始大小。如果为tempdb 数据库定义的大小较小，则每次重新启动 SQL Server时，将tempdb数据库的大小自动增加到支持工作负荷所需的大小这一工作可能会成为系统处理负荷的一部分。为避免这种开销，可以使用ALTER DATABASE增加tempdb数据库的大小。ALTER DATABASE将在后面介绍。

3. model 数据库

model数据库用作在系统上创建的所有数据库的模板。当创建数据库时，新数据库的第一部分通过复制model数据库中的内容创建，剩余部分由空页填充。由于SQL Server每次启动时都要创建tempdb数据库，model数据库必须一直存在于SQL Server系统中。如果想让每个新的数据库开始就具有某些对象或者权限等，可以把它们放到model数据库中，这样新的数据库会继承它们。

4. msdb 数据库

msdb数据库供SQL Server代理程序调度警报和作业以及记录操作员时使用。

3.2.2 查看用户数据库

提 示：在从数据库中删除文件时，要求文件为空。否则无法删除。无法删除主数据文件和日志文件。

3.2.3 查看表之间的关系图

3.2.4 查看表的结构和内容

3.2.5 查看视图

视图（Views）是一种虚拟表，它的所有数据均来自表，本身并不存储数据。

3.2.6 查看存储过程

存储过程是预先使用SQL语言编写的，经过SQL Server编译后，存储在SQL Server中的程序，因此执行效率比较高，而且可以重复调用。

3.2.7 查看用户和角色

用户（User）是对数据库有存取权限的使用者。角色（Roles）是指一组数据库用户的集合（和Windows中的用户组类似）。数据库中的角色可以根据需要添加。用户如果被加入到某一角色，则将具有该角色所拥有的权限。

提 示：应用程序角色是一个数据库主体，它使应用程序能够使用其自身及类似用户的特权来运行。使用应用程序角色，可以只允许通过特定应用程序连接的用户访问特定数据。与数据库角色不同的是，应用程序角色默认情况下不包含任何成员，而且是非活动的。因为应用程序角色是数据库级别的主体，所以它们只能通过其他数据库中授予guest用户账户的权限来访问这些数据库。因此，任何已禁用的guest用户账户的数据库对其他数据库中的应用程序角色都是不可访问的。

3.2.8 数据库架构

架构是单个用户所拥有的数据库对象的集合，这些对象形成单个命名空间。命名空间是一组名称不重复的对象。例如，只有当两个表位于不同的架构中时才可以具有相同的名
称。数据库对象（例如，表）由架构所拥有，而架构由数据库用户或角色所拥有。当架构所有者离开单位时，会在删除离开的用户之前将该架构的所有权移交给新的用户或角色。

例如，假设用户userA拥有架构schemaA，而表Table1、Table2为架构schemeA所拥有。当用户userA离开单位时，则可以将schemaA的所有权移交给用于管理Table1和Table2表的新用户userB。当userB拥有schemaA后，同时也拥有了对Table1和Table2的所有权。

当架构被新用户或者角色所拥有时，其所拥有的表等对象也将被新用户或角色所拥有。

2. SQL Server 2005 中的用户架构分离（略混乱……）
在SQL Server 2000中，数据库用户和架构是隐式连接在一起的。每个数据库用户都是与该用户同名的架构的所有者。对象的所有者在功能上与包含它的架构所有者相同。因而，SQL Server 2000中的完全限定名称的“架构”也是数据库中的用户。因此，从SQL Server 2000数据库中删除用户之前，管理员需要删除该用户所拥有的所有对象或更改这些对象的所有者。以包含此对象的SQL Server 2000数据库为例：
accounting.ap.george.reconciliation
此对象的所有者为用户“george”。如果管理员需要删除用户george，则必须先删除此对象或更改此对象的所有者。在后一种情况下，可以按如下方式将其重命名：
accounting.ap.sandra.reconciliation
转让对象的所有权也会更改其完全限定名称。引用accounting.ap.george.reconciliation的任何代码必须经过更新以反映对名称所做的更改。
在SQL Server 2005中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。并且可以在架构中创建具有用户友好名称的对象，明确

指示对象的功能。

例如，除了accounting.ap.sandra.reconciliation外，还可以创建名为accounting.ap.invoice.reconciliation的架构。因为“invoice”不是用户，所以从数据库中删除用户后，无需更改此名称。这就简化了数据库管理员和开发人员的工作。

将架构与数据库用户分离对管理员和开发人员而言有下列好处：

• 多个用户可以通过角色成员身份或Windows组成员身份拥有一个架构。这扩展了允许角色和组拥有对象的用户熟悉的功能。
• 极大地简化了删除数据库用户的操作。
• 删除数据库用户不需要重命名该用户架构所包含的对象。因而，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。
• 多个用户可以共享一个默认架构以进行统一名称解析。
• 开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是DBO(系统管理员)架构中。
• 可以用比早期版本中的粒度更大的粒度管理架构和架构包含的对象的权限。
• 完全限定的对象名称现在包含4个部分：server.database.schema和object。

3.3 数据库的建立和删除

3.3.1 建立数据库

在创建数据库之前，应注意下列事项：

• 若要创建数据库，必须至少拥有CREATE DATABASE、CREATE ANY DATABASE或ALTER ANY DATABASE权限。
• 在SQL Server 2005中，对各个数据库的数据和日志文件设置了某些权限。如果这些文件位于具有打开权限的目录中，那么以上权限可以防止文件被意外篡改。
• 创建数据库的用户将成为该数据库的所有者。
• 对于一个SQL Server实例，最多可以创建32 767个数据库。（一般来说， 一个SQL Server服务器就是一个实例）
• 数据库名称必须遵循为标识符指定的规则。标识符不能是Transact-SQL保留字。SQL Server保留其保留字的大写和小写形式。并且不允许嵌入空格或其他特殊字符。
• model 数据库中的所有用户定义对象都将复制到所有新创建的数据库中。可以向model数据库中添加任何对象（例如，表、视图、存储过程和数据类型），以便将这些对象包含到所有新创建的数据库中。

提 示：由于新建数据库是以model数据库为模板创建的，因此，其大小不可能低于2MB（这里假定model数据库的大小为2MB）。另外，数据库大小虽然可以自动增长，但
是增长后会造成数据库在磁盘中存放不连续，容易降低数据库的效率，因此建议先估算数据库所需容量。

3.3.2 删除数据库

注 意：在数据库删除之后应该备份master数据库，因为删除数据库将更新master数据库中的系统表。如果master需要还原，则从上次备份master之后删除的所有数据库都将仍然在系统表中有引用，因而可能导致出现错误信息。

3.3.3 数据库文件和文件组设置

文件组允许对文件进行分组，以便于管理和数据的分配及放置。例如，可以分别在3个磁盘驱动器上创建3个文件（Data1.ndf、Data2.ndf 和 Data3.ndf），并将这3个文件指派到文件组fgroup1中。然后，可以明确地在文件组fgroup1上创建一个表。对表中数据的查询将分散到3个磁盘上，因而性能得以提高。在RAID（独立磁盘冗余阵列）条带集上创建单个文件也可以获得相同的性能改善。然而，文件和文件组使用户得以在新磁盘上轻易地添加新文件。另外，如果数据库超过单个Microsoft Windows文件的最大大小，则可以使用次要数据文件允许数据库继续增长。（？）

将不允许修改的表（如历史数据）置于一些文件组上，然后将这些文件组标记为只读。这会防止意外的更新

3.3.4 数据库大小估算和收缩数据库

用来估算每个表所需页面数的估算公式：页数＝表的行数/（8080/行的长度）

式中，行的长度就是指表的每一笔记录所占的字节数。

为了避免造成数据库中数据的丢失，在更改数据库属性时，要更改数据文件或者日志文件的大小，SQL Server只允许增大文件的大小，而不允许减小文件的大小。

SQL Server 2000允许收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。数据库文件可以作为组或单独地进行手工收缩。数据库也可设置为按给定的时间间隔自动收缩。

选择该选项可使释放的文件空间保留在数据库文件中，并使包含数据的页移到数据库文件的起始位置

3.4 表的建立、删除与修改

提 示：如果要将多个字段设置为主键，可按住Ctrl键，再设置

3.4.2 修改表的结构

3.4.3 建立表间的关联

3.4.4 删除表

如果要删除的表与其他表存在关联，为了保持数据库中数据的完整性，不允许删除和其他表有关联的表。

此时，可以通过查看表之间的依赖关系来确定，如果一定要删除选择的表，而该表又与其他表相关联。则必须在数据库关系图将关联先删除，然后才可以删除表。

提 示：在关系图窗口中，直接在要删除的表上右击鼠标，然后选择“从数据库中删除表”也可以删除表。

3.4.5 记录的新增和修改

添加记录，如果表之间有关联性存在，例如，表A的某个字段参考到表B时，则必须先输入表B的记录，然后才能输入表A与之相关的记录，否则将会出错。

课后习题：

作为一个数据库管理员，要创建一个新的数据库，该数据库中只包含一个表，不包含其他任何数据库对象。该表中每一笔记录的长度为1024B，如果表中包含100 000条记
录，则应创建多大的数据库才能满足要求？

因为1MB包含128个页面（一个数据页8KB）。而每个页面的前132字节作为页面头使用，所以实际上只有8060个字节(8060B)可以使用。每一笔记录为1024，所以每个页面只能包含7笔记录。所以1MB字节只能包含128×7笔记录。因此数据库的大小应为100 000/（128×7）＝120MB。