5009-SQLServer 分库分表操作指引

echo 按任意键开始创建数据库FileGroup文件夹（请根据实际情况修改路径，5.83应为E盘）
pause
echo --创建基本资料表FileGroup文件夹
md D:\SQLServerData\MStore\FG_BaseTable
echo --创建促销数据FileGroup文件夹
md D:\SQLServerData\MStore\FG_Promotion
echo --创建商品单据FileGroup文件夹
md D:\SQLServerData\MStore\FG_StoreBill
echo --创建促销数据商品资料文件夹
md D:\SQLServerData\MStore\FG_StoreGoods
echo --创建销售单据FileGroup文件夹
md D:\SQLServerData\MStore\FG_VendorBill
pause
echo 创建完成。
pause

USE MStore
ALTER DATABASE MStore ADD FILEGROUP FG_BaseTable 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_01', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_02', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_BaseTable_03', FILENAME = N'D:\SQLServerData\FG_BaseTable\FG_BaseTable_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_BaseTable

ALTER DATABASE MStore ADD FILEGROUP FG_Promotion 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_01', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_02', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_Promotion_03', FILENAME = N'D:\SQLServerData\FG_Promotion\FG_Promotion_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_Promotion

ALTER DATABASE MStore ADD FILEGROUP FG_StoreBill 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_01', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_02', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreBill_03', FILENAME = N'D:\SQLServerData\FG_StoreBill\FG_StoreBill_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreBill

ALTER DATABASE MStore ADD FILEGROUP FG_StoreGoods 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_01', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_02', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_StoreGoods_03', FILENAME = N'D:\SQLServerData\FG_StoreGoods\FG_StoreGoods_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_StoreGoods

ALTER DATABASE MStore ADD FILEGROUP FG_VendorBill 
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_01', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_01.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_02', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_02.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill
ALTER DATABASE MStore ADD FILE ( NAME = N'FG_VendorBill_03', FILENAME = N'D:\SQLServerData\FG_VendorBill\FG_VendorBill_03.ndf' , SIZE = 65536KB , MAXSIZE = 51200000KB , FILEGROWTH = 204800KB ) TO FILEGROUP FG_VendorBill

--创建分区函数
CREATE PARTITION FUNCTION [pf_dc_createtime](datetime) AS RANGE LEFT FOR VALUES (N'2016-01-01T00:00:00.000', N'2019-01-01T00:00:00.000', N'2019-02-01T00:00:00.000', 
N'2019-03-01T00:00:00.000', N'2019-04-01T00:00:00.000', N'2019-05-01T00:00:00.000', N'2019-06-01T00:00:00.000', 
N'2019-07-01T00:00:00.000', N'2019-08-01T00:00:00.000', N'2019-09-01T00:00:00.000', N'2019-10-01T00:00:00.000')
--创建分区方案
CREATE PARTITION SCHEME [ps_fg_baseTable_dc_createtime] AS PARTITION [pf_dc_createtime] ALL TO (FG_BaseTable)

CREATE TABLE [dbo].[goods](
[Pid] UNIQUEIDENTIFIER,
[CompanyCode] VARCHAR(50),
[GID] BIGINT,
[Code] VARCHAR(20),
[Name] VARCHAR(50),
[SPEC] VARCHAR(50),
[Sort] VARCHAR(50),
[Brand] VARCHAR(50),
[Code2] VARCHAR(50),
[GoodsStatus] VARCHAR(50),
[GoodsStatusName] VARCHAR(50),
[QPC] NUMERIC(18,2),
[QPCStr] NVARCHAR(100),
[Munit] VARCHAR(50),
[SALE] VARCHAR(50),
[SALETAX] NUMERIC(18,4),
[TaxRate] NUMERIC(18,4),
[Alc] VARCHAR(50),
[AlcQty] INT,
[SALCQSTART] INT,
[BCKSTORE] VARCHAR(100),
[BCKVEN] VARCHAR(100),
[INPRC] NUMERIC(18,4),
[DXPRC] NUMERIC(18,4),
[PAYRATE] MONEY,
[CNTINPRC] NUMERIC(18,4),
[LSTINPRC] NUMERIC(18,4),
[INVPRC] NUMERIC(18,4),
[distributionprc] NUMERIC(18,4),
[WHSPrc] NUMERIC(18,4),
[OLDINVPRC] NUMERIC(18,4),
[RTLPRC] NUMERIC(18,4),
[LWTRTLPRC] NUMERIC(18,4),
[TOPRTLPRC] NUMERIC(18,4),
[CHKVD] SMALLINT,
[MBRPRC] NUMERIC(18,4),
[BillTo] INT,
[isltd] INT,
[isBind] INT,
[ISPKG] INT,
[ISUnit] INT,
[BaseGoodsType] INT,
[ORDERQTY] INT,
[wrh] INT,
[Status] INT,
[Memo] VARCHAR(100),
[PROMOTE] SMALLINT,
[GFT] SMALLINT,
[GoodsAccountType] INT,
[salcqty] INT,
[AddTime] DATETIME,
[AddUser] VARCHAR(50),
[AddUserName] VARCHAR(50),
[AddIP] VARCHAR(50),
[UpdateTime] DATETIME,
[UpdateUser] VARCHAR(50),
[UpdateUserName] VARCHAR(50),
[UpdateIP] VARCHAR(50),
[DataStatus] SMALLINT,
[SSStart] DATETIME,
[SSEnd] DATETIME,
[FCATEGORY] VARCHAR(20),
[wd1] NVARCHAR(510),
[wd2] NVARCHAR(510),
[wdname1] NVARCHAR(510),
[wdname2] NVARCHAR(510),
[GRADE] NVARCHAR(510),
[VERSION] INT,
[dc_createtime] [datetime] NOT NULL default getdate()
CONSTRAINT [PK_goods] PRIMARY KEY NONCLUSTERED
(
[Pid] ASC,
[dc_createtime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [ps_fg_Basetable_dc_createtime]([dc_createtime])
) ON [ps_fg_Basetable_dc_createtime]([dc_createtime])
GO

CREATE INDEX index_goods ON [goods]([dc_createtime])