Cursori (SQL Server)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Nei database relazionali le operazioni vengono eseguite su set di righe completi. Il set di righe restituito da un'istruzione SELECT, ad esempio, è costituito da tutte le righe che soddisfano le condizioni specificate nella clausola WHERE dell'istruzione. Il set di righe completo restituito dall'istruzione è noto come set di risultati. Le applicazioni, soprattutto le applicazioni online interattive, non sono sempre in grado di gestire in modo efficiente un intero set di risultati come singola unità. In tali applicazioni deve essere pertanto disponibile un meccanismo per l'elaborazione di una riga singola o di un blocco di righe di dimensioni ridotte. I cursori sono un'estensione dei set di risultati che implementano appunto tale meccanismo.

I cursori estendono l'elaborazione dei risultati nel modo seguente:

  • Consentono il posizionamento su righe specifiche del set di risultati.

  • Recuperano una riga o un blocco di righe dalla posizione corrente del set di risultati.

  • Supportano la modifica dei dati delle righe in corrispondenza della posizione corrente del set di risultati.

  • Supportano livelli diversi di visibilità per le modifiche apportate da altri utenti ai dati del database inclusi nel set di risultati.

  • Consentono alle istruzioni Transact-SQL incluse in script, stored procedure e trigger di accedere ai dati di un set di risultati.

Osservazioni:

In alcuni scenari, se in una tabella è presente una chiave primaria, è possibile usare un ciclo WHILE anziché un cursore. Ciò consente di evitare il sovraccarico di un cursore.

In alcuni scenari, tuttavia, i cursori non solo sono inevitabili, ma sono in effetti necessari. In questi casi, se l'aggiornamento basato su un cursore non rappresenta un requisito, usare cursori Firehose, vale a dire cursori fast forward e di sola lettura.

Implementazione dei cursori

SQL Server supporta l'implementazione di tre tipi di cursori.

Implementazione dei cursori Descrizione
Cursori Transact-SQL I cursori Transact-SQL, basati sulla sintassi DECLARE CURSOR, sono usati principalmente all'interno di trigger, stored procedure e script Transact-SQL. Vengono implementati nel server e gestiti dalle istruzioni Transact-SQL inviate dal client al server. Possono essere inoltre inclusi in batch, stored procedure o trigger.
Cursori API (Application Programming Interface) del server Le funzioni dei cursori API sono supportate in OLE DB e ODBC. Questi cursori sono implementati nel server. Ogni volta che un'applicazione client chiama una funzione per un cursore API, il provider OLE DB o il driver ODBC di SQL Server Native Client trasmette al server la richiesta di eseguire un'azione sul cursore API del server.
Cursori client I cursori client sono implementati internamente dal driver ODBC di SQL Server Native Client e dalla DLL che implementa l'API ADO. I cursori client vengono implementati mediante la memorizzazione nella cache del client di tutte le righe del set di risultati. Ogni volta che un'applicazione client chiama una funzione per un cursore API, la DLL ADO o il driver ODBC di SQL Server Native Client esegue l'operazione del cursore nelle righe del set di risultati memorizzate nella cache del client.

Tipo di cursori

SQL Server supporta quattro tipi di cursore.

I cursori possono usare tabelle di lavoro tempdb. Come le operazioni di aggregazione o di ordinamento, i cursori possono eseguire spill, che comportano costi di I/O e possono causare la formazione di colli di bottiglia per le prestazioni. I cursori STATIC usano tabelle di lavoro dall'inizio. Per altre informazioni, vedere la sezione Tabelle di lavoro nella Guida sull'architettura di elaborazione delle query.

Forward-only

I cursori forward-only sono specificati come FORWARD_ONLY e READ_ONLY e non supportano lo scorrimento. Questi cursori, detti anche Firehose, supportano solo il recupero seriale delle righe dall'inizio alla fine del cursore. Le righe vengono prelevate dal database solo quando si esegue l'operazione di recupero. Gli effetti delle istruzioni INSERT, UPDATE e DELETE, create dall'utente corrente o eseguite da altri utenti e che interessano alcune righe del set di risultati, sono visibili nel momento in cui le righe vengono recuperate dal cursore.

Poiché non è supportato lo scorrimento a ritroso del cursore, la maggior parte delle modifiche apportate alle righe nel database in seguito al recupero non sono visibili tramite il cursore. Nei casi in cui un valore usato per determinare la posizione della riga all'interno del set di risultati viene modificato, ad esempio tramite l'aggiornamento di una colonna coperta da un indice cluster, il valore modificato sarà visibile tramite il cursore.

Anche se i modelli di cursore API del database considerano un cursore forward-only un tipo distinto di cursore, SQL Server non fa questa distinzione. SQL Server considera il forward-only e lo scorrimento come opzioni da applicare ai cursori statici, gestiti da keyset e dinamici. I cursori Transact-SQL supportano i cursori dinamici, i cursori gestiti da keyset e i cursori statici forward-only. In base ai modelli di cursore dell'API di database i cursori dinamici, gestiti da keyset o statici sono sempre scorrevoli. I cursori API del database con una proprietà o un attributo impostato su forward-only vengono implementati in SQL Server come cursori forward-only dinamici.

Statico

Il set di risultati completo di un cursore statico viene compilato nel database tempdb all'apertura del cursore. Un cursore statico visualizza sempre il set di risultati così come è visualizzato all'apertura del cursore. I cursori statici rilevano poche modifiche o addirittura nessuna, ma utilizzano un numero relativamente ridotto di risorse per lo scorrimento.

Nel cursore non vengono riportate né le modifiche al database che hanno effetto sull'appartenenza del set di risultati, né le modifiche apportate ai valori inclusi nelle colonne delle righe del set di risultati. Un cursore statico non visualizza le nuove righe inserite nel database dopo l'apertura del cursore, neanche se tali righe soddisfano le condizioni di ricerca dell'istruzione SELECT del cursore. Non visualizza inoltre gli aggiornamenti eseguiti da altri utenti nelle righe del set di risultati. Un cursore statico riflette invece le operazioni di eliminazione di righe dal database dopo l'apertura del cursore. Il risultato delle operazioni UPDATE, INSERT e DELETE non si riflette mai sul cursore statico (a meno che il cursore non venga chiuso e riaperto), neanche se le modifiche sono state eseguite con la stessa connessione con cui il cursore è stato aperto.

Nota

I cursori statici SQL Server sono sempre di sola lettura.

Poiché il set di risultati di un cursore statico viene archiviato in una tabella di lavoro in tempdb, la dimensione delle righe del set di risultati non può essere maggiore della dimensione massima delle righe consentita per le tabelle di SQL Server.

Per altre informazioni, vedere la sezione Tabelle di lavoro nella Guida sull'architettura di elaborazione delle query. Per altre informazioni sulla dimensione massima delle righe, vedere Specifiche di capacità massima per SQL Server.

Transact-SQL definisce i cursori statici come cursori di tipo insensitive. Alcune API di database li identificano come cursori snapshot.

Keyset

L'appartenenza e l'ordine delle righe di un cursore gestito da keyset vengono fissati al momento dell'apertura del cursore. I cursori gestiti da keyset vengono controllati da un set di identificatori univoci, o chiavi, noti come keyset. Le chiavi sono costituite da un set di colonne che identificano in modo univoco le righe del set di risultati. Il keyset corrisponde al set di valori chiave di tutte le righe risultanti dall'istruzione SELECT al momento dell'apertura del cursore. Il keyset di un cursore gestito da keyset viene compilato nel database tempdb all'apertura del cursore.

Dinamico

I cursori dinamici sono l'opposto dei cursori statici. Quando si scorre un cursore dinamico vengono visualizzate tutte le modifiche apportate alle righe del set di risultati corrispondente. I valori di dati, l'ordine e l'appartenenza delle righe del set di risultati possono variare a ogni operazione di recupero. I risultati delle istruzioni UPDATE, INSERT e DELETE eseguite da tutti gli utenti sono visibili tramite il cursore. Gli aggiornamenti sono visibili immediatamente se eseguiti tramite il cursore con una funzione API quale SQLSetPos o la clausola WHERE CURRENT OF di Transact-SQL. Gli aggiornamenti eseguiti all'esterno del cursore risultano visibili solo dopo l'operazione di commit, a meno che il livello di isolamento della transazione non sia impostato su read uncommitted. Per altre informazioni sui livelli di isolamento, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Nota

I piani dinamici del cursore non usano mai indici spaziali.

Richiedere un cursore

SQL Server supporta due metodi per richiedere un cursore:

  • Transact-SQL

    Il linguaggio Transact-SQL supporta una sintassi per l'utilizzo dei cursori creati in base alla sintassi del cursore ISO.

  • Funzioni per i cursori delle API di database

    SQL Server supporta la funzionalità per i cursori delle API di database seguenti:

    • ADO (Microsoft ActiveX Data Object)

    • OLE DB

    • ODBC (Open Database Connectivity)

Questi due metodi non devono essere implementati entrambi nella stessa applicazione. Un'applicazione che usa l'API per specificare i comportamenti del cursore, non è consentito richiedere un cursore Transact-SQL tramite l'istruzione Transact-SQL DECLARE CURSOR. L'istruzione DECLARE CURSOR può essere eseguita solo dopo il ripristino di tutti i valori predefiniti degli attributi dei cursori API.

Se non è stato richiesto né un cursore Transact-SQL, né un cursore API, per impostazione predefinita SQL Server restituisce all'applicazione un set di risultati completo, ovvero un set di risultati predefinito.

Processo del cursore

I cursori Transact-SQL e i cursori API prevedono una sintassi diversa, ma per tutti i cursori SQL Server viene usata la seguente procedura generale:

  1. Associare un cursore al set di risultati di un'istruzione Transact-SQL e definirne le caratteristiche, ad esempio se le righe del cursore sono aggiornabili.

  2. Eseguire l'istruzione Transact-SQL per popolare il cursore.

  3. Recuperare le righe del cursore da visualizzare. L'operazione che consente di ottenere una riga o un blocco di righe da un cursore è definita operazione di recupero. L'esecuzione di una serie di operazioni di recupero in avanti o a ritroso è definita scorrimento.

  4. Facoltativamente, eseguire operazioni di modifica (aggiornamento o eliminazione) sulla riga che si trova nella posizione corrente del cursore.

  5. Chiudere il cursore.

Contenuto correlato