Los administradores de bases de datos de SQL Server suelen preferir utilizar la instrucción TRUNCATE TABLE en lugar de la instrucción DELETE porque es más rápida, registra mínimamente y consume menos recursos del servidor. Sin embargo, la desventaja de la instrucción TRUNCATE TABLE es que elimina todas las filas de la tabla ya que no se puede agregar una cláusula WHERE para especificar los criterios de eliminación.

Entonces, ¿cómo podemos obtener los beneficios de la instrucción TRUNCATE TABLE sin eliminar todas las filas de la tabla?

Solución:

SQL Server 2016 introduce una modificación interesante a la instrucción TRUNCATE TABLE que te permite truncar una partición específica o un conjunto de particiones de tu tabla, al mismo tiempo que obtienes las ventajas de la instrucción TRUNCATE TABLE sin borrar todos los datos de la tabla. Este nuevo mecanismo de filtrado en la instrucción TRUNCATE TABLE se logra utilizando la opción WITH PARTITIONS() especificando la partición o conjunto de particiones.

Vamos a realizar una demostración sencilla para mostrar cómo funciona:

USE [master]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q1]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q2]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q3]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q4]
GO

USE [master]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q1_2016', FILENAME = N'D:\Data\Q1_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q1]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q2_2016', FILENAME = N'D:\Data\Q2_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q2]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q3_2016', FILENAME = N'D:\Data\Q3_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q3]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q4_2016', FILENAME = N'D:\Data\Q4_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q4]
GO

USE [MSSQLTipsDemo]
GO
CREATE PARTITION FUNCTION PartitionByQuarter(INT) AS
  RANGE RIGHT FOR VALUES
  (20161,20162,20163,20164)
GO

USE [MSSQLTipsDemo]
GO
CREATE PARTITION SCHEME PartitionByQuarterScheme AS
  PARTITION PartitionByQuarter  TO
  (
   [Q1],
   [Q2],
   [Q3],
   [Q4],
   [PRIMARY] )
GO

USE [MSSQLTipsDemo]
GO
CREATE TABLE [dbo].[PartitionDemo2016](
 [ID] [int] NULL,
 [ActionGUID] [nvarchar](50) NULL,
 [TS] [datetime] NULL,
 [QuarterNum]  AS (datepart(year,[TS])*(10)+datepart(quarter,[TS])) PERSISTED
) ON [PartitionByQuarterScheme] ([QuarterNum])

GO

USE [MSSQLTipsDemo]
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (1, N'asgdhfjflflgl', CAST(N'2016-01-15T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (2, N'djfjhgsfgdgd', CAST(N'2016-05-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (3, N'fvkifvjfvjfhy', CAST(N'2016-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (4, N'mvfhufvhfgtdh', CAST(N'2016-10-30T00:00:00.000' AS DateTime))
GO

USE MSSQLTipsDemo
GO
SELECT  [ID]
      ,[ActionGUID]
      ,[TS]
      ,[QuarterNum]
  FROM [MSSQLTipsDemo].[dbo].[PartitionDemo2016]

Lo que debería haber ocurrido en segundo plano es que el primer registro se insertará en el primer grupo de archivos ya que cumple con la primera condición de partición, y el segundo registro se insertará en el segundo grupo de archivos ya que cumple con la segunda condición de partición, y así sucesivamente.

Para asegurarnos de que esto ocurrió de la manera correcta, consultaremos la tabla del sistema sys.partitions para la tabla PartitionDemo2016 para obtener el número de registros en cada partición:

SELECT partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionDemo2016';

El resultado debería mostrar que cada registro se inserta en su partición correspondiente, y la primera partición está vacía según cómo se creó la función de partición.

Ahora, vamos a probar la nueva adición a la instrucción TRUNCATE TABLE. La siguiente instrucción TRUNCATE se utiliza para truncar la segunda y tercera particiones de la tabla PartitionDemo2016, lo que eliminará los datos en estas particiones:

TRUNCATE TABLE PartitionDemo2016
WITH (PARTITIONS (2 TO 3));

Al recuperar los datos de la tabla PartitionDemo2016 nuevamente, el resultado mostrará que los datos de las segunda y tercera particiones se eliminaron utilizando la instrucción TRUNCATE TABLE, mientras que los datos en las cuarta y quinta particiones permanecen intactos.

Como puedes ver en la demostración, la instrucción TRUNCATE TABLE no se limita a eliminar todos los datos de una tabla. Con la nueva adición en SQL Server 2016, ahora puedes utilizar la instrucción TRUNCATE TABLE para eliminar particiones específicas o conjuntos de particiones, al mismo tiempo que te beneficias de su velocidad y eficiencia de recursos.