专业网站建设品牌，十四年专业建站经验，服务6000+客户--广州京杭网络

免费热线：400-683-0016      微信咨询  |  联系我们

• 网站首页
• 网站建设
• 短视频
• 网站推广
• 网站模板
• 客户案例
• 资讯动态
• 关于我们

亿万级SqlServer数据优化

当前位置：网站建设 > 技术支持

--创建分区文件组

alter database seclab_sgk_db add filegroup seclab_sgk_db_01

alter database seclab_sgk_db add filegroup seclab_sgk_db_02

alter database seclab_sgk_db add filegroup seclab_sgk_db_03

alter database seclab_sgk_db add filegroup seclab_sgk_db_04

alter database seclab_sgk_db add filegroup seclab_sgk_db_05

alter database seclab_sgk_db add filegroup seclab_sgk_db_06

alter database seclab_sgk_db add filegroup seclab_sgk_db_07

alter database seclab_sgk_db add filegroup seclab_sgk_db_08

alter database seclab_sgk_db add filegroup seclab_sgk_db_09

alter database seclab_sgk_db add filegroup seclab_sgk_db_10

alter database seclab_sgk_db

add file(name='seclab_sgk_db_01'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_01.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_01;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_02'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_02.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_02;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_03'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_03.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_03;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_04'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_04.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_04;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_05'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_05.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_05;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_06'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_06.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_06;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_07'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_07.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_07;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_08'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_08.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_08;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_09'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_09.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_09;

alter database seclab_sgk_db

add file(name='seclab_sgk_db_10'

,filename='E:\DataDB\seclab_sgk_db\seclab_sgk_db_10.mdf'

,size=3mb

,filegrowth=10mb

,maxsize=unlimited)

to filegroup seclab_sgk_db_10;

create partition function

Part_Fun(int)

as range right

for values ('10000000','20000000','30000000','40000000','50000000','60000000','70000000','80000000','90000000','100000000')

create partition scheme -- 创建分区方案

Part_Plan -- 分区方案名称

as partition Part_Fun -- 分区函数名称

to ('seclab_sgk_db_01','seclab_sgk_db_02','seclab_sgk_db_03','seclab_sgk_db_04','seclab_sgk_db_05','seclab_sgk_db_06','seclab_sgk_db_07','seclab_sgk_db_08','seclab_sgk_db_09','seclab_sgk_db_10','seclab_sgk_db_10') -- 分区文件组

CREATE TABLE sgk(

[id] [int] primary key IDENTITY(1,1) NOT NULL,

[Username1] [nvarchar](max) NULL,

[Username2] [nvarchar](max) NULL,

[Username3] [nvarchar](max) NULL,

[Username4] [nvarchar](max) NULL,

[Username5] [nvarchar](max) NULL,

[Name] [nvarchar](max) NULL,

[IDcard] [nvarchar](max) NULL,

[Address] [nvarchar](max) NULL,

[Company] [nvarchar](max) NULL,

[Post] [nvarchar](max) NULL,

[Phone1] [nvarchar](max) NULL,

[Phone2] [nvarchar](max) NULL,

[Phone3] [nvarchar](max) NULL,

[Phone4] [nvarchar](max) NULL,

[Phone5] [nvarchar](max) NULL,

[Email1] [nvarchar](max) NULL,

[Email2] [nvarchar](max) NULL,

[Email3] [nvarchar](max) NULL,

[Email4] [nvarchar](max) NULL,

[Email5] [nvarchar](max) NULL,

[QQ1] [nvarchar](max) NULL,

[QQ2] [nvarchar](max) NULL,

[QQ3] [nvarchar](max) NULL,

[QQ4] [nvarchar](max) NULL,

[QQ5] [nvarchar](max) NULL,

[WeiXin1] [nvarchar](max) NULL,

[WeiXin2] [nvarchar](max) NULL,

[WeiXin3] [nvarchar](max) NULL,

[WeiXin4] [nvarchar](max) NULL,

[WeiXin5] [nvarchar](max) NULL,

[Password1] [nvarchar](max) NULL,

[Password2] [nvarchar](max) NULL,

[Password3] [nvarchar](max) NULL,

[Password4] [nvarchar](max) NULL,

[Password5] [nvarchar](max) NULL,

[md51] [nvarchar](max) NULL,

[md52] [nvarchar](max) NULL,

[md53] [nvarchar](max) NULL,

[md54] [nvarchar](max) NULL,

[md55] [nvarchar](max) NULL,

[sha1601] [nvarchar](max) NULL,

[sha1602] [nvarchar](max) NULL,

[sha1603] [nvarchar](max) NULL,

[sha1604] [nvarchar](max) NULL,

[sha1605] [nvarchar](max) NULL,

[sha2241] [nvarchar](max) NULL,

[sha2242] [nvarchar](max) NULL,

[sha2243] [nvarchar](max) NULL,

[sha2244] [nvarchar](max) NULL,

[sha2245] [nvarchar](max) NULL,

[sha2501] [nvarchar](max) NULL,

[sha2502] [nvarchar](max) NULL,

[sha2503] [nvarchar](max) NULL,

[sha2504] [nvarchar](max) NULL,

[sha2505] [nvarchar](max) NULL,

[Hobby] [nvarchar](max) NULL,

[Color] [nvarchar](max) NULL,

[Girlfriend] [nvarchar](max) NULL,

[Boyfriend] [nvarchar](max) NULL,

[Classmate] [nvarchar](max) NULL,

[md5161] [nvarchar](max) NULL,

[ip1] [nvarchar](max) NULL,

[birthdate] [nvarchar](max) NULL,

[md51salt] [nvarchar](max) NULL,

[md52salt] [nvarchar](max) NULL,

[md53salt] [nvarchar](max) NULL,

[md54salt] [nvarchar](max) NULL,

[md55salt] [nvarchar](max) NULL,

[BankInfo] [nvarchar](max) NULL,

[BankNumber] [nvarchar](max) NULL,

[BankName] [nvarchar](max) NULL,

) on Part_Plan(ID)

create nonclustered

index Part_Non_Name

on sgk(id)

on Part_Plan(ID)