CREATE PARTITION FUNCTION (Transact-SQL)

Artigo
09/12/2014

Cria uma função no banco de dados atual que mapeia as linhas de uma tabela ou índice em partições com base nos valores de uma coluna especificada. Usar CREATE PARTITION FUNCTION é a primeira etapa na criação de uma tabela particionada ou índice. No SQL Server 2012, uma tabela ou índice pode ter no máximo 15.000 partições.

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

Argumentos

partition_function_name
É o nome da função de partição. Os nomes de funções de partição devem ser exclusivos no banco de dados e estar em conformidade com as regras para identificadores.
input_parameter_type
É o tipo de dados da coluna usada para particionamento. Todos os tipos de dados são válidos para uso como colunas de particionamento, com exceção de text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), tipos de dados de alias ou tipos de dados CLR definidos pelo usuário.

A coluna real, conhecida como uma coluna de particionamento, é especificada na instrução CREATE TABLE ou CREATE INDEX.

boundary_value
Especifica os valores de limite para cada partição de uma tabela particionada ou índice que usa partition_function_name. Se boundary_value estiver vazio, a função de partição mapeará a tabela inteira ou o índice usando partition_function_name em uma única partição. É possível usar somente uma coluna de divisão, especificada em uma instrução CREATE TABLE ou CREATE INDEX.

boundary_value é uma expressão constante que pode fazer referência a variáveis. Isso inclui variáveis ou funções de tipo definido pelo usuário e funções definidas pelo usuário. Não pode fazer referência a expressões Transact-SQL. boundary_value deve corresponder ou ser implicitamente conversível no tipo de dados fornecido no input_parameter_type, e não pode ser truncado durante a conversão implícita de maneira que o tamanho e a escala do valor não correspondam ao de seu input_parameter_type correspondente.

Observação
Se boundary_value consistir em literais datetime ou smalldatetime, esses literais serão avaliados supondo que us_english é o idioma da sessão. Este comportamento é preterido. Para certificar-se de que a definição da função de partição se comporta conforme esperado para todos os idiomas de sessão, recomendamos usar constantes que sejam interpretadas da mesma maneira para todas as configurações de idioma, tal como o formato aaaammdd; ou converter explicitamente literais em um estilo específico. Para determinar a sessão de idioma de seu servidor, execute SELECT @@LANGUAGE.

Se boundary_value consistir em literais datetime ou smalldatetime, esses literais serão avaliados supondo que us_english é o idioma da sessão. Este comportamento é preterido. Para certificar-se de que a definição da função de partição se comporta conforme esperado para todos os idiomas de sessão, recomendamos usar constantes que sejam interpretadas da mesma maneira para todas as configurações de idioma, tal como o formato aaaammdd; ou converter explicitamente literais em um estilo específico. Para determinar a sessão de idioma de seu servidor, execute SELECT @@LANGUAGE.

...n
Especifica o número de valores fornecidos por boundary_value, que não deve exceder 14.999. O número de partições criadas é igual a n + 1. Os valores não precisam ser listados em ordem. Se os valores não estiverem em ordem, o Mecanismo de Banco de Dados os classifica, cria a função e retorna um aviso de que os valores não foram fornecidos em ordem. O Database Engine retornará um erro se n incluir qualquer valor duplicado.
LEFT | RIGHT
Especifica a qual lado de cada intervalo de valor de limite, esquerdo ou direito, o boundary_value [ ,...n ] pertence, quando valores de intervalo forem classificados pelo Mecanismo de Banco de Dados em ordem crescente da esquerda para a direita. Se não for especificado, LEFT será o padrão.

Comentários

O escopo de uma função de partição é limitado ao banco de dados em que é criado. No banco de dados, as funções das partições residem em um namespace separado das outras funções.

Quaisquer linhas cuja coluna de particionamento tenha valores nulos serão colocadas na partição mais à esquerda, a menos que NULL seja especificado como um valor de limite e RIGHT seja indicado. Nesse caso, a partição mais à esquerda será uma partição vazia e os valores NULL serão colocados na partição seguinte.

Permissões

Qualquer uma das permissões a seguir pode ser usada para executar CREATE PARTITION FUNCTION:

Permissão ALTER ANY DATASPACE. Essa permissão tem como padrão os membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_ddladmin.
Permissão CONTROL ou ALTER no banco de dados no qual a função de partição está sendo criada.
Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual a função de partição está sendo criada.

Exemplos

A.Criando uma função de partição RANGE LEFT em uma coluna int

A função de partição a seguir particionará uma tabela ou um índice em quatro partições.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

A tabela a seguir mostra como uma tabela que usa essa função de partição na coluna de particionamento col1 seria particionada.

Partition	1	2	3	4
Valores	col1 <= 1	col1 > 1 AND col1 <= 100	col1 > 100 AND col1 <=1000	col1 > 1000

B.Criando uma função de partição RANGE RIGHT em uma coluna int

A função de partição a seguir usa os mesmos valores para boundary_value [ ,...n ] que o do exemplo anterior, com exceção de que ela especifica RANGE RIGHT.

CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE RIGHT FOR VALUES (1, 100, 1000);

A tabela a seguir mostra como uma tabela que usa essa função de partição na coluna de particionamento col1 seria particionada.

Partition	1	2	3	4
Valores	col1 < 1	col1 >= 1 AND col1 < 100	col1 >= 100 AND col1 < 1000	col1 >= 1000

C.Criando uma função de partição RANGE RIGHT em uma coluna datetime

A função de partição a seguir particiona uma tabela ou um índice em 12 partições, uma para cada mês de valores válidos no ano em uma coluna datetime.

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
               '20030501', '20030601', '20030701', '20030801', 
               '20030901', '20031001', '20031101', '20031201');

A tabela a seguir mostra como uma tabela ou um índice que usa essa função de partição na coluna de particionamento datecol seria particionada.

Partition	1	2	...	11	12
Valores	datecol < February 1, 2003	datecol >= February 1, 2003 AND datecol < March 1, 2003		datecol >= November 1, 2003 AND col1 < December 1, 2003	datecol >= December 1, 2003

D.Criando uma função de partição em uma coluna char

A função de partição a seguir particiona uma tabela ou um índice em quatro partições.

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

A tabela a seguir mostra como uma tabela que usa essa função de partição na coluna de particionamento col1 seria particionada.

Partition	1	2	3	4
Valores	col1 < EX...	col1 >= EX AND col1 < RXE...	col1 >= RXE AND col1 < XR...	col1 >= XR

E.Criando 15.000 partições

A função de partição a seguir particiona uma tabela ou um índice em 15.000 partições.

--Create integer partition function for 15,000 partitions.
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
    SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
    SET @i += 1;    
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO

F.Criando partições para vários anos

A função de partição a seguir particiona uma tabela ou um índice em 50 partições em uma coluna datetime2. Há uma partição para cada mês entre janeiro de 2007 e janeiro de 2011.

--Create date partition function with increment by month.
DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '20070101';
WHILE @i < '20110101'
BEGIN
    SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';
    SET @i = DATEADD(MM, 1, @i);    
END
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';
EXEC sp_executesql @DatePartitionFunction;
GO