Creación de tablas e índices con particiones

Artículo
05/30/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

Puede crear una tabla o un índice con particiones en SQL Server, Azure SQL Database y Azure SQL Managed Instance mediante SQL Server Management Studio o Transact-SQL. Los datos en tablas e índices con particiones se dividen horizontalmente en unidades que pueden propagarse por más de un grupo de archivos en una base de datos, o almacenarse en un único grupo de archivos. Las particiones pueden hacer que las tablas y los índices grandes sean más escalables y fáciles de administrar.

La creación de una tabla o índice con particiones tiene lugar normalmente en tres o cuatro partes:

Opcionalmente, cree un grupo o grupos de archivos y los archivos de datos correspondientes que contendrán las particiones especificadas por el esquema de partición. La principal razón para colocar las particiones en varios grupos de archivos es garantizar que se puedan realizar operaciones de copia de seguridad y restauración en grupos de archivos de forma independiente. Si no es necesario, puede optar por asignar todas las particiones a un único grupo de archivos, mediante un grupo de archivos existente, como PRIMARY, o un nuevo grupo de archivos con archivos de datos relacionados. En casi todos los escenarios, obtendrá todas las ventajas de crear particiones tanto si usa varios grupos de archivos como si no.
Cree una función de partición que asigna las filas de una tabla o un índice a particiones según los valores de una columna especificada. Puede usar una sola función de partición para crear particiones de varios objetos.
Cree un esquema de partición que asigna las particiones de una tabla con particiones o índice a un grupo de archivos o a múltiples grupos de archivos. Puede usar un único esquema de partición para crear particiones de varios objetos.
Cree o modifique una tabla o un índice y especifique el esquema de partición como ubicación de almacenamiento, junto con la columna que servirá como columna de partición.

Nota:

La creación de particiones es totalmente compatible con Azure SQL Database. Dado que solo se admite el grupo de archivos PRIMARY en Azure SQL Database, todas las particiones deben colocarse en el grupo de archivos PRIMARY.

La creación de particiones de tablas también está disponible en grupos de SQL dedicados en Azure Synapse Analytics, con algunas diferencias de sintaxis. Obtenga más información en Creación de particiones de tablas en un grupo de SQL dedicado.

Permisos

La creación de una tabla con particiones requiere el permiso CREATE TABLE en la base de datos y el permiso ALTER en el esquema en el que se crea la tabla. Crear un índice con particiones requiere el permiso ALTER en la tabla o vista donde se crea el índice. Crear una tabla o índice con particiones requiere alguno de los permisos adicionales siguientes:

Permiso ALTER ANY DATASPACE. De forma predeterminada, este permiso corresponde a los miembros del rol fijo de servidor sysadmin y a los roles fijos de base de datos db_owner y db_ddladmin .
Permiso CONTROL o ALTER en la base de datos en la que se está creando la función de partición y el esquema de partición.
Permiso CONTROL SERVER o ALTER ANY DATABASE en el servidor de la base de datos en la que se está creando la función de partición y el esquema de partición.

Creación de una tabla con particiones en un grupo de archivos mediante Transact-SQL

Si no necesita realizar operaciones de copia de seguridad y restauración de forma independiente en grupos de archivos, la creación de particiones de una tabla mediante un único grupo de archivos simplifica la administración de la tabla con particiones a lo largo del tiempo.

Este ejemplo es adecuado para Azure SQL Database, que no admite la adición de archivos y grupos de archivos. La creación de particiones de tablas se admite en Azure SQL Database mediante la creación de particiones en el grupo de archivos PRIMARY. Para SQL Server y Azure SQL Managed Instance, puede especificar un grupo de archivos creado por el usuario, en función de los procedimientos de administración de archivos y grupos de archivos.

En el ejemplo se describe cómo crear una tabla con particiones en SQL Server Management Studio (SSMS) mediante Transact-SQL y se asignan todas las particiones al grupo de archivos PRIMARY. El ejemplo:

Crea una función de partición RANGE RIGHT denominada myRangePF1 con tres valores de límite mediante el tipo de datos datetime2. Tres valores de límite darán lugar a una tabla con particiones con cuatro particiones.
Crea un esquema de partición denominado myRangePS1 que usa la sintaxis ALL TO para asignar todas las particiones de la función de partición myRangePF1 al grupo de archivos PRIMARY.
Crea una tabla denominada PartitionTable, en el esquema de partición myRangePS1 especificando col1 como columna de partición.

En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra Estándar, seleccione Nueva consulta.
Copie y pegue el ejemplo siguiente en la ventana de consulta y seleccione Ejecutar. En este ejemplo, se crean una función de partición y un esquema de partición. Se crea una nueva tabla con el esquema de partición especificado como ubicación de almacenamiento.

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

Creación de una tabla con particiones en múltiples grupos de archivos mediante Transact-SQL

Siga los pasos de esta sección para crear uno o más grupos de archivos, los archivos correspondientes y una tabla con particiones mediante Transact-SQL en SSMS.

Tanto SQL Server como Azure SQL Managed Instance admiten la creación de grupos de archivos y archivos. Azure SQL Managed Instance configura automáticamente la ruta de acceso para todos los archivos de base de datos agregados, por lo que el comando ALTER DATABASE ADD FILE de Azure SQL Managed Instance no permite el parámetro FILENAME. Azure SQL Database solo admite la creación de tablas con particiones en el grupo de archivos PRIMARY. Encontrará código de ejemplo para Azure SQL Database en Creación de una tabla con particiones en un grupo de archivos mediante Transact-SQL.

Ejecute el ejemplo siguiente en una base de datos vacía. El ejemplo:

Agrega cuatro nuevos grupos de archivos a una base de datos.
Agrega un archivo a cada grupo de archivos.
Crea una función de partición RANGE RIGHT denominada myRangePF1 con tres valores de límite que dividirán una tabla en cuatro particiones.
Crea un esquema de partición denominado myRangePS1 que aplica myRangePF1 a los cuatro grupos de archivos nuevos.
Crea una tabla con particiones denominada PartitionTable que usa myRangePS1 para crear particiones de col1.

En el Explorador de objetos, conéctese a una instancia del Motor de base de datos.
En la barra de herramientas Estándar, seleccione Nueva consulta.

En este ejemplo se crea y se usa una nueva base de datos. Después, se crean nuevos grupos de archivos, una función de partición y un esquema de partición. Se crea una nueva tabla con el esquema de partición especificado como ubicación de almacenamiento. Copie y pegue el ejemplo siguiente en la ventana de consulta.

Si usa una instancia administrada, quite el parámetro FILENAME y el valor asociado del comando ALTER DATABASE ADD FILE. La instancia administrada determinará automáticamente la ruta de acceso del archivo.

Si usa una instancia de SQL Server, personalice el valor del parámetro FILENAME en una ubicación adecuada para la instancia.

Si desea usar una base de datos existente, quite el comando CREATE DATABASE y modifique la instrucción USE al nombre de la base de datos correspondiente.

Seleccione Ejecutar.

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

Creación de particiones de una tabla con SSMS

Siga los pasos de esta sección para crear opcionalmente grupos de archivos y archivos correspondientes y, después, crear una tabla con particiones o crear una tabla existente mediante el Asistente para crear particiones en SQL Server Management Studio (SSMS). El Asistente para crear particiones está disponible en SSMS para SQL Server y Azure SQL Managed Instance. Para Azure SQL Database, consulte Creación de una tabla con particiones en un grupo de archivos mediante Transact-SQL.

Creación de nuevos grupos de archivos (opcional)

Si desea colocar la tabla con particiones en uno o varios grupos de archivos nuevos, siga los pasos descritos en esta sección. Tanto SQL Server como Azure SQL Managed Instance admiten la creación de grupos de archivos y archivos. Para Azure SQL Managed Instance, la ruta de acceso de los archivos creados se configurará automáticamente.

En el Explorador de objetos, haga clic con el botón derecho en la base de datos en la que quiere crear una tabla con particiones y seleccione Propiedades.
En el cuadro de diálogo Propiedades de la base de datos -nombre_base_de_datos, en Seleccionar una página, haga clic en Grupos de archivos.
En Filas, seleccione Agregar. En la nueva fila, escriba el nombre del grupo de archivos.

Advertencia

Al especificar varios grupos de archivos, siempre debe haber uno adicional, además de los que especifique para los valores de límite en el momento de crear las particiones.
Continúe agregando filas hasta que haya creado todos los grupos de archivos de la tabla o tablas con particiones.
Seleccione Aceptar.
En Seleccionar una página, seleccione Archivos.
En Filas, seleccione Agregar. En la nueva fila, escriba un nombre de archivo y seleccione un grupo de archivos.
Continúe agregando filas hasta que haya creado al menos un archivo para cada grupo de archivos.

Creación de una tabla con particiones

Opcionalmente, expanda la carpeta de Tablas y cree una tabla como haría normalmente. Para obtener más información, vea Crear tablas (motor de base de datos). O bien, puede especificar una tabla existente en el paso siguiente.
Haga clic con el botón derecho en la tabla donde quiere crear las particiones, seleccione Almacenamiento y, después, seleccione Crear partición....
En el Asistente para la creación de particiones, en la página Asistente para la creación de particiones, seleccione Siguiente.
En la página Seleccionar una columna de particionamiento , en la cuadrícula de Columnas de particionamiento disponibles , seleccione la columna en la que desea crear particiones de la tabla. En la cuadrícula Columnas de particionamiento disponibles solo se mostrarán las columnas con tipos de datos que se pueden utilizar para crear particiones de los datos. Si selecciona una columna calculada como columna de partición, la columna se debe crear como una columna PERSISTED.

Las opciones de que dispone para la columna de partición y el intervalo de valores vienen determinados, principalmente, por el grado en que los datos se pueden agrupar de un modo lógico. Por ejemplo, puede decidir dividir los datos en agrupaciones lógicas por meses o trimestres del año. Las consultas que planea realizar en los datos determinarán si esta agrupación lógica es adecuada para administrar las particiones de la tabla. Todos los tipos de datos son válidos para su uso como columnas de partición, excepto text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), tipos de datos de alias o tipos de datos definidos por el usuario CLR.

En esta página están disponibles las opciones siguientes:

Colocar esta tabla en la tabla con partición seleccionada
Permite seleccionar una tabla con particiones que contenga los datos relacionados para combinar con esta tabla en la columna de partición. Las tablas con particiones combinadas en las columnas de partición suelen dar lugar a consultas más eficientes.

Alinear almacenamiento de índices no únicos e índices únicos con una columna de partición indizada
Alinea todos los índices de la tabla que tiene particiones con el mismo esquema de partición. Cuando una tabla y sus índices están alineados, puede mover las particiones dentro y fuera de las tablas con particiones de forma más eficaz, porque las particiones de los datos se realizan de la misma manera.

Después de seleccionar la columna de partición y otras opciones, seleccione Siguiente.
En la página Seleccionar una función de partición, en Seleccionar una función de partición, seleccione Nueva función de partición o Función de partición existente. Si elige Nueva función de partición, escriba el nombre de la función. Si elige Función de partición existente, seleccione el nombre de la función que quiera usar en la lista. La opción Función de partición existente no estará disponible si no hay otras funciones de partición en la base de datos.

Después de completar esta página, seleccione Siguiente.
En la página Seleccionar un esquema de partición , en Seleccionar un esquema de partición, seleccione Nuevo esquema de partición o Esquema de partición existente. Si elige Nuevo esquema de partición, escriba el nombre del esquema. Si elige Esquema de partición existente, seleccione el nombre del esquema que quiera usar en la lista. La opción Esquema de partición existente no estará disponible si no hay otros esquemas de partición en la base de datos.

Después de completar esta página, seleccione Siguiente.
En la página Particiones de mapa, en Intervalo, seleccione Límite izquierdo o Límite derecho. El límite izquierdo especifica que el valor de límite más alto se incluirá dentro de una partición. El límite derecho especifica que el valor de límite más bajo se incluirá en cada partición. Obtenga más información sobre los intervalos derecho e izquierdo en Función de partición.

Al especificar varios puntos de límite, siempre debe escribir una fila adicional además de las filas que asignan valores de límite a un grupo de archivos.

En la cuadrícula Seleccione grupos de archivos y especifique valores límite , debajo de Grupo de archivos, seleccione el grupo de archivos en el que desea crear particiones de los datos. Debajo de Límite, escriba el valor límite para cada grupo de archivos. Si desea asignar varias o todas las particiones al mismo grupo de archivos, seleccione el mismo nombre de grupo de archivos para cada fila. Si selecciona un grupo de archivos o una única fila y el valor límite está vacío, la función de partición asigna la tabla o el índice completos a una sola partición y usa el nombre de la función de partición.

En esta página están disponibles las opciones siguientes:

Establecer límites...
Abre el cuadro de diálogo Establecer valores límite para seleccionar los valores límite y los rangos de fechas que desea para las particiones. Esta opción solo está disponible cuando ha seleccionado una columna de partición que contiene uno de los tipos de datos siguientes: date, datetime, smalldatetime, datetime2o datetimeoffset.

Estimar almacenamiento
Calcula el número de filas, el espacio necesario y el espacio disponible para el almacenamiento de cada grupo de archivos especificado para las particiones. Estos valores se muestran en la cuadrícula como valores de solo lectura.

El cuadro de diálogo Establecer valores límite admite las opciones adicionales siguientes:

Fecha de inicio
Selecciona la fecha de inicio para los valores de rango de las particiones.

Fecha de finalización
Selecciona la fecha de finalización para los valores de rango de las particiones. Si ha seleccionado la opción Límite izquierdo en la página Asignar particiones , esta fecha será el último valor para cada grupo de archivos o partición. Si ha seleccionado la opción Límite derecho en la página Asignar particiones , esta fecha será el primer valor en el grupo de archivos siguiente al último.

Intervalo de fechas
Selecciona la granularidad de fechas o el incremento de los valores de rango que desea para cada partición.

Después de completar esta página, seleccione Siguiente.
En la página Seleccionar la opción de salida , especifique cómo desea completar la tabla con particiones. Seleccione Crear script para crear un script SQL basado en las páginas anteriores del asistente. Seleccione Ejecutar inmediatamente para crear la nueva tabla con particiones después de completar todas las páginas restantes del asistente. Seleccione Programar para crear la tabla con particiones en un momento predeterminado en el futuro.

Si selecciona Crear script, las opciones siguientes están disponibles debajo de Opciones de script:

Generar script en archivo
Genera el script como un archivo .sql. Escriba un nombre de archivo y una ubicación en el cuadro de diálogo Nombre de archivo, o bien seleccione Examinar para abrir Ubicación del archivo de script. En Guardar como, seleccione Texto Unicode o Texto ANSI.

Generar script en Portapapeles
Guarda el script en el Portapapeles.

Generar script en la ventana Nueva consulta
Genera el script en una nueva ventana del Editor de consultas. Esta es la selección predeterminada.

Si selecciona Programar, seleccione Cambiar programación.
1. En el cuadro de diálogo Nueva programación de trabajo, en el cuadro Nombre, escriba el nombre de la programación de trabajo.
2. En la lista Tipo de programación , seleccione el tipo de la programación:
  - Iniciar automáticamente al iniciar el Agente SQL Server.
  - Iniciar al quedar inactivas las CPU
  - Periódica. Seleccione esta opción si la nueva tabla con particiones se actualiza con la nueva información de forma periódica.
  - Una vez. Esta es la selección predeterminada.
3. Active o desactive la casilla Habilitado para habilitar o deshabilitar la programación.
4. Si selecciona Periódica:
  1. En Frecuencia, en la lista Sucede , especifique la frecuencia de repetición:
    - Si selecciona Diario, en el cuadro Se repite cada , escriba la frecuencia con que se repite la programación de trabajo en días.
    - Si selecciona Semanal, en el cuadro Se repite cada , escriba la frecuencia con que se repite la programación de trabajo en semanas. Seleccione el día o días de la semana en que se ejecuta la programación de trabajo.
    - Si selecciona Mensual, seleccione Día o El.
      - Si selecciona Día, especifique la fecha del mes que desea que se ejecute la programación de trabajo y con qué frecuencia debe repetirse la programación de trabajo en meses. Por ejemplo, si quiere que la programación de trabajo se ejecute el decimoquinto día de cada mes, seleccione Día y escriba "15" en el primer cuadro y "2" en el segundo. El mayor número permitido en la segunda casilla es "99".
      - Si selecciona El, seleccione el día concreto de la semana del mes en que desea que se ejecute la programación de trabajo y con qué frecuencia debe repetirse la programación de trabajo en meses. Por ejemplo, si quiere que la programación de trabajo se ejecute el último día de la semana de cada mes, seleccione Día, seleccione último en la primera lista y día de la semana en la segunda y, después, escriba "2" en el último cuadro. En las primeras dos listas, también puede seleccionar primero, segundo, terceroo cuarto, así como días de la semana concretos (por ejemplo: domingo o miércoles). El mayor número permitido en la última casilla es "99".
  2. Debajo de Frecuencia diaria, especifique la frecuencia con que se repite la programación de trabajo en el día en que se ejecuta:
    - Si selecciona Sucede una vez a las, escriba la hora concreta en que debe ejecutarse la programación de trabajo en el cuadro Sucede una vez a las . Especifique la hora, minuto y segundo del día, así como a.m. o p.m.
    - Si selecciona Sucede cada, especifique la frecuencia con que se ejecuta la programación de trabajo durante el día seleccionado en Frecuencia. Por ejemplo, si quiere que la programación de trabajo se repita cada dos horas al día cuando se ejecuta, seleccione Sucede cada, escriba "2" en el primer cuadro y, después, seleccione horas en la lista. En esta lista también puede seleccionar minutos y segundos. El mayor número permitido en la primera casilla es "100".
      
      En el cuadro A partir de , especifique la hora en que la programación de trabajo debe iniciar su ejecución. En el cuadro Finaliza , especifique la hora en que la programación de trabajo debe dejar de repetirse. Especifique la hora, minuto y segundo del día, así como a.m. o p.m.
  3. Debajo de Duración, en Fecha de inicio, escriba la fecha en que desea que la programación de trabajo inicie su ejecución. Seleccione Fecha de finalización o Sin fecha de finalización para indicar cuándo se debe detener la ejecución de la programación de trabajo. Si selecciona Fecha de finalización, escriba la fecha en que desea que deje de ejecutarse la programación de trabajo.
5. Si selecciona Una vez, debajo de Única repetición, en el cuadro Fecha , escriba la fecha en que se ejecutará la programación de trabajo. En el cuadro Hora , especifique la hora a la que se ejecutará la programación de trabajo. Especifique la hora, minuto y segundo del día, así como a.m. o p.m.
6. Debajo de Resumen, en Descripción, compruebe que todos los valores de la programación de trabajo son correctos.
7. Seleccione Aceptar.
Después de completar esta página, seleccione Siguiente.
En la página Revisar resumen , debajo de Revisar opciones seleccionadas, expanda todas las opciones disponibles para comprobar que todos los valores de la partición son correctos. Si todo está como se esperaba, seleccione Finalizar.
En la página Progreso del Asistente para la creación de particiones , supervise la información de estado sobre las acciones del Asistente para la creación de particiones. Según las opciones que se seleccionen en el asistente, la página de progreso puede contener una o varias acciones. El cuadro superior muestra el estado general del asistente y el número de mensajes de estado, error y advertencia que ha recibido.

Las siguientes opciones están disponibles en la página Progreso del Asistente para la creación de particiones :

Detalles
Proporciona la acción, el estado y los mensajes devueltos por la acción llevada a cabo por el asistente.

Acción
Especifica el tipo y el nombre de cada acción.

Estado
Indica si la acción del asistente como conjunto ha devuelto el valor Correcto o Error.

Mensaje
Proporciona los mensajes de error o de advertencia devueltos por el proceso.

Report
Crea un informe que contiene los resultados del Asistente para la creación de particiones. Las opciones son Ver informe, Guardar informe en archivo, Copiar informe al Portapapelesy Enviar informe como correo electrónico.

Ver informe
Abre el cuadro de diálogo Ver informe , que contiene un informe de texto del progreso del Asistente para la creación de particiones.

Guardar informe en archivo
Abre el cuadro de diálogo Guardar informe como .

Copiar informe al Portapapeles
Copia los resultados del informe de progreso del asistente al Portapapeles.

Enviar informe como correo electrónico
Copia los resultados del informe de progreso del asistente en un mensaje de correo electrónico.

Cuando haya finalizado, seleccione Cerrar.

El Asistente para la creación de particiones crea la función y el esquema de partición y aplica las particiones a la tabla especificada. Para comprobar las particiones de tabla, en el Explorador de objetos, haga clic con el botón derecho en la tabla y seleccione Propiedades. Seleccione la página Almacenamiento. La página muestra información como el nombre de la función y el esquema de partición y el número de particiones.

Consulta de metadatos de tablas e índices con particiones

Puede consultar metadatos para determinar si una tabla tiene particiones, los puntos de límite de una tabla con particiones, la columna de creación de particiones para una tabla con particiones, el número de filas de cada partición y si se ha implementado la compresión de datos en las particiones.

Determinación de si se crean particiones de una tabla

La consulta siguiente devuelve una o varias filas si la tabla PartitionTable tiene particiones o si se particionan índices no agrupados en la tabla. Si la tabla no tiene particiones y no hay índices no agrupados en la tabla particionados, no se devuelve ninguna fila.

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

Determinación de los valores de límite para una tabla con particiones

La consulta siguiente devuelve los valores de límite para cada partición de la tabla PartitionTable .

La consulta usa la columna type en sys.indexes para devolver solo información para el índice agrupado de la tabla o para la tabla base si la tabla es un montón. Para incluir los índices no agrupados con particiones en los resultados de la consulta, quite o convierta en comentario AND i.type <= 1 de la 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;

Determinación de la columna de partición de una tabla con particiones

La consulta siguiente devuelve el nombre de la columna de partición de la tabla 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

Determinación de las filas que describen el intervalo posible de valores en cada partición

La consulta siguiente devuelve las filas por partición de la tabla PartitionTable y una descripción de los operadores de comparación para la función de partición en uso. Original de consulta proporcionado por 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;

En la columna TextComparison se describe el posible intervalo de valores de cada partición en función de la definición de la función de partición. Esta es una vista de los resultados de ejemplo de la consulta:

SchemaName	TableName	IndexName	PartitionNumber	PartitionFunctionName	rows	BoundaryValue	TextComparison
dbo	PartitionTable	PK_PartitionTable	1	PFTest	0	2022-03-01 00:00:00.000	>= Valor mínimo y < 1 de marzo de 2022 12:00 a. m.
dbo	PartitionTable	PK_PartitionTable	2	PFTest	2	2022-04-01 00:00:00.000	>= 1 de marzo de 2022 12:00 a. m. y < 1 de abril de 2022 12:00 a. m.
dbo	PartitionTable	PK_PartitionTable	3	PFTest	1	2022-05-01 00:00:00.000	>= 1 de abril de 2022 12:00 a. m. y < 1 de mayo de 2022 12:00 a. m.
dbo	PartitionTable	PK_PartitionTable	4	PFTest	0	2022-06-01 00:00:00.000	>= 1 de mayo de 2022 12:00 a. m. y < 1 de junio de 2022 12:00 a. m.
dbo	PartitionTable	PK_PartitionTable	5	PFTest	1	2022-07-01 00:00:00.000	>= 1 de junio de 2022 12:00 a. m. y < 1 de julio de 2022 12:00 a. m.
dbo	PartitionTable	PK_PartitionTable	6	PFTest	0	NULL	>= 1 de julio de 2022 12:00AM y < Valor máximo

Limitaciones

Obtenga información sobre las limitaciones, así como las consideraciones de rendimiento para la creación de particiones en Limitaciones

Pasos siguientes

Obtenga más información sobre los conceptos relacionados en los artículos siguientes: