Encapsulación de transformaciones de datos en un procedimiento almacenado

Completado

Aunque puede ejecutar una instrucción CREATE EXTERNAL TABLE AS SELECT (CETAS) en un script siempre que necesite transformar datos, se recomienda encapsular la operación de transformación en el procedimiento almacenado. Este enfoque puede facilitar la puesta en funcionamiento de las transformaciones de datos al permitirle proporcionar parámetros, recuperar salidas e incluir lógica adicional en una sola llamada a procedimiento.

Por ejemplo, el código siguiente crea un procedimiento almacenado que quita la tabla externa si ya existe antes de volver a crearla con datos de pedido para el año especificado:

CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN

	-- Drop the table if it already exists
	IF EXISTS (
                SELECT * FROM sys.external_tables
                WHERE name = 'SpecialOrders'
            )
        DROP EXTERNAL TABLE SpecialOrders

	-- Create external table with special orders
	-- from the specified year
	CREATE EXTERNAL TABLE SpecialOrders
		WITH (
			LOCATION = 'special_orders/',
			DATA_SOURCE = files,
			FILE_FORMAT = ParquetFormat
		)
	AS
	SELECT OrderID, CustomerName, OrderTotal
	FROM
		OPENROWSET(
			BULK 'sales_orders/*.csv',
			DATA_SOURCE = 'files',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0',
			HEADER_ROW = TRUE
		) AS source_data
	WHERE OrderType = 'Special Order'
	AND YEAR(OrderDate) = @order_year
END

Nota:

Como se ha explicado anteriormente, quitar una tabla externa existente no elimina la carpeta que contiene sus archivos de datos. Debe eliminar explícitamente la carpeta de destino si existe antes de ejecutar el procedimiento almacenado o se producirá un error.

Además de encapsular la lógica de Transact-SQL, los procedimientos almacenados también proporcionan las siguientes ventajas:

Reducen el tráfico de red del cliente al servidor

Los comandos de un procedimiento se ejecutan como un único lote de código; que puede reducir significativamente el tráfico de red entre el servidor y el cliente porque solo se envía la llamada para ejecutar el procedimiento a través de la red.

Proporcionan un límite de seguridad

Varios usuarios y programas cliente pueden realizar operaciones en los objetos de base de datos subyacentes a través de un procedimiento, aunque los usuarios y los programas no tengan permisos directos sobre esos objetos subyacentes. El procedimiento controla qué procesos y actividades se realizan y protege los objetos de base de datos subyacentes; eliminando el requisito de conceder permisos en el nivel de objeto individual y simplifica las capas de seguridad.

Facilitan el mantenimiento

Cualquier cambio en la lógica o en las ubicaciones del sistema de archivos involucrados en la transformación de datos solo se puede aplicar al procedimiento almacenado; sin necesidad de actualizaciones de las aplicaciones del cliente u otras funciones de llamadas.

rendimiento mejorado.

Los procedimientos almacenados se compilan la primera vez que se ejecutan, y el plan de ejecución resultante se mantiene en la memoria caché y se reutiliza en ejecuciones posteriores del mismo procedimiento almacenado. Como resultado, se tarda menos en procesar el procedimiento.