Stored procedure e table-valued functions

Le stored procedure sono blocchi di T-SQL persistenti nel database, ideali per incapsulare business logic, ridurre i round-trip di rete e centralizzare i permessi: puoi concedere EXECUTE sulla procedura senza dare accesso diretto alle tabelle sottostanti. Supportano parametri di input/output, controllo esplicito delle transaction e possono restituire più result set. In un contesto DP-800 sono anche il punto naturale dove incapsulare chiamate a un external REST endpoint per arricchire i dati con servizi AI, isolando la logica di orchestrazione dal client.

Le table-valued functions (TVF) restituiscono una tabella e si usano in FROM, JOIN o con APPLY. Distinzione chiave d’esame: la inline TVF è una singola SELECT parametrizzata, di fatto una “view con parametri”, e viene espansa (inlined) dall’optimizer, quindi tende a performare bene; la multi-statement TVF costruisce una tabella intermedia ed è opaca all’optimizer, con stime di cardinalità spesso scadenti. A parità di scopo, preferisci la inline. Le funzioni non possono avere side effect: non modificano dati e non chiamano procedure con effetti persistenti.

View: astrazione, sicurezza e performance

Una view è una query salvata che espone un risultato logico senza duplicare i dati. Serve a semplificare query complesse, a fornire un layer di compatibilità stabile quando lo schema fisico cambia, e soprattutto a implementare la security: si concede SELECT sulla view esponendo solo colonne o righe filtrate, senza aprire le tabelle base. Le view sono aggiornabili solo a condizioni restrittive (una sola tabella base, niente aggregazioni/DISTINCT/GROUP BY); quando servono UPDATE su view complesse si ricorre a un trigger INSTEAD OF.

Per le performance esiste l’indexed view (materializzata): richiede WITH SCHEMABINDING e un indice cluster univoco, e memorizza fisicamente il risultato. SCHEMABINDING lega la view alle tabelle sottostanti, impedendo modifiche di schema che la romperebbero: un trade-off da conoscere.

Trigger: automazione e insidie

I trigger DML (AFTER/FOR e INSTEAD OF) eseguono codice in risposta a INSERT/UPDATE/DELETE, accedendo alle pseudo-tabelle inserted e deleted. AFTER agisce dopo l’operazione (audit, propagazione); INSTEAD OF la sostituisce (tipico per rendere aggiornabili view complesse). Esistono anche trigger DDL e logon.

L’insidia più frequente: un trigger scatta una volta per statement, non una per riga. Codice scritto assumendo una sola riga fallisce quando un UPDATE tocca molte righe. Vanno quindi scritti set-based. I trigger allungano inoltre la transaction del comando che li attiva: logica pesante o chiamate esterne dentro un trigger degradano il throughput e rischiano lock prolungati. Per semplici validazioni preferisci CHECK/FOREIGN KEY constraint, più economici e dichiarativi.

Gestione degli errori con TRY…CATCH

Il costrutto TRY…CATCH intercetta gli errori runtime: il blocco CATCH ispeziona il contesto con ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE() ed ERROR_PROCEDURE(). Per rilanciare l’errore preservandone il contesto usa THROW; RAISERROR resta per messaggi custom con maggiore controllo su severità e stato.

Attenzione ai limiti: TRY…CATCH non cattura gli errori di compilazione, quelli di risoluzione dei nomi nel batch, né gli errori di severità molto alta che chiudono la connessione. Con le transaction, apri BEGIN TRANSACTION nel TRY e, nel CATCH, verifica XACT_STATE() prima di decidere: il valore -1 indica una transaction “uncommittable” da forzare in ROLLBACK. Impostare SET XACT_ABORT ON rende molti errori fatali per la transaction, irrobustendo il rollback automatico.

Trappole tipiche d’esame

  • Serve alta performance da una funzione che restituisce righe → soluzione: usa una inline TVF, non una multi-statement TVF: la prima è inlinata dall’optimizer, la seconda produce stime di cardinalità scadenti.
  • Rendere aggiornabile una view con JOIN o aggregazioni → soluzione: un trigger INSTEAD OF, non un AFTER: la view di per sé non è direttamente updatable.
  • Trigger che aggiorna solo la prima riga di un UPDATE massivo → soluzione: riscrivilo set-based su inserted/deleted; il trigger scatta una volta per statement, non per riga.
  • Rollback affidabile in una procedura con transaction → soluzione: TRY…CATCH con XACT_STATE() nel CATCH (e/o SET XACT_ABORT ON); non affidarti al solo @@ERROR riga per riga.
  • Rilanciare al chiamante l’errore originale → soluzione: THROW senza argomenti nel CATCH; RAISERROR non ripropaga automaticamente number e severity originali.
  • Esporre dati a un ruolo senza dare accesso alle tabelle → soluzione: concedi EXECUTE su una stored procedure o SELECT su una view, sfruttando l’ownership chaining, non permessi diretti sulle tabelle base.