sqlserver表和库的管理方法

48次阅读

没有评论

共计 3734 个字符，预计需要花费 10 分钟才能阅读完成。

本篇内容介绍了“sqlserver 表和库的管理方法”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1.1. ql server 2008R2 如何存储数据

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

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

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 允许通过缩小数据库，把不使用的空间释放出来，数据文件和日志文件都可以收缩。可以采用手动收缩和自动收缩数据库。

手动收缩数据库的方法