Incapsulare le trasformazioni dei dati in una stored procedure

Completato

Sebbene sia possibile eseguire un'istruzione (CETAS) CREATE EXTERNAL TABLE AS SELECT in uno script ogni volta che è necessario trasformare i dati, è consigliabile incapsulare l'operazione di trasformazione nella stored procedure. Questo approccio può semplificare l'azione di rendere operative le trasformazioni dei dati consentendo all'utente di fornire parametri, recuperare output e includere una logica aggiuntiva in una singola chiamata di procedura.

Ad esempio, il codice seguente crea una stored procedure che elimina la tabella esterna, se esiste già, prima di ricrearla con i dati relativi all'ordine per l'anno specificato:

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

Come discusso in precedenza, l'eliminazione di una tabella esterna esistente non comporta l'eliminazione della cartella contenente i relativi file di dati. È necessario eliminare in modo esplicito la cartella di destinazione, se esiste prima, di eseguire la stored procedure oppure si verificherà un errore.

Oltre all'incapsulamento della logica di Transact-SQL, le stored procedure offrono anche i vantaggi aggiuntivi seguenti:

Riduzione del traffico di rete da client a server

I comandi in una procedura vengono eseguiti come un singolo batch di codice, che può ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la procedura viene inviata attraverso la rete.

Definizione di un limite di sicurezza

Tramite una stored procedure, più utenti e programmi client sono in grado di eseguire operazioni su oggetti di database sottostanti, anche se gli utenti e i programmi non dispongono di autorizzazioni dirette su tali oggetti sottostanti. La procedura controlla i processi e le attività che vengono eseguiti, protegge gli oggetti di database sottostanti, eliminando la necessità di dover concedere le autorizzazioni a livello di singolo oggetto, e semplifica i livelli di sicurezza.

Semplificazione della manutenzione

Le modifiche apportate alla logica o ai percorsi del file system coinvolti nella trasformazione dei dati possono essere applicate solo alla stored procedure, senza dover aggiornare le applicazioni client o ad altre funzioni che eseguono le chiamate.

Prestazioni migliorate

Le stored procedure vengono compilate la prima volta che vengono eseguite e il piano di esecuzione risultante viene mantenuto nella cache e riutilizzato per le esecuzioni successive della stessa stored procedure. Di conseguenza, l'elaborazione della stored procedure richiede meno tempo.