sqlserver表和庫的管理方法

本篇內容介紹了“sqlserver表和庫的管理方法”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

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允許通過縮小數據庫，把不使用的空間釋放出來，數據文件和日志文件都可以收縮。可以采用手動收縮和自動收縮數據庫。

手動收縮數據庫的方法

從圖中可以看出來即可以選擇收縮數據庫，也可以選擇單獨收縮某個數據文件。

收縮數據庫：

在收縮數據庫時，無法將整個數據庫收縮到比初始大小更小，如果數據庫創建時的大小是10mb，后來增長到10mb,則該數據庫最小只能收縮到10mb，即使已經刪除了所有數據也是10mb。但是收縮文件時，可以將數據庫文件收縮得比其初始大小更小

收縮數據文件

選擇--“任務”--“收縮”--“文件”命令，打開收縮文件對話框

在“文件類型”下拉列表框中選擇“數據”選項，也可以在這里選擇收縮日志文件。”當前分配的空間“選項和”可用空間“選項顯示了該文件的占用空間，使用空間和收縮的百分比。

釋放未使用的空間：將釋放文件中所有未使用的空間，并將文件收縮到上次分配的大小，這樣將減小文件大小，但不移動任何數據。

在釋放未使用空間前重新組織頁：將釋放文件中所有未使用的空間，并嘗試重新定位到未分配的空間。這里需要指定”將文件收縮到“選項的值。

通過將數據遷移到同一文件組中的其他文件來清空文件：將指定文件中所有數據遷移至同一文件組中的其他文件中，然后可以刪除空文件。

根據需要確定選項，然后單擊“確定”，執行收縮操作。

自動收縮數據庫

可以通過設置“數據庫屬性--class”窗口中的“選項”選擇頁中的“自動收縮”選項參數來實現自動收縮功能。將“自動收縮”選項設置為“true”后，將自動收縮可用空間的數據庫。

“sqlserver表和庫的管理方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！