sqlserver表和库的管理方法

2025-03-21 技术教程

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

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

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

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

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

1. 主数据文件

2. 辅数据文件

3. 事物日志文件

4. 文件流

举例说明

--创建数据库

createdatabaseschoolDB

useschoolDB

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

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

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

数据文件结构

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

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

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

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

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

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

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

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

在数据库中创建一张Tstudent表

createTABLETStudent(

StudentIDvarchar(10)NOTNULL,

Snamevarchar(10)DEFAULTNULL,

sexchar(2)DEFAULTNULL,

cardIDvarchar(20)DEFAULTNULL,

BirthdaydatetimeDEFAULTNULL,

Emailvarchar(40)DEFAULTNULL,

Classvarchar(20)DEFAULTNULL,

enterTimedatetimeDEFAULTNULL

)

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页的数据

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

selectOBJECT_NAME(i.object_id)as表名,data_pagesas数据页数

fromsys.indexesasi

joinsys.partitionsaspONp.object_id=i.object_idandp.index_id=i.index_id

joinsys.allocation_unitsasaONa.container_id=p.partition_id

wherei.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。但是收缩文件时，可以将数据库文件收缩得比其初始大小更小

收缩数据文件

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

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

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

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

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

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