点击(此处)折叠或打开

USE [master]
RESTORE DATABASE [news_par] FROM DISK = N'F:\SQLBak\news40.bak' WITH FILE = 1,
MOVE N'news' TO N'F:\SQLData\news_par.mdf', MOVE N'news_log' TO N'F:\SQLLog\news_par_log.ldf', NOUNLOAD, REPLACE, STATS = 5
GO
use news_par
go
news_par.dbo.sp_maintain_partion 'news_par','2013-01-01'
declare @dd datetime,
@dbname varchar(100) --需要分区的库名
set @dbname='news_par'
set @dd='2013-01-01'
set @dd=DATEADD(mm, DATEDIFF(mm,0,@dd), 0) --取当月第一天
while @dd<=DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)
begin
--print @dd
exec sp_maintain_partion @dbname,@dd
--print ('[DYDB].[dbo].[sp_maintain_partion]'+''''+'DYDB'+''''+','+ ''''+CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@dd), 0), 23 )+'''')
set @dd=DATEADD(mm, DATEDIFF(mm,0,@dd)+1, 0)
end
select * into news_industry_par from news_industry
select distinct create_time from news_industry where create_time>='2017/1/1 0:00:00'
select count (1), $partition.pf_time(create_time) from news_industry group by $partition.pf_time(create_time)
select distinct create_time from news_industry where $partition.pf_time(create_time)=$partition.pf_time( '2017/1/1 0:00:00')

点击(此处)折叠或打开

select distinct create_time from news_industry where create_time>='2017/1/1 0:00:00'
select count (1), $partition.pf_time(create_time) from news_industry group by $partition.pf_time(create_time)
select distinct create_time from news_industry where $partition.pf_time(create_time)=$partition.pf_time( '2017/1/1 0:00:00')

点击(此处)折叠或打开

USE [news_par]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[news_industry_par] DROP CONSTRAINT [PK__news_ind__3214EC2707C4AFD4]
ALTER TABLE [dbo].[news_industry_par] ADD PRIMARY KEY NONCLUSTERED
(
[ID] 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]
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_Time_636205266173245553] ON [dbo].[news_industry_par]
(
[CREATE_TIME]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_Time]([CREATE_TIME])
DROP INDEX [ClusteredIndex_on_ps_Time_636205266173245553] ON [dbo].[news_industry_par]
COMMIT TRANSACTION

ITPub博客

分区实战