Shulou(Shulou.com)06/03 Report--

This article mainly describes how SQL Server table partition deletion, the article is very detailed, has a certain reference value, interested friends must read!

I. Introduction

Deleting partitions is also known as merging partitions, which simply means merging data from multiple partitions. The table Sales.SalesOrderHeader is used as an example to demonstrate table partition deletion.

Important thing to say three times: backup database! Backup the database! Backup the database!

II. Demo 2.1. Data Query 2.1.1. Viewing Partition Metadata SELECT * FROM SYS.PARTITION_FUNCTIONS --Partition function SELECT * FROM SYS.PARTITION_RANGE_VALUES --Zoning schemes

2.1.2 SELECT $PARTITION.SalesOrderHeader_OrderDate(OrderDate) AS NUMBER,COUNT(1) AS COUNTFROM [Sales]. [SalesOrderHeader]GROUP BY $PARTITION.SalesOrderHeader_OrderDate(OrderDate)

When there is data in the partition table, the partition scheme and partition function cannot be deleted. The data can only be moved to other tables first and then deleted.

2.2. Delete the actual operation 2.2.1. Merge the original table partition 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. Create script for backing up all indexes of the original table 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. Delete all references in the original table ALTER TABLE [Sales]. [SalesOrderHeader] DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID]2.2.4. Create temporary table 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. Change the data space type of the original table

1) Click "Right"->"Design" for the original table Sales.SalesOrderHeader.

2) Click "View"->"Properties Window" in the menu bar.

3) Change the data space type to File Group, and the general data space specification defaults to PRIMARY.

2.2.6 ALTER TABLE [Sales]. [SalesOrderHeader] SWITCH PARTITION 1 TO [Sales]. [SalesOrderHeader_Temp] PARTITION 12.2.7, Create all indexes of the original table to the temporary table 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. Delete the original table DROP TABLE Sales.SalesOrderHeader2.2.9. Delete partition scheme and partition function DROP PARTITION SCHEME SalesOrderHeader_OrderDateDROP PARTITION FUNCTION SalesOrderHeader_OrderDate2.2.10 Rename table name EXEC SP_RENAME '[Sales]. [SalesOrderHeader_Temp]','SalesOrderHeader'The above is "SQL Server how to delete table partitions" All the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.