SQLServer2008數(shù)據(jù)庫創(chuàng)建過程和可配置選項說課材料

1、Good is good, but better carries it.精益求精，善益求善。SQLServer2008數(shù)據(jù)庫創(chuàng)建過程和可配置選項HYPERLINK/xupengnannan20070617/archive/2012/08/26/2655681.htmlSQLServer2008數(shù)據(jù)庫創(chuàng)建過程和可配置選項系統(tǒng)數(shù)據(jù)庫在安裝SQLServer2008時，創(chuàng)建了5個系統(tǒng)數(shù)據(jù)庫來存儲系統(tǒng)信息和支持數(shù)據(jù)庫操作。在普通的數(shù)據(jù)庫操作中，我們可以看到四個系統(tǒng)數(shù)據(jù)庫master、modle、msdb、tempdb，但是看不到第五個Resource數(shù)據(jù)庫。如果SQLServer實例被配置為用于SQL

2、Server復制的分發(fā)服務(wù)器，就可以創(chuàng)建分發(fā)數(shù)據(jù)庫。用戶數(shù)據(jù)庫用戶數(shù)據(jù)庫是由具有適當權(quán)限的任意服務(wù)器登錄名創(chuàng)建。數(shù)據(jù)庫規(guī)劃數(shù)據(jù)庫管理員應(yīng)該考慮為什么數(shù)據(jù)庫像現(xiàn)在這樣運行和如何運行。管理數(shù)據(jù)庫最好時機是在安裝之前。無論是內(nèi)部開發(fā)還是從軟件供應(yīng)商那里購買數(shù)據(jù)應(yīng)用程序，數(shù)據(jù)庫管理員都必須深入規(guī)劃和創(chuàng)建支持數(shù)據(jù)庫。規(guī)劃一個數(shù)據(jù)庫時必須決定的第一件事是需要多大的磁盤空間來支持該數(shù)據(jù)庫。一個比較有效和現(xiàn)實的方法是先取得數(shù)據(jù)庫原型（數(shù)據(jù)庫的測試或開發(fā)版本），然后在其中填充適量的測試數(shù)據(jù)。之后，價差磁盤上數(shù)據(jù)文件的大小，然后將其乘以1.5.其結(jié)果應(yīng)該足夠容納新數(shù)據(jù)庫的初始數(shù)據(jù)量，而且還會有空間剩余。一旦數(shù)據(jù)庫

3、投入生產(chǎn)環(huán)境，監(jiān)控數(shù)據(jù)庫文件大小就相當重要，因為這樣可以分析增長趨勢。例如可以配置為當數(shù)據(jù)庫中填充的數(shù)據(jù)量達到75%是發(fā)出警報，這樣可以在需要時增加文件的大小，但同時又能以足夠的百分比在增長他們，從而避免經(jīng)常執(zhí)行增長。規(guī)劃事務(wù)日志文件的大小更加復雜。要想精確地規(guī)劃日志大小，必須知道數(shù)據(jù)庫中執(zhí)行的事務(wù)的平均大小、發(fā)生的頻率以及被修改的表的物理結(jié)構(gòu)。創(chuàng)建數(shù)據(jù)庫新建數(shù)據(jù)庫時可使用命令CREATEDATABASESampleDB，但是這樣生成的數(shù)據(jù)庫文件在系統(tǒng)默認的文件夾下，不推薦使用，可以使用圖形界面進行創(chuàng)建。在新建數(shù)據(jù)庫界面中“所有者”字段的值為“”，這時創(chuàng)建數(shù)據(jù)庫時使用的登錄帳戶。這個帳戶一般是

4、Sa，這是一個內(nèi)置的SQLServer系統(tǒng)管理員帳戶。數(shù)據(jù)庫所有者應(yīng)該總是為Sa，這樣可以避免任何可能發(fā)生的問題。HYPERLINK數(shù)據(jù)庫的所有者可以完全控制該數(shù)據(jù)庫，要修改數(shù)據(jù)庫的所有權(quán)，可以使用TSQL語句ALTERAUTHORIZATION指定任意的有效的登錄名，如：ALTERAUTHORIZATIONONDATABASE:SampleDBTOSA想要檢索數(shù)據(jù)庫信息有兩種不同的方法，sp_helpdb存儲過程可用于檢索所有數(shù)據(jù)庫或特定數(shù)據(jù)庫的信息，非常易于用作快速查看，要檢索所有數(shù)據(jù)庫，執(zhí)行存儲過程時不使用參數(shù)。對于特定數(shù)據(jù)庫，將數(shù)據(jù)庫名稱傳遞給該存儲過程，如：USERMasterGOE

5、XECsp_helpAdventureWorks2008還有一種檢索數(shù)據(jù)庫信息的方法是是使用目錄視圖，他們提供的信息比相應(yīng)的存儲過程多，且允許使用標準的TSQL命令，如WHERE和GROUPBY。如使用sys.database目錄視圖與sys.server_principals目錄視圖連接起來，查看服務(wù)器上所有數(shù)據(jù)庫的基本信息：SELECTASdatabase_name,ASowner,db.create_date,patibility_level,db.recovery_model_descFROMsys.databasedbINNERJOINsys.server_principalsspO

6、Ndb.owner_sid=sp.sid數(shù)據(jù)庫文件在“新建數(shù)據(jù)庫”對話框中的“數(shù)據(jù)庫文件”部分中，第一個數(shù)據(jù)文件的邏輯名稱和第一個日志文件的邏輯名稱已經(jīng)被自動命名，單擊對話框底部的“添加”按鈕，就會像數(shù)據(jù)庫文件中添加一個新的文件行，新的文件默認類型為“行數(shù)據(jù)”，也可以通過下拉列表中選擇“日志”或“行數(shù)據(jù)”，一旦創(chuàng)建好數(shù)據(jù)庫，文件類型不可改變。然后在文件組列中單擊下拉列表，選擇文件組文件組數(shù)據(jù)庫時基于組織在文件組中的文件創(chuàng)建的。文件組是用來存放為數(shù)據(jù)庫定義的所有數(shù)據(jù)和數(shù)據(jù)庫對象的數(shù)據(jù)文件的邏輯分組。唯一必須的文件組是主文件組，主要由主數(shù)據(jù)文件和其他用戶定義的數(shù)據(jù)文件組成。主數(shù)據(jù)文件的作用是存儲針

7、對數(shù)據(jù)庫的所有系統(tǒng)引用，包括指向Resource數(shù)據(jù)庫中定義的對象的指針。如果作為默認文件組，則主文件組包含用戶定義的對象以及系統(tǒng)創(chuàng)建的對象的所有對象定義。除了主文件組意外，還可以創(chuàng)建更多的用戶定義文件組。使用用戶定義文件組的一個最大好處是控制，如果沒有用戶定義文件組，那么所有數(shù)據(jù)都會存儲在主文件組中，這樣數(shù)據(jù)庫的靈活性和可擴展性都大大削弱。輸入新文件組的名稱，選中“默認值”復選框，HYPERLINK然后單擊“確定”按鈕，這會把新的用戶定義的文件組設(shè)置為默認文件組，用戶創(chuàng)建的所有對象都會放在這個新的文件組中。這實際上就把系統(tǒng)數(shù)據(jù)和用戶數(shù)據(jù)分離開來，從而獲得對數(shù)據(jù)庫結(jié)構(gòu)的更多控制。使用文件組的一

8、個不錯的功能就是可以將該文件組中的數(shù)據(jù)標記為“只讀”，方法是選擇“新建文件組”對話框中的“只讀”復選框。當在一個數(shù)據(jù)庫中組織不同的對象時，這個選項相當有用，要改變的對象可以放在一個可更ixnd文件組中，而不會改變的對象放在一個只讀的文件組中，分離對象可以減少需要備份和還原的數(shù)據(jù)量，對于大型數(shù)據(jù)庫來說是一個很有用的選項。文件組帶來的性能優(yōu)勢主要有三方面，第一個方面是可以并行讀寫，這時通過將數(shù)據(jù)文件分割到多個物理設(shè)備實現(xiàn)的。然后，如果把多個物理我呢間放到單個文件組中，可以可獲得同樣的性能；第二個方面則是將非聚集索引和大型對象數(shù)據(jù)溢出作為常規(guī)數(shù)據(jù)空間而保留的文件組，將非聚集索引與數(shù)據(jù)分離可以讓數(shù)據(jù)庫

9、引擎同時使用獨立的線程從索引中搜搜行位置和從表中檢索行，將不經(jīng)常訪問的大型對象數(shù)據(jù)和事務(wù)密集的關(guān)系數(shù)據(jù)分離還可以提升一些實例中的掃描性能；第三個方面，也是最顯著的方面，是可以跨多個文件組物理分區(qū)大型表。選項在“新建數(shù)據(jù)庫”對話框中的左邊點擊“選項”按鈕，會看到各類組織級別的選項ANSINULL默認值：在CREATETABLE或ALTERTABLE操作中添加至表中的列是否允許空置，默認值為False，也就是除非有顯示指定，否則添加的列不允許空值，可以手動進行設(shè)置-更改默認值SETANSI_NULL_DELT_ONOFFSETANSI_NULL_DELT_ONON更改數(shù)據(jù)庫的選項值A(chǔ)LTERDAT

10、ABASEAdventureWorks2008SETANSI_NULL_DEFAULTOFFALTERDATABASEAdventureWorks2008SETANSI_NULL_DEFAULTONANSI警告已啟用：當設(shè)置為True時，只要聚合函數(shù)中出現(xiàn)空值，數(shù)據(jù)庫引擎就會發(fā)出警告，設(shè)定為False時，則不會發(fā)出警告，可以在連接級別或者數(shù)據(jù)庫級別設(shè)置SETANSI_WARNINGSOFFSETANSI_WARNINGSONALTERDATABASEAdventureWorks2008SETANSI_WARNINGSOFFALTERDATABASEAdventureworks2008SETAN

11、SI_WARNINGSON算術(shù)終止啟用：當設(shè)置為true的時候，任何語句或事務(wù)在遇到算術(shù)溢出或被零除錯誤后都將終止，設(shè)定為False的時候，會發(fā)出一個警告，但不是終止語句。同樣可以在連接級別或者數(shù)據(jù)庫級別設(shè)置：SETARITHABORTOFFSETARITHABORTONALTERDATABASEAdventureworks2008SETARITHABORTOFFALTERDATABASEAdventureworks2008SETARITHABORTON自動創(chuàng)建統(tǒng)計信息：設(shè)置為True的時候，數(shù)據(jù)庫引擎為那些JOIN操作的WHERE子句或ON子句中引用的、缺少統(tǒng)計信息的非索引列生成統(tǒng)計信息AL

12、TERDATABASEAdventureworks2008SETAUTO_CREATE_STATISTICSOFFALTERDATABASEAdventureworks2008SETAUTO_CREATE_STATISTICSON自動更新統(tǒng)計信息：設(shè)置為True時，數(shù)據(jù)庫引擎會自動更新列的統(tǒng)計信息，從而保持最有效的查詢計劃，如果設(shè)置為False則需要數(shù)據(jù)庫管理員手動更新統(tǒng)計信息ALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICSOFFALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATI

13、STICSON自動異步更新統(tǒng)計信息：設(shè)置為True時，查詢中發(fā)現(xiàn)的國企統(tǒng)計信息將被更新，但發(fā)現(xiàn)這些國企統(tǒng)計信息時正在執(zhí)行的查詢不會等待新的統(tǒng)計信息，后續(xù)的查詢將會利用新的統(tǒng)計信息，當設(shè)置為False的時候，只有統(tǒng)計信息更新之后才進行查詢編譯：ALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICS_ASYNCOFFALTERDATABASEAdventureworks2008SETAUTO_UPDATE_STATISTICS_ASYNCON默認游標：游標始終基于生命它的連接。當設(shè)置為Global時，制定了生命的游標可被統(tǒng)一連接上執(zhí)行的任意

14、批處理、存儲過程和觸發(fā)器引用。如果設(shè)置為Local，游標只能在聲明了它的批處理、存儲過程或觸發(fā)器中引用ALTERDATABASEAdventureworks2008SETCURSOR_DEFAULTLOCALALTERDATABASEAdventureworks2008SETCURSOR_DEFAULTGLOBAL驗證頁：驗證頁為數(shù)據(jù)庫管理員可以為寫頁設(shè)定不同的選項。可用的選項包括Checksun，Torn_Page_Detection和None。對于性能來說最好的選擇的None，但是此設(shè)置會使數(shù)據(jù)庫無法檢測到數(shù)據(jù)損壞。Checksun提供了最好的保證，無論是數(shù)據(jù)寫操作還是寫操作之后發(fā)生的對磁

15、盤數(shù)據(jù)的任意修改都會被校驗和驗證檢測到，但是會占用最多的CPU周期；Torn_Page_Detection選項是一種檢測損壞頁的低成本方法，但是只會檢測在寫操作期間發(fā)生的頁損壞，推薦設(shè)置是ChecksunALTERDATABASEAdventureworks2008SETPAGE_VERITYNONEALTERDATABASEAdventureworks2008SETPAGE_VERITYTORN_PAGE_DETECTIONALTERDATABASEAdventureworks2008SETPAGE_VERITYCHECKSUM參數(shù)化：默認情況下數(shù)據(jù)庫引擎將一些查詢參數(shù)化，這樣即使在WHER

16、E子句中定義了不同的值，創(chuàng)建并編譯的查詢計劃也可重用，如果設(shè)置為簡單，SQLServer會決定參數(shù)化哪些查詢和不參數(shù)化哪些查詢；設(shè)置為強制時，SQLServer會把所有可參數(shù)化的查詢參數(shù)化，而同樣的查詢會得到一個參數(shù)化查詢計劃。強制自動參數(shù)化優(yōu)勢能改善性能，但是需要仔細監(jiān)控以確保它對性能沒有負面影響ALTERDATABASEAdventureworks2008SETPARAMETERIZATIONSIMPLEALTERDATABASEAdventureworks2008SETPARAMETERIZATIONFORCED允許帶引號的標識符：默認情況下SQLServer使用方括號來界定對象，只有在

17、對象名中包含嵌入的空格或保留字時才需要界定對象。ANSI標準定界符是雙引號，下面的例子說明了如何使用方括號和雙引號創(chuàng)建和引用一個包含嵌入空格的對象：USERAdventureworks2008GOCREATETABLESales.USACustomers(AccuNumberintIDENTITY(1,1)NOTNULL,LastNamevarchar(75)NOTNULL,FirstNamevarchar(75)NOTNULL)SELECFTAcctNumber,LastName,FirstNameFROMSales.USACustomersUSERAdventureworks2008GOC

18、REATETABLESales.USACustomers(AccuNumberintIDENTITY(1,1)NOTNULL,LastNamevarchar(75)NOTNULL,FirstNamevarchar(75)NOTNULL)SELECFTAcctNumber,LastName,FirstNameFROMSales.USACustomers當“允許帶引號的標識符”為True的時候，方括號和雙引號都可以使用，為False的時候，值有方括號定界符可以使用，要在連接級別或者數(shù)據(jù)庫級別進行設(shè)置的時候如下SETQUOTED_IDENTIFIEROFFSETQUOTED_IDENTIFIERON

19、ALTERDATABASEAdventureworks2008SETQUOTED_IDENTIFIEROFFALTERDATABASEAdvetnureworks2008SETQUOTED_IDENTIFIERON遞歸觸發(fā)器已啟用：遞歸觸發(fā)器是一項高級編程技術(shù)，它允許統(tǒng)一觸發(fā)器在同一事務(wù)中按順序執(zhí)行多次。當設(shè)置為False是，這一操作時不允許的。ALTERDATABASEAdventureworks2008SETRECURSIVE_TRIGGERSOFFALTERDATABASEAdventureworks2008SETRECURSIVE_TRIGGERSON限制訪問：限制訪問選項使數(shù)據(jù)庫管理

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

21、RICTED_USERALTERDATABASEAdventureworks2008SETSINGLE_USER生成數(shù)據(jù)庫創(chuàng)建腳本幾乎每一個創(chuàng)建或修改數(shù)據(jù)庫對象的配置屏幕都包括腳本操作選項。單擊“腳本”按鈕可用的腳本操作選項會顯示，單擊任何腳本操作都將生成一個腳本，腳本復制您在圖形化界面中指定的所有設(shè)置。然后通過這個腳本，可以使用同樣的選項創(chuàng)建新的數(shù)據(jù)庫，值需要改變數(shù)據(jù)庫和相關(guān)文件的邏輯及物理名稱即可。另外一種重用腳本的方法是使用變量替代對象和文件的實際名稱，接下來只需要更新變量值并執(zhí)行腳本，創(chuàng)建數(shù)據(jù)定義語言DataDefinitionLanguageDDL腳本時唯一棘手的部分是必須使用動態(tài)S

22、QL，因為在DDL腳本中不能直接使用變量，下面的例子演示了如何使用動態(tài)SQL來創(chuàng)建一個新的數(shù)據(jù)庫，并將一個用戶定義的文件組標記為默認文件組：DECLAREDatabaseNameASnvarchar(255)DECLAREFileGroupNameASnvarchar(255)SETDatabaseName=NSlateGravelSETFileGroupName=NUserDataEXECUTE(CREATEDATABASE+DatabaseName+ONPRIMARY(NAME=+DatabaseName+,FILENAME=S:SQLDataFiles+DatabaseName+_dat

23、a.mdf,SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=30%),FILEGROUPUserData(NAME=+FileGroupName+,FILENAME=S:SQLDataFiles+DatabaseName+_data.ndf,SIZE=2048KB,FILEGROWTH=20%)LOGON(NAME=+dATABAEnAME+_log,FILENAME=T:SQLLogFiles+DatabaseName+_log.ldf,SIZE=100MB,FILEGROWTH=20%);ALTERDATABASE+DatabaseName+MODIFYFILEGRO

24、UP+FileGroupName+DEFAULT)架構(gòu)架構(gòu)是一種對象容器,在SQLServer2008中引用數(shù)據(jù)庫對象是，設(shè)定對象引用的上下文是很重要的。每個用于都被指派一個默認的架構(gòu)，當他們登錄到SQLServer應(yīng)引用數(shù)據(jù)庫對象時，這個默認架構(gòu)將在該對象的引用方式中發(fā)揮獨特的作用。例如，假設(shè)在AdventureWorks2008數(shù)據(jù)庫中創(chuàng)建了一個名為FredF的用戶，并把默認架構(gòu)Sales指派給他。如果FredF登錄并執(zhí)行SELECT*FROMCreditCard查詢，由于其默認架構(gòu)師Sales，CreditCard將被解析為Adventureworks2008.Sales.CreditC

25、ard。由于Sales.CreditCard表存在，因此查詢將返回該表的內(nèi)容，如果FredF執(zhí)行SELECT*FROMPerson查詢，Person將被解析為Adventureworks2008.Sales.Person，這是一個并不存在的表。因此，SQLServer會返回“無效的對象名稱”這個錯誤。要創(chuàng)建架構(gòu)唯一需要的信息是指定架構(gòu)名稱，架構(gòu)的所有權(quán)默認屬于運行創(chuàng)建腳本的用戶，但可以把任何有效的數(shù)據(jù)庫用戶指定為所有者，最簡單的方法就是把dbo指定為架構(gòu)所有者USERAdventureWorks2008GOCREATESCHEMAOperationsAUTHORIZATIONdbo該CREAT

26、ESCHEMA語句之后的任何架構(gòu)作用于的語句都將位于剛才創(chuàng)建的架構(gòu)的作用于內(nèi)。記住，架構(gòu)作用于總是起始于用戶的默認架構(gòu)，如果一個被引用的對象不是作用域限定的，解析將返回到dbo架構(gòu)如果視圖刪除一個包含對象的架構(gòu)，將會產(chǎn)生錯誤，如果仍然需要架構(gòu)中的對象，可以使用ALTERSCHEMA語句把它轉(zhuǎn)移到另一個架構(gòu)中，如把表DeliveryDriver從Operations架構(gòu)轉(zhuǎn)移到Production架構(gòu)中。ALTERSCHEMAProductionTRANSFEROperations.DeliveryDriver不能從數(shù)據(jù)庫中刪除擁有架構(gòu)的對象，這就是為什么讓dbo用戶擁有所有架構(gòu)的原因之一。要想改

27、變一個架構(gòu)的所有權(quán)，需要改變架構(gòu)的AUTHORIZATION屬性：ALTERAUTHORIZATIONONSCHEMA:OperationsTOFredF表表的排序規(guī)則：創(chuàng)建數(shù)據(jù)庫時，可以配置不同于服務(wù)器的排序規(guī)則支持。對于包含字符數(shù)據(jù)的表列來說也是如此，如創(chuàng)建一個各地客戶可以使用自己的語言瀏覽器和搜索產(chǎn)品目錄的表：USERAdventureWorks2008GOCREATETABLEProduction.GlobalProductDescription(ProductDescriptionIDintIDENTITY(1,1)NOTNULL,EnglishDescriptionnvarchar

28、(400)COLLATESQL_Latin1_General_CP1_CI_ASNULL,FrenchDescriptionnvarchar(400)COLLATEFrench_CI_ASNULL,ChineseDescriptionnvarchar(400)COLLATEChinese_PRC_CI_AINULL,ModifiedDatedatetimeNOTNULL)SQLServer絕對不是使用飯一起，只是提供了一個框架來存放多種語言。您需要準備產(chǎn)品描述的正確翻譯并把它們放到合適的列中，另外還要處理任何因為tempdb的排序規(guī)則導致的愛需規(guī)則不兼容性。表的體系結(jié)構(gòu)：SQLServer使用

29、8KB的數(shù)據(jù)頁春初信息，表中的所有數(shù)據(jù)都存儲在這些數(shù)據(jù)頁中，但數(shù)據(jù)在頁中的組織方式會因表的創(chuàng)建方式和在表創(chuàng)建后所進行的操作有所不同。默認情況下，所有的數(shù)據(jù)以稱為堆的無組織的方式進行存儲。SQLServer并不嘗試組織或是以任何方式排序數(shù)據(jù)，也不維護頁間的連接。SQLServer將所有數(shù)據(jù)頁存放在分區(qū)的邏輯單元中，除非明確分隔，否則表一般存儲在定義于單個文件組上的單個分區(qū)中。不過，通過跨多個由文件組定義管理的文件橫向分區(qū)表，SQLServer可以把大型表劃分為較小的可管理單元。如下創(chuàng)建實驗表并進行差序性能測試：-創(chuàng)建新表USERAdventureWorks2008GOCREATETABLEdbo

30、.Transactions(TransactionIDintNOTNULL,ProductIDintNOTNULL,ReferenceOrderIDintNOTNULL,ReferenceOrderLineIDintNOTNULL,TransactionDatedatetimeNOTNULL,TransactionTypenchar(1)NOTNULL,QuantityintNOTNULL,ActualCostmoneyNOTNULL,ModifiedDatedatetimeNOTNULL)-填充新表USEAdventureWorks2008GOINSERTdbo.TransactionsSE

31、LECT*FROMProduction.TransactionHistoryUNIONALLSELECT*FROMProduction.TransactionHistoryArchive-查看查詢性能USERAdventureWorks2008GODBCCDROPCLEANBUFFERSSETSTATISTICSIOONDECLAREBeginDateASdatetime,EndDateASdatetimeSETBeginDate=2002-01-01SETEndDate=2002-12-31SELECTSUM(Quantity)ASTotalQuantity,SUM(ActualCost)A

32、STotalCostFROMdbo.TransactionsWHERETransactionDateBETWEENBeginDateANDEndDate可以看到，為了滿足查詢要求，SQLServer不得不掃描表，現(xiàn)在對表進行分區(qū)，把該物理劃分為多個文件，使所有的事務(wù)按照年份分開，看看會發(fā)生什么情況：-創(chuàng)建四個文件組USERMASTERGOALTERDATABASEAdventureWorks2008ADDFILEGROUPFGPre2002GOALTERDATABASEAdventureWorks2008ADDFILE(NAME=AworksPre2002,FILENAME=E:SQLData

33、AworksPre2002.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFGPre2002GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2002GOALTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2002,FILENAME=E:SQLDataAworks2002.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFG2002GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2003GOA

34、LTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2003,FILENAME=E:SQLDataAworks2003.ndf,SIZE=20MB,FILEGROWTH=20%)TOFILEGROUPFG2003GOALTERDATABASEAdventureWorks2008ADDFILEGROUPFG2004AndAfterGOALTERDATABASEAdventureWorks2008ADDFILE(NAME=Aworks2004AndAfter,FILENAME=E:SQLDataAworks2004AndAfter.ndf,SIZE=2

35、0MB,FILEGROWTH=20%)TOFILEGROUPFG2004AndAfterGO-創(chuàng)建分區(qū)函數(shù)CREATEPARTITIONFUNCTIONYearFunction(datetime)ASRANGERIGHTFORVALUES(1/1/2002,1/1/2003,1/1/2004)-將YearFunction創(chuàng)建的分區(qū)映射到之前創(chuàng)建的文件組CREATEPARTITIONSCHEMEYearSchemeASPARTITIONYearFunctionTO(FGPre2002,FG2002,FG2003,FG2004AndAfter)-將數(shù)據(jù)從原Transactions表中移至分區(qū)表中U

36、SERAdventureWorks2008GOCREATETABLEdbo.PartitionedTransactions(TransactionIDintNOTNULL,ProductIDintNOTNULL,ReferenceOrderIDintNOTNULL,ReferenceOrderLineIDintNOTNULL,TransactionDatedatetimeNOTNULL,TransactionTypenchar(1)NOTNULL,QuantityintNOTNULL,ActualCostmoneyNOTNULL,ModifiedDatedatetimeNOTNULL)ONYe

37、arScheme(TransactionDate)GOINSERTINTOdbo.PartitionedTransactionsSELECT*FROMdbo.Transactions-查詢性能測試USERAdventureWorks2008GODBCCDROPCLEANBUFFERSSETSTATISTICSIOONDECLAREBeginDateASdatetime,EndDateASdatetimeSETBeginDate=2002-01-01SETEndDate=2002-12-31SELECTSUM(Quantity)ASTotalQuantity,SUM(ActualCost)AST

38、otalCostFROMdbo.PartitionedTransactionsWHERETransactionDateBETWEENBeginDateANDEndDate創(chuàng)建表分區(qū)還可以通過右擊表選擇存儲，選擇創(chuàng)建分區(qū)，通過GUI進行創(chuàng)建，這里略掉索引堆用來存儲數(shù)據(jù)很好，也能有效地處理新紀錄，但是要在表中尋找特定數(shù)據(jù)時就沒有那么好用了。這就是索引發(fā)揮作用的地方那個。SQLServer支持兩種類型的索引，聚集索引和非聚集索引。它還支持XML索引和空間索引等其他類型索引，這這些索引和普通索引的關(guān)系索引打不相同，后者將來用在數(shù)據(jù)庫表中定位大多數(shù)數(shù)據(jù)。聚集索引和非聚集索引之間的主要區(qū)別在于索引的葉級。

39、在非聚集索引中，葉級包含數(shù)據(jù)的指針。在聚集索引中，葉級包含實際數(shù)據(jù)。聚集索引：表的所有數(shù)據(jù)可存儲在堆中或聚集索引中，堆和聚集索引是相互排斥的。堆是一個無組織的表行集合，而聚集索引是一個有組織的表行集合。聚集鍵在索引中必須是唯一的，但創(chuàng)建索引時不必將此列標記為唯一的。當在未標記的唯一的列上創(chuàng)建聚集索引時，SQLServer會生成一個隱藏列，它保存一個名為唯一標識符的4字節(jié)內(nèi)部值來唯一標識重復的聚集索引鍵。聚集索引的葉級是實際的數(shù)據(jù)行，而不是數(shù)據(jù)指針。非聚集索引：非聚集索引更像是一本書后面的索引。當找到索引值時，并不能找到實際數(shù)行，只是獲得指定實際數(shù)據(jù)行的位置的指針。堆上的非聚集索引：當在一個以堆

40、形式組織的表上構(gòu)建非聚集索引時，索引列根據(jù)指向數(shù)據(jù)實際位置的指針分類，該指針由文件ID，頁ID和數(shù)據(jù)所在的頁槽號組成，如數(shù)據(jù)是第一個文件行總第84593頁上的第20條記錄，那么SQL將使用指針值1：84593：20，這使得SQLServer能夠在索引找到數(shù)據(jù)之后快速訪問數(shù)據(jù)。聚集索引上的非聚集索引：當在一個聚集索引上構(gòu)建非聚集索引時，索引中的指針值就是數(shù)據(jù)行的聚集索引鍵值。一旦定位到索引值，SQLServer就是用聚集鍵導航聚集索引來檢索所有需要的列。包含列：非聚集索引的功能可以通過向索引的節(jié)點添加非鍵值得到提升，這就可使得索引覆蓋更多的查詢，減少為檢索額外值而歷遍聚集索引的次數(shù)。包含列可以提

41、高查詢覆蓋率，而不會導致符合索引鍵的開銷。索引中標記為included的列值出現(xiàn)在索引的頁節(jié)點中，在行排序中不做考慮。CREATENONCLUSTEREDINDEXIX_Person_LastNameONPerson.Person(LastName)INCLUDE(FirstName)篩選索引：篩選索引時優(yōu)化非聚集索引，它允許在數(shù)據(jù)子集上創(chuàng)建索引，使得索引結(jié)構(gòu)更小從而減少了構(gòu)建索引的時間和索引維護的成本，對于包含大量NULL值或包含數(shù)據(jù)范圍的列上的索引，篩選索引特別有用：CREATENONCLUSTEREDINDEXIX_ListPrice_ProductONProduction.Produc

42、t(ListPrice)WHEREListPrice800.00分層索引：HierarchyId是SQLServer2008中引入的一種新類型數(shù)據(jù)。為了幫助檢索分層數(shù)據(jù)，可使用兩種不同的方法在此類型上的列構(gòu)建索引，廣度優(yōu)先索引和深度優(yōu)先索引。廣度優(yōu)先索引將統(tǒng)一級別的所有記錄組合到一起，這樣SQLServer就可以非?？焖俚叵鄳?yīng)具有共同父節(jié)點的所有記錄的查詢。空間索引：SQLServer2008通過兩種新的CLR數(shù)據(jù)類型支持空間索引，geometry和geography，geometry數(shù)據(jù)類型用于平面空間而geography數(shù)據(jù)類型用于地形測量空間。創(chuàng)建空間索引需要兩個階段：分解和鑲嵌。在分解

43、階段，SQLServer將一個有限區(qū)域劃分為一個網(wǎng)格結(jié)構(gòu)，在鑲嵌階段，表中的每個空間值都映射到每個網(wǎng)格級別。XML索引：憑借XML上建立索引的功能，能夠幫助定位和檢索XML文本中的特定數(shù)據(jù)。XML數(shù)據(jù)早SQLServer數(shù)據(jù)庫中存儲為BinaryLargeObject（BLOB，二進制大型對象）。要在XML中搜索特定元素，屬性或者值，SQLServer必須首先打開BLOB，然后將其內(nèi)容拆解開。SQLServer通過拆解操作創(chuàng)建一個自己可以導航的XML對象的集合。它實質(zhì)上是提取了XML的數(shù)據(jù)結(jié)構(gòu)，然后將其存儲在臨時的關(guān)系結(jié)構(gòu)中。XML主索引：主XML索引實際并不是構(gòu)建在XML列上，而是構(gòu)建在內(nèi)部

44、表（在索引創(chuàng)建過程中創(chuàng)建）之上的聚集索引。這個內(nèi)部表稱為節(jié)點表。節(jié)點表直接連接至在其中創(chuàng)建XML索引的表的聚集索引。要創(chuàng)建一個XML索引，具有XML列的表必須在其逐漸上有一個聚集索引。創(chuàng)建語法如下：USEAdventureWorks2008GOCREATEPRIMARYXMLINDEXXML_IX_IllustrationONProduction.Illustration(Diagram)PATH輔助XML索引：PATH輔助XML索引可以提升對XML列指定路徑表達式的XML查詢的性能。PATH輔助索引建立愛主XML索引提供的節(jié)點之上，語法如下：USERAdventureWorks2008GOC

45、REATEXMLINDEXIXML_MyPerson_AdditionalContactInfo_PathONdbo.MyPerson(AdditionalContactInfo)USINGXMLINDEXPXML_MyPerson_AdditionalContactInfoFORPATHVALUE輔助XML索引：VALUE輔助索引用來支持沒有完全指定路徑的或采用通配符來搜索值的XML查詢。語法如下：CREATEXMLINDEXIXML_MyPerson_AdditionalContactInfo_ValueONdbo.MyPerson(AdditionalContactInfo)USINGX

46、MLINDEXPXML_MyPerson_AdditionalContactInfoFORVALUEPROPERTY輔助XML索引：PROPERTY輔助索引用于優(yōu)化通過制定節(jié)點的完整路徑檢索節(jié)點值的查詢。語法如下：CREATEXMLINDEX_IXML_MyPerson_AdditionalContactInfo_PropertyONdbo.MyPerson(AdditionalContactInfo)USINGXMLINDEXPXML_MyPerson_AdditionalContactInfoFORPROPERTY維護表我們已經(jīng)深入了解了數(shù)據(jù)在表中的組織方式和優(yōu)化數(shù)據(jù)檢索的方法，接下來套路

47、如何對這個環(huán)境進行維護，表的維護基本可以分為兩種：索引的維護和索引統(tǒng)計信息的創(chuàng)建和維護索引在更新時會變得支離破碎，這是因為索引時一個連續(xù)的、排序的數(shù)據(jù)集合。要維護索引的排序順序，SQLServer必須分割完整的數(shù)據(jù)頁以容納更多的數(shù)據(jù)。這些也拆分導致產(chǎn)生了索引碎片，索引的碎片化將最終導致SQLServer在檢索數(shù)據(jù)時執(zhí)行過量的讀取，從而造成性能低下。要檢查表上的所有索引或一些特定索引的碎片，可以使用動態(tài)管理函數(shù)sys.dm_db_index_physical_stats，此函數(shù)會返回表的索引的大量信息，包括每個數(shù)據(jù)頁上的數(shù)據(jù)量、索引的葉級和非葉級上的碎片量，以及索引中記錄的平均大小。當擦和訊這個

48、表值函數(shù)時，最感興趣的是碎片級別和每一頁填充的平均百分比。SELECT*columnlistFROMsys.dm_db_index_physical_stats(databae_id|NULL,object_id|NULL,index_id|NULL,partition_number|NULL,mode|NULL|DEFAULT)DECLAREdbIDsmallint,objectIDintSETDbID=DB_ID(AdventureWorks2008)SETobjectID=OBJECT_ID(dbo.MyPersons)SELECTindex_id,avg_gragmentation_i

49、n_percent,avg_page_space_used_in_percentFROMsys.dm_db_index_physical_stats(DbID,ObjectID,NULL,NULL,DETAILED)WHEREindex_level=0為減少頁拆分產(chǎn)生的碎片，數(shù)據(jù)庫管理員可以設(shè)計或重新生成索引，使數(shù)據(jù)頁不會完全填充。為此，可以使用填充因此，當建立或重新生成索引時，可以指定填充因子百分比，如果一個索引頁只有90%被填充，那么只有插入更多的索引時才會導致頁拆分，雖因產(chǎn)生碎片的時間會久些。個人認為，索引的填充因子最好不要低于90%，在更新和查詢都很頻繁的表上，這個比例可以低至85%。

50、要去除碎片可以有三種方法：刪除并重新生成索引、原地重新生成索引或重新組織索引。-使用DROP_EXISTING選項創(chuàng)建索引CREATEUNIQUECLUSTEREDINDEXPK_Address_AddressIDONPerson.Address(AddressID)WITH(fillfactor=90,DROP_EXISTING=ON)-重新生成索引USEAdventureWorks2008GOALTERINDEXAK_Product_ProductNumberONPerson.ProductREBUILDWITH(FILLFACTOR=90,ONLINE=ON)USEAdventureWo

51、rks2008GOALTERINDEXALLONPerson.ProductREBUILDWITH(FILLFACTOR=90,ONLINE=ON)重新組織索引消耗的系統(tǒng)資源最少，但不如重新生成索引來的徹底。強制數(shù)據(jù)完整性主鍵約束：一個表有且只能有一個主鍵約束。它用來唯一標識表中的每一行。主鍵約束可定義在單個列或者列組合上。SQLServer使用方法是在參與主鍵的列上創(chuàng)建唯一的索引。在不排序的情況下來強制唯一性會非常低效。SQLServer這方面的問題是，如果聚集索引不存在，他會默認一個唯一的聚集索引。決定什么列參與主鍵和決定什么列定義表數(shù)據(jù)的物理結(jié)構(gòu)是完全不同的。不應(yīng)假定一個主鍵同時也是表的

52、聚集鍵。記住，表的所有聚集索引將包含聚集索引鍵作為指向數(shù)據(jù)行的指針。如果主鍵很長，使用聚集索引支持主鍵對于非聚集索引的存儲和檢索是非常不利的。USERAdventureWorks2008GOCREATETABLEdbo.CreditCards(CreditCardIDintIDENTITY(1,1)NOTNULLCONSTRAINTPK_CreditCardIDPRIMARYKEYNONCLUSTERED(CreditCardID),CardTypenvarchar(50)NOTNULL,CardNumbernvarchar(25)NOTNULL,ExpMonthtinyintNOTNULL,

53、ExpYearsmallintNOTNULL,ModifiedDatedatetimeNOTNULL)USERAdventureWorks2008GOCREATETABLEdbo.CreditCards(CreditCardIDintIDENTITY(1,1)NOTNULL,CardTypenvarchar(50)NOTNULL,CardNumbernvarchar(25)NOTNULL,ExpMonthtinyintNOTNULL,ExpYearsmallintNOTNULL,ModifiedDatedatetimeNOTNULL,CONSTRAINTPK_CreditCardIDPRIMA

54、RYKEYNONCLUSTERED(CreditCardID)ALTERTABLEdbo.CreditCardsADDCONSTRAINTPK_CreditCardIDPRIMARYKEYNONCLUSTERED(CreditCardID)唯一約束：雖然一個表只能有一個主鍵約束，但是它可以有很多唯一約束。要創(chuàng)建唯一約束，有兩個選擇，在表上創(chuàng)建唯一索引或唯一約束。唯一索引和唯一約束的行為相似，而SQLServer將創(chuàng)建唯一索引來強制唯一約束。ALTERTABLEdbo.DriverADDCONSTRAINTUX_LincenseNumUNIQUENONCLUSTERED(LicenseNum)C

55、REATEUNIQUENONCLUSTEREDINDEXINDEXUX_LincenseNumONdbo.Driver(LicenseNum)外鍵約束：外鍵約束用來保證表之間的完整性，要在表上創(chuàng)建一個外鍵約束，定義在外鍵中的列必須映射到主鍵表中的列，這些列被指定為主鍵或者有唯一約束（唯一約束和唯一索引都可以）。外鍵約束有一些高級選項，他們可以在創(chuàng)建中和創(chuàng)建后改變外鍵約束的行為。-WITHCHECK添加外鍵約束時的默認設(shè)置，設(shè)置指定了應(yīng)對外鍵表中任何現(xiàn)有數(shù)據(jù)進行驗證ALTERTABLEdbo.DriverRecordWITHCHECKADDCONSTRAINTFK_DriverRecord_Dr

56、iverFOREIGNKEY(DriverID)REFERENCESdbo.Driver(DriverID)-WITHNOCHECK，設(shè)定了部隊現(xiàn)有數(shù)據(jù)進行驗證來使其符合新的約束ALTERTABLEdbo.DriverRecordWITHNOCHECKADDCONSTRAINTFK_DriverRecord_DriverFOREIGNKEY(DriverID)REFERENCESdbo.Driver(DriverID)級聯(lián)約束：外鍵默認組織更新或刪除父值，然而有些時候這種行為并不合適。SQLServer提供了指定在福記錄被刪除或更新的情況下對子記錄采取何種行為的選項。ONDELETENOACTION和ONUPDATENOACTION是外鍵的默認設(shè)置。這些設(shè)定指定，任何試圖刪除或更新由其他表中現(xiàn)有行的外鍵所引用的鍵值的行為都會失敗。此外還有CASCADE,SETNULL和SETDEFAULT選項，他們允許刪除或更新鍵值，以定義的方式級聯(lián)至定義為擁有外鍵關(guān)系的表-ONDELETECASCADE如果父行被刪除，子記錄也刪除ALTERTABLEdbo.DriverRecordWITHNOCHECKADDCONSTRAINTFK_DriverRecord_DriverFOREIGNKEY(DriverID)REFERENCES