sqlserver表和库管理

数据库在磁盘上是以文件为单位存储的，由数据文件和事物日志文件组成。一个数据库至少应该包含一个数据文件和一个事物日志文件。

数据库创建在物理介质（磁盘）上的一个或多个文件上，它预先分配了被数据和事物日志所要适用的物理存储空间。存储数据的文件叫做数据文件，数据文件包含数据和对象，如表和索引。存储事物日志的文件叫做事物日志文件（又称日志文件）。

sql server 2008 R2数据库有一下类型的文件

1. 主数据文件

2. 辅数据文件

3. 事物日志文件

4. 文件流

举例说明

--创建数据库

create database schoolDB

go

use schoolDB

go

这样创建完的数据库只有一个数据文件和一个事物日志文件，查看方式在创建的schoolDB上右键选择“属性”--“文件”

可以在界面中单击“添加”增加新的数据文件schooldb2，以后再schooldb数据库中创建新的表，表中的数据就会存放在schooldb和schoolDB2数据文件中。

数据文件在数据库中存放的时候都有标识，我们可以通过sp_helpdb schoolDB

数据文件结构

数据文件1和数据文件3的结构如果所示：

数据文件由64kb大小的区（extend)组成的,每个区由8个8KB的连续的页组成的。

sqlserver所能识别的最小存储单元被称为页，一个页的大小是8KB,是sql server 存储数据的单位。

在数据库中一页的大小是8KB，在计算机中1KB就是1024，所以1页可以存放1024*8=8192个字节

如何根据表的记录数量估算占用的磁盘空间

1.先算一个表中一行有多少个字节，例如学号字段6个字节，姓名字段8个字节，出生日期字段多少个字节

2.算一个页能够存多少行

3.数据库中的表每天增加多少行记录，就能够算出每天要增加多少硬盘空间，这样就可根据数据量估算规划多大的空间。

在数据库中创建一张Tstudent表

create TABLE TStudent (

  StudentID varchar(10) NOT NULL,

  Sname varchar(10) DEFAULT NULL,

sex char(2) DEFAULT NULL,

cardID varchar(20) DEFAULT NULL,

  Birthday datetime DEFAULT NULL,

  Email varchar(40) DEFAULT NULL,

  Class varchar(20) DEFAULT NULL,

enterTime datetime DEFAULT NULL

 )

go

10个字节+10个字节+2个字节+20字节+8字节+40字节+20字节+8个字节=118字节

Tstudent表的一个行有118个字节

如果每天增加10000条记录，10000条记录占多少页？

1页可以存放1024*8=8192个字节，抛去标头96字节，真正存放的字节数就8192-96=8096字节。

一页能存8096字节，一页能够存8096/118=68条记录

10000行有多少页？10000/68=148(页）

一页是8KB，148*8==1184KB

在Tstudent表中存放10000条记录需要预备出来1MB多的磁盘空间。这样就可以根据每天增加的记录数，合理规划好磁盘空间了。

现在Tstudent表中还没有插入任何记录，所以占用了0页的数据

提供查看数据文件页数的命令

select OBJECT_NAME(i.object_id) as 表名,data_pages as 数据页数

from sys.indexes as i

join sys.partitions as p ON p.object_id=i.object_id and p.index_id=i.index_id

join sys.allocation_units as a ON a.container_id=p.partition_id

where i.object_id=object_id(‘dbo.TStudent‘)

执行教学环境中的存储过程，添加10000条记录

这个页数和我们刚才算出来的有些差距，这是正常的，每一页可能并没有插入68条记录。

1.1. 事物日志

事物是一个或多个T-SQL语句的集合，事物有一个特性：要么执行成功，要么执行失败。每个sql server数据库都具有事物日志，用于记录所有事物的SQL语句。当发生数据灾难时候，通过事物日志记录的T-SQL语句可以恢复数据库。

如果系统出现故障，sql server将使用事物日志重做（前滚）所有已确认的事物，撤销（回滚）所有未完成的事物。1.1. 创建数据库（扩展/收缩）

使用图形界面创建数据库，在对象资源管理器中，右击“数据库”节点，在弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口，设置数据库名称为“class”，制定数据库的所有者，默认创建数据库的用户将成为该数据库的所有者。主数据文件初始值为3mb,自动增长值为1mb，当数据文件或日志文件空间使用完之后，会根据设定的自动增长值增大文件的容量。

在路径列中可以设置数据文件和日志文件的保存路径。单击“确定”之后一个新的数据库就建立成功了，随之会生成两个文件一个是包含数据文件的.mdf，一个是包含日志信息的.ldf文件。

创建数据库之前，必须先确定数据库的名称，所有者（创建数据库的用户），大小以及存储该数据文件和事物日志文件的位置。

如果要对数据库做数据库级别的设置

在数据库属性中选择“选项”，在右侧可以修改数据库相关的属性。

1.1.1. 扩展数据库

当数据库中的数据文件和日志文件被充满时候，需要为数据文件和日志文件分配更多的空间。sql server 可以根据在新建数据库时定义的增长参数自动扩展数据库，也可以通过在现有的数据文件上分配更多的文件空间，或者在另一个新的数据文件上分配空间来手动扩展数据库。

扩展数据库时，必须使数据库的容量至少增加1mb，还可以指定允许文件增长到的最大值，这样可以防止文件无限制的增长，导致用尽整个磁盘空间。

举例说明：

将class数据库的class数据文件大小设置为100MB,不自动增长，然后添加一个新的数据文件，文件名为“class1”，初始文件大小设置为“50MB”，自动增长，最大文件大小设置为“500MB”

在对象资源管理器中，右击class数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性-class”窗口，在“数据库属性-class”窗口的“选择页”中选择“文件”选项，打开“文件”选择页

1.1.1. 收缩数据库

数据库在使用一段时间后，时常会因为数据删除而造成数据库中空闲空间增多的情况，这时就需要减少分配给数据库文件和事物日志文件的磁盘空间，以免浪费磁盘空间。当数据库中没有数据时，可以修改数据库文件属性，直接改变其占用空间，但是当数据库中有数据时，这样做会破坏数据库中的数据，因此需要使用收缩的方式来缩减数据库空间。

数据库中的每个文件都可以通过删除未使用的空间的方法来减小，SQL server允许通过缩小数据库，把不使用的空间释放出来，数据文件和日志文件都可以收缩。可以采用手动收缩和自动收缩数据库。

手动收缩数据库的方法

从图中可以看出来即可以选择收缩数据库，也可以选择单独收缩某个数据文件。

收缩数据库：

在收缩数据库时，无法将整个数据库收缩到比初始大小更小，如果数据库创建时的大小是10mb，后来增长到10mb,则该数据库最小只能收缩到10mb，即使已经删除了所有数据也是10mb。但是收缩文件时，可以将数据库文件收缩得比其初始大小更小

收缩数据文件

选择--“任务”--“收缩”--“文件”命令，打开收缩文件对话框

在“文件类型”下拉列表框中选择“数据”选项，也可以在这里选择收缩日志文件。”当前分配的空间“选项和”可用空间“选项显示了该文件的占用空间，使用空间和收缩的百分比。

释放未使用的空间：将释放文件中所有未使用的空间，并将文件收缩到上次分配的大小，这样将减小文件大小，但不移动任何数据。

在释放未使用空间前重新组织页：将释放文件中所有未使用的空间，并尝试重新定位到未分配的空间。这里需要指定”将文件收缩到“选项的值。

通过将数据迁移到同一文件组中的其他文件来清空文件：将指定文件中所有数据迁移至同一文件组中的其他文件中，然后可以删除空文件。

根据需要确定选项，然后单击“确定”，执行收缩操作。

自动收缩数据库

可以通过设置“数据库属性--class”窗口中的“选项”选择页中的“自动收缩”选项参数来实现自动收缩功能。将“自动收缩”选项设置为“true”后，将自动收缩可用空间的数据库。

本文出自 “chaixinwang” 博客，请务必保留此出处http://chaixinwang.blog.51cto.com/13052229/1943849

sqlserver表和库管理

标签：sqlserver