Moviendo tablas entre Filegroups en SQL Server

Moviendo tablas de un Filegroup a otro en SQL Server

Una base de datos en SQL Server se puede crear con varios archivos donde se guarda la información de las tablas, estos archivos se agrupan en Grupos de archivos llamados Filegroups, cada grupo de archivos puede tener archivos ubicados en diferentes discos y en diferentes carpetas, el objetivo es que la información de las tablas se almacene en archivos diferentes lo que puede optimizar la distribución de la información guardada en la base de datos.

En este artículo se va a crear una base de datos y las tablas en determinados grupos,
luego se va a mover las tablas a grupos diferentes.

Para mayor información ver
Crear bases de datos
Modificar una base de datos, grupos de archivos
Modificar una base de datos, archivos de base de datos
Crear Tablas
Crear Esquemas
Crear índices
Uso de restricciones
Tablas particionadas

Ejercicios

Creando la base de datos Ferreteria, esta se va a crear en las unidades C: y D: y al momento de crearla se va a crear adicional al grupo de archivos Primary, dos grupos, COMERCIAL y LOGISTICA.

xp_create_subdir ‘C:\Bases’
go
xp_create_subdir ‘D:\Bases’
go
Create database Ferreteria
on Primary
(name = ‘F01’, Filename = ‘C:\Bases\F01.mdf’),
Filegroup COMERCIAL
(name = ‘FerreteriaComercial01’, Filename = ‘D:\Bases\FerreteriaComercial01.ndf’),
Filegroup LOGISTICA
(name = ‘F04’, Filename = ‘D:\Bases\F04.ndf’),
(name = ‘F05’, Filename = ‘D:\Bases\F05.ndf’)
LOG on
(name = ‘FerreteriaLog01’, Filename = ‘D:\Bases\FerreteriaLog01.ldf’)
go
use Ferreteria
go

La imagen muestra la estructura que se va a crear con las tablas Categorias, Productos, Clientes, Facturas y Detalle de Facturas

Note que algunas tablas se han ubicado en el esquema Ventas, eso se muestra en el diagrama, el nombre del esquema aparece entre paréntesis después del nombre de la tabla.

Create schema Ventas
go
La Tabla Categorias se va a crear en el grupo COMERCIAL.
Create table Ventas.Categorias
(
CategoriasCodigo nchar(7),
CategoriasDescripcion nvarchar(100) not null ,
CategoriasFechaCreacion Date constraint CategoriasFechaCreacionDF Default GetDate(),
CategoriasEstado nchar(1) constraint CategoriasEstadoDF Default ‘A’,
constraint CategoriasPK Primary key (CategoriasCodigo),
constraint CategoriasDescripcionUQ Unique (CategoriasDescripcion),
constraint CategoriasFechaCreacionCK Check (CategoriasFechaCreacion<= GetDate()),
constraint CategoriasEstadoCK Check (CategoriasEstado = ‘A’ or CategoriasEstado = ‘E’),
)
on COMERCIAL
go

La Tabla Productos se va a crear en el grupo COMERCIAL, note al final de la definición de la tabla se incluye on COMERCIAL

Create table Ventas.Productos
(
ProductosCodigo nchar(10),
ProductosCategoriasCodigo nchar(7),
ProductosDescripcion nvarchar(80) not null ,
ProductosFechaCreacion Date constraint ProductosFechaCreacionDF Default GetDate(),
ProductosEstado nchar(1) constraint ProductosEstadoDF Default ‘A’,
ProductosStock Numeric(9,2) constraint ProductosStockDF Default 0,
ProductosPrecio Numeric(9,2) constraint ProductosPrecioDF Default 0,
— Constraints: Check, Unique, Default, PK, FK
constraint ProductosCategoriasFK Foreign key (ProductosCategoriasCodigo)
references Ventas.Categorias(CategoriasCodigo),
constraint ProductosDescripcionUQ Unique (ProductosDescripcion),
constraint ProductosFechaCreacionCK Check (ProductosFechaCreacion<= GetDate()), constraint ProductosEstadoCK Check (ProductosEstado = ‘A’ or ProductosEstado = ‘E’ or ProductosEstado = ‘D’), constraint ProductosStockCK Check (ProductosStock>=0),
constraint ProductosPrecioCK Check (ProductosPrecio>=0),
constraint ProductosPK Primary key (ProductosCodigo)
)
on COMERCIAL
go

La Tabla Clientes se va a crear en el grupo PRIMARY, note al final de la definición
de la tabla no se ha especificado un grupo, por lo que se crea en Primary.

Create table Clientes
(
ClientesCodigo nchar(7),
ClientesRazonSocial nvarchar(100) not null ,
ClientesFechaRegistro Date constraint ClientesFechaCreacionDF Default GetDate(),
ClientesRUC nchar(11),
ClientesEstado nchar(1) constraint ClientesEstadoDF Default ‘A’,
constraint ClientesPK Primary key (ClientesCodigo),
constraint ClientesDescripcionUQ Unique (ClientesRazonSocial)
)
go

La Tabla Facturas se va a crear en el grupo PRIMARY, note al final de la definición
de la tabla no se ha especificado un grupo, por lo que se crea en Primary.

Create table Facturas
(
FacturasNumeroSerie nchar(3),
FacturasNumeroFactura nchar(7),
FacturasFecha DateTime,
FacturasSubtotal Numeric(9,2),
FacturasValorPorcentajeIGV Numeric(9,4),
FacturasMontoIGV As FacturasSubtotalFacturasValorPorcentajeIGV, FacturasTotalFactura As FacturasSubtotal + FacturasSubtotalFacturasValorPorcentajeIGV,
ClientesCodigo nchar(7),
ClientesRazonSocial nvarchar(100) not null ,
ClientesRUC nchar(11),
constraint FacturasPK Primary key (FacturasNumeroSerie, FacturasNumeroFactura),
constraint FacturasClientesFK Foreign key (ClientesCodigo)
references Clientes(ClientesCodigo)
)
go

La Tabla Detalle de Factura se va a crear en el grupo LOGISTICA, note al final de
la definición de la tabla se incluye on LOGISTICA, esta tabla se va a usar como ejemplo para moverla a otro grupo.

Create table DetalleFactura
(
FacturasNumeroSerie nchar(3),
FacturasNumeroFactura nchar(7),
ProductosCodigo nchar(10),
ProductosDescripcion nvarchar(80) not null ,
DetalleFacturaCantidadVendida Numeric(19,2),
DetalleFacturaPrecioVenta Numeric(9,2),
DetalleFacturaImporteItem As DetalleFacturaCantidadVendida* DetalleFacturaPrecioVenta,
constraint DetalleFacturaPK Primary key
(FacturasNumeroSerie,FacturasNumeroFactura,ProductosCodigo),
constraint DetalleFacturaFacturasFK Foreign key (FacturasNumeroSerie,FacturasNumeroFactura)
references dbo.Facturas(FacturasNumeroSerie,FacturasNumeroFactura),
constraint DetalleFacturasProductosFK Foreign key (ProductosCodigo)
references Ventas.Productos(ProductosCodigo)
)
on LOGISTICA
go

Grupos y tablas

Para visualizar los grupos de archivos y sus tablas se va a consultas las vistas del sistema sys.Tables que contiene las tablas de la base de datos, sys.Indexes que contiene los índices de la base de datos y sys.filegroups que contiene los grupos de archivos de la base de datos.

select * from sys.tables
select * from sys.indexes
select * from sys.filegroups
go

Para la lista de las tablas y los grupos a las que pertenecen

SELECT  
T.name As ‘Tabla’,
G.name AS ‘Grupo de archivos’
from sys.tables As T
JOIN sys.indexes As I on T.object_id = I.object_id
AND I.index_id <= 1 — 1 indica PK
LEFT JOIN sys.filegroups As G on G.data_space_id = I.data_space_id
where T.lob_data_space_id = 0 — Para las tablas que almacenan datos
ORDER BY  [Grupo de archivos], Tabla
go
La imagen muestra el resultado

Mover la tabla DetalleFactura del grupo LOGISTICA al grupo COMERCIAL

Para mover una tabla de un grupo de archivos a otro, se puede proceder de dos formas:

  1. Se debe cambiar el índice agrupado, se elimina la restricción de tipo PK moviéndola al nuevo grupo, luego se crea la PK nuevamente.
  2. Crear el índice agrupado (Clustered), sobre escribiendo el actual y direccionándolo al nuevo grupo.

Antes de mover la tabla de un grupo a otro, la ventana de propiedades permite mostrar el grupo de archivos donde se encuentra la información de la tabla.

La imagen anterior muestra la ubicación de la tabla Detalle de Factura en el grupo
de archivos LOGISTICA, pulsando botón derecho en la tabla y luego seleccionar propiedades, en la página Storage se puede visualizar que pertenece a LOGISTICA

Se puede visualizar también usando la siguiente instrucción
sp_help ‘[dbo].[DetalleFactura]’
go
La imagen siguiente muestra el resultado

Moviendo la tabla al grupo de archivos COMERCIAL

Forma 1: moviendo la PK.

Paso 1: Eliminar la PK y moverla a COMERCIAL
Alter table DetalleFactura
drop constraint DetalleFacturaPK
with ( move to COMERCIAL)
go

Note que se ha usado la opción move to para mover los datos al nuevo grupo.

Paso 2: Crear nuevamente la PK
Alter table DetalleFactura add constraint
DetalleFacturaPK primary key
(FacturasNumeroSerie,FacturasNumeroFactura,ProductosCodigo)
go

El listado de las tablas y sus grupos se muestra con el resultado
SELECT  
T.name As ‘Tabla’,
G.name AS ‘Grupo de archivos’
from sys.tables As T
JOIN sys.indexes As I on T.object_id = I.object_id
AND I.index_id <= 1 — 1 indica PK
LEFT JOIN sys.filegroups As G on G.data_space_id = I.data_space_id
where T.lob_data_space_id = 0 — Para las tablas que almacenan datos
ORDER BY  [Grupo de archivos], Tabla
go
La imagen muestra el resultado.

Forma 2: creando y sobre escribiendo el índice agrupado.

Otra forma de pasar la tabla a otro grupo de archivos es redefinir el índice agrupado
usando la opción On NombreGrupo al final de la instrucción.

Para pasar la tabla Detalle de Factura al grupo Primary

CREATE unique Clustered INDEX DetalleFacturaPK
on DetalleFactura(FacturasNumeroSerie,FacturasNumeroFactura,ProductosCodigo)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON [Primary]
go

Ver la estructura de la tabla DetalleFactura, para visualizar el grupo al que pertenece.
sp_help ‘[dbo].[DetalleFactura]’
go
La imagen muestra el resultado

SELECT  
T.name As ‘Tabla’,
G.name AS ‘Grupo de archivos’
from sys.tables As T
JOIN sys.indexes As I on T.object_id = I.object_id
AND I.index_id <= 1 — 1 indica PK
LEFT JOIN sys.filegroups As G on G.data_space_id = I.data_space_id
where T.lob_data_space_id = 0 — Para las tablas que almacenan datos
ORDER BY  [Grupo de archivos], Tabla
go
La imagen muestra el resultado

Tabla particionada

Al crear una tabla particionada la información de esta se distribuye en los grupos de
acuerdo al esquema de partición. Se va a crear una tabla para Pedidos particionada para ver como se muestra en el listado. (Ver Tablas particionadas)

Función de partición
CREATE PARTITION FUNCTION FuncionParticionPedidosFP (nchar(15))
AS RANGE LEFT FOR VALUES (‘D’,’L’,’Q’,’V’)
go

Esquema de partición
CREATE PARTITION SCHEME EsquemaParticionPedidosEP
AS PARTITION FuncionParticionPedidosFP
TO (COMERCIAL,LOGISTICA,[PRIMARY],[PRIMARY],COMERCIAL)
go

Create table Pedidos
(
PedidosCodigo nchar(15) ,
PedidosFecha Date,
ClientesCodigo nchar(7),
constraint PedidosPK Primary key (PedidosCodigo)
) on EsquemaParticionPedidosEP (PedidosCodigo)
go

Listado de las tablas y los grupos a los que pertenecen

SELECT  T.name As ‘Tabla’,
G.name As ‘Grupo de archivos’
from sys.tables As T
JOIN sys.indexes As I on T.object_id = I.object_id
AND I.index_id <= 1 — 1 indica PK
LEFT JOIN sys.filegroups As G on G.data_space_id = I.data_space_id
where T.lob_data_space_id = 0 — Para las tablas que almacenan datos
ORDER BY  [Grupo de archivos], Tabla
go
La imagen siguiente muestra el resultado
Note que la tabla Pedidos muestra en Grupo de archivos el valor de Null, esto
porque es una tabla particionada.

Se puede incluir la función IIF para mostrar el mensaje «Tabla Particionada»

SELECT  T.name As ‘Tabla’,
IIF(G.type=’FG’,G.name,’Tabla Particionada’) 
As ‘Grupo de archivos’
from sys.tables As T
JOIN sys.indexes As I on T.object_id = I.object_id
AND I.index_id <= 1 — 1 indica PK
LEFT JOIN sys.filegroups As G on G.data_space_id = I.data_space_id
where T.lob_data_space_id = 0 — Para las tablas que almacenan datos
ORDER BY  [Grupo de archivos], Tabla
go
La imagen siguiente muestra el resultado

Borrar el grupo de archivos LOGISTICA

Al crear la base de datos, al grupo LOGISTICA se le incluyó dos archivos, las siguientes instrucciones eliminan esos archivos. Estos archivos no contienen tablas porque ninguna de ellas ha sido direccionada a este grupo.

Alter database Ferreteria remove file F04
Alter database Ferreteria remove file F05
go
Mensaje del resultado de la instrucción
Se ha quitado archivo ‘F04’.
Se ha quitado archivo ‘F05’.

Ahora se va a intentar borrar el grupo LOGISTICA
alter database Ferreteria remove filegroup LOGISTICA
go
Aparece un mensaje de error
Msg 5042, Level 16, State 12, Line 310
El objeto ‘LOGISTICA’ de tipo grupo de archivos no se puede quitar porque no está vacío.

La imagen muestra que el grupo de archivos LOGISTICA no tiene archivos.

No se puede eliminar el grupo de archivos porque el esquema de partición de la tabla Pedidos direcciona una de las particiones al grupo LOGISTICA. El esquema de partición llamado EsquemaParticionPedidosEP en el que se asignó una de las particiones al grupo LOGISTICA.

Para poder eliminar el grupo, debemos reconstruir el índice de la tabla Pedidos, cambiar el índice y mover el contenido a un grupo.

CREATE unique Clustered INDEX PedidosPK
on Pedidos(PedidosCodigo)
WITH (DROP_EXISTING=ON,ONLINE=ON) ON [Primary]
go
Eliminar luego el esquema de partición
Drop partition scheme EsquemaParticionPedidosEP
go

Ahora si es posible eliminar el grupo de archivos

alter database Ferreteria remove filegroup LOGISTICA
go
La imagen muestra los grupos de archivos de la BD Ferreteria

Restablecer el grupo de archivos y los archivos eliminados

Se debe realizar una copia de seguridad del Log de la Base de datos, previamente se debe crear un Device para el Backup.
La siguiente instrucción crea un Device en el disco llamado FerreteriaBKLog
Execute sp_addumpdevice ‘disk’, ‘FerreteriaBAKLog’, ‘C:\Bases\FerreLog.bak’ ;
go
Obtener el Backup del Log.
Backup log Ferreteria to FerreteriaBAKLog
go
Ahora se puede volver a agregar el grupo y los archivos.
Alter database Ferreteria add filegroup LOGISTICA
go
Alter database Ferreteria add file
(name = ‘F04’, Filename = ‘D:\Bases\F04.ndf’),
(name = ‘F05’, Filename = ‘D:\Bases\F05.ndf’)
to filegroup LOGISTICA
go
La imagen muestra los grupos de archivos de la base de datos