SQL 千万级大数据，按年份分表并删除原表数据
原有数据量主表38w，子表1200w
最终分表成为主表为当年数据，将2015,2016分表存储
--下面分表过程
--先查订单主表的是否有外键约束，
selecta.name as 约束名,
object_name(b.parent_object_id) as 外键表,
d.name as 外键列,
object_name(b.referenced_object_id) as 主健表,
c.name as 主键列
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id
inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id
where object_name(B.referenced_object_id)='Hishop_Orders';
--删除约束
ALTER TABLE dbo.Hishop_OrderGifts DROP CONSTRAINT FK_Hishop_OrderGifts_Orders
ALTER TABLE dbo.Hishop_OrderItems DROP CONSTRAINT FK_Hishop_OrderItems_Orders
ALTER TABLE dbo.Hishop_OrderRefund DROP CONSTRAINT FK_Hishop_OrderRefund_Orders
ALTER TABLE dbo.Hishop_OrderReplace DROP CONSTRAINT FK_Hishop_OrderReplace_Orders
ALTER TABLE dbo.Hishop_OrderSendNote DROP CONSTRAINT FK_Hishop_OrderSendNote_Orders
按年份整表架构copy 并数据copy （经过测试速度快）
select * into Hishop_Orders_2016 from Hishop_Orders where hisyear='2016
--建立索引
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index2] ON [dbo].[Hishop_Orders_2016]
([PaymentTypeId] ASC)
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index3] ON [dbo].[Hishop_Orders_2016]
([Username] ASC)
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index4] ON [dbo].[Hishop_Orders_2016]
([UserId] ASC)
--建立主键
alter table Hishop_Orders_2016 add constraint [Hishop_Orders_Index_2016] primary key(orderID)
执行历史删除（因主订单表数据转存）需要将主表数据删除，大数据如何删除
因建立了索引问题，先删除索引在删数据 在建立索引 这样删除 800W数据 16分钟
先删除索引，主键不删
DROP INDEX [Hishop_Orders_Index5] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_ORDERID_OrderItems] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_ORDERID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_SKU] ON [dbo].[Hishop_OrderItems]
DROP INDEX [IDX_SKUID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
DROP INDEX [idxHishop_OrderItems_GuidId] ON [dbo].[Hishop_OrderItems] WITH ( ONLINE = OFF )
DROP INDEX [idxHishop_OrderItems_OrderIdExt] ON [dbo].[Hishop_OrderItems]
DROP INDEX [PK_Hishop_OrderItemsMain] ON [dbo].[Hishop_OrderItems]
在按年份删除只留当年数据
delete [dbo].[Hishop_OrderItems] where HisYear=2016
delete [dbo].[Hishop_OrderItems] where HisYear=2015
在把索引建立回来
CREATE NONCLUSTERED INDEX [Hishop_Orders_Index5] ON [dbo].[Hishop_OrderItems]([ProductId] ASC)
CREATE NONCLUSTERED INDEX [IDX_ORDERID_OrderItems] ON [dbo].[Hishop_OrderItems]([OrderId] ASC)
CREATE NONCLUSTERED INDEX [IDX_ORDERID_PRODUCTID] ON [dbo].[Hishop_OrderItems]
([OrderId] ASC,[ProductId] ASC,[SkuId] ASC,[SKU] ASC)
CREATE NONCLUSTERED INDEX [IDX_SKU] ON [dbo].[Hishop_OrderItems]([SKU] ASC)
CREATE NONCLUSTERED INDEX [IDX_SKUID_PRODUCTID] ON [dbo].[Hishop_OrderItems]([SkuId] ASC,[ProductId] ASC)
CREATE CLUSTERED INDEX [idxHishop_OrderItems_GuidId] ON [dbo].[Hishop_OrderItems]([GuidId] ASC)
CREATE NONCLUSTERED INDEX [idxHishop_OrderItems_OrderIdExt] ON [dbo].[Hishop_OrderItems]([OrderIdExt] ASC)
CREATE NONCLUSTERED INDEX [PK_Hishop_OrderItemsMain] ON [dbo].[Hishop_OrderItems]([MainOrderId] ASC)
分表完成