PFSI20150101,
PFSI20150701,
PFSI20160101,
PFSI20160701,
PFSI20170101,
PFSI20170701,
PFSI20180101,
PFSI20180701,
PFSI20190101,
PFSI20190701,
PFSI20200101,
PFSI20200701,
PFSI20210101,
PFSI20210701,
PFSI20220101,
PFSI20220701,
PFSI20230101,
PFSI20230701,
PFSI20240101
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20150101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20150101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20150101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20150101;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20150701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20150701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20150701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20150701;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20160101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20160101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20160101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20160101;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20160701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20160701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20160701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20160701;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20170101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20170101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20170101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20170101;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20170701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20170701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20170701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20170701;
–创建分区文件组
alter database ESCPBASE add filegroup PFSI20180101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20180101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20180101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20180101;
alter database ESCPBASE add filegroup PFSI20180701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20180701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20180701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20180701;
alter database ESCPBASE add filegroup PFSI20190101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20190101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20190101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20190101;
alter database ESCPBASE add filegroup PFSI20190701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20190701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20190701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20190701;
alter database ESCPBASE add filegroup PFSI20200101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20200101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20200101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20200101;
alter database ESCPBASE add filegroup PFSI20200701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20200701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20200701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20200701;
alter database ESCPBASE add filegroup PFSI20210101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20210101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20210101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20210101;
alter database ESCPBASE add filegroup PFSI20210701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20210701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20210701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20210701;
alter database ESCPBASE add filegroup PFSI20220101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20220101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20220101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20220101;
alter database ESCPBASE add filegroup PFSI20220701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20220701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20220701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20220701;
alter database ESCPBASE add filegroup PFSI20230101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20230101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20230101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20230101;
alter database ESCPBASE add filegroup PFSI20230701
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20230701’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20230701.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20230701;
alter database ESCPBASE add filegroup PFSI20240101
–创建分区文件
alter database ESCPBASE
add file(name=‘PFSI20240101’
,filename=‘D:\MSSQL_DATA\DATA_MSSQLSERVER\ESCPBASE_PFSI20240101.ndf’
,size=1mb
,filegrowth=1mb)
to filegroup PFSI20240101;
create partition function F_CreateDate(datetime)
as range right for values
(‘2015-7-1’,
‘2016-1-1’,
‘2016-7-1’,
‘2017-1-1’,
‘2017-7-1’,
‘2018-1-1’,
‘2018-7-1’,
‘2019-1-1’,
‘2019-7-1’,
‘2020-1-1’,
‘2020-7-1’,
‘2021-1-1’,
‘2021-7-1’,
‘2022-1-1’,
‘2022-7-1’,
‘2023-1-1’,
‘2023-7-1’
)
create partition scheme S_CreateDate
as partition F_CreateDate to (
PFSI20150101,
PFSI20150701,
PFSI20160101,
PFSI20160701,
PFSI20170101,
PFSI20170701,
PFSI20180101,
PFSI20180701,
PFSI20190101,
PFSI20190701,
PFSI20200101,
PFSI20200701,
PFSI20210101,
PFSI20210701,
PFSI20220101,
PFSI20220701,
PFSI20230101,
PFSI20230701,
PFSI20240101
)
–删除聚集索引
DROP INDEX [CLI_FP_CreateDate] ON [dbo].[PublicFlowSerializeInfo20190326]
CREATE CLUSTERED INDEX [CLI_FP_CreateDate] ON [dbo].[PublicFlowSerializeInfo20190326]
(
[CreateDate]
) ON S_CreateDate
–创建复合非聚集索引
create nonclustered index Index_PFSI01
on PublicFlowSerializeInfo20190326(PublicFlowInstanceID,PublicNodeID)
create nonclustered index Index_PFSI02
on PublicFlowSerializeInfo20190326(PublicFlowInstanceID,PublicNodeID,PublicFlowWaitForID)
