[Comunicación] Resumen de tablas particionadas en SQL Server

Documentación oficial

Pasos de alto nivel para crear una tabla de particiones

1. Crear un grupo de archivos (similar a Oracle, espacio de tablas), por supuesto, no puedes crearlo, puedes poner todas las particiones en un solo grupo de archivos

2. Establecer una función de partición y distribuir los datos según el rango

3. Establece un esquema de particiones, asocia la función de partición, y también asocia el grupo de archivos; la función de partición divide los datos en varios rangos, necesitas asociar varios grupos de archivos, por supuesto, también puedes poner estos rangos de particiones en el mismo grupo de archivos

4. Crear una tabla y asociar el esquema de zonificación

Un bug encontrado

Cuando haces clic derecho directamente en la tabla para exportar la estructura de la tabla, no puedes exportar la información de la partición, y solo puedes exportar la información de la tabla haciendo clic derecho en el script database-task-generate

Algunas conclusiones de la tabla de particiones:

1. Los campos particionados no necesitan necesariamente ser indexados

2. Los campos particionados pueden crearse como índices agrupados o sin clústeres

3. Los campos particionados, ya sean índices agrupados o sin clusters, se reconstruyen en índices agrupados sin esquemas de partición asociados, y la tabla particionada se convierte en una tabla no particionada

4. Convierte una tabla ordinaria en una tabla particionada, simplemente crea un índice agrupado en la tabla y utiliza el esquema de partición sobre el índice agrupado. Por ejemplo, cuando el campo 1 de una tabla no particionada crea un índice agrupado y se asocia a un esquema de partición, la tabla se convierte en una tabla particionada, y el campo particionado es el campo 1.

5. Convertir la tabla particionada en una tabla normal; si hay un campo de partición con índice, reconstruir el campo particionado como un índice agrupado y no asociarlo al esquema de partición; si el campo de partición no tiene índice, el campo de partición puede crear un nuevo índice agrupado y no asociarse con el esquema de particiones

6. Cambiar una tabla normal a una tabla de particiones o cambiar una tabla de particiones a una tabla ordinaria solo puede lograrse usando índices agrupados, porque con índices agrupados hay tablas de organización de índices, y la redistribución de tablas se realiza mediante la reconstrucción de índices agrupados. Si la tabla ordinaria se convierte en una tabla particionada, reconstruye el campo de partición en un índice agrupado y asócialo con el esquema de particiones, y si la tabla de particiones se convierte en una tabla normal, reconstruye el campo de partición en un índice agrupado sin asociar el esquema de partición.

7. Para crear una tabla de particiones, la restricción de unicidad debe incluir una columna de partición

8. Al crear un esquema de particiones, es necesario asegurarse de que el número de grupos de archivos coincida con el segmento de rango de particiones de la función de partición, y no importa si el nombre del grupo de archivos está duplicado.

9. Las funciones de partición y los esquemas de partición están en una base de datos, no afrontando toda la instancia

10. La tabla de particiones es demasiado grande para ocupar mucho espacio en disco, y el tamaño sigue sin cambiar tras eliminar algunos campos

Pasos para crear una tabla de particiones

1.1. Ejemplo de establecimiento de un grupo de archivos

alterar la base de datos prueba1 añadir grupo de archivos parte 1;

alter database test1 add filegroup part1000;

alter database test1 add filegroup part2000;

alter database test1 añadir grupo de archivos parte 3000;

alter database test1 añadir grupo de archivos part4000;

1.2. Establecer un ejemplo de archivo y asociar un grupo de archivos

ALTER DATABASE TEST1 ADD FILE(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) AL FILEGROUP parte 1;

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME = 'G:\test1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) al filegroup part1000;

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) A FILEGROUP part2000;

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) al filegroup part3000;

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB, FILEGROWTH = 5MB) AL FILEGROUP part4000;

2. Un ejemplo de establecimiento de una función de partición, la función de partición se llama partfun1

CREATE PARTITION FUNCTION partfun1 (int)

COMO RANGO RESTANTE PARA VALORES ('1000', '2000', '3000', '4000')

--VALORES ('1000', '2000', '3000', '4000') indican que la tabla se dividirá en 5 zonas, que se dividen según el tamaño de los valores en los campos de la tabla, las cinco zonas son las más pequeñas - 1000, 1000-2000, 2000-3000, 3000-4000, 4000-mayor

3. Un ejemplo de establecimiento de esquema, asociar la función de partición partfun1 y asociar el grupo de archivos

CREAR ESQUEMA DE PARTICIÓN partschema1

COMO PARTICIÓN PARTFUN1

TO (parte 1, parte 1000, parte 2000, parte 3000, parte 4000);

--Construido sobre varios grupos de archivos de parte 1, parte 1000, parte 2000, parte 3000, parte 4000

CREAR ESQUEMA DE PARTICIÓN partschema2

COMO PARTICIÓN PARTFUN1

TO (PARTE1,[PRINCIPAL], [PRIMARIA], [PRIMARIA]);

--Construido sobre la parte 1, grupo de archivos [PRIMARY], no hay problema en reemplazar la parte 1 por [PRIMARY], para que sea similar a construir sobre el grupo de archivos [PRIMARY]

CREAR ESQUEMA DE PARTICIÓN partschema3

COMO PARTICIÓN PARTFUN1

TODO A (parte 1);

--todos construidos sobre el grupo de archivos parte 1

CREAR ESQUEMA DE PARTICIONES partschema4

COMO PARTICIÓN PARTFUN1

TODOS A ([PRIMARIO]);

--todos construidos sobre el grupo de archivos [PRIMARY]

4. Ejemplo de establecimiento de una tabla de particiones

CREAR TABLA PARTTABLE1(

[ID] [int] NO NULO,

[IDText] [nvarchar] (max) NULO

[Fecha] [hora de cita] NULL)

ON [partschema1](ID);

insertar en parttable1 los valores (1,'1', getdate()-4);

insertar en parttable1 los valores (1001, '1001', getdate()-3);

insertar en la tabla de part1 valores (2001, '2001', getdate()-2);

insertar en parttable1 los valores (3001,'3001', getdate()-1);

insertar en parttable1 los valores (4001,'4001', getdate());

5. Verificar los datos de la tabla de particiones

SELECT * DESDE la tabla de la parte1;

--Devuelve todas las filas de la tabla de particiones

SELECT distinto $PARTITION. [partfun1] (4) DE la tabla parcial1;

--Devuelve a cuál pertenece la fila con el valor del campo ID 4

SELECT * DESDE parttable1 donde $PARTITION. [partfun1] (ID)=2

--Devuelve todas las filas de la segunda partición, el ID es el ID del campo de partición

Nota: No puedes usar SELECT * FROM parttable1 donde $PARTITION. [partfun1] (ID)=2 tiene un resultado, significa que es una tabla particionada, y la última prueba 7 de este artículo es una tabla no particionada, pero SELECT * FROM parttable1 donde $PARTITION. [partfun1] (ID)=2 sigue teniendo un resultado

Nuevas particiones

1. Especificar un grupo de archivos que pueda usarse para el esquema de particiones (añadir un grupo de archivos para el esquema de particiones).

2. Modificar la función de partición (añadir el rango de datos de la función de partición)

ALTERAR PARTITION SCHEME partschema1 SIGUIENTE USO [PRIMARIO]

ALTER PARTITION FUNCTION partfun1() RANGO DIVIDIDO ('4500')

selecciona p.partition_number,p.rows de sys.indexes i inner join sys.partitions p on p.object_id = i.object_id y i.object_id = object_id('parttable2') ordenan por 1

--La primera sentencia, si el esquema de partición usa ALL TO ([PRIMARIA]), esta sentencia no necesita ejecutarse

--Se añade una nueva partición a la segunda afirmación, el rango es de 4000-4500

--La tercera afirmación verifica si existe la nueva partición y si hay filas

Eliminar\Fusionar partición

ALTERAR FUNCIÓN DE PARTICIÓN partfun1() RANGO DE FUSIÓN ('2000')

La partición de 1000-2000 fue eliminada y fusionada en 1000-3000

--No se puede ejecutar ALTERAR TABLENAME TABLENAME PARTITION PARTITIONNAME como Oracle;

Elimina la tabla de particiones y el grupo de archivos correspondiente

El orden de eliminación es: eliminar la tabla de particiones, eliminar el esquema de particiones, eliminar la función de partición y finalmente eliminar el grupo de archivos, y el archivo correspondiente se elimina después de eliminar el grupo

Ejemplo de convertir una tabla particionada en una tabla normal y una tabla normal en tabla particionada

TABLA DE DEPÓSITO 1;

CREAR TABLA PARTTABLE1(

[Id] [int] IDENTIDAD(1,1) NO NULA,

[Nombre] [varchar] (16) NO NULO,

[Id2] [int] NO NULO

) ON partschema1(Id2);

insertar en parttable1 valores ('1',1);

insertar en la tabla de part1 los valores ('1001', 1001);

insertar en los valores de la tabla de part1 ('2001', 2001);

insertar en los valores de la tabla de part1 ('3001', 3001);

insertar valores en la tabla de part1 ('4001', 4001);

1. La restricción única creada en la tabla de particiones debe contener una columna de partición.

ALTER TABLE PARTTABLE1 AÑADIR RESTRICCIÓN PK_prattable1_id CLAVES PRIMARIAS AGRUPADAS ([ID] ASC)

La columna de error 'Id2' es la columna de partición del índice 'PK_prattable1_id'. Las columnas de partición para un índice único deben ser un subconjunto de la clave de índice.

2. Crear un nuevo índice agrupado en la columna de partición id2, y parttable1 sigue siendo una tabla particionada

crear CI_prattable1_id2 índice agrupado en parttable1(id2);

3. La columna de partición id2 crea un índice no agrupado, y parttable1 sigue siendo una tabla particionada

Drop index CI_prattable1_id2 en parttable1;

crear NCI_prattable1_id2 índice no agrupado en parttable1(id2);

4. La columna de id de columna no particionada crea un índice agrupado, parttable1 o una tabla particionada, lo que indica que la columna no particionada puede ser una columna índice de clúster

crear CI_prattable1_id índice agrupado en parttable1(id);

5. La columna de partición ID2 se reconstruye en un índice no agrupado y no utiliza un esquema de particionamientos, parttable1 sigue siendo una tabla de particiones

crear NCI_prattable1_id2 índice no agrupado en parttable1(id2) CON (DROP_EXISTING = ENCENDIDO) EN [PRIMARIO];

6. La columna de partición id2 se reconstruye en un índice agrupado sin la condición ON, y parttable1 sigue siendo una tabla de particiones

Índice de caídas CI_prattable1_id en parttable1;

Drop index NCI_prattable1_id2 en parttable1;

crear CI_prattable1_id2 índice agrupado en parttable1(id2);

crear índice agrupado CI_prattable1_id2 en parttable1(id2) CON (DROP_EXISTING = ON);

7. La columna particionada id2 se reconstruye en un índice agrupado con condición ON pero sin usar el esquema de partición, y parttable1 se convierte en una tabla no particionada

crear CI_prattable1_id2 índice agrupado en parttable1(id2) CON (DROP_EXISTING = ENCENDIDO) EN [PRIMARIA];

8. La columna de partición id2 se reconstruye en un índice agrupado y, usando el esquema de particionamiento, parttable1 se convierte en una tabla particionada

crear CI_prattable1_id2 índice agrupado en parttable1(id2) CON (DROP_EXISTING = ON) en partschema1(Id2);

9. Tras eliminar el índice agrupado en el 8 anterior, parttable1 sigue siendo una tabla particionada

Drop index CI_prattable1_id2 en parttable1;

10. La columna de partición ID2 se crea como un índice agrupado y no utiliza el esquema de particionamiento, y parttable1 se convierte en una tabla no particionada

crear CI_prattable1_id2 índice agrupado en parttable1(id2) EN [PRIMARY];

11. Tras eliminar el índice agrupado de los 10 anteriores, parttable1 sigue siendo una tabla no particionada

Drop index CI_prattable1_id2 en parttable1;

12. La columna de partición id2 se crea recientemente como un índice no agrupado, aunque se utiliza el esquema de particionamiento, sigue siendo una tabla no particionada

crear NCI_prattable1_id2 índice no agrupado en parttable1(id2) en partschema1(Id2);

Si la tabla particionada se convierte en una tabla normal, se elimina la restricción de clave primaria y el índice del clúster se reconstruye o se reconstruye como clave primaria para los campos de la clave primaria original, pero no está relacionado con el esquema de partición

ALTERAR TABLA Table_name DROP RESTRICCIÓN PK_NAME CON (>

CREAR PK_NAME ÍNDICE AGRUPADO EN Table_name(columna) CON (EN [PRIMARIA];

o

ALTERAR TABLE Table_name AÑADIR RESTRICCIÓN PK_NAME CLAVE PRIMARIA CLUSTERED(column) CON (EN [PRIMARIA];

Si la tabla normal se convierte en una tabla particionada, si se va a conservar la clave primaria original, se elimina la restricción de la clave primaria, y luego se crea la clave primaria sin establecerla como índice agregado, y después se crea un nuevo índice agregado, y el esquema de partición se utiliza en el índice agregado

ALTERAR TABLA Table_name DROP RESTRICCIÓN PK_NAME CON (>

ALTERAR TABLE Table_name AÑADIR RESTRICCIÓN PK_NAME CLAVE PRIMARIA NONCLUSTERED(column) CON (EN [PRIMARIA];

--Crear una clave primaria, pero no configurarse como un índice agrupado

CREAR index_name ÍNDICE AGRUPADO EN Table_name(columna) EN ESQUEMA de Partición (Campo de Partición)

--Crear un nuevo índice agrupado en el que se utilice un esquema de partición

Consulta el número total de filas y el tamaño de una tabla particionada, por ejemplo, la tabla es CRM. EmailLog

Ejecutivo sp_spaceused 'CRM. EmailLog';

Consulta la información de una tabla particionada, cuántas filas tiene cada partición, por ejemplo, la tabla es CRM. EmailLog

selecciona convertir(varchar(50), ps.name

) como partition_scheme,

p.partition_number,

convertir (varchar(10), ds2.name

) como grupo de archivos,

convert(varchar(19), isnull(v.value, ''), 120) como range_boundary,

str(p.rows, 9) como filas

de sys.indexes i

unir sys.partition_schemes ps en i.data_space_id = ps.data_space_id

Únete sys.destination_data_spaces DDS

en ps.data_space_id = dds.partition_scheme_id

Unir sys.data_spaces DS2 en dds.data_space_id = ds2.data_space_id

Join sys.partitions p on dds.destination_id = p.partition_number

y p.object_id = i.object_id y p.index_id = i.index_id

Únete sys.partition_functions pf en ps.function_id = pf.function_id

A LA IZQUIERDA UNIRSE A SYS. Partition_Range_values v sobre pf.function_id = v.function_id

y v.boundary_id = p.partition_number - pf.boundary_value_on_right

DONDE i.object_id = object_id('crm. Correo Electrónico')

y i.index_id en (0, 1)

Pedido por p.partition_number

Funciones de partición de consulta

seleccionar * de sys.partition_functions

Ver la arquitectura de particiones

seleccionar * de sys.partition_schemes