O SQL Server possui três tipos de arquivos:
Arquivo de dados primário | Os arquivos de dados primários é o ponto de partida do banco de dados e para os outros arquivos no banco de dados. Todo banco de dados tem um arquivo de dados primário. A extensão de nome de arquivo recomendado para arquivo de dados primário é mdf. |
Arquivo de dados secundário | Os arquivos de dados secundários compõem todos os arquivos de dados, diferente do arquivo de dados primário. Alguns bancos de dados podem não ter qualquer arquivo de dados secundário, enquanto outros possuem vários arquivos de dados secundários. A extensão do nome de arquivo recomendado para arquivos de dados secundários é ndf. |
Arquivo de log | Os arquivos de log armazenam todas as informações de log que é usado para recuperar o banco de dados. Deve haver pelo menos um arquivo de log para cada banco de dados, embora possa haver mais de um. A extensão de nome de arquivo recomendado para arquivos de log é .ldf. |
Obs: O SQL Server não obriga o uso das extensões .mdf, .ndf e .ldf no nome dos arquivos, mas estas extensões são regularmente utilizadas por ajudar na identificação dos diferentes tipos de arquivos e seu uso.
Podemos criar grupos de arquivos denominados de Filegroups coleções de arquivos. São usados para ajudar na colocação de dados e tarefas administrativas como, operações de backup e restore.
A ilustração abaixo mostra um exemplo dos nomes de arquivo lógico e os nomes de arquivo físico de um banco de dados criado em uma instância default do SQL Server 2005:
Particionamento da tabela e do índice
O particionamento da tabela e do índice alivia o gerenciamento de bancos de dados de grande porte, facilitando o gerenciamento em partes menores e mais manejáveis. O particionamento horizontal permite a divisão de uma tabela em pequenos grupos baseados no esquema de particionamento. O particionamento da tabela é designado para amplos bancos de dados, de centenas de gigabytes a terabytes ou mais. Nas versões anteriores do SQL Server era necessário carregar cada partição em tabelas separadas, definir uma visualização da tabela e em seguida utilizar uma sentença complexa UNION para incorporá-la na consulta. No SQL Server 2005, as partições podem ser alternadas dentro e fora do esquema de partição existente de maneira rápida, eliminando a necessidade de criar e indexar novas tabelas ou perder tempo modificando a sentença UNION para adaptar a nova tabela. Este recurso está somente disponível na versão Enterprise.
Particionando uma Tabela
1º Passo Criando um Banco de Dados: Vamos criar o banco de dados Particionamento com um arquivo de dados primário, seis arquivos secundários e um arquivo de log.
Listagem 1. Criação do Banco de Dados
USE master
GO
CREATE DATABASE Particionamento
ON PRIMARY
( NAME = db_data,
FILENAME = ‘d:bancodb.mdf’,
SIZE = 3MB),
FILEGROUP FG1
( NAME = FG1_data,
FILENAME = ‘d:bancoFG1.ndf’,
SIZE = 3MB),
FILEGROUP FG2
( NAME = FG2_data,
FILENAME = ‘d:bancoFG2.ndf’,
SIZE = 3MB),
FILEGROUP FG3
( NAME = FG3_data,
FILENAME = ‘d:bancoFG3.ndf’,
SIZE = 3MB),
FILEGROUP FG4
( NAME = FG4_data,
FILENAME = ‘d:bancoFG4.ndf’,
SIZE = 3MB),
FILEGROUP FG5
( NAME = FG5_data,
FILENAME = ‘d:bancoFG5.ndf’,
SIZE = 3MB),
FILEGROUP FG6
( NAME = FG6_data,
FILENAME = ‘d:bancoFG6.ndf’,
SIZE = 3MB)
LOG ON
( NAME = db_log,
FILENAME = ‘d:bancolog.ndf’,
SIZE = 2MB,
FILEGROWTH = 10% );
GO
USE Particionamento
GO
2º Passo Criando uma Partition Function
Uma Partition Function é um objeto independente no banco de dados que define o limite para partição dos dados. É o primeiro passo para particionar uma tabela ou índice.
Listagem 2. Criação da Partition Function
CREATE PARTITION FUNCTION particao_funcao (int) AS
RANGE LEFT FOR VALUES (100, 200, 300, 400, 500);
GO
Abaixo temos os intervalos das Partições:
Id da Partição | Intervalo dos Valores |
1 | 1 infinito a 100 |
2 | |
3 | |
4 | |
5 | |
6 |
Este comando cria uma função de partição chamada de particao_funcao que é aplicado a um tipo de dados inteiro. A clausula RANGE LEFT especifica o ponto de limite definido para a partição à esquerda e para a direita usamos RANGE RIGHT. A clausula VALUES define os pontos de limite da partição. O SQL Server 2005 suporta todos os tipos de dados para o uso de particionamento, exceto TEXT, NTEXT, IMAGE, XML, TIMESTAMP, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX).
3º Passo Criando um PARTITION SCHEME
O Esquema de Partição define em quais filegroups os intervalos serão armazenados.
Listagem 3. Criação da Partition Scheme
CREATE PARTITION SCHEME particao_esquema AS
PARTITION particao_funcao
TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6])
GO
Este comando cria uma função de partição chamada particao_esquema e é mapeado a um esquema de partição. A clausula TO define quais filegroups que irá utilizar da Function Partition.
4º Passo Utilize o scrip abaixo para Verificar a PARTITION SCHEME criada
SELECT * FROM sys.partition_schemes
Como resultado para o exemplo deste artigo temos a figura abaixo.
5º Passo Criando uma tabela particionada: Vamos criar uma tabela particionada utilizando um PARTITION SCHEME criado no passo 3. A clausua ON define o esquema a ser utilizado.
Listagem 5. Criação de uma tabela utilizando um PARTITION SCHEME
CREATE TABLE dbo.AULA_PARTICAO
(aula_id int, descricao Char(10) default (‘AAAAA’))
ON particao_esquema(aula_id);
Testando os recursos implementados
Criando um script para inserir 60 registros com intervalo de 10 cada.
Listagem 6. Carregando a tabela
SET NOCOUNT ON
DECLARE @i INT
SET @i=1
WHILE @i<=600
BEGIN
INSERT dbo.aula_particao (aula_id) select @i
SET @i=@i+10
END
GO
O script abaixo verifica a quantidade de registros por partição. A VIEW sys.partitions contem informações sobre partições de tabela e indices do banco de dados.
SELECT * FROM sys.partitions
WHERE object_id = OBJECT_ID(‘dbo.AULA_PARTICAO’);
Listagem 8. Verificando em que partição os dados estão alocado: A função $partition retorna a partição em que o dado se encontra e tambem permite consultar somente os dados de uma partição.
SELECT *,$partition.particao_funcao(aula_id) as nr_particao
FROM dbo.aula_particao
where aula_id in (1,11,101,151,201,221,301,331,401,431,511,561)
Listagem 9. Consultando os dados da partição 2
SELECT *,$partition.particao_funcao(aula_id) as nr_particao
FROM dbo.aula_particao
WHERE $partition.particao_funcao(aula_id) = 2
Bom pessoal por hoje é só uma abraço a todos e até a próxima ! Em Março/2007 estarei disponibilizando Particionando Tabelas e Índices no SQL Server 2005 Parte II onde falarei sobre Particionanmento Índices e Gerenciamento de Partições.
ROGÉRIO SANCHES DE OLIVEIRA
(rsanches@hpgsoft.com.br) é formado em Sistemas de Informação para o Setor Financeiro pela UNIBAN. Trabalha como Analista Programador e DBA no CPDI (Centro de Processamento de Documentos Inteligentes) da XEROX, participa ativamente dos fóruns do portal www.mcdbabrasil.com.br. Possui certificação MCTS em SQL Server 2005 e está se preparando para a certificação MCITP:Database Administrator.