SQL Server表分區(qū)刪除詳情
一、引言
刪除分區(qū)又稱為合并分區(qū)，簡(jiǎn)單地講就是將多個(gè)分區(qū)的數(shù)據(jù)進(jìn)行合并?，F(xiàn)以表Sales.SalesOrderHeader作為示例，演示如何進(jìn)行表分區(qū)刪除。
重要的事情說(shuō)三遍：備份數(shù)據(jù)庫(kù)！備份數(shù)據(jù)庫(kù)！備份數(shù)據(jù)庫(kù)！
二、演示
2.1、數(shù)據(jù)查詢
2.1.1、 查看分區(qū)元數(shù)據(jù)
SELECT * FROM SYS.PARTITION_FUNCTIONS --分區(qū)函數(shù) SELECT * FROM SYS.PARTITION_RANGE_VALUES --分區(qū)方案
2.1.2、統(tǒng)計(jì)每個(gè)分區(qū)的數(shù)據(jù)量
SELECT $PARTITION.SalesOrderHeader_OrderDate(OrderDate) AS NUMBER,COUNT(1) AS COUNT FROM [Sales].[SalesOrderHeader] GROUP BY $PARTITION.SalesOrderHeader_OrderDate(OrderDate)
分區(qū)表中有數(shù)據(jù)時(shí)，是不能夠刪除分區(qū)方案和分區(qū)函數(shù)的，只能將數(shù)據(jù)先移到其它表中，再刪除。
2.2、刪除實(shí)操
2.2.1、合并原表分區(qū)
ALTER PARTITION FUNCTION SalesOrderHeader_OrderDate() MERGE RANGE('2011-01-01 00:00:00.000')
ALTER PARTITION FUNCTION SalesOrderHeader_OrderDate() MERGE RANGE('2012-01-01 00:00:00.000')
ALTER PARTITION FUNCTION SalesOrderHeader_OrderDate() MERGE RANGE('2013-01-01 00:00:00.000')
ALTER PARTITION FUNCTION SalesOrderHeader_OrderDate() MERGE RANGE('2014-01-01 00:00:00.000')
2.2.2、備份原表所有索引的創(chuàng)建腳本
ALTER TABLE [Sales].[SalesOrderHeader] ADD CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY NONCLUSTERED
(
[SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
2.2.3、刪除原表所有索引
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID]
2.2.4、創(chuàng)建臨時(shí)表
CREATE TABLE [Sales].[SalesOrderHeader_Temp](
[SalesOrderID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[RevisionNumber] [TINYINT] NOT NULL,
[OrderDate] [DATETIME] NOT NULL,
[DueDate] [DATETIME] NOT NULL,
[ShipDate] [DATETIME] NULL,
[Status] [TINYINT] NOT NULL,
[OnlineOrderFlag] [dbo].[Flag] NOT NULL,
[SalesOrderNumber] AS (ISNULL(N'SO'+CONVERT([NVARCHAR](23),[SalesOrderID]),N'*** ERROR ***')),
[PurchaseOrderNumber] [dbo].[OrderNumber] NULL,
[AccountNumber] [dbo].[AccountNumber] NULL,
[CustomerID] [INT] NOT NULL,
[SalesPersonID] [INT] NULL,
[TerritoryID] [INT] NULL,
[BillToAddressID] [INT] NOT NULL,
[ShipToAddressID] [INT] NOT NULL,
[ShipMethodID] [INT] NOT NULL,
[CreditCardID] [INT] NULL,
[CreditCardApprovalCode] [VARCHAR](15) NULL,
[CurrencyRateID] [INT] NULL,
[SubTotal] [MONEY] NOT NULL,
[TaxAmt] [MONEY] NOT NULL,
[Freight] [MONEY] NOT NULL,
[TotalDue] AS (ISNULL(([SubTotal]+[TaxAmt])+[Freight],(0))),
[Comment] [NVARCHAR](128) NULL,
[rowguid] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[ModifiedDate] [DATETIME] NOT NULL
)
2.2.5、更改原表數(shù)據(jù)空間類(lèi)型
1）對(duì)著原表Sales.SalesOrderHeader點(diǎn)擊"右鍵"->"設(shè)計(jì)"。
2）點(diǎn)擊菜單欄"視圖"->"屬性窗口"。
3）將數(shù)據(jù)空間類(lèi)型更改為"文件組"，常規(guī)數(shù)據(jù)空間規(guī)范默認(rèn)為"PRIMARY"。
2.2.6、移動(dòng)原表分區(qū)數(shù)據(jù)到臨時(shí)表
ALTER TABLE [Sales].[SalesOrderHeader] SWITCH PARTITION 1 TO [Sales].[SalesOrderHeader_Temp] PARTITION 1
2.2.7、創(chuàng)建原表所有索引到臨時(shí)表
ALTER TABLE [Sales].[SalesOrderHeader_Temp] ADD CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY NONCLUSTERED
(
[SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
2.2.8、刪除原表
DROP TABLE Sales.SalesOrderHeader
2.2.9、刪除分區(qū)方案和分區(qū)函數(shù)
DROP PARTITION SCHEME SalesOrderHeader_OrderDate DROP PARTITION FUNCTION SalesOrderHeader_OrderDate
2.2.10重命名表名
EXEC SP_RENAME '[Sales].[SalesOrderHeader_Temp]','SalesOrderHeader'
到此這篇關(guān)于SQL Server表分區(qū)刪除詳情的文章就介紹到這了,更多相關(guān)SQL Server表分區(qū)刪除內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信