filename=’D:studydb_study_fg_data2_2.ndf’),–次要数据文件2
filegroup db_Study_filegroup2
(name=’db_study_fg_data3′,
filename=’D:studydb_study_fg_data3_1.ndf’)
log on
(name=’db_study_log’,
filename=’D:studydb_study.ldf’)
go
–5.2查询文件组和文件
SELECT df.[name],
df.physical_name,
df.[size],
df.growth,
f.[name][filegroup],
f.is_default
FROM sys.database_files df JOIN sys.filegroups f
ON df.data_space_id = f.data_space_id
/*
name physical_name size growth filegroup is_default
db_study_Primary D:studydb_study_pri.mdf 280 128 PRIMARY 1
db_study_fg_data1 D:studydb_study_fg_data1_1.ndf 128 128 db_Study_filegroup1 0
db_study_fg_data2 D:studydb_study_fg_data2_2.ndf 128 128 db_Study_filegroup1 0
db_study_fg_data3 D:studydb_study_fg_data3_1.ndf 128 128 db_Study_filegroup2 0
*/
–5.3修改默认数据文件组
alter database db_study
modify filegroup db_study_filegroup1 default
–5.4
–在默认文件组db_study_filegroup1创建表,
–并且指定图像数据保存在用户定义文件组db_study_filegroup1
create table my_test
(
ID int primary key,
[Name] varchar(10),
PIC image
)textimage_on db_study_filegroup2
–在用户定义文件组db_study_filegroup2上创建索引
create index ix_my_test on my_test(ID) on db_study_filegroup2
GO
–5.5将要删除数据文件db_study_fg_data1的数据转移到其他数据文件中，
–并且清空数据文件db_study_fg_data1
DBCC SHRINKFILE(db_study_fg_data1,EMPTYFILE)
go
–删除数据文件db_study_fg_data1
ALTER DATABASE DB_study
REMOVE FILE db_study_fg_data1
go
