SQL Server大数据表的分区存储

最新推荐文章于 2022-07-29 10:44:46 发布

Linux????? Mr.Liyz

最新推荐文章于 2022-07-29 10:44:46 发布

阅读量503

点赞数

文章标签：数据库 php

原文链接：https://my.oschina.net/mj23/blog/888282

版权

为什么80%的码农都做不了架构师？>>>

1. 建立分区存储的文件组（filegroup）和文件（file）

ALTER DATABASE Student ADD FILEGROUP [studentdata01];

ALTER DATABASE Student ADD FILEGROUP [studentdata02];

ALTER DATABASE Student ADD FILEGROUP [studentdata03];

ALTER DATABASE Student ADD FILEGROUP [studentdata04];


ALTER DATABASE Student
ADD FILE(
NAME=N'studentdata01',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.SSCCMDB\MSSQL\DATA\studentdata01.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)TO FILEGROUP [studentdata01]

ALTER DATABASE Student
ADD FILE(
NAME=N'studentdata02',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.SSCCMDB\MSSQL\DATA\studentdata02.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)TO FILEGROUP [studentdata02]

studentdata03, studentdata04略

2. 创建分区函数

Use Student
CREATE  PARTITION FUNCTION Studentdata_Partition_Function (datetime)
AS RANGE RIGHT
FOR VALUES ('2017-04-01T00:00:00','2017-07-01T00:00:00','2017-10-01T00:00:00')

3. 创建分区方案

Use Student

CREATE PARTITION SCHEME Studentdata_Partition_SCHEME  
AS PARTITION Studentdata_Partition_Function  
TO (studentdata01, studentdata02, studentdata03, studentdata04)

4. 创建分区表和分区索引：

Use Student

CREATE TABLE StudentData
(
      GradeID int NOT NULL,
      ClassID int NOT NULL,
      StudentID int NOT NULL,
      Datetime datetime NOT NULL,
      Data float(4) NOT NULL
)ON Studentdata_Partition_SCHEME([Datetime]) ;


CREATE CLUSTERED INDEX DatetimeIndex

    ON StudentData

    (

    Datetime

)  WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, FILLFACTOR = 95)

    ON Studentdata_Partition_SCHEME ([Datetime]);

https://techinfoofmicrosofttech.osscons.jp/index.php?SQL%20Server%20%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E5%88%86%E5%89%B2

转载于:https://my.oschina.net/mj23/blog/888282