sql studio SET NOCOUNT ON usage




sqlcmd set nocount (10)

Ispirato da questa domanda dove ci sono opinioni diverse su SET NOCOUNT ...

Dovremmo usare SET NOCOUNT ON per SQL Server? Se no, perché no?

Cosa fa Modifica 6, il 22 luglio 2011

Sopprime il messaggio "xx rows affected" dopo qualsiasi DML. Questo è un set di risultati e quando viene inviato, il client deve elaborarlo. È piccolo, ma misurabile (vedi le risposte di seguito)

Per i trigger ecc, il client riceverà più "xx rows affected" e questo causa tutti i tipi di errori per alcune ORM, MS Access, JPA ecc. (Vedi le modifiche seguenti)

Sfondo:

La best practice generalmente accettata (ho pensato fino a questa domanda) è di utilizzare SET NOCOUNT ON in trigger e stored procedure in SQL Server. Lo usiamo ovunque e un rapido google mostra un sacco di MVP di SQL Server che concordano.

MSDN dice che questo può rompere un .net SQLDataAdapter .

Ora, questo significa per me che SQLDataAdapter è limitato alla semplice elaborazione CRUD perché si aspetta che il messaggio "n rows affected" corrisponda. Quindi, non posso usare:

  • SE ESISTE per evitare duplicati (nessun messaggio sulle righe interessate) Nota: usare con cautela
  • DOVE NON ESISTE (meno file quindi previste
  • Filtrare gli aggiornamenti banali (ad esempio, nessun dato cambia effettivamente)
  • Prima di qualsiasi accesso alla tabella (come la registrazione)
  • Nascondi complessità o denormlizzazione
  • eccetera

Nella domanda marc_s (che conosce le sue cose SQL) dice di non usarlo. Questo è diverso da quello che penso (e mi considero piuttosto competente anche per SQL).

È possibile che mi manchi qualcosa (sentiti libero di indicare l'ovvio), ma cosa ne pensi?

Nota: sono passati anni da quando ho visto questo errore perché oggigiorno non uso SQLDataAdapter.

Modifiche dopo commenti e domande:

Modifica: altri pensieri ...

Abbiamo più client: uno può utilizzare un C # SQLDataAdaptor, un altro può usare nHibernate da Java. Questi possono essere influenzati in diversi modi con SET NOCOUNT ON .

Se si considerano i proc memorizzati come metodi, allora è una cattiva forma (anti-pattern) assumere che alcune elaborazioni interne funzionino in un certo modo per i propri scopi.

Modifica 2: una domanda nHibernate che interrompe il trigger , in cui non è possibile impostare SET NOCOUNT ON

(e no, non è un duplicato di this )

Modifica 3: Ancora più informazioni, grazie al mio collega MVP

Modifica 4: 13 maggio 2011

Interrompe anche Linq 2 SQL quando non specificato?

Modifica 5: 14 giugno 2011

Interrompe JPA, stored proc con variabili di tabella: JPA 2.0 supporta le variabili di tabella di SQL Server?

Modifica 6: 15 agosto 2011

La griglia di dati "Modifica righe" SSMS richiede SET NOCOUNT ON: trigger di aggiornamento con GROUP BY

Modifica 7: 07 marzo 2013

Dettagli più approfonditi da @RemusRusanu:
SET NOCOUNT ON rende davvero molto di una differenza di prestazioni


SET NOCOUNT ON;

Questa riga di codice viene utilizzata in SQL per non restituire le righe numeriche interessate nell'esecuzione della query. Se non è necessario il numero di righe interessate, è possibile utilizzarlo in quanto ciò contribuirebbe a risparmiare l'utilizzo della memoria e ad aumentare la probabilità di esecuzione della query.


A rischio di complicare le cose, incoraggio una regola leggermente diversa da tutti quelli che vedo sopra:

  • Impostare sempre NOCOUNT ON nella parte superiore di un proc, prima di eseguire qualsiasi operazione nel proc, ma anche sempre SET NOCOUNT OFF nuovo, prima di restituire qualsiasi recordset dal proc memorizzato.

Quindi "in genere tieni premuto nocount, tranne quando stai effettivamente restituendo un set di risultati". Non conosco alcun modo in cui questo possa infrangere qualsiasi codice client, significa che il codice cliente non ha mai bisogno di sapere nulla sull'interno del proc e non è particolarmente oneroso.


Se stai dicendo che potresti avere anche client diversi, ci sono problemi con ADO classico se SET NOCOUNT non è impostato su ON.

Uno che ho esperienza regolarmente: se una stored procedure esegue un numero di istruzioni (e quindi viene restituito un numero di messaggi "xxx righe interessate"), ADO sembra non gestirlo e genera l'errore "Impossibile modificare la proprietà ActiveConnection di un oggetto Recordset che ha come oggetto un oggetto Command. "

Quindi in genere sostengo di impostarlo su ON a meno che non ci sia davvero una buona ragione per non farlo. potresti aver trovato la ragione veramente buona che ho bisogno di leggere e leggere di più.


Ok ora ho fatto le mie ricerche, ecco l'affare:

Nel protocollo TDS, SET NOCOUNT ON salva solo 9 byte per query . Ero solito pensare che 9 row(s) affected fossero restituite dal server in testo normale in un pacchetto di rete separato, ma non è questo il caso. In realtà è una piccola struttura chiamata DONE_IN_PROC incorporata nella risposta. Non è un pacchetto di rete separato, quindi non vengono sprecati roundtrip.

Penso che tu possa limitarti a SET NOCOUNT ON senza preoccuparti delle prestazioni.

Ecco un'analisi molto dettagliata sull'insignificanza dell'impostazione SET NOCOUNT : http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/


  • Quando SET NOCOUNT è ON, il conteggio (che indica il numero di righe interessate da un'istruzione Transact-SQL) non viene restituito. Quando SET NOCOUNT è OFF, viene restituito il conteggio. Viene utilizzato con qualsiasi istruzione SELECT, INSERT, UPDATE, DELETE.

  • L'impostazione di SET NOCOUNT è impostata al momento dell'esecuzione o del tempo di esecuzione e non in fase di analisi.

  • SET NOCOUNT ON migliora le prestazioni della stored procedure (SP).

  • Sintassi: SET NOCOUNT {ON | OFF}

Esempio di SET NOCOUNT ON:

Esempio di SET NOCOUNT OFF:


Non so come testare SET NOCOUNT ON tra client e SQL, quindi ho testato un comportamento simile per l'altro comando SET "SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED"

Ho inviato un comando dalla mia connessione cambiando il comportamento predefinito di SQL (READ COMMITTED), ed è stato modificato per i comandi successivi. Quando ho cambiato il livello di ISOLATION all'interno di una procedura memorizzata, non ha modificato il comportamento della connessione per il comando successivo.

Conclusione attuale,

  1. La modifica delle impostazioni all'interno della stored procedure non modifica le impostazioni predefinite della connessione.
  2. La modifica dell'impostazione inviando comandi utilizzando la regola ADOCO modifica il comportamento predefinito.

Penso che questo sia rilevante per altri comandi SET come "SET NOCOUNT ON"


Per quanto riguarda i grilletti che rompono NHibernate, ho avuto quell'esperienza di prima mano. Fondamentalmente, quando NH esegue un UPDATE, si aspetta che venga influenzato un certo numero di righe. Aggiungendo SET NOCOUNT ON ai trigger si ottiene di nuovo il numero di righe su quanto previsto da NH, risolvendo in tal modo il problema. Quindi sì, consiglierei di disattivarlo per i trigger se usi NH.

Per quanto riguarda l'utilizzo in SP, è una questione di preferenze personali. Avevo sempre disattivato il conteggio delle righe, ma poi di nuovo, non ci sono veri argomenti forti in entrambi i casi.

In una nota diversa, dovresti prendere in considerazione l'idea di abbandonare l'architettura basata su SP, quindi non avrai nemmeno questa domanda.


if (set no count == off)

{quindi manterrà i dati sul numero di record interessati in modo da ridurre le prestazioni} altrimenti {non traccerà il record delle modifiche quindi migliorerà la perfomazia}}


So che è una domanda piuttosto vecchia. ma solo per l'aggiornamento.

Il modo migliore per usare "SET NOCOUNT ON" è metterlo come prima dichiarazione nel tuo SP e impostarlo nuovamente su OFF appena prima dell'ultima istruzione SELECT.


Suppongo che in un certo senso si tratti di un problema di DBA vs. sviluppatori.

Per lo più, per lo più, direi che non lo uso a meno che tu non abbia assolutamente bisogno di farlo, perché usarlo può infrangere il tuo codice ADO.NET (come documentato da Microsoft).

E suppongo che, come DBA, saresti di più dall'altra parte - usalo quando è possibile, a meno che tu non debba impedirne l'utilizzo.

Inoltre, se i tuoi sviluppatori usano mai "RecordsAffected" restituito dalla chiamata del metodo ExecuteNonQuery di ADO.NET, sei nei guai se tutti utilizzano SET NOCOUNT ON poiché in questo caso, ExecuteNonQuery restituirà sempre 0.

Vedi anche il post sul blog di Peter Bromberg e controlla la sua posizione.

Quindi si riduce a chi riesce a stabilire gli standard :-)

Marc





concurrency