Splitting Tables across database files

Problem: Tables cannot be split across filegroups, can they split across database files?

Solution: Yes, script below

USE [master]
GO

–DROP DATABASE
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’james’)
BEGIN
ALTER DATABASE james
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [james]
END

GO
–CREATE DATABASE
CREATE DATABASE [james] ON PRIMARY
( NAME = N’james’, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james.mdf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB ),
( NAME = N’james2′, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james2.ndf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB ),
( NAME = N’james3′, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james3.ndf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB )
LOG ON
( NAME = N’james_log’, FILENAME = N’E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\james_log.ldf’ , SIZE = 109568KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
–LOAD DATA
USE james
GO

select *
into james.dbo.a
from Table between 100 and 150 MB

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: