sqlserver数据表（计算列）表分区

概述--分区函数： [myPartFunc]CREATE TABLE [Calculate]( [id] [int] identity(1,1) , [name] [varchar](20) NULL, [val] [int] NULL, [cal] AS ([val]-(1)) PERSISTED)/*添加主键*//* 添加测试数据*/

     --分区函数： [myPartFunc]CREATE table [Calculate](	[ID] [int] IDentity(1,1),[name] [varchar](20) NulL,[val] [int] NulL,[cal]  AS ([val]-(1)) PERSISTED)/*添加主键*//*	添加测试数据*/declare @rowCount intset @rowCount=1while @rowCount<4000001begin	insert into [Calculate]([name],[val])		select 'name_'+CAST(@rowCount as varchar(10)) as [name],@rowCount	set @rowCount=@rowCount+1end/*1 建立分区函数*/create partition function myCalPartFunc(int)  as range left   for values(        1000000,2000000,3000000,4000000,5000000)     ;/*2.1建立分区文件组*/ALTER DATABASE Calculate ADD fileGROUP [party1000000]    ALTER DATABASE Calculate ADD fileGROUP [party2000000] ALTER DATABASE Calculate ADD fileGROUP [party3000000] ALTER DATABASE Calculate ADD fileGROUP [party4000000] ALTER DATABASE Calculate ADD fileGROUP [party5000000] ;/*2.2建立文件组地址*/ALTER DATABASE Calculate    ADD file     (name = N'[party1000000]',filename = N'D:\sqlserverTest\分区测试\Calculate\party1.ndf',SIZE = 5MB,fileGROWTH = 5MB)    TO fileGROUP [party1000000]ALTER DATABASE Calculate    ADD file     (name = N'[party2000000]',filename = N'D:\sqlserverTest\分区测试\Calculate\party2.ndf',fileGROWTH = 5MB)    TO fileGROUP [party2000000]ALTER DATABASE Calculate    ADD file     (name = N'[party3000000]',filename = N'D:\sqlserverTest\分区测试\Calculate\party3.ndf',fileGROWTH = 5MB)    TO fileGROUP [party3000000]ALTER DATABASE Calculate    ADD file     (name = N'[party4000000]',filename = N'D:\sqlserverTest\分区测试\Calculate\party4.ndf',fileGROWTH = 5MB)    TO fileGROUP [party4000000]ALTER DATABASE Calculate    ADD file     (name = N'[party5000000]',filename = N'D:\sqlserverTest\分区测试\Calculate\party5.ndf',fileGROWTH = 5MB)    TO fileGROUP [party5000000];/*3.0 建立分区架构*/CREATE PARTITION SCHEME [myPartFunc]    AS    PARTITION myCalPartFunc TO    (   [party1000000],[party2000000],[party3000000],[party4000000],[party5000000],[PRIMARY] )    GO  ;/*4.0 对已存在的表添加表分区函数 进行数据分区*/select  COUNT(0) ID from dbo.Calculate--直接添加表分区 不用设置聚集主键--创建一个新的聚集索引，在该聚集索引中使用分区方案 /*	4.1 适用于 无主键*/--CREATE CLUSTERED INDEX index_CalCu ON Calculate([cal]) ON myPartFunc([cal])--/*   4.2适用于 有主键 同时主键为 聚集索引的表*//*ALTER table [Calculate]    ADD CONSTRAINT PK_Calculate       PRIMARY KEY CLUSTERED(ID)  ALTER table Calculate    DROP CONSTRAINT PK_Calculate     WITH(MOVE TO myPartFunc(Cal))     */        --分区完成以后 数据为185M/*新建一个表 使用相同的 分区函数*/create table SomeCalculate(	[ID] [int] IDentity(1,[cal]  AS ([val]-(1)) PERSISTED);declare @rowCount intset @rowCount=1while @rowCount<4000001begin	insert into SomeCalculate([name],@rowCount	set @rowCount=@rowCount+1endCREATE CLUSTERED INDEX index_CalCu ON SomeCalculate([cal]) ON myPartFunc([cal])--第二次使用相同函数分区以后 340M

  总结

以上是内存溢出为你收集整理的sqlserver数据表（计算列）表分区全部内容，希望文章能够帮你解决sqlserver数据表（计算列）表分区所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。