伊人看片,日本不卡视频网站,99国产福利

--查詢一張表的分區

      
        select
      
       $partition.Part_func_UserLog(createdDate) 
      
        as
      
       Patition 
      
        from
      
       dbo.UserLog GROUP BY $partition.Part_func_UserLog(createdDate)

--創建分區的時間段

      
        CREATE PARTITION FUNCTION Part_func_UserLog(datetime)

 AS RANGE LEFT FOR VALUES 

(


      
      
        '
      
      
        20120709 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120710 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120711 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120712 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120713 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120714 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120715 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120716 23:59:59.997
      
      
        '
      
      
        , 


      
      
        '
      
      
        20120717 23:59:59.997
      
      
        '
      
      
        ,


      
      
        '
      
      
        20120718 23:59:59.997
      
      
        '
      
      
        

);

--創建分區分組

      ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks1];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks2];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks3];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks4];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks5];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks6];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks7];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks8];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks9];

GO

ALTER DATABASE GoodBooks

ADD FILEGROUP [FGGoodBooks10];

--創建分區文件

      
        ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks1_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks1_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks1];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks2_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks2_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks2];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks3_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks3_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks3];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks4_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks4_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks4];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks5_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks5_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks5];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks6_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks6_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks6];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks7_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks7_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks7];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks8_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks8_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks8];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks9_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks9_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks9];

ALTER DATABASE GoodBooks

ADD FILE

(NAME
      
      =FGGoodBooks10_data,FILENAME=
      
        '
      
      
        E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FGGoodBooks10_data.ndf
      
      
        '
      
      ,SIZE=
      
        3MB)

TO FILEGROUP [FGGoodBooks10];

--綁定分組

      CREATE PARTITION SCHEME Part_func_UserLog_Scheme

AS PARTITION Part_func_UserLog

TO ([FGGoodBooks1],[FGGoodBooks2],[FGGoodBooks3],[FGGoodBooks4],[FGGoodBooks5],[FGGoodBooks6],[FGGoodBooks7],[FGGoodBooks8],[FGGoodBooks9],[FGGoodBooks10],[Primary]);

GO

--給表添加主鍵

      ALTER TABLE dbo.UserLog ADD CONSTRAINT PK_UserLog PRIMARY KEY NONCLUSTERED  (UserLogID)

   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_UserLog_partitioncol ON dbo.UserLog (UserLogID)

  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

  ON Part_func_UserLog_Scheme(CreatedDate)

GO

--刪除表的主鍵、聚集索引

      alter table UserLog drop CONSTRAINT PK_UserLog

--查詢指定分區的數據

      select *  from dbo.UserLog WHERE $PARTITION.Part_func_UserLog(CreatedDate)=3

--自動添加分區

      declare  @databaseName varchar(50),  @fileGroupName varchar(50),  @ndfName varchar(50),  @newNameStr varchar(50),  @fullPath 

varchar(500),  @newDay varchar(50),  @oldDay datetime,  @partFunName varchar(50),  @schemeName varchar(50)

set @databaseName='GoodBooks'

set @newDay=CONVERT(varchar(100), getdate(), 23)--23:按天 114:按時間

set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-5,getdate()), 120 ) as datetime)

set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')

set @fileGroupName=N'G'+@newNameStr

set @ndfName=N'F'+@newNameStr+''

set @fullPath=N'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@ndfName+'.ndf'

set @partFunName=N'Part_func_UserLog'

set @schemeName=N'Part_func_UserLog_Scheme'

--創建文件組

if exists(select * from sys.filegroups where name=@fileGroupName)

begin

	print '文件組存在,不需添加'

end

else

begin

	exec('ALTER DATABASE '+@databaseName+' ADD FILEGROUP ['+@fileGroupName+']')

	print '新增文件組'

	if exists(select * from sys.partition_schemes where name =@schemeName)

	begin

		exec('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')

		print '修改分區方案'

	end

	if exists(select * from sys.partition_range_values where function_id=(select function_id from 

	sys.partition_functions where name =@partFunName) and value=@oldDay)

	begin

		exec('alter partition function  '+@partFunName+'() split range('''+@newDay+''')')

		print '修改分區函數'

	end

end

--創建NDF文件

if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))

begin

	print 'ndf文件存在,不需添加'

end

else

begin

	exec('ALTER DATABASE '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')

	PRINT 'ALTER DATABASE '+@databaseName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'

	print '新創建ndf文件'

end

/*--------------------以上創建數據庫的文件組和物理文件------------------------*/

--分區函數

if exists(select * from sys.partition_functions where name =@partFunName)

begin

	print '此處修改需要在修改分區函數之前執行'

end

else

begin

	exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')')

	print '新創建分區函數'

end

--分區方案

if exists(select * from sys.partition_schemes where name =@schemeName)

begin

	print '此處修改需要在修改分區方案之前執行'

end

else

	begin

	exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')

	print '新創建分區方案'

end

print '---------------以下是變量定義值顯示---------------------'

print '當前數據庫:'+@databaseName

print '當前日期:'+@newDay+'(用作隨機生成的各種名稱和分區界限)'

print '合法命名方式:'+@newNameStr

print '文件組名稱:'+@fileGroupName

print 'ndf物理文件名稱:'+@ndfName

print '物理文件完整路徑:'+@fullPath

print '分區函數:'+@partFunName

print '分區方案:'+@schemeName

/*

--查看創建的分區函數

select * from sys.partition_functions

--查看分區函數的臨界值

select * from sys.partition_range_values

--查詢分區方案

select * from sys.partition_schemes

--查詢表數據在哪個分區中存儲,where條件查詢第一個分區中存在的數據

select *,$partition.pf_SaveTime(分區字段) as Patition from 表名 where $partition.pf_SaveTime(分區字段)=1

*/

GO

數據庫分區實踐

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義