SQLServer2008數據庫創建過程和可配置選項

系統數據庫

　　在安裝SQL Server 2008時，創建了5個系統數據庫來存儲系統信息和支持數據庫操作。在普通的數據庫操作中，我們可以看到四個系統數據庫master、modle、msdb、tempdb，但是看不到第五個Resource數據庫。如果SQL Server實例被配置為用于SQL Server復制的分發服務器，就可以創建分發數據庫。

用戶數據庫

　　用戶數據庫是由具有適當權限的任意服務器登錄名創建。

數據庫規劃

　　數據庫管理員應該考慮為什么數據庫像現在這樣運行和如何運行。管理數據庫最好時機是在安裝之前。無論是內部開發還是從軟件供應商那里購買數據應用程序，數據庫管理員都必須深入規劃和創建支持數據庫。

　　規劃一個數據庫時必須決定的第一件事是需要多大的磁盤空間來支持該數據庫。一個比較有效和現實的方法是先取得數據庫原型（數據庫的測試或開發版本），然后在其中填充適量的測試數據。之后，價差磁盤上數據文件的大小，然后將其乘以1.5.其結果應該足夠容納新數據庫的初始數據量，而且還會有空間剩余。一旦數據庫投入生產環境，監控數據庫文件大小就相當重要，因為這樣可以分析增長趨勢。例如可以配置為當數據庫中填充的數據量達到75%是發出警報，這樣可以在需要時增加文件的大小，但同時又能以足夠的百分比在增長他們，從而避免經常執行增長。

　　規劃事務日志文件的大小更加復雜。要想精確地規劃日志大小，必須知道數據庫中執行的事務的平均大小、發生的頻率以及被修改的表的物理結構。

創建數據庫

　　新建數據庫時可使用命令CREATE DATABASE SampleDB，但是這樣生成的數據庫文件在系統默認的文件夾下，不推薦使用，可以使用圖形界面進行創建。在新建數據庫界面中“所有者”字段的值為“<默認值>”，這時創建數據庫時使用的登錄帳戶。這個帳戶一般是Sa，這是一個內置的SQL Server系統管理員帳戶。數據庫所有者應該總是為Sa，這樣可以避免任何可能發生的問題。數據庫的所有者可以完全控制該數據庫，要修改數據庫的所有權，可以使用TSQL語句ALTER AUTHORIZATION指定任意的有效的登錄名，如：ALTER AUTHORIZATION ON DATABASE::SampleDB TO SA

　　想要檢索數據庫信息有兩種不同的方法，sp_helpdb存儲過程可用于檢索所有數據庫或特定數據庫的信息，非常易于用作快速查看，要檢索所有數據庫，執行存儲過程時不使用參數。對于特定數據庫，將數據庫名稱傳遞給該存儲過程，如：

USER MasterGOEXEC sp_help AdventureWorks2008

　　還有一種檢索數據庫信息的方法是是使用目錄視圖，他們提供的信息比相應的存儲過程多，且允許使用標準的TSQL命令，如WHERE和GROUP BY。如使用sys.database目錄視圖與sys.server_principals目錄視圖連接起來，查看服務器上所有數據庫的基本信息：

SELECT db.name AS database_name,sp.name AS owner,db.create_date,db.compatibility_level,db.recovery_model_desc FROM sys.database db INNER JOIN sys.server_principals sp ON db.owner_sid=sp.sid

數據庫文件

　　在“新建數據庫”對話框中的“數據庫文件”部分中，第一個數據文件的邏輯名稱和第一個日志文件的邏輯名稱已經被自動命名，單擊對話框底部的“添加”按鈕，就會像數據庫文件中添加一個新的文件行，新的文件默認類型為“行數據”，也可以通過下拉列表中選擇“日志”或“行數據”，一旦創建好數據庫，文件類型不可改變。然后在文件組列中單擊下拉列表，選擇文件組

文件組

　　數據庫時基于組織在文件組中的文件創建的。文件組是用來存放為數據庫定義的所有數據和數據庫對象的數據文件的邏輯分組。

　　唯一必須的文件組是主文件組，主要由主數據文件和其他用戶定義的數據文件組成。主數據文件的作用是存儲針對數據庫的所有系統引用，包括指向Resource數據庫中定義的對象的指針。如果作為默認文件組，則主文件組包含用戶定義的對象以及系統創建的對象的所有對象定義。除了主文件組意外，還可以創建更多的用戶定義文件組。

　　使用用戶定義文件組的一個最大好處是控制，如果沒有用戶定義文件組，那么所有數據都會存儲在主文件組中，這樣數據庫的靈活性和可擴展性都大大削弱。輸入新文件組的名稱，選中“默認值”復選框，然后單擊“確定”按鈕，這會把新的用戶定義的文件組設置為默認文件組，用戶創建的所有對象都會放在這個新的文件組中。這實際上就把系統數據和用戶數據分離開來，從而獲得對數據庫結構的更多控制。使用文件組的一個不錯的功能就是可以將該文件組中的數據標記為“只讀”，方法是選擇“新建文件組”對話框中的“只讀”復選框。當在一個數據庫中組織不同的對象時，這個選項相當有用，要改變的對象可以放在一個可更ixnd文件組中，而不會改變的對象放在一個只讀的文件組中，分離對象可以減少需要備份和還原的數據量，對于大型數據庫來說是一個很有用的選項。

　　文件組帶來的性能優勢主要有三方面，第一個方面是可以并行讀寫，這時通過將數據文件分割到多個物理設備實現的。然后，如果把多個物理我呢間放到單個文件組中，可以可獲得同樣的性能；第二個方面則是將非聚集索引和大型對象數據溢出作為常規數據空間而保留的文件組，將非聚集索引與數據分離可以讓數據庫引擎同時使用獨立的線程從索引中搜搜行位置和從表中檢索行，將不經常訪問的大型對象數據和事務密集的關系數據分離還可以提升一些實例中的掃描性能；第三個方面，也是最顯著的方面，是可以跨多個文件組物理分區大型表。

選項

　　在“新建數據庫”對話框中的左邊點擊“選項”按鈕，會看到各類組織級別的選項

　　ANSI NULL默認值：在CREATE TABLE或ALTER TABLE操作中添加至表中的列是否允許空置，默認值為False，也就是除非有顯示指定，否則添加的列不允許空值，可以手動進行設置

--更改默認值SET ANSI_NULL_DELT_ON OFFSET ANSI_NULL_DELT_ON ON更改數據庫的選項值ALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT OFFALTER DATABASE AdventureWorks2008 SET ANSI_NULL_DEFAULT ON

　　ANSI 警告已啟用：當設置為True時，只要聚合函數中出現空值，數據庫引擎就會發出警告，設定為False時，則不會發出警告，可以在連接級別或者數據庫級別設置

SET ANSI_WARNINGS OFFSET ANSI_WARNINGS ONALTER DATABASE AdventureWorks2008 SET ANSI_WARNINGS OFFALTER DATABASE Adventureworks2008 SET ANSI_WARNINGS ON

　　算術終止啟用：當設置為true的時候，任何語句或事務在遇到算術溢出或被零除錯誤后都將終止，設定為False的時候，會發出一個警告，但不是終止語句。同樣可以在連接級別或者數據庫級別設置：

SET ARITHABORT OFFSET ARITHABORT ONALTER DATABASE Adventureworks2008 SET ARITHABORT OFFALTER DATABASE Adventureworks2008 SET ARITHABORT ON

　　自動創建統計信息：設置為True的時候，數據庫引擎為那些JOIN操作的WHERE子句或ON子句中引用的、缺少統計信息的非索引列生成統計信息

ALTER DATABASE Adventureworks2008 SET AUTO_CREATE_STATISTICS OFFALTER DATABASE Adventureworks2008 SET AUTO_CREATE_STATISTICS ON

　　自動更新統計信息：設置為True時，數據庫引擎會自動更新列的統計信息，從而保持最有效的查詢計劃，如果設置為False則需要數據庫管理員手動更新統計信息

ALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS OFFALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS ON

　　自動異步更新統計信息：設置為True時，查詢中發現的國企統計信息將被更新，但發現這些國企統計信息時正在執行的查詢不會等待新的統計信息，后續的查詢將會利用新的統計信息，當設置為False的時候，只有統計信息更新之后才進行查詢編譯：

ALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS_ASYNC OFFALTER DATABASE Adventureworks2008 SET AUTO_UPDATE_STATISTICS_ASYNC ON

　　默認游標：游標始終基于生命它的連接。當設置為Global時，制定了生命的游標可被統一連接上執行的任意批處理、存儲過程和觸發器引用。如果設置為Local，游標只能在聲明了它的批處理、存儲過程或觸發器中引用

ALTER DATABASE Adventureworks2008 SET CURSOR_DEFAULT LOCALALTER DATABASE Adventureworks2008 SET CURSOR_DEFAULT GLOBAL

　　驗證頁：驗證頁為數據庫管理員可以為寫頁設定不同的選項。可用的選項包括Checksun，Torn_Page_Detection和None。對于性能來說最好的選擇的None，但是此設置會使數據庫無法檢測到數據損壞。Checksun提供了最好的保證，無論是數據寫操作還是寫操作之后發生的對磁盤數據的任意修改都會被校驗和驗證檢測到，但是會占用最多的CPU周期；Torn_Page_Detection選項是一種檢測損壞頁的低成本方法，但是只會檢測在寫操作期間發生的頁損壞，推薦設置是Checksun

ALTER DATABASE Adventureworks2008 SET PAGE_VERITY NONEALTER DATABASE Adventureworks2008 SET PAGE_VERITY TORN_PAGE_DETECTIONALTER DATABASE Adventureworks2008 SET PAGE_VERITY CHECKSUM

　　參數化：默認情況下數據庫引擎將一些查詢參數化，這樣即使在WHERE子句中定義了不同的值，創建并編譯的查詢計劃也可重用，如果設置為簡單，SQL Server會決定參數化哪些查詢和不參數化哪些查詢；設置為強制時，SQL Server會把所有可參數化的查詢參數化，而同樣的查詢會得到一個參數化查詢計劃。強制自動參數化優勢能改善性能，但是需要仔細監控以確保它對性能沒有負面影響

ALTER DATABASE Adventureworks2008 SET PARAMETERIZATION SIMPLEALTER DATABASE Adventureworks2008 SET PARAMETERIZATION FORCED

　　允許帶引號的標識符：默認情況下SQL Server使用方括號來界定對象，只有在對象名中包含嵌入的空格或保留字時才需要界定對象。ANSI標準定界符是雙引號，下面的例子說明了如何使用方括號和雙引號創建和引用一個包含嵌入空格的對象：

USER Adventureworks2008GOCREATE TABLE "Sales.USA Customers"(AccuNumber int IDENTITY(1,1) NOT NULL,"Last Name" varchar(75) NOT NULL,"First Name" varchar(75) NOT NULL)SELECFT AcctNumber,"Last Name","First Name" FROM "Sales.USA Customers"USER Adventureworks2008GOCREATE TABLE [Sales.USA Customers](AccuNumber int IDENTITY(1,1) NOT NULL,[Last Name] varchar(75) NOT NULL,[First Name] varchar(75) NOT NULL)SELECFT AcctNumber,[Last Name],[First Name] FROM [Sales.USA Customers]

　　當“允許帶引號的標識符”為True的時候，方括號和雙引號都可以使用，為False的時候，值有方括號定界符可以使用，要在連接級別或者數據庫級別進行設置的時候如下

SET QUOTED_IDENTIFIER OFFSET QUOTED_IDENTIFIER ONALTER DATABASE Adventureworks2008 SET QUOTED_IDENTIFIER OFFALTER DATABASE Advetnureworks2008 SET QUOTED_IDENTIFIER ON

　　遞歸觸發器已啟用：遞歸觸發器是一項高級編程技術，它允許統一觸發器在同一事務中按順序執行多次。當設置為False是，這一操作時不允許的。

ALTER DATABASE Adventureworks2008 SET RECURSIVE_TRIGGERS OFFALTER DATABASE Adventureworks2008 SET RECURSIVE_TRIGGERS ON

　　限制訪問：限制訪問選項使數據庫管理員可以把對數據庫的訪問限制為一組已定義的登錄名。該選項的默認值是MULTI_USER，允許多個無權限的用戶訪問數據庫，此外還有兩個選項SINGLE_USER和RESTRICTED_USER，如果設置為SINGLE_USER，那么一次就只有一個用戶帳戶可以訪問數據庫，如果設置為RESTRICTED_USER，那么只有db_owner,dbcreator或者sysadmin的成員可以連接至數據庫。要在數據庫級別進行設置，可以使用如下命令

ALTER DATABASE Adventureworks2008 SET MULTI_USERALTER DATABASE Adventureworks2008 SET RESTRICTED_USERALTER DATABASE Adventureworks2008 SET SINGLE_USER

生成數據庫創建腳本

　　幾乎每一個創建或修改數據庫對象的配置屏幕都包括腳本操作選項。單擊“腳本”按鈕可用的腳本操作選項會顯示，單擊任何腳本操作都將生成一個腳本，腳本復制您在圖形化界面中指定的所有設置。然后通過這個腳本，可以使用同樣的選項創建新的數據庫，值需要改變數據庫和相關文件的邏輯及物理名稱即可。

　　另外一種重用腳本的方法是使用變量替代對象和文件的實際名稱，接下來只需要更新變量值并執行腳本，創建數據定義語言Data Definition LanguageDDL腳本時唯一棘手的部分是必須使用動態SQL，因為在DDL腳本中不能直接使用變量，下面的例子演示了如何使用動態SQL來創建一個新的數據庫，并將一個用戶定義的文件組標記為默認文件組：

DECLARE @DatabaseName AS nvarchar(255)DECLARE @FileGroupName AS nvarchar(255)SET @DatabaseName=N'SlateGravel'SET @FileGroupName=N'UserData'EXECUTE('CREATE DATABASE '+@DatabaseName+' ON PRIMARY( NAME="'+@DatabaseName+'",FILENAME="S:SQLDataFiles'+@DatabaseName+'_data.mdf",SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=30%),FILEGROUP UserData(NAME="'+@FileGroupName+'",FILENAME="S:SQLDataFiles'+DatabaseName+'_data.ndf",SIZE=2048KB,FILEGROWTH=20%)LOG ON(NAME="'+@dATABAEnAME+'_log",FILENAME="T:SQLLogFiles'+@DatabaseName+'_log.ldf",SIZE=100MB,FILEGROWTH=20%);ALTER DATABASE '+@DatabaseName+'MODIFY FILEGROUP '+@FileGroupName+' DEFAULT')

架構

　　架構是一種對象容器,在SQL Server 2008中引用數據庫對象是，設定對象引用的上下文是很重要的。每個用于都被指派一個默認的架構，當他們登錄到SQL Server應引用數據庫對象時，這個默認架構將在該對象的引用方式中發揮獨特的作用。例如，假設在AdventureWorks2008數據庫中創建了一個名為FredF的用戶，并把默認架構Sales指派給他。如果FredF登錄并執行SELECT * FROM CreditCard查詢，由于其默認架構師Sales，CreditCard將被解析為Adventureworks2008.Sales.CreditCard。由于Sales.CreditCard表存在，因此查詢將返回該表的內容，如果FredF執行SELECT * FROM Person查詢，Person將被解析為Adventureworks2008.Sales.Person，這是一個并不存在的表。因此，SQL Server會返回“無效的對象名稱”這個錯誤。

　　要創建架構唯一需要的信息是指定架構名稱，架構的所有權默認屬于運行創建腳本的用戶，但可以把任何有效的數據庫用戶指定為所有者，最簡單的方法就是把dbo指定為架構所有者

USER AdventureWorks2008GOCREATE SCHEMA Operations AUTHORIZATION dbo

　　該CREATE SCHEMA語句之后的任何架構作用于的語句都將位于剛才創建的架構的作用于內。記住，架構作用于總是起始于用戶的默認架構，如果一個被引用的對象不是作用域限定的，解析將返回到dbo架構

　　如果視圖刪除一個包含對象的架構，將會產生錯誤，如果仍然需要架構中的對象，可以使用ALTER SCHEMA語句把它轉移到另一個架構中，如把表DeliveryDriver從Operations架構轉移到Production架構中。

ALTER SCHEMA Production TRANSFER Operations.DeliveryDriver

　　不能從數據庫中刪除擁有架構的對象，這就是為什么讓dbo用戶擁有所有架構的原因之一。要想改變一個架構的所有權，需要改變架構的AUTHORIZATION屬性：

ALTER AUTHORIZATION ON SCHEMA::Operations TO FredF

表

　　表的排序規則：創建數據庫時，可以配置不同于服務器的排序規則支持。對于包含字符數據的表列來說也是如此，如創建一個各地客戶可以使用自己的語言瀏覽器和搜索產品目錄的表：

USER AdventureWorks2008GOCREATE TABLE Production.GlobalProductDescription(ProductDescriptionID int IDENTITY(1,1) NOT NULL,EnglishDescription nvarchar(400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,FrenchDescription nvarchar(400) COLLATE French_CI_AS NULL,ChineseDescription nvarchar(400) COLLATE Chinese_PRC_CI_AI NULL,ModifiedDate datetime NOT NULL)

　　SQL Server絕對不是使用飯一起，只是提供了一個框架來存放多種語言。您需要準備產品描述的正確翻譯并把它們放到合適的列中，另外還要處理任何因為tempdb的排序規則導致的愛需規則不兼容性。

　　表的體系結構：SQL Server使用8KB的數據頁春初信息，表中的所有數據都存儲在這些數據頁中，但數據在頁中的組織方式會因表的創建方式和在表創建后所進行的操作有所不同。默認情況下，所有的數據以稱為堆的無組織的方式進行存儲。SQL Server并不嘗試組織或是以任何方式排序數據，也不維護頁間的連接。

　　SQL Server將所有數據頁存放在分區的邏輯單元中，除非明確分隔，否則表一般存儲在定義于單個文件組上的單個分區中。不過，通過跨多個由文件組定義管理的文件橫向分區表，SQL Server可以把大型表劃分為較小的可管理單元。如下創建實驗表并進行差序性能測試：

--創建新表USER AdventureWorks2008GOCREATE TABLE dbo.Transactions(TransactionID int NOT NULL,ProductID int NOT NULL,ReferenceOrderID int NOT NULL,ReferenceOrderLineID int NOT NULL,TransactionDate datetime NOT NULL,TransactionType nchar(1) NOT NULL,Quantity int NOT NULL,ActualCost money NOT NULL,ModifiedDate datetime NOT NULL)--填充新表USE AdventureWorks2008GOINSERT dbo.TransactionsSELECT * FROM Production.TransactionHistory UNION ALL SELECT * FROM Production.TransactionHistoryArchive--查看查詢性能USER AdventureWorks2008GODBCC DROPCLEANBUFFERSSET STATISTICS IO ONDECLARE @BeginDate AS datetime,@EndDate AS datetimeSET @BeginDate='2002-01-01'SET @EndDate='2002-12-31'SELECT SUM(Quantity) AS TotalQuantity,SUM(ActualCost) AS TotalCost FROM dbo.Transactions WHERE TransactionDate BETWEEN @BeginDate AND @EndDate

　　可以看到，為了滿足查詢要求，SQL Server不得不掃描表，現在對表進行分區，把該物理劃分為多個文件，使所有的事務按照年份分開，看看會發生什么情況：

--創建四個文件組USER MASTERGOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FGPre2002GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='AworksPre2002',FILENAME='E:SQLDataAworksPre2002.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FGPre2002GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2002GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2002',FILENAME='E:SQLDataAworks2002.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2002GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2003GOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2003',FILENAME='E:SQLDataAworks2003.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2003GOALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2004AndAfterGOALTER DATABASE AdventureWorks2008 ADD FILE(NAME='Aworks2004AndAfter',FILENAME='E:SQLDataAworks2004AndAfter.ndf',SIZE=20MB,FILEGROWTH=20%) TO FILEGROUP FG2004AndAfterGO--創建分區函數CREATE PARTITION FUNCTION YearFunction(datetime) AS RANGE RIGHT FOR VALUES('1/1/2002','1/1/2003','1/1/2004')--將YearFunction創建的分區映射到之前創建的文件組CREATE PARTITION SCHEME YearSchemeAS PARTITION YearFunctionTO (FGPre2002,FG2002,FG2003,FG2004AndAfter)--將數據從原Transactions表中移至分區表中USER AdventureWorks2008GOCREATE TABLE dbo.PartitionedTransactions(TransactionID int NOT NULL,ProductID int NOT NULL,ReferenceOrderID int NOT NULL,ReferenceOrderLineID int NOT NULL,TransactionDate datetime NOT NULL,TransactionType nchar(1) NOT NULL,Quantity int NOT NULL,ActualCost money NOT NULL,ModifiedDate datetime NOT NULL) ON YearScheme(TransactionDate)GOINSERT INTO dbo.PartitionedTransactions SELECT * FROM dbo.Transactions--查詢性能測試USER AdventureWorks2008GODBCC DROPCLEANBUFFERSSET STATISTICS IO ONDECLARE @BeginDate AS datetime,@EndDate AS datetimeSET @BeginDate='2002-01-01'SET @EndDate='2002-12-31'SELECT SUM(Quantity) AS TotalQuantity,SUM(ActualCost) AS TotalCost FROM dbo.PartitionedTransactions WHERE TransactionDate BETWEEN @BeginDate AND @EndDate

　　創建表分區還可以通過右擊表選擇存儲，選擇創建分區，通過GUI進行創建，這里略掉

索引

　　堆用來存儲數據很好，也能有效地處理新紀錄，但是要在表中尋找特定數據時就沒有那么好用了。這就是索引發揮作用的地方那個。SQL Server支持兩種類型的索引，聚集索引和非聚集索引。它還支持XML索引和空間索引等其他類型索引，這這些索引和普通索引的關系索引打不相同，后者將來用在數據庫表中定位大多數數據。聚集索引和非聚集索引之間的主要區別在于索引的葉級。在非聚集索引中，葉級包含數據的指針。在聚集索引中，葉級包含實際數據。

　　聚集索引：表的所有數據可存儲在堆中或聚集索引中，堆和聚集索引是相互排斥的。堆是一個無組織的表行集合，而聚集索引是一個有組織的表行集合。聚集鍵在索引中必須是唯一的，但創建索引時不必將此列標記為唯一的。當在未標記的唯一的列上創建聚集索引時，SQL Server會生成一個隱藏列，它保存一個名為唯一標識符的4字節內部值來唯一標識重復的聚集索引鍵。聚集索引的葉級是實際的數據行，而不是數據指針。

　　非聚集索引：非聚集索引更像是一本書后面的索引。當找到索引值時，并不能找到實際數行，只是獲得指定實際數據行的位置的指針。

　　堆上的非聚集索引：當在一個以堆形式組織的表上構建非聚集索引時，索引列根據指向數據實際位置的指針分類，該指針由文件ID，頁ID和數據所在的頁槽號組成，如數據是第一個文件行總第84593頁上的第20條記錄，那么SQL將使用指針值1：84593：20，這使得SQL Server能夠在索引找到數據之后快速訪問數據。

　　聚集索引上的非聚集索引：當在一個聚集索引上構建非聚集索引時，索引中的指針值就是數據行的聚集索引鍵值。一旦定位到索引值，SQL Server就是用聚集鍵導航聚集索引來檢索所有需要的列。

　　包含列：非聚集索引的功能可以通過向索引的節點添加非鍵值得到提升，這就可使得索引覆蓋更多的查詢，減少為檢索額外值而歷遍聚集索引的次數。包含列可以提高查詢覆蓋率，而不會導致符合索引鍵的開銷。索引中標記為included的列值出現在索引的頁節點中，在行排序中不做考慮。

CREATE NONCLUSTERED INDEX IX_Person_LastName ON Person.Person(LastName) INCLUDE (FirstName)

　　篩選索引：篩選索引時優化非聚集索引，它允許在數據子集上創建索引，使得索引結構更小從而減少了構建索引的時間和索引維護的成本，對于包含大量NULL值或包含數據范圍的列上的索引，篩選索引特別有用：

CREATE NONCLUSTERED INDEX IX_ListPrice_Product ON Production.Product(ListPrice) WHERE ListPrice>800.00

　　分層索引：HierarchyId是SQL Server 2008中引入的一種新類型數據。為了幫助檢索分層數據，可使用兩種不同的方法在此類型上的列構建索引，廣度優先索引和深度優先索引。廣度優先索引將統一級別的所有記錄組合到一起，這樣SQL Server就可以非常快速地相應具有共同父節點的所有記錄的查詢。

　　空間索引：SQL Server 2008通過兩種新的CLR數據類型支持空間索引，geometry和geography，geometry數據類型用于平面空間而geography數據類型用于地形測量空間。創建空間索引需要兩個階段：分解和鑲嵌。在分解階段，SQL Server將一個有限區域劃分為一個網格結構，在鑲嵌階段，表中的每個空間值都映射到每個網格級別。

　　XML索引：憑借XML上建立索引的功能，能夠幫助定位和檢索XML文本中的特定數據。XML數據早SQL Server數據庫中存儲為Binary Large Object（BLOB，二進制大型對象）。要在XML中搜索特定元素，屬性或者值，SQL Server必須首先打開BLOB，然后將其內容拆解開。SQL Server通過拆解操作創建一個自己可以導航的XML對象的集合。它實質上是提取了XML的數據結構，然后將其存儲在臨時的關系結構中。

　　XML主索引：主XML索引實際并不是構建在XML列上，而是構建在內部表（在索引創建過程中創建）之上的聚集索引。這個內部表稱為節點表。節點表直接連接至在其中創建XML索引的表的聚集索引。要創建一個XML索引，具有XML列的表必須在其逐漸上有一個聚集索引。創建語法如下：

USE AdventureWorks2008GOCREATE PRIMARY XML INDEX XML_IX_IllustrationON Production.Illustration(Diagram)

　　PATH輔助XML索引：PATH輔助XML索引可以提升對XML列指定路徑表達式的XML查詢的性能。PATH輔助索引建立愛主XML索引提供的節點之上，語法如下：

USER AdventureWorks2008GOCREATE XML INDEX IXML_MyPerson_AdditionalContactInfo_Path ON dbo.MyPerson(AdditionalContactInfo)USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR PATH

　　VALUE輔助XML索引：VALUE輔助索引用來支持沒有完全指定路徑的或采用通配符來搜索值的XML查詢。語法如下：

CREATE XML INDEX IXML_MyPerson_AdditionalContactInfo_Value ON dbo.MyPerson(AdditionalContactInfo) USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR VALUE

　　PROPERTY輔助XML索引：PROPERTY輔助索引用于優化通過制定節點的完整路徑檢索節點值的查詢。語法如下：

CREATE XML INDEX_IXML_MyPerson_AdditionalContactInfo_Property ON dbo.MyPerson(AdditionalContactInfo) USING XML INDEX PXML_MyPerson_AdditionalContactInfo FOR PROPERTY

維護表

　　我們已經深入了解了數據在表中的組織方式和優化數據檢索的方法，接下來套路如何對這個環境進行維護，表的維護基本可以分為兩種：索引的維護和索引統計信息的創建和維護

　　索引在更新時會變得支離破碎，這是因為索引時一個連續的、排序的數據集合。要維護索引的排序順序，SQL Server必須分割完整的數據頁以容納更多的數據。這些也拆分導致產生了索引碎片，索引的碎片化將最終導致SQL Server在檢索數據時執行過量的讀取，從而造成性能低下。要檢查表上的所有索引或一些特定索引的碎片，可以使用動態管理函數sys.dm_db_index_physical_stats，此函數會返回表的索引的大量信息，包括每個數據頁上的數據量、索引的葉級和非葉級上的碎片量，以及索引中記錄的平均大小。當擦和訊這個表值函數時，最感興趣的是碎片級別和每一頁填充的平均百分比。

SELECT {*  column list} FROM sys.dm_db_index_physical_stats ({databae_id | NULL},{object_id | NULL},{index_id | NULL},{partition_number | NULL},{mode | NULL | DEFAULT}) 

DECLARE @dbID  smallint,@objectID intSET @DbID=DB_ID('AdventureWorks2008')SET objectID=OBJECT_ID('dbo.MyPersons')SELECT index_id,avg_gragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(@DbID,@ObjectID,NULL,NULL,'DETAILED') WHERE index_level=0

　　為減少頁拆分產生的碎片，數據庫管理員可以設計或重新生成索引，使數據頁不會完全填充。為此，可以使用填充因此，當建立或重新生成索引時，可以指定填充因子百分比，如果一個索引頁只有90%被填充，那么只有插入更多的索引時才會導致頁拆分，雖因產生碎片的時間會久些。個人認為，索引的填充因子最好不要低于90%，在更新和查詢都很頻繁的表上，這個比例可以低至85%。

　　要去除碎片可以有三種方法：刪除并重新生成索引、原地重新生成索引或重新組織索引。

--使用DROP_EXISTING選項創建索引CREATE UNIQUE CLUSTERED INDEX PK_Address_AddressID ON Person.Address(AddressID) WITH (fillfactor=90,DROP_EXISTING=ON)--重新生成索引USE AdventureWorks2008GOALTER INDEX AK_Product_ProductNumber ON Person.Product REBUILD WITH (FILLFACTOR=90,ONLINE=ON)USE AdventureWorks2008GOALTER INDEX ALL ON Person.Product REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

　　重新組織索引消耗的系統資源最少，但不如重新生成索引來的徹底。

強制數據完整性

　　主鍵約束：一個表有且只能有一個主鍵約束。它用來唯一標識表中的每一行。主鍵約束可定義在單個列或者列組合上。SQL Server使用方法是在參與主鍵的列上創建唯一的索引。在不排序的情況下來強制唯一性會非常低效。SQL Server這方面的問題是，如果聚集索引不存在，他會默認一個唯一的聚集索引。決定什么列參與主鍵和決定什么列定義表數據的物理結構是完全不同的。不應假定一個主鍵同時也是表的聚集鍵。記住，表的所有聚集索引將包含聚集索引鍵作為指向數據行的指針。如果主鍵很長，使用聚集索引支持主鍵對于非聚集索引的存儲和檢索是非常不利的。

USER AdventureWorks2008GOCREATE TABLE dbo.CreditCards(CreditCardID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID),CardType nvarchar(50) NOT NULL,CardNumber nvarchar(25) NOT NULL,ExpMonth tinyint NOT NULL,ExpYear smallint NOT NULL,ModifiedDate datetime NOT NULL)USER AdventureWorks2008GOCREATE TABLE dbo.CreditCards(CreditCardID int IDENTITY(1,1) NOT NULL,CardType nvarchar(50) NOT NULL,CardNumber nvarchar(25) NOT NULL,ExpMonth tinyint NOT NULL,ExpYear smallint NOT NULL,ModifiedDate datetime NOT NULL,CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID))ALTER TABLE dbo.CreditCardsADD CONSTRAINT PK_CreditCardID PRIMARY KEY NONCLUSTERED (CreditCardID)

　　唯一約束：雖然一個表只能有一個主鍵約束，但是它可以有很多唯一約束。要創建唯一約束，有兩個選擇，在表上創建唯一索引或唯一約束。唯一索引和唯一約束的行為相似，而SQL Server將創建唯一索引來強制唯一約束。

ALTER TABLE dbo.Driver ADD CONSTRAINT UX_LincenseNum UNIQUE NONCLUSTERED(LicenseNum)CREATE UNIQUE NONCLUSTERED INDEX INDEX UX_LincenseNum ON dbo.Driver(LicenseNum)

　　外鍵約束：外鍵約束用來保證表之間的完整性，要在表上創建一個外鍵約束，定義在外鍵中的列必須映射到主鍵表中的列，這些列被指定為主鍵或者有唯一約束（唯一約束和唯一索引都可以）。外鍵約束有一些高級選項，他們可以在創建中和創建后改變外鍵約束的行為。

--WITH CHECK添加外鍵約束時的默認設置，設置指定了應對外鍵表中任何現有數據進行驗證ALTER TABLE dbo.DriverRecord WITH CHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID)--WITH NOCHECK，設定了部隊現有數據進行驗證來使其符合新的約束ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID)

　　級聯約束：外鍵默認組織更新或刪除父值，然而有些時候這種行為并不合適。SQL Server提供了指定在福記錄被刪除或更新的情況下對子記錄采取何種行為的選項。ON DELETE NO ACTION和ON UPDATE NO ACTION是外鍵的默認設置。這些設定指定，任何試圖刪除或更新由其他表中現有行的外鍵所引用的鍵值的行為都會失敗。此外還有CASCADE,SET NULL和SET DEFAULT選項，他們允許刪除或更新鍵值，以定義的方式級聯至定義為擁有外鍵關系的表

--ON DELETE CASCADE如果父行被刪除，子記錄也刪除ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE CASCADE--ON UPDATE CASCADE如果父鍵被更新，子記錄也更新ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE CASCADE--ON DELETE SET NULL如果父行刪除，子記錄設置為NULLALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE SET NULL--ON UPDATE SET NULL如果父鍵更新，子記錄設置為NULLALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE SET NULL--ON DELETE SET DEFAULT父記錄刪除，子記錄設置為DEFAULT約束值ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON DELETE SET  DEFAULT--ON UPDATE SET DEFAULT父鍵更新時，子記錄設置為DEFAULT約束值ALTER TABLE dbo.DriverRecord WITH NOCHECKADD CONSTRAINT FK_DriverRecord_Driver FOREIGN KEY (DriverID) REFERENCES dbo.Driver (DriverID) ON UPDATE SET DEFAULT

　　CHEKC約束：CHEK約束用來確保字段中的數據符合一個以定義的表達式，如

ALTER TABLE dbo.Driver ADD CONSTRAINT CK_DriverSocialSecurityNumber CHECK (SocSecNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')GO

　　默認約束：在插入的時候如果沒有指定任何值，默認約束將指定一個值插入到表中

ALTER TABLE dbo.Driver ADD CONSTRAINT DF_Driver_SocSecNum DEFAULT '00000000' FOR SocSecNum

數據庫關系圖

　　一旦創建了數據庫及其對象，就可以很方便地創建和底層結構鏈接的實體關系圖。這樣，任何必要改動，尤其是創建外鍵約束都可以通過一個方便的圖形環境應用到數據庫中。通過SQL Server Management Studio的對象資源管理器中的用戶數據庫節點可訪問數據庫關系圖功能。在使用時前，需要安裝關系圖支持對象。方法是右擊“數據庫關系圖”節點并選擇“安裝關系圖支持程序”命令。安裝支持對象會使SQL Server創建一個名為dbo.sysdiagrams的由系統所有的表，它將包括創建的任意關系圖的定義。

視圖

　　視圖時保存的命名查詢，可以獨立于他們引用的表進行管理。視圖和他們所引用的表非常相似，只是他們默認是邏輯對象而非物理對象。創建視圖時為了抽象復雜的數據庫設計，通過允許訪問視圖而不是多個表來簡化權限，以及安排將數據導出到其他數據存儲器中。具體這里不做介紹

　　系統視圖時數據庫管理員的系統對象視圖，因為太多的系統視圖，這里不一一敘述，可以查閱SQL Server 2008聯機叢書獲取相關信息，系統視圖主要分為四類：信息架構視圖、目錄視圖、動態管理視圖、兼容性目錄視圖

同義詞

　　所謂同義詞，是指SQL Server架構范圍內的數據對象指定一個名稱，數據庫應用程序可以使用該名稱來太低其由兩部分、三部分或四部分組成的名稱：

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈