Como criar Databases em grupos de arquivos (filegroup) diferentes para melhorar a performance do servidor de dados

Muitas vezes acontece de criarmos o banco de dados sem dimensioná-lo corretamente e especificar exatamente onde desejamos que as tabelas fiquem. Pois então, aqui vai um exemplo de como podemos fazer isso:


-- Neste exemplo estamos identificando cada grupo de arquivos
-- utilizando a palavra FILEGROUP, desta forma poderemos escolhar
-- em qual grupo iremos criar as nossas tabelas, índices etc...
CREATE DATABASE EMPRESA
ON ( NAME = 'EMPRESA_CADASTROS',
FILENAME = 'C:\DADOS\EMPRESA_CADASTROS.MDF',
SIZE = 5, -- assume MB (KB, GB, TB)
MAXSIZE = 100,
FILEGROWTH = 5 ),
FILEGROUP VENDAS
( NAME = 'EMPRESA_VENDAS',
FILENAME = 'C:\DADOS\EMPRESA_VENDAS.NDF',
SIZE = 5, -- assume MB (KB, GB, TB)
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 ),
FILEGROUP COMPRAS
( NAME = 'EMPRESA_COMPRAS',
FILENAME = 'C:\DADOS\EMPRESA_COMPRAS.NDF',
SIZE = 5, -- assume MB (KB, GB, TB)
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 ),
FILEGROUP FINANCEIRO
( NAME = 'EMPRESA_FINANCEIRO_1',
FILENAME = 'C:\DADOS\EMPRESA_FINANCEIRO_1.NDF',
SIZE = 5, -- assume MB (KB, GB, TB)
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 ),
( NAME = 'EMPRESA_FINANCEIRO_2',
FILENAME = 'C:\DADOS\EMPRESA_FINANCEIRO_2.NDF',
SIZE = 5, -- assume MB (KB, GB, TB)
MAXSIZE = UNLIMITED,
FILEGROWTH = 5 )
LOG ON
( NAME = 'EMPRESA_LOG',
FILENAME = 'C:\DADOS\EMPRESA_LOG.LDF' );
GO
--
USE EMPRESA;
EXEC SP_HELPFILE;

-- Esta tabela será criada em PRIMARY porque
-- não informaremos o FILEGROUP
CREATE TABLE TABELA_1
( COD INT PRIMARY KEY,
NOME VARCHAR(30) )

-- Esta tabela será criada em VENDAS
CREATE TABLE TABELA_2
( COD INT,
NOME VARCHAR(30),
CONSTRAINT PK_TABELA_2 PRIMARY KEY (COD) ) ON VENDAS

EXEC SP_HELP TABELA_2 --> Está no filegroup VENDAS


-- Criar tabela no grupo VENDAS
CREATE TABLE TABELA_3
( COD INT PRIMARY KEY, NOME VARCHAR(30) ) ON COMPRAS;

-- Criar tabela no grupo FINANCEIRO
CREATE TABLE TABELA_4
( COD INT PRIMARY KEY, NOME VARCHAR(30) ) ON FINANCEIRO;

-- Mostra em que FILEGROUP está cada tabela
SELECT I.OBJECT_ID AS ID_DA_TABELA,
I.NAME AS CHAVE_PRIMARIA,
T.NAME AS NOME_TABELA,
I.DATA_SPACE_ID AS ID_FEILEGROUP,
FG.NAME AS FILEGROUP
FROM SYS.INDEXES I
JOIN SYS.SYSOBJECTS T ON I.OBJECT_ID = T.ID
JOIN SYS.DATA_SPACES FG ON I.DATA_SPACE_ID = FG.DATA_SPACE_ID
WHERE I.IS_PRIMARY_KEY = 1

Execução de comandos no shell do windows e definição do Database no Sql Server

-------------------------------------------------------------
Exec SP_Configure

Exec SP_Configure 'show advanced options',1
RECONFIGURE

Exec SP_Configure 'xp_cmdshell',1
RECONFIGURE

Exec xp_cmdshell 'MD C:\Bancos'
-------------------------------------------------------------
CREATE DATABASE Escola
ON
(
name = 'Escola_Dados' ,
filename = 'C:\Bancos\Escola_Dados.mdf',
size = 10MB,
maxsize = 100MB,
filegrowth = 10MB
)
-------------------------------------------------------------
Exec xp_cmdshell 'Dir C:\Bancos\*.*'
-------------------------------------------------------------
Use master

DROP DATABASE Escola

Exec xp_cmdshell 'Dir C:\Bancos\*.*'
-------------------------------------------------------------
CREATE DATABASE Escola
ON
(
name = 'Escola_Dados' ,
filename = 'C:\Bancos\Escola_Dados.mdf',
size = 10MB,
maxsize = 100MB,
filegrowth = 10MB
)
LOG ON
(
name = 'Escola_Log' ,
filename = 'C:\Bancos\Escola_Log.ldf',
size = 1MB,
maxsize = 10MB,
filegrowth = 1MB
)

Exec xp_cmdshell 'Dir C:\Bancos\*.*'
-------------------------------------------------------------