21. SQL -- 创建数据库及数据库文件

浏览数：42 / 时间：2015年06月12日

创建数据库及数据库文件

SQL Server 的物理存储

SQL SERVER 数据库分为：系统数据库/用户数据库

系统数据库：

Master：master 数据库记录 SQL Server 系统的所有系统级信息。主要包括实例范围的元数据、端点、链接服务器和系统配置设置以及记录了所有其他数据库的存在、数据库文件的位置以及SQL Server 的初始化信息

Model: 提供了SQL Server 实例上创建的所有数据库的模板

Msdb: 主要由 SQL Server 代理用于计划警报和作业

Tempdb: tempdb 系统数据库是一个全局资源，可供连接到 SQL Server 实例的所有用户使用，并可用于保存显式创建的临时用户对象、SQL Server 数据库引擎创建的内部对象，行版本数据等

用户数据库：由用户自己创建的数据库

DatabaseOptions

Optioncategory Purpose

Automatic: Controlsautomatic behaviors such as statistics, database closing, and shrinking

Availability:Controls whether database is online, who canconnect to it, and whether database is read-only

Cursor: Controlscursor behavior and scope

Recovery:Controls the recovery model for the database

SQL: Controls ANSI compliance options such as ANSInulls and recursive triggers

SQL 文件类型：

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

A、主数据文件（行数据文件）：主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。

B、次要数据文件（行数据文件）：除主数据文件以外的所有其他数据文件都是次要数据文件。某些数据库可能不含有任何次要数据文件，而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是.ndf。

C、日志文件（记录数据变化的过程）：日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。日志文件的推荐文件扩展名是.ldf。

SQL Server 不强制使用 .mdf、.ndf和 .ldf 文件扩展名，但使用它们有助于标识文件的各种类型和用途。

SELECT * FROM SYS.database_files 查询数据库文件结构

SQL 文件名：

SQL Server 文件有两个名称：

A、logical_file_name 是在所有 Transact-SQL 语句中引用物理文件时所使用的名称。逻辑文件名必须符合 SQL Server 标识符规则，而且在数据库中的逻辑文件名中必须是唯一的。

B、os_file_name 是包括目录路径的物理文件名。它必须符合操作系统文件命名规则。

文件大小

SQL Server 文件可以从它们最初指定的大小开始自动增长。在定义文件时，您可以指定一个特定的增量。每次填充文件时，其大小均按此增量来增长。如果文件组中有多个文件，则它们在所有文件被填满之前不会自动增长。填满后，这些文件会循环增长。每个文件还可以指定一个最大大小。如果没有指定最大大小，文件可以一直增长到用完磁盘上的所有可用空间

文件数据页：

SQL Server 中数据存储的基本单位是页。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入所有数据页。SQL Server 数据文件中的页按顺序编号，文件的首页以 0 开始。数据库中的每个文件都有一个唯一的文件 ID 号。若要唯一标识数据库中的页，需要同时使用文件 ID 和页码。每个文件的第一页是一个包含有关文件属性信息的文件的页首页。在文件开始处的其他几页也包含系统信息（例如分配映射）。有一个存储在主数据文件和第一个日志文件中的系统页是包含数据库属性信息的数据库引导页。SQL 中，每页大小为8K，1M 共128 页. 每页的开头是 96 字节的标头，用于存储有

关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

数据库文件中所使用的页类型:

页类型内容

Data 当 text in row 设置为 ON 时，包含除 text、 ntext、image、

nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所

有数据的数据行。

Index 索引条目。

Text/Image 大型对象数据类型：

text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和

xml 数据。

数据行超过 8 KB 时为可变长度数据类型列：

varchar、nvarchar、varbinary 和 sql_variant GlobalAllocation Map、

Shared GlobalAllocation Map 有关区是否分配的信息。

Page Free Space 有关页分配和页的可用空间的信息。

Index Allocation Map 有关每个分配单元中表或索引所使用的区的信息。

Bulk Changed Map 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。

Differential ChangedMap 有关每个分配单元中自最后一条BACKUP DATABASE 语句之后更改的区的信息。

查看SQL 中数据页结构：

DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])

具体参数描述如下：

dbid 包含页面的数据库ID

dbname 包含页面的数据库的名称

filenum 包含页面的文件编号

pagenum 文件内的页面

printopt 可选的输出选项；选用其中一个值：

0:默认值，输出缓冲区的标题和页面标题

1:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表

2:输出缓冲区的标题、页面标题(整体输出页面)，以及行偏移量表

3:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表；每一行后跟分别列出的它的列值

执行代码：

--开启跟踪标记

dbcc traceon(3604)

--从sysindexes 中取出first 的值

declare @firstbinary(6)

select @first = firstfrom sysindexes where id = object_id(‘test‘) and indid in(0,1)

declare @PageNum int

select @PageNum =convert(int, substring(@first,4,1) + substring(@first,3,1) +

substring(@first,2,1)+ substring(@first,1,1) )

declare @FileNum int

select @FileNum =convert(int, substring(@first,6,1) + substring(@first,5,1))

select @FileNum,@PageNum

--执行DBCC Page

declare @sqlvarchar(1000)

select @sql = ‘dbccpage (‘‘‘ + db_name() + ‘‘‘, ‘ + convert(varchar(10),@FileNum) + ‘, ‘ +

convert(varchar(10),@PageNum)+ ‘, 1)‘

select @sql

dbcc traceon(3604)

exec (@sql)

DBCC Page 的输出主要分为5 个部分: Page、BUFFER、Page Hearder、Data、OFFSET Table(行的偏移量数组)。

Page：指出该表的文件编号及页码。

Buffer：指出关于给定页面的缓冲区的信息。

Page Header：指出页面中所有标题字段的数据。

Data：指出每一行的信息。对于每一行，DBCC Page 指出行在槽中的位置，以及该行在页面中的偏移量。页面数据被分成3 部分，左列指出的是所显示的数据在行中的字节位置。接着4 列包含页面存储的实际数据，以16 进制的形式显示。右列包含数据的字符表示(只有字符数据是可读的)。

Offset Table：指出页面末尾的行偏移量数组的内容。注意这里并不是行物理存储的顺序，而是以槽的编号的顺序来显示的，不过这里可以通过槽的编号看出它的物理存储顺序，比如槽的编号最小的为物理存储的第一行，依此类推。

区

区是管理空间的基本单位。一个区是八个物理上连续的页（即 64 KB）。这意味着 SQLServer数据库中每 MB 有 16 个区。为了使空间分配更有效，SQLServer 不会将所有区分配给包含少量数据的表。

SQLServer 有两种类型的区：

统一区，由单个对象所有。区中的所有 8 页只能由所属对象使用。

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

文件组

每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。

文件组说明

主要包含主要文件的文件组。所有系统表都被分配到主要文件组中。

用户定义用户首次创建数据库或以后修改数据库时明确创建的任何文件组。

文件组概念：

它只是个容器，任何一个文件必然属于一个文件组。用来管理存储空间，用来组织空间，用于分区表。获得对数据存储空间的分配，还可以获得可管理性。多个文件和多个文件组可提高性能，CPU 时间，I/O，提高一点可管理性，每增加一个文件就增加一线程，去抢占CPU。创建时，先创建文件组，再去创建文件。

文件组的好处：

备份还原时你可以针对文件组来还原和备份，而不需要把整个db 的数

据文件拷贝过来进行；而且在使用时，部分还原的部分就可以联机，而其他文件组可以继续还原。我们不能指定将TABLE 存储于哪个文件，只能只定文件组，再把管理的权限交于文件组，由文件组去分配存储在哪些文件中。

Primary 文件组：

任何一个文件都必然属于一个文件组，当数据库在创建时，默认会创建primary 文件组。用户自己创建的数据库对象不建议存在primary 文件组，建议存在自己建的文件组中，以提高数据库的可用性

Schema(架构)；

架构是指包含表、视图、过程等的容器。它位于数据库内部，而数据库位于服务器内部。这些实体就像嵌套框放置在一起。服务器是最外面的框，而架构是最里面的框。

完全限定的对象名称包含四部分：server.database.schema.object。

默认架构，DBO 是内置的架构

架构包含的所有安全对象：

必须位于架构内部的安全对象类

类型 TYPE

XML 架构集合 XML SCHEMA COLLECTION

表 OBJECT

视图 OBJECT

过程 OBJECT

函数 OBJECT

聚合函数 OBJECT

约束 OBJECT

同义词 OBJECT

队列 OBJECT

统计信息 OBJECT

架构的优点：

通过将架构的所有权分配给一个角色，多个用户都能够拥有一个架构，如果删除其中一个用户，对象的所有权不需要改变。简化删除用户的操作。多个用户可以共享一个架构，在用户之间提供统一的对象命名解决方案。完全限定名称包含架构，为：服务器.数据库.架构.对象。

架构概念的引入就是为了解决数据库对象太多不好管理的缺点。到现在为止，我们的数据库管理就变成了用户-架构-数据库对象的模式了

安全对象与架构关系：

快照

数据库快照是数据库（源数据库）的只读、静态视图。多个快照可以位于一个源数据库中，并且可以作为数据库始终驻留在同一服务器实例上。创建快照时，每个数据库快照在事务上与源数据库一致。在被数据库所有者显式删除之前，快照始终存在。是一个源数据库在某个时间点的只读副本，它总是与相关源数据库保持事务一致性。快照可用于报表。另外，如果源数据库出现用户错误，还可将源数据库恢复到创建快照时的状态。丢失的数据仅限于创建快照后数据库更新的数据

快照的工作方式：

数据库快照与源数据库相关。数据库快照必须与数据库在同一服务器实例上。此外，如果数据库因某种原因而不可用，则它的所有数据库快照也将不可用。快照可用于报表。另外，如果源数据库出现用户错误，还可将源数据库恢复到创建快照时的状态。丢失的数据仅限于创建快照后数据库更新的数据。此外，在对数据库进行重大更改（例如，更改表的架构或结构）之前创建数据库快照也很有用。

数据库快照在数据页级运行。在第一次修改源数据库页之前，先将原始页从源数据库复制到快照。此过程称为―写入时复制操作‖。快照将存储原始页，保留它们在创建快照时的数据记录。对已修改页中的记录进行后续更新不会影响快照的内容。对要进行第一次修改的每一页重复此过程。这样，快照将保留自创建快照后经修改的所有数据记录的原始页。

为了存储复制的原始页，快照使用一个或多个―稀疏文件‖。最初，稀疏文件实质上是空文件，不包含用户数据并且未被分配存储用户数据的磁盘空间。随着源数据库中更新的页越来越多，文件的大小也不断增长。创建快照时，稀疏文件占用的磁盘空间很少。然而，由于数据库随着时间的推移不断更新，稀疏文件会增长为一个很大的文件下图说明了写入时复制操作。快照关系图中的浅灰色方框表示稀疏文件中尚未分配的潜在空间。收到源数据库中页的第一次更新时，数据库引擎将写入文件，操作系统向快照的稀疏文件分配空间并将原始页复制到该处。然后，数据库引擎更新源数据库中的页。

对快照的读操作：

对于用户而言，数据库快照似乎始终保持不变，因为对数据库快照的读操作始终访问原始数据页，而与页驻留的位置无关。如果未更新源数据库中的页，则对快照的读操作将从源数据库读取原始页。更新页之后，对快照的读操作仍访问原始页，该原始页现在存储在稀疏文件中。

快照与备份的区别：

BAK 文件不能直接执行语句，快照DB 能直接提取数据，一时SOURCE DB 中需要还原数据的时候，就可以从快照中恢复。

创建快照：

根据源数据库的当前大小，确保有足够的磁盘空间存放数据库快照。数据库快照的最大大小为创建快照时源数据库的大小。

使用 AS SNAPSHOT OF 子句对文件执行 CREATE DATABASE 语句。创建快照需

要指定源数据库的每个数据库文件的逻辑名称。

创建数据库快照时，CREATE DATABASE 语句中不允许有日志文件、脱机文件、

还原文件和不起作用的文件。

语句：

CREATE DATABASE AdventureWorks_Snapshot1200 ON

( NAME = N‘AdventureWorks_Data‘, FILENAME =

N‘C:\Program Files\Microsoft SQL

Server\MSSQL.1\MSSQL\Data\AW_1200.ss‘)

AS SNAPSHOT OF AdventureWorks

SELECT AddressID, AddressLine1, ModifiedDate

FROM AdventureWorks.Person.Address

WHERE AddressID = 1

SELECT AddressID, AddressLine1, ModifiedDate

FROM AdventureWorks_Snapshot1200.Person.Address

WHERE AddressID = 1

UPDATE AdventureWorks.Person.Address

SET AddressLine1 = ‘1000 Napa Ct.‘

WHERE AddressID = 1

恢复数据库快照：

RESTORE DATABASE <数据库名称> FROM DATABASE_SNAPSHOT = <数据库快

照名称>

恢复指定表或行

删除快照：

DROP DATABASE

创建数据库：

1)、通过mangement studio

数据库实例 → 数据库 → 新建数据库 → 配置数据库名称、数据库文件逻辑名称、物理名称 → 配置文件类型、文件组、文件初始大小

2)、使用T-SQL 创建数据库：

Create dataase mydb1

创建schema

1)、通过mangement studio

数据库实例 → security → schema → 新架构

创建登录名

创建数据库用户名

绑定登录名、用户名、架构

初始配置数据库

配置SQL SERVER 2008 启用FILE STREAM:
A、服务管理器 → SQL Server(MSSQLSERVER) → Logon → Local system account
B、SQL SERVER 配置管理器 → SQL SERVER Service →
SQL Server(MSSQLSERVER) → FileStream →
启用FILESTREAM for T-SQL
启用FILESTREAM for File I/O
允许remote client to have stream asscess
B、 SQL Management Studio → 属性 → 高级 → FILE STREAM → 启用完全访
问
C、重启SQL Server(MSSQLSERVER)服务

配置SQL SERVER 支持远程访问：
A、SQL SERVER 配置管理器 → SQL Native Client 10.0 config（32 位） →
client protocols → 启用 Shared memory
TCP/IP
Named pipes
B 、SQL SERVER 配置管理器 → SQL Server network config → protocols for
MSSQLSERVER → 启用： Shared memory
TCP/IP
Named pipes
C、SQL SERVER 配置管理器 → SQL Server network config → client protocols →
学习是不断积累及重复学习的过程，当学习变成一种习惯的时候，就真正进入了学习的殿堂！
第 55 页共 588 页
启用 Shared memory
TCP/IP
Named pipes
用户配置
若安装时是选择WINDOWS身份认证，将其更改为SQL 和WINDOWS混合模式身份认证：
A、实例 → 属性 → 安全性 → SQL 和WINDOWS 混合身份验证模式
B、实便 → Security → logins → Sa → 属性 →
配置密码
状态：启用登录
授权连接到Database