Incapsulare logica Transact-SQL con stored procedure

  • 6 minuti

I pool SQL di Azure Synapse supportano l'inserimento di logica di elaborazione dati complessa all'interno di stored procedure. Le stored procedure sono un ottimo strumento per incapsulare una o più istruzioni SQL o un riferimento a un metodo CLR (Common Language Runtime) di Microsoft .NET Framework.

Le stored procedure possono accettare parametri di input e restituire più valori sotto forma di parametri di output al programma che esegue la chiamata. Nel contesto dei pool SQL serverless si eseguirà la trasformazione dei dati usando l'istruzione CREATE EXTERNAL TABLE AS SELECT (CETAS), come mostrato nell'esempio seguente.

-- this sample references external data source and external file format defined in previous section
CREATE PROCEDURE usp_calculate_population_by_year_state
AS
BEGIN
    CREATE EXTERNAL TABLE population_by_year_state
    WITH (
        LOCATION = 'population_by_year_state/',
        DATA_SOURCE = destination_ds,  
        FILE_FORMAT = parquet_file_format
    )  
    AS
    SELECT decennialTime, stateName, SUM(population) AS population
    FROM
        OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
        FORMAT='PARQUET') AS [r]
    GROUP BY decennialTime, stateName
END
GO

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

Riduzione del traffico di rete da client a server

I comandi in una stored procedure vengono eseguiti come un solo batch di codice. In questo modo è possibile ridurre significativamente il traffico di rete tra il server e il client perché solo la chiamata per eseguire la stored procedure 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 stored procedure consente di controllare quali processi e attività vengono eseguiti e di proteggere gli oggetti di database sottostanti. In questo modo si elimina la necessità di concedere autorizzazioni a livello di singolo oggetto, semplificando i livelli di sicurezza.

Semplificazione della manutenzione

Quando si effettua la chiamata delle stored procedure tramite le applicazioni client e si mantengono le operazioni nel database solo nel livello dati, in caso di modifiche nel database sottostante è necessario aggiornare unicamente le stored procedure.

Prestazioni migliorate

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