SQLSERVER 表分區(qū)操作和設(shè)計(jì)方法
一 .聚集索引
聚集索引的頁(yè)級(jí)別包含了索引鍵，還包含數(shù)據(jù)頁(yè)，因此，關(guān)于 除了鍵值以外聚集索引的葉級(jí)別還存放了什么的答案就是一切，也就是說(shuō)，每行的所有字段都在葉級(jí)別種。
另一種說(shuō)話是:數(shù)據(jù)本身也是聚集索引的一部分，聚集索引基于鍵值保持表中的數(shù)據(jù)有序。
SQL SERVER 中，所有的聚集索引都是唯一的，如果在創(chuàng)建聚集索引時(shí)沒(méi)有指定UNIQUE 關(guān)鍵字，SQL SERVER 會(huì)在需要時(shí)通過(guò)往記錄中添加一個(gè)唯一標(biāo)識(shí)符(Uniqueifier)在內(nèi)部保證索引的唯一性，該唯一標(biāo)識(shí)符是一個(gè)4字節(jié)的值，作為附加在聚集索引鍵的字段添加到數(shù)據(jù)中，只有那些聲明為索引鍵字段并擁有重復(fù)值的行才會(huì)被添加。
二 .非聚集索引
對(duì)于非聚集索引，葉級(jí)別不包含全部的數(shù)據(jù)。除了鍵值以外，每個(gè)葉級(jí)別(樹的最低層)中的索引行包含了一個(gè)書簽(bookmark),告訴SQL Server 可以在哪里找到與索引鍵相應(yīng)的數(shù)據(jù)行。一個(gè)書簽課能有兩種格式。如果表上存在聚集索引，書簽就是相應(yīng)的數(shù)據(jù)行的聚集索引鍵。如果表是堆(heap)結(jié)構(gòu) ，就是沒(méi)有聚集索引的情況下 ，書簽就是一個(gè)行標(biāo)識(shí)符 row identifier,rid ，以 文件號(hào) 頁(yè)號(hào) 槽號(hào) 的格式來(lái)定位實(shí)際的行。
非聚集索引的存在與否并不影響數(shù)據(jù)分頁(yè)的組織，因此每張表上并不像聚集索引那樣只局限于擁有一個(gè)非聚集索引，SQL Server 2005 每張表能夠包含249 個(gè)非聚集索引 SQL Server 2008 每張表能夠包含999 個(gè)非聚集索引 ，但是實(shí)際上所用到的比這個(gè)數(shù)要少的多。
三 .包含索引
索引鍵字段數(shù)量限制是16個(gè)，總共900個(gè)字節(jié)大小 ，包含性列只在葉級(jí)別中出現(xiàn)而且不以任何方式控制索引行的排序。它們的目的是使葉級(jí)別能夠包含更多的信息從而更大地發(fā)揮覆蓋索引(Covering index)的索引調(diào)優(yōu)能力.覆蓋索引是一種非聚集索引，在其葉級(jí)別就可以找到滿足查詢的全部信息，這樣sql server就根本沒(méi)有必要訪問(wèn)數(shù)據(jù)分頁(yè)了，在一些情況下 sql serer 會(huì)悄悄的為索引添加一個(gè)包含性列。這可能發(fā)生在索引建立于分區(qū)表 也就是我今天是發(fā)的博客 O(∩_∩)O (partitioned table )上沒(méi)有指定 on filegroup 或者 no partition_scheme 的情況下。
一 .SQL SERVER 表分區(qū)介紹：
SQL Server 引入的表分區(qū)技術(shù)，讓用戶能夠把數(shù)據(jù)分散存放到不同的物理磁盤中，提高這些磁盤的并行處理性能以優(yōu)化查詢性能……
二 .SQL SERVER 數(shù)據(jù)庫(kù)表分區(qū)由三個(gè)步驟來(lái)完成:
1.創(chuàng)建分區(qū)函數(shù)
2.創(chuàng)建分區(qū)架構(gòu)
3.對(duì)表進(jìn)行分區(qū)
基于緩存更新機(jī)制，我使用時(shí)間來(lái)進(jìn)行分區(qū)，這里大家根據(jù)業(yè)務(wù)的要求使用合適的字段來(lái)作為分區(qū)
創(chuàng)建數(shù)據(jù)庫(kù)分區(qū)文件數(shù)量，這里存儲(chǔ)一年的數(shù)據(jù)分成十二個(gè)分區(qū)，需要現(xiàn)在D盤建立好Data 的文件夾 里面包含Primary 文件夾和 FG1 FG2 FG3 FG4............
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AirAvCache')
DROP DATABASE [AirAvCache]
GO
CREATE DATABASE [AirAvCache]
ON PRIMARY
(NAME='Data Partition DB Primary FG',
FILENAME=
'D:\Data\Primary\AirAvCache Primary FG.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG1]
(NAME = 'AirAvCache FG1',
FILENAME =
'D:\Data\FG1\AirAvCache FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG2]
(NAME = 'AirAvCache FG2',
FILENAME =
'D:\Data\FG2\AirAvCache FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG3]
(NAME = 'AirAvCache FG3',
FILENAME =
'D:\Data\FG3\AirAvCache FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG4]
(NAME = 'AirAvCache FG4',
FILENAME =
'D:\Data\FG4\AirAvCache FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG5]
(NAME = 'AirAvCache FG5',
FILENAME =
'D:\Data\FG5\AirAvCache FG5.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG6]
(NAME = 'AirAvCache FG6',
FILENAME =
'D:\Data\FG6\AirAvCache FG6.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG7]
(NAME = 'AirAvCache FG7',
FILENAME =
'D:\Data\FG7\AirAvCache FG7.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG8]
(NAME = 'AirAvCache FG8',
FILENAME =
'D:\Data\FG8\AirAvCache FG8.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG9]
(NAME = 'AirAvCache FG9',
FILENAME =
'D:\Data\FG9\AirAvCache FG9.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG10]
(NAME = 'AirAvCache FG10',
FILENAME =
'D:\Data\FG10\AirAvCache FG10.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG11]
(NAME = 'AirAvCache FG11',
FILENAME =
'D:\Data\FG11\AirAvCache FG11.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [AirAvCache FG12]
(NAME = 'AirAvCache FG12',
FILENAME =
'D:\Data\FG12\AirAvCache FG12.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
創(chuàng)建好后如圖：
打開FG1 文件夾 看到多了AirAvCacheFG1.ndf 文件
創(chuàng)建分區(qū)函數(shù)
代碼
USE AirAvCache
GO
-- 創(chuàng)建函數(shù)
CREATE PARTITION FUNCTION [AirAvCache Partition Range](DATETIME)
AS RANGE LEFT FOR VALUES ('2010-09-01','2010-10-01','2010-11-01','2010-12-01','2011-01-01','2011-02-01','2011-03-01','2011-04-01','2011-05-01','2011-06-01','2010-07-01');
創(chuàng)建分區(qū)架構(gòu)
代碼
CREATE PARTITION SCHEME [AirAvCache Partition Scheme]
AS PARTITION [AirAvCache Partition Range]
TO ([AirAvCache FG1], [AirAvCache FG2], [AirAvCache FG3],[AirAvCache FG4],[AirAvCache FG5],[AirAvCache FG6],[AirAvCache FG7],[AirAvCache FG8],
[AirAvCache FG9],[AirAvCache FG10],[AirAvCache FG11],[AirAvCache FG12]);
創(chuàng)建一個(gè)使用AirAvCache Partitiion Scheme 架構(gòu)的表
CREATE TABLE [dbo].[AvCache](
[CityPair] [varchar](6) NOT NULL,
[FlightNo] [varchar](10) NULL,
[FlightDate] [datetime] NOT NULL,
[CacheTime] [datetime] NOT NULL DEFAULT (getdate()),
[AVNote] [varchar](300) NULL
) ON [AirAvCache Partition Scheme] (FlightDate); --注意這里使用[AirAvCache Partition Scheme]架構(gòu)，根據(jù)FlightDate 分區(qū)
查詢分區(qū)情況
-- 查看使用情況
SELECT *, $PARTITION.[AirAvCache Partition Range](FlightDate)
FROM dbo.AVCache
可以看到9 月和 10 月已經(jīng)分開了。
相關(guān)文章
sum(case when then)(判斷男女生的個(gè)數(shù))
判斷類似一個(gè)班級(jí)的男生和女生的人數(shù)使用sum (cese when then)示例代碼如下，感興趣的朋友可以參考下2013-09-09
將MSSQL Server 導(dǎo)入/導(dǎo)出到遠(yuǎn)程服務(wù)器教程的圖文方法分享
如何將MSSQL Server 導(dǎo)入/導(dǎo)出到遠(yuǎn)程服務(wù)器教程，需要mssql數(shù)據(jù)庫(kù)備份的朋友用得到。2011-08-08
SQL語(yǔ)句 一個(gè)簡(jiǎn)單的字符串分割函數(shù)
SQL語(yǔ)句 一個(gè)簡(jiǎn)單的字符串分割函數(shù),需要的朋友可以參考下。2011-10-10
SQL Server中參數(shù)化SQL寫法遇到parameter sniff ，導(dǎo)致不合理執(zhí)行計(jì)劃重用的快速解決方法
這篇文章主要介紹了SQL Server中參數(shù)化SQL寫法遇到parameter sniff ，導(dǎo)致不合理執(zhí)行計(jì)劃重用的快速解決方法的相關(guān)資料,需要的朋友可以參考下2016-07-07
sql server使用公用表表達(dá)式CTE通過(guò)遞歸方式編寫通用函數(shù)自動(dòng)生成連續(xù)數(shù)字和日期
CTE是在內(nèi)存中準(zhǔn)備好數(shù)據(jù)，而不是每次一條往返服務(wù)器和客戶端一次。如果需要再插入到臨時(shí)表的話就是全部數(shù)據(jù)一次性插入。 這篇文章主要介紹了sql server使用公用表表達(dá)式CTE通過(guò)遞歸方式編寫通用函數(shù)自動(dòng)生成連續(xù)數(shù)字和日期 ,需要的朋友可以參考下2019-07-07
如何創(chuàng)建SQL Server 2000故障轉(zhuǎn)移群集
如何創(chuàng)建SQL Server 2000故障轉(zhuǎn)移群集...2007-01-01
SQL?Server數(shù)據(jù)庫(kù)創(chuàng)建表及其約束條件的操作方法
這篇文章主要介紹了SQL?Server?創(chuàng)建表及其約束條件，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-11-11
mysql 數(shù)據(jù)庫(kù)中索引原理分析說(shuō)明
實(shí)際上，您可以把索引理解為一種特殊的目錄。微軟的SQLSERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。2010-03-03
Sql Server 2000刪除數(shù)據(jù)庫(kù)備份文件
Sql Server 2000刪除數(shù)據(jù)庫(kù)備份文件的語(yǔ)句。2009-09-09
SQL Server中row_number分頁(yè)查詢的用法詳解
這篇文章主要介紹了SQL Server中row_number的用法詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07