目录一、引言二、演示2.1、数据查询2.1.1、 查看分区元数据2.1.2、统计每个分区的数据量2.2、删除实操2.2.1、合并原表分区2.2.2、备份原表所有索引的创建脚本2.2.
删除分区又称为合并分区,简单地讲就是将多个分区的数据进行合并。现以表Sales.SalesOrderHeader
作为示例,演示如何进行表分区删除。
SELECT * FROM SYS.PARTITioN_FUNCTIONS --分区函数
SELECT * FROM SYS.PARTITION_RANGE_VALUES --分区方案
SELECT $PARTITION.SalesOrderHeader_OrderDate(OrderDate) AS NUMBER,COUNT(1) AS COUNT
FROM [Sales].[SalesOrderHeader]
GROUP BY $PARTITION.SalesOrderHeader_OrderDate(OrderDate)
分区表中有数据时,是不能够删除分区方案和分区函数的,只能将数据先移到其它表中,再删除。
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)对着原表Sales.SalesOrderHeader
点击"右键"->"设计"。
2)点击菜单栏"视图"->"属性窗口"。
3)将数据空间类型更改为"文件组",常规数据空间规范默认为"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
表分区删除详情的文章就介绍到这了,更多相关sql Server
表分区删除内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
--结束END--
本文标题: SQL Server表分区删除详情
本文链接: https://www.lsjlt.com/news/154578.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-03-01
2024-03-01
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
2024-02-29
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0