Criar tabelas e índices particionados

Artigo
09/04/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Você pode criar uma tabela particionada ou índice no SQL Server, no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure usando o SQL Server Management Studio ou o Transact-SQL. Os dados em tabelas e índices particionados são divididos horizontalmente em unidades que podem ser disseminadas por mais de um grupo de arquivos em um banco de dados. O particionamento pode tornar as tabelas e os índices grandes mais gerenciáveis e escalonáveis.

A criação de uma tabela ou um índice particionado ocorre normalmente em três ou quatro partes:

Opcionalmente, crie um ou mais grupos de arquivos e os arquivos de dados correspondentes que conterão as partições especificadas pelo esquema de partição. A principal razão para colocar partições em vários grupos de arquivos é garantir que você possa executar independentemente operações de backup e restauração em grupos de arquivo. Se isso não for necessário, você poderá optar por atribuir todas as partições a um só grupo de arquivos, usando um grupo de arquivos existente, como PRIMARY, ou um novo grupo de arquivos com arquivo(s) de dados relacionados. Em quase todos os cenários, você obterá todos os benefícios do particionamento, use ou não vários grupos de arquivos.
Cria uma função de partição que mapeia as linhas de uma tabela ou de um índice em partições com base nos valores de uma coluna especificada. Você pode usar uma só função de partição para particionar vários objetos.
Cria um esquema de partição que mapeia as partições de uma tabela particionada ou índice para os novos grupos de arquivos. Você pode usar um só esquema de partição para particionar vários objetos.
Crie ou altere uma tabela ou índice e especifique o esquema de partição como o local de armazenamento, junto com a coluna que servirá como a coluna de particionamento.

Observação

O particionamento tem suporte total no Banco de Dados SQL do Azure. Como há suporte apenas para o grupo de arquivos PRIMARY no Banco de Dados SQL do Azure, todas as partições devem ser colocadas no grupo de arquivos PRIMARY.

O particionamento de tabela também está disponível em pools de SQL dedicados no Azure Synapse Analytics, com algumas diferenças de sintaxe. Saiba mais em Como particionar tabelas no pool de SQL dedicado.

Permissões

A criação de uma tabela particionada requer a permissão CREATE TABLE no banco de dados e a permissão ALTER no esquema no qual a tabela está sendo criada. A criação de um índice particionado requer a permissão ALTER na tabela ou exibição onde o índice está sendo criado. A criação de uma tabela ou um índice particionado requer uma das permissões adicionais a seguir:

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 e o esquema de partição estão sendo criados.
Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual a função e o esquema de partição estão sendo criados.

Criar uma tabela particionada em um grupo de arquivos usando o Transact-SQL

Se você não precisar executar independentemente operações de backup e restauração em grupos de arquivos, o particionamento de uma tabela usando um só grupo de arquivos simplificará o gerenciamento da tabela particionada ao longo do tempo.

Este exemplo é adequado para o Banco de Dados SQL do Azure, que não dá suporte para adicionar arquivos e grupos de arquivos. Há suporte para o particionamento de tabela no Banco de Dados SQL do Azure criando partições no grupo de arquivos PRIMARY. Para o SQL Server e a Instância Gerenciada de SQL do Azure, talvez você queira especificar um grupo de arquivos criado pelo usuário, dependendo do seu grupo de arquivos e práticas de gerenciamento de arquivos.

O exemplo percorre a criação de uma tabela particionada no SQL Server Management Studio (SSMS) usando Transact-SQL e atribui todas as partições ao grupo de arquivos PRIMARY. O exemplo:

Cria uma função de partição RANGE RIGHT chamada myRangePF1 com três valores de marco de delimitação usando o tipo de dados datetime2. Três valores de marco de delimitação resultarão em uma tabela particionada com quatro partições.
Cria um esquema de partição chamado myRangePS1 que usa a sintaxe ALL TO para atribuir todas as partições na função de partição myRangePF1 ao grupo de arquivos PRIMARY.
Cria uma tabela chamada PartitionTable no esquema de partição myRangePS1 especificando uma coluna chamada col1 como a coluna de particionamento.

No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.
Na barra Padrão, selecione Nova Consulta.
Copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Este exemplo cria uma função e um esquema de partição. Uma nova tabela é criada com o esquema de partição especificado no local de armazenamento.

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))  
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;  
GO  

CREATE PARTITION SCHEME myRangePS1  
    AS PARTITION myRangePF1  
    ALL TO ('PRIMARY') ;  
GO  

CREATE TABLE dbo.PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))  
    ON myRangePS1 (col1) ;  
GO

Criar uma tabela particionada em vários grupos de arquivos usando o Transact-SQL

Siga as etapas nesta seção para criar um ou mais grupos de arquivo, arquivos correspondentes e uma tabela particionada usando Transact-SQL no SSMS.

O SQL Server e a Instância Gerenciada de SQL do Azure dão suporte à criação de grupos de arquivos e arquivos. A Instância Gerenciada de SQL do Azure configura automaticamente o caminho para todos os arquivos de banco de dados adicionados, portanto, o comando ALTER DATABASE ADD FILE na Instância Gerenciada SQL do Azure não permite o parâmetro FILENAME. O Banco de Dados SQL do Azure dá suporte à criação de tabelas particionadas apenas no grupo de arquivos PRIMARY. Encontre código de exemplo para o Banco de Dados SQL do Azure em Criar uma tabela particionada em um grupo de arquivos usando Transact-SQL.

Execute o exemplo a seguir em um banco de dados vazio. O exemplo:

Adiciona quatro novos grupos de arquivos a um banco de dados.
Adiciona um arquivo a cada grupo de arquivos.
Cria uma função de partição RANGE RIGHT chamada myRangePF1 com três valores de limite que particionará uma tabela em quatro partições.
Cria um esquema de partição chamado myRangePS1 que aplica myRangePF1 aos quatro novos grupos de arquivos.
Cria uma tabela particionada chamada PartitionTable que usa myRangePS1 para particionar col1.

No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.
Na barra Padrão, selecione Nova Consulta.

Esse exemplo cria um banco de dados e o utiliza. Ele então cria novos grupos de arquivo, uma função de partição e um esquema de partição. Uma nova tabela é criada com o esquema de partição especificado no local de armazenamento. Copie e cole o exemplo a seguir na janela de consulta.

Se você estiver usando uma instância gerenciada, remova o parâmetro FILENAME e o valor associado do comando ALTER DATABASE ADD FILE. A instância gerenciada determinará o caminho do arquivo para você automaticamente.

Se você estiver usando uma instância do SQL Server, personalize o valor do parâmetro FILENAME para um local apropriado para sua instância.

Se desejar usar um banco de dados existente, remova o comando CREATE DATABASE e altere a instrução USE para o nome do banco de dados apropriado.

Selecione Executar.

CREATE DATABASE PartitionTest;
GO

USE PartitionTest;
GO

ALTER DATABASE PartitionTest  
ADD FILEGROUP test1fg;  
GO  
ALTER DATABASE PartitionTest  
ADD FILEGROUP test2fg;  
GO  
ALTER DATABASE PartitionTest  
ADD FILEGROUP test3fg;  
GO  
ALTER DATABASE PartitionTest  
ADD FILEGROUP test4fg;   

ALTER DATABASE PartitionTest   
ADD FILE   
(  
    NAME = partitiontest1,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest1.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test1fg;  
ALTER DATABASE PartitionTest   
ADD FILE   
(  
    NAME = partitiontest2,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest2.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test2fg;  
GO  
ALTER DATABASE PartitionTest   
ADD FILE   
(  
    NAME = partitiontest3,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest3.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test3fg;  
GO  
ALTER DATABASE PartitionTest   
ADD FILE   
(  
    NAME = partitiontest4,  
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\partitiontest4.ndf',  
    SIZE = 5MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP test4fg;  
GO  

CREATE PARTITION FUNCTION myRangePF1 (datetime2(0))  
    AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;  
GO  

CREATE PARTITION SCHEME myRangePS1  
    AS PARTITION myRangePF1  
    TO (test1fg, test2fg, test3fg, test4fg) ;  
GO  

CREATE TABLE PartitionTable (col1 datetime2(0) PRIMARY KEY, col2 char(10))  
    ON myRangePS1 (col1) ;  
GO

Particionar uma tabela com o SSMS

Siga as etapas nesta seção para opcionalmente criar grupos de arquivos e arquivos correspondentes e, em seguida, criar uma tabela particionada ou particionar uma tabela existente usando o Assistente para Criar Partição no SSMS (SQL Server Management Studio). O Assistente para Criar Partição está disponível no SSMS para SQL Server e Instância Gerenciada de SQL do Azure. Para o Banco de Dados SQL do Azure, confira Criar uma tabela particionada em um grupo de arquivos usando Transact-SQL.

Criar novos grupos de arquivos (opcional)

Se você quiser colocar sua tabela particionada em um ou mais novos grupos de arquivos, siga as etapas nesta seção. O SQL Server e a Instância Gerenciada de SQL do Azure dão suporte à criação de grupos de arquivos e arquivos. Para a Instância Gerenciada de SQL do Azure, o caminho para todos os arquivos criados será configurado automaticamente para você.

No Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados no qual você deseja criar uma tabela particionada e selecione Propriedades.
Na caixa de diálogo Propriedades do Banco de dados –database_name, em Selecionar uma página, selecione Grupos de Arquivos.
Em Linhas, selecione Adicionar. Na nova linha, digite o nome do grupo de arquivos.

Aviso

Ao especificar vários grupos de arquivos, você precisa sempre ter um grupo de arquivos extra, além do número de grupos de arquivos especificado para os valores de limite durante a criação de partições.
Continue adicionando linhas até que você tenha criado todos os grupos de arquivos para a tabela particionada.
Selecione OK.
Em Selecione uma página, selecione Arquivos.
Em Linhas, selecione Adicionar. Na nova linha, digite um nome de arquivo e selecione um grupo de arquivos.
Continue adicionando linhas até que tenha criado ao menos um arquivo para cada grupo de arquivos.

Criar uma tabela particionada

Como alternativa, expanda a pasta Tabelas e crie uma tabela como você faz normalmente. Para obter mais informações, confira Criar tabelas (Mecanismo de Banco de Dados). Outra opção é especificar uma tabela existente no próximo procedimento.
Clique com o botão direito do mouse na tabela que você deseja particionar, aponte para Armazenamento e selecione Criar partição....
No Assistente para Criar Partição, na página Bem-vindo ao Assistente para Criar Partição, selecione Avançar.
Na página Selecione uma Coluna de Particionamento , na grade Colunas de particionamento disponíveis , selecione a coluna em que deseja particionar sua tabela. Somente colunas com tipos de dados que possam ser usados para particionar dados serão exibidas na grade Colunas de particionamento disponíveis. Se você selecionar uma coluna computada como a coluna de particionamento, a coluna deverá ser criada como uma coluna persistente.

As opções que você tem para as colunas de particionamento e o intervalo de valores são determinadas, basicamente, pela extensão na qual seus dados podem ser agrupados de forma lógica. Por exemplo, você pode optar por dividir seus dados em agrupamentos lógicos por meses ou trimestres de um ano. As consultas que você planejar fazer nos seus dados determinarão se o agrupamento lógico é adequado para o gerenciamento de suas partições de tabela. Todos os tipos de dados são válidos para uso como colunas de particionamento, exceto 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.

As opções adicionais a seguir estão disponíveis nessa página:

Colocar essa tabela na tabela particionada selecionada
Permite selecionar uma tabela particionada que contenha dados relacionados para unir a essa tabela na coluna de particionamento. Tabelas com partições unidas nas colunas de particionamento costumam ser consultadas de forma mais eficiente.

Alinhamento de armazenamento de índices não exclusivos e índices exclusivos com uma coluna de partição indexada
Alinha todos os índices da tabela que são particionados com o mesmo esquema de partição. Quando uma tabela e seus índices são alinhados, você pode mover partições para dentro e para fora de tabelas particionadas com mais eficiência, pois seus dados são particionados do mesmo modo.

Depois de selecionar a coluna de particionamento e as outras opções, selecione Avançar.
Na página Selecione uma Função de Partição , em Selecione uma função de partição, selecione Nova função de partição ou em Função de partição existente. Se você escolher Nova função de partição, digite o nome da função. Se você escolher Função de partição existente, selecione o nome da função que você gostaria de usar na lista. A opção Função de partição existente não estará disponível se não houver outra função de partição no banco de dados.

Depois de concluir essa página, selecione Avançar.
Na página Selecione um Esquema de Partição , em Selecione um esquema de partição, selecione Novo esquema de partição ou Esquema de partição existente. Se você escolher Novo esquema de partição, digite o nome do esquema. Se você escolher Esquema de partição existente, selecione o nome do esquema que você gostaria de usar na lista. A opção Esquema de partição existente não estará disponível se não houver outro esquema de partição no banco de dados.

Depois de concluir essa página, selecione Avançar.
Na página Mapear Partições, em Intervalo, selecione Limite esquerdo ou Limite direito. O limite esquerdo especifica que o valor delimitador mais alto será incluído em uma partição. O limite direito especifica que o valor delimitador mais baixo será incluído em cada partição. Saiba mais sobre os intervalos direito e esquerdo na função de Partição.

Ao especificar vários pontos de marco de delimitação, você sempre deve inserir uma linha extra além das linhas que atribuem valores de marco de delimitação a um grupo de arquivos.

Na grade Selecione os grupos de arquivos e especifique os valores de limite , em Grupo de arquivos, selecione um grupo de arquivos no qual deseja particionar seus dados. Em Limite, digite o valor de limite para cada grupo de arquivos. Se quiser atribuir várias ou todas as partições ao mesmo grupo de arquivos, selecione o mesmo nome de grupo de arquivos para cada linha. Se você selecionar um grupo de arquivos em uma só linha e o valor de limite ficar vazio, a função de partição mapeará a tabela inteira ou o índice para uma só partição usando o nome da função de partição.

As opções adicionais a seguir estão disponíveis nessa página:

Definir Limites...
Abre a caixa de diálogo Definir Valores de Limite para selecionar os valores de limite e os intervalos de datas que deseja para suas partições. Essa opção estará disponível somente quando você tiver selecionado uma coluna de particionamento que contenha um dos seguintes tipos de dados: date, datetime, smalldatetime, datetime2ou datetimeoffset.

Estimar armazenamento
Calcula o número de linhas, o espaço necessário e o espaço disponível para armazenamento para cada grupo de arquivos especificado para as partições. Esses valores são exibidos na grade como valores somente leitura.

A caixa de diálogo Definir Valores de Limite permite as opções adicionais a seguir:

Data de início
Seleciona a data inicial para obter os valores de intervalo de suas partições.

Data de término
Seleciona a data de término para obter os valores de intervalo de suas partições. Se você selecionar Limite esquerdo na página Mapear Partições , essa data será o último valor de cada grupo de arquivos/partição. Se você selecionar Limite direito na página Mapear Partições , essa data será o primeiro valor do próximo ao último grupo de arquivos.

Intervalo de datas
Seleciona a granularidade de data ou incremento de valor de intervalo desejado para cada partição.

Depois de concluir essa página, selecione Avançar.
Na página Selecione uma Opção de Saída , especifique como você deseja preencher sua tabela particionada. Selecione Criar Script para criar um script SQL baseado nas páginas anteriores no assistente. Selecione Executar imediatamente para criar a nova tabela particionada depois de concluir todas as páginas restantes no assistente. Selecione Agenda para criar uma nova tabela particionada em um momento predeterminado no futuro.

Se você selecionar Criar script, as opções a seguir estarão disponíveis em Opções de script:

Script para arquivo
Gera o script como um arquivo .sql. Insira o nome do arquivo e a localização na caixa Nome do arquivo ou selecione Procurar para abrir a caixa de diálogo Localização do Arquivo de Script. Em Salvar Como, selecione Texto Unicode ou Texto ANSI.

Script para Área de Transferência
Salva o script na área de transferência.

Script para Nova Janela de Consulta
Gera o script para uma nova janela do Editor de Consultas. Essa é a seleção padrão.

Se você selecionar Agenda, selecione Alterar agenda.
1. Na caixa de diálogo Nova Agenda de Trabalho, na caixa Nome, digite o nome da agenda de trabalho.
2. Na lista Tipo de Agenda , selecione o tipo de agenda:
  - Iniciar automaticamente quando o SQL Server Agent for iniciado
  - Iniciar sempre que as CPUs estiverem ociosas
  - Recorrente. Selecione essa opção se a sua nova tabela particionada for atualizada com novas informações regularmente.
  - Uma vez. Essa é a seleção padrão.
3. Marque ou desmarque a caixa de seleção Habilitado para habilitar ou desabilitar a agenda.
4. Se você selecionar Recorrente:
  1. Em Frequência, na lista Ocorre , especifique a frequência de ocorrência:
    - Se você selecionar Diário, na caixa Ocorre periodicamente a cada , digite a frequência com que a agenda de trabalho se repete em dias.
    - Se você selecionar Semanal, na caixa Ocorre periodicamente a cada , digite a frequência com que a agenda de trabalho se repete em semanas. Selecione o dia ou os dias da semana em que a agenda de trabalho é executada.
    - Se você selecionar Mensalmente, selecione Dia ou O.
      - Se você selecionar Dia, digite o dia do mês que você deseja que a agenda de trabalho seja executada e a frequência com que a agenda de trabalho se repete em meses. Por exemplo, se desejar que a agenda de trabalho seja executada no 15º dia do mês a cada dois meses, selecione Dia e digite "15" na primeira caixa e "2" na segunda caixa. O maior número permitido na segunda caixa é "99".
      - Se você selecionar O, selecione o dia específico da semana no mês que você deseja que a agenda de trabalho seja executada e a frequência com que a agenda de trabalho se repete em meses. Por exemplo, se você desejar que a agenda de trabalho seja executada no último dia da semana do mês a cada dois meses, selecione Dia, selecione último na primeira lista e dia da semana na segunda lista e depois digite “2” na última caixa. Você também pode selecionar primeiro, segundo, terceiro ou quarto, bem como dias específicos da semana (por exemplo: domingo ou quarta-feira) nas primeiras duas listas. O maior número permitido na última caixa é "99".
  2. Em Frequência diária, especifique a frequência com que a agenda de trabalho se repete no dia da execução da agenda de trabalho:
    - Se você selecionar Ocorre uma vez às, digite a hora específica do dia em que a agenda de trabalho deve ser executada na caixa Ocorre uma vez às. Digite a hora, os minutos e os segundos do dia, bem como AM ou PM.
    - Se você selecionar Ocorre a cada, especifique a frequência com que a agenda de trabalho é executada durante o dia escolhido em Frequência. Por exemplo, se você desejar que o agendamento de trabalho se repita a cada 2 horas durante o dia em que é executado, selecione Ocorre a cada, digite "2" na primeira caixa e selecione hora(s) na lista. Nessa lista, você pode selecionar também minuto(s) e segundo(s). O maior número permitido na primeira caixa é "100".
      
      Na caixa Iniciando às , digite a hora em que a agenda de trabalho deve começar a ser executada. Na caixa Terminando às , digite a hora em que a agenda de trabalho deve parar de se repetir. Digite a hora, os minutos e os segundos do dia, bem como AM ou PM.
  3. Em Duração, em Data de início, digite a data que você deseja que a agenda de trabalho inicie a execução. Selecione Data de término ou Nenhuma data de término para indicar quando a execução da agenda de trabalho deve parar. Se você selecionar Data de término, digite a data em que você deseja que a execução da agenda de trabalho pare.
5. Se você selecionar Uma Vez, em Ocorrência única, na caixa Data , insira a data em que o agendamento de trabalho será executado. Na caixa Hora , digite a hora em que a agenda de trabalho será executada. Digite a hora, os minutos e os segundos do dia, bem como AM ou PM.
6. Em Resumo, em Descrição, verifique se todas as configurações da agenda de trabalho estão corretas.
7. Selecione OK.
Depois de concluir essa página, selecione Avançar.
Na página Resumo da Revisão , em Examinar as seleções, expanda todas as opções disponíveis para verificar se todas as configurações de partição estão corretas. Se tudo estiver como esperado, selecione Concluir.
Na página Progresso do Assistente para Criar Partição , monitore as informações de status das ações do Assistente para Criar Partição. Dependendo das opções selecionadas no assistente, a página de progresso pode conter uma ou várias ações. A caixa superior exibe o status geral do assistente e o número de mensagens de status, erro e aviso que ele recebeu.

As opções a seguir estão disponíveis na página Progresso do Assistente para Criar Partição :

Detalhes
Fornece a ação, status e qualquer mensagem retornada pela ação executada pelo assistente.

Ação
Especifica o tipo e o nome de cada ação.

Status
Indica se a ação do assistente retornou como um todo o valor de Êxito ou de Falha.

Mensagem
Fornece qualquer mensagem de aviso ou erro retornada pelo processo.

Report
Cria um relatório contendo os resultados do Assistente para Criar Partição. As opções são Exibir Relatório, Salvar Relatório no Arquivo, Copiar Relatório na Área de Transferênciae Enviar Relatório como Email.

Exibir Relatório
Abre a caixa de diálogo Exibir Relatório , que contém um relatório de texto do progresso do Assistente para Criar Partições.

Salvar Relatório no Arquivo
Abre a caixa de diálogo Salvar Relatório Como.

Copiar Relatório na Área de Transferência
Copia os resultados do relatório de progresso do assistente na Área de transferência.

Enviar Relatório como Email
Copia os resultados do relatório de progresso do assistente para uma mensagem de email.

Quando concluir, selecione Fechar.

O Assistente para Criar Partição cria a função e o esquema de partição e aplica o particionamento à tabela especificada. Para verificar o particionamento da tabela, no Pesquisador de Objetos, clique com o botão direito do mouse na tabela e selecione Propriedades. Selecione a página Armazenamento. A página exibe informações como o nome da função e do esquema de partição e o número de partições.

Consultar metadados de tabelas e índices particionados

Você pode consultar metadados para determinar se uma tabela está particionada, os pontos de marco de delimitação para uma tabela particionada, a coluna de particionamento para uma tabela particionada, o número de linhas em cada partição e se a compactação de dados foi implementada em partições.

Determinar se uma tabela é particionada

A consulta a seguir retorna uma ou mais linhas se a tabela PartitionTable estiver particionada ou se índices não clusterizados na tabela estiverem particionados. Se a tabela não estiver particionada e nenhum índice não clusterizado na tabela for particionado, nenhuma linha será retornada.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, *   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
JOIN sys.partition_schemes ps   
    ON i.data_space_id = ps.data_space_id   
WHERE t.name = 'PartitionTable';   
GO

Determinar os valores de limite para uma tabela particionada

A consulta a seguir retorna os valores de limite para cada partição na tabela PartitionTable.

A consulta usará a coluna type em sys.indexes para retornar apenas informações para o índice clusterizado da tabela ou para a tabela base se a tabela for um heap. Para incluir índices não clusterizados particionados nos resultados da consulta, remova ou comente AND i.type <= 1 para excluí-lo da consulta.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName, 
    p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, 
    r.boundary_id, r.value AS BoundaryValue   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE 
    t.name = 'PartitionTable' 
    AND i.type <= 1  
ORDER BY SchemaName, t.name, i.name, p.partition_number;

Determinar a coluna de partição para uma tabela particionada

A consulta a seguir retorna o nome da coluna de particionamento para a tabela PartitionTable.


SELECT   
    t.[object_id] AS ObjectID
    , SCHEMA_NAME(t.schema_id) AS SchemaName
    , t.name AS TableName   
    , ic.column_id AS PartitioningColumnID   
    , c.name AS PartitioningColumnName
    , i.name as IndexName
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] <= 1 -- clustered index or a heap   
JOIN sys.partition_schemes AS ps   
    ON ps.data_space_id = i.data_space_id   
JOIN sys.index_columns AS ic   
    ON ic.[object_id] = i.[object_id]   
    AND ic.index_id = i.index_id   
    AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c   
    ON t.[object_id] = c.[object_id]   
    AND ic.column_id = c.column_id   
WHERE t.name = 'PartitionTable';   
GO

Determinar as linhas que descrevem o intervalo possível de valores em cada partição

A consulta a seguir retorna as linhas por partição da tabela PartitionTable e uma descrição dos “operadores de comparação” para a função de partição em uso. Consulta original fornecida por Kalen Delaney.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName, 
    p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue, 
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A' 
ELSE
    CASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>=' 
        WHEN f.boundary_value_on_right = 0 THEN '>' 
        ELSE '>=' 
    END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' + 
        CASE f.boundary_value_on_right WHEN 1 THEN 'and <' 
                ELSE 'and <=' END 
        + ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value') 
END AS TextComparison
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
LEFT JOIN sys.partition_range_values AS rv
    ON f.function_id = rv.function_id
    AND p.partition_number = rv.boundary_id     
LEFT JOIN sys.partition_range_values AS rv2
    ON f.function_id = rv2.function_id
    AND p.partition_number - 1= rv2.boundary_id
WHERE 
    t.name = 'PartitionTable'
    AND i.type <= 1 
ORDER BY t.name, p.partition_number;

A coluna TextComparison descreve o intervalo possível de valores em cada partição com base na definição da função de partição. Aqui está uma exibição dos resultados de exemplo da consulta:

SchemaName	TableName	IndexName	PartitionNumber	PartitionFunctionName	rows	Valor do marco de delimitação	TextComparison
dbo	PartitionTable	PK_PartitionTable	1	PFTest	0	2022-03-01 00:00:00.000	>= Valor mínimo e < 1 de março de 2022 12h
dbo	PartitionTable	PK_PartitionTable	2	PFTest	2	2022-04-01 00:00:00.000	>= 1 de março de 2022 12h e < 1 de abril de 2022 12h
dbo	PartitionTable	PK_PartitionTable	3	PFTest	1	2022-05-01 00:00:00.000	>= 1 de abril de 2022 12h e < 1 de maio de 2022 12h
dbo	PartitionTable	PK_PartitionTable	4	PFTest	0	2022-06-01 00:00:00.000	>= 1 de maio de 2022 12h e < 1 de junho de 2022 12h
dbo	PartitionTable	PK_PartitionTable	5	PFTest	1	2022-07-01 00:00:00.000	>= 1 de junho de 2022 12h e < 1 de julho de 2022 12h
dbo	PartitionTable	PK_PartitionTable	6	PFTest	0	NULL	>= 1 de julho de 2022 12h e < valor máximo

Limitações

Saiba mais sobre limitações, bem como considerações de desempenho para particionamento em Limitações

Próximas etapas

Saiba mais sobre conceitos relacionados nos seguintes artigos: