SQLServer 2008 切换表的存储文件组

CREATE DATABASE [Demo]
ON  PRIMARY		( NAME = N'Demo',FILENAME = N'D:MSSQLDATADemo.mdf',SIZE = 3072KB,FILEGROWTH = 1024KB ),FILEGROUP [FG1] ( NAME = N'Demo01',FILENAME = N'D:MSSQLDATADemo01.ndf',FILEGROUP [FG2] ( NAME = N'Demo02',FILENAME = N'D:MSSQLDATADemo02.ndf',FILEGROUP [FG3] ( NAME = N'Demo03',FILENAME = N'D:MSSQLDATADemo03.ndf',FILEGROWTH = 1024KB )
LOG ON			( NAME = N'Demo_log',FILENAME = N'D:MSSQLDATADemo_log.ldf',SIZE = 1024KB,FILEGROWTH = 10MB)
GO

USE [Demo]
GO
--	drop table dbo.TAB
CREATE TABLE dbo.TAB(
	Guid uniqueidentifier not null,name nvarchar(50) not null,sex	 bit not null,age  smallint not null,indate	datetime not null,--CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED ([Guid] ASC)ON [FG3]
) ON [FG3]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_Guid] DEFAULT(NEWSEQUENTIALID()) FOR [Guid]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_sex] DEFAULT(1) FOR [sex]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_indate] DEFAULT(GETDATE()) FOR [indate]
GO

--	查看表在哪个文件组
SELECT o.[name],o.[type],i.[name],i.[index_id],f.[name]
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]
WHERE o.name in( 'TAB')
GO

--	创建聚集索引主键，并指定存储的文件组
ALTER TABLE dbo.TAB WITH NOCHECK 
ADD CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED([Guid]) ON [PRIMARY]
GO

--	删除聚集索引主键，并指定存储的文件组
ALTER TABLE dbo.TAB DROP CONSTRAINT [PK_TAB] WITH (MOVE TO [FG2],ONLINE=ON)
GO

--	创建聚集索引，并指定存储的文件组
CREATE CLUSTERED INDEX [IX_TAB] ON dbo.TAB([Guid]) ON [FG1]
GO

--	删除聚集索引，并指定存储的文件组
DROP INDEX [IX_TAB] ON dbo.TAB WITH (MOVE TO [FG3],ONLINE=ON)
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!