SQL Server 现有数据库中添加文件组和数据文件
use CURRENT_DB --进入当前操作数据库
go
alter database CURRENT_DB add filegroup FG1 --向CURRENT_DB 数据库添加FG1文件组
go --批处理标示
alter database CURRENT_DB add file --向新建的文件组中添加数据文件
(
name='FG1_E_currentdb_data',
filename='e:\project\FG1',
size=5MB,
filegrowth=10%
)to filegroup FG1
go
alter database CURRENT_DB
modify filegroup FG1 default --设置FG1文件组为默认文件组
go
创建新的数据库指定文件组合文件(文件组里有多个文件)
CREATE DATABASE MultipleFileGroups ON PRIMARY
(
-- Primary File Group
NAME = 'MultipleFileGroups',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups.mdf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
),
-- Secondary File Group
FILEGROUP FileGroup1
(
-- 1st file in the first secondary File Group
NAME = 'MultipleFileGroups1',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups1.ndf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
),
(
-- 2nd file in the first secondary File Group
NAME = 'MultipleFileGroups2',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups2.ndf',
SIZE = 1MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
(
-- Log File
NAME = 'MultipleFileGroups_Log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\MultipleFileGroups.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
GO
创建表指定文件组
CREATE TABLE Customers
(
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
Address CHAR(100) NOT NULL,
ZipCode CHAR(5) NOT NULL,
Rating INT NOT NULL,
ModifiedDate DATETIME NOT NULL,
)
ON [FileGroup1] --指定文件组
GO
--添加索引时,指定存放的文件组
CREATE NONCLUSTERED INDEX idx_on_other_fileGroue on ProductDemo(UPCode) ON AddFileGroup --红色部分是主角,指定文件组。
--如果不指定文件组,则存放到默认的文件组当中
CREATE NONCLUSTERED INDEX idx_on_other_fileGroue on ProductDemo(UPCode)