大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说sql删除分区数据_sqlserver分组排序取最大一条,希望您对编程的造诣更进一步.
一、引言
删除分区又称为合并分区，简单地讲就是将多个分区的数据进行合并。现以表Sales.SalesOrderHeader作为示例，演示如何进行表分区删除。
重要的事情说三遍：备份数据库！备份数据库！备份数据库！
二、演示
2.1、数据查询
1）查看分区元数据
SELECT * FROM SYS.PARTITION_FUNCTIONS --分区函数 SELECT * FROM SYS.PARTITION_RANGE_VALUES --分区方案
2）统计每个分区的数据量
SELECT $PARTITION.SalesOrderHeader_OrderDate(OrderDate) AS NUMBER,COUNT(1) AS COUNT FROM [Sales].[SalesOrderHeader] GROUP BY $PARTITION.SalesOrderHeader_OrderDate(OrderDate)
分区表中有数据时，是不能够删除分区方案和分区函数的，只能将数据先移到其它表中，再删除。
2.2、删除实操
2.2.1、合并原表分区
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、备份原表所有索引的创建脚本
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、创建临时表
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、更改原表数据空间类型
1）对着原表Sales.SalesOrderHeader点击”右键”->”设计”。
2）点击菜单栏”视图”->”属性窗口”。
3）将数据空间类型更改为”文件组”，常规数据空间规范默认为”PRIMARY”。
2.2.6、移动原表分区数据到临时表
ALTER TABLE [Sales].[SalesOrderHeader] SWITCH PARTITION 1 TO [Sales].[SalesOrderHeader_Temp] PARTITION 1
2.2.7、创建原表所有索引到临时表
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、删除分区方案和分区函数
DROP PARTITION SCHEME SalesOrderHeader_OrderDate DROP PARTITION FUNCTION SalesOrderHeader_OrderDate
2.2.10重命名表名
EXEC SP_RENAME "[Sales].[SalesOrderHeader_Temp]","SalesOrderHeader"
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5812.html
