当前位置：首页 > 数据库 > MSSQL教程 > 分区实战 > 正文

分区实战

来源：这里教程网 时间：2026-03-02 11:07:27 作者：

点击(此处)折叠或打开

    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



编辑推荐：

相关推荐

最新软件资讯

SQLSERVER数据库状态揭秘
SQLSERVER数据库状态揭秘

热文推荐

天极热推