demosql40.sql

use master
go
create database ricotest on primary
(name='ricotest',filename='E:\sqldata\ricotest.mdf',size=100mb,maxsize=300mb,filegrowth=50mb),
--存放大資料表的檔案群組
filegroup fg_BigTbl
(name='ricotest_BigTbl',filename='E:\sqldata\ricotest_BigTbl.ndf',size=100mb,maxsize=300mb,filegrowth=50mb),
--存放大索引的檔案群組
filegroup fg_BigIdx
(name='ricotest_BigIdx',filename='E:sqldata\ricotest_BigIdx.ndf',size=100mb,maxsize=300mb,filegrowth=50mb)
log  on
(name='ricotest_log',filename='E:\sqlldf\ricotest_log.ldf',size=20mb,maxsize=100mb,filegrowth=10mb)
go
--復原模式=完整
alter database ricotest set recovery full

 
use ricotest
--存放在預設群組(primary)
create table tblA
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
)  on [primary]
create clustered index cidx_c1
on tblA(c1)
on [primary]
--存放在大資料表群組(fg_BigTbl)
create table tblB
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
) on [fg_BigTbl]
--存放在大索引群組(fg_BigIdx)
create nonclustered index nidx_c2
on tblB(c2)
on [fg_BigIdx]
--存放在大資料表群組(fg_BigTbl)
create table tblC
(
c1 int identity(1,1),
c2 varchar(10),
c3 datetime
) on [fg_BigTbl]

 
--確認相關物件所屬的檔案群組
SELECT t.name as [資料表名稱] ,o.[type] as [物件類型], i.[name] as [索引名稱],  f.name as [檔案群組名稱]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
inner join sys.tables t
on t.object_id=o.object_id
and o.type='U'
GO