I got this one wrong but only after I tried using the script below on my SQL Server 2008 R2 environment.
Could someone explain why the script below allows me to move a file between Filegroups?
The script removes the file from one file group and then adds it to another. I understand that the file is physically removed from the file system and then a new file is created on the file system when the script adds a file to the other filegroup but why do the select statements selecting data from Table_1 continue to work?
USE [master]
GO
/****** Object: Database [Test] Script Date: 08/25/2011 07:09:25 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test')
DROP DATABASE [Test]
GO
USE [master]
GO
/****** Object: Database [Test] Script Date: 08/25/2011 07:09:25 ******/
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'C:\SQL Server User Databases\Test.mdf'),
FILEGROUP [SECONDARY]
( NAME = N'Test2', FILENAME = N'C:\SQL Server User Databases\Test2.ndf'),
( NAME = N'Test3', FILENAME = N'C:\SQL Server User Databases\Test3.ndf'),
FILEGROUP [THIRDGROUP]
( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf')
LOG ON
( NAME = N'Test_log', FILENAME = N'C:\SQL Server User Databases\Test_log.ldf')
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 100
GO
USE Test
GO
IF EXISTS
(
SELECT1
FROMINFORMATION_SCHEMA.TABLEST
WHERET.TABLE_NAME='TABLE_1'
ANDT.TABLE_SCHEMA='dbo'
)
BEGIN
DROP TABLETABLE_1
END
GO
CREATE TABLE dbo.Table_1
(
C1 int NOT NULL IDENTITY (1, 1),
C2 varchar(50) NOT NULL
) ON [THIRDGROUP]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(
C1
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE TEST
GO
SELECT*
FROMTABLE_1
GO
USE [master]
GO
ALTER DATABASE [Test] REMOVE FILE [Test4]
GO
GO
ALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
TO FILEGROUP [SECONDARY]
USE TEST
GO
SELECT*
FROMTABLE_1
GO
USE master
GO
ALTER DATABASE [Test] REMOVE FILE [Test4]
GO
GO
ALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
TO FILEGROUP [THIRDGROUP]
USE TEST
GO
SELECT*
FROMTABLE_1
GO