您好,登錄后才能下訂單哦!
這篇文章主要介紹SQL Server如何進(jìn)行表分區(qū)刪除,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!
刪除分區(qū)又稱為合并分區(qū),簡(jiǎn)單地講就是將多個(gè)分區(qū)的數(shù)據(jù)進(jìn)行合并?,F(xiàn)以表Sales.SalesOrderHeader
作為示例,演示如何進(jìn)行表分區(qū)刪除。
重要的事情說三遍:備份數(shù)據(jù)庫!備份數(shù)據(jù)庫!備份數(shù)據(jù)庫!
SELECT * FROM SYS.PARTITION_FUNCTIONS --分區(qū)函數(shù) SELECT * FROM SYS.PARTITION_RANGE_VALUES --分區(qū)方案
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ù)先移到其它表中,再刪除。
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')
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]
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID]
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 )
1)對(duì)著原表Sales.SalesOrderHeader
點(diǎn)擊"右鍵"->"設(shè)計(jì)"。
2)點(diǎn)擊菜單欄"視圖"->"屬性窗口"。
3)將數(shù)據(jù)空間類型更改為"文件組",常規(guī)數(shù)據(jù)空間規(guī)范默認(rèn)為"PRIMARY"。
ALTER TABLE [Sales].[SalesOrderHeader] SWITCH PARTITION 1 TO [Sales].[SalesOrderHeader_Temp] PARTITION 1
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]
DROP TABLE Sales.SalesOrderHeader
DROP PARTITION SCHEME SalesOrderHeader_OrderDate DROP PARTITION FUNCTION SalesOrderHeader_OrderDate
EXEC SP_RENAME '[Sales].[SalesOrderHeader_Temp]','SalesOrderHeader'
以上是“SQL Server如何進(jìn)行表分區(qū)刪除”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對(duì)大家有幫助,更多相關(guān)知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。