Best practice SQL per gestire l’ordinamento predefinito

Un sacco di codice SQL che ho letto sembra che lo sviluppatore supponga che l’ordinamento predefinito sia sempre valido. Ad esempio, quando si costruisce un elenco di selezione HTML, sceglierebbe solo SELECT id, name FROM table senza emettere una clausola ORDER BY .

Dalla mia esperienza personale sembra che dbms ordini sempre i dati usando FIFO se non viene fornita alcuna clausola ORDER BY e nessun indice. Tuttavia, l’ordine non è garantito. Ma non ho mai visto un dbms riordinare i dati se non ci sono cambiamenti al tavolo.

Hai mai sperimentato un dbms selezionando i dati in un ordine non deterministico se non ci sono cambiamenti nella tabella?

È consigliabile mettere sempre una clausola ORDER BY?

Non esiste un ordinamento predefinito. Anche se la tabella ha un indice cluster, non è garantito ottenere i risultati in tale ordine. È necessario utilizzare una clausola order by se si desidera un ordine specifico.

Come menzionano gli altri poster, se non si specifica un ordinamento, lo standard SQL dice che i risultati possono essere in qualsiasi ordine che il processore di query trova più vantaggioso ed efficiente.

Supponiamo che tu faccia un semplice SELECT non ordinato per tutte le righe di una tabella CUSTOMER, che non ha indici e nessuna chiave primaria. È abbastanza probabile, e anche probabile, che il processore di query esegua una scansione di tabella dritta e produca le righe nell’ordine in cui sono state originariamente inserite (dando il comportamento FIFO che hai visto).

Se si aggiunge un indice nei campi STATE e CITY (nell’ordine indicato) e quindi si esegue una query per WHERE STATE = 'NY' il processore di query potrebbe decidere che è più efficiente eseguire la scansione delle voci dell’indice per STATE = ‘NY’ anziché per eseguire una scansione completa della tabella. In questo caso probabilmente materializzerebbe le righe nell’ordine STATE, CITY.

Anche questo non è sicuro. Ad esempio se il processore di query ha raccolto statistiche che mostrano che quasi tutti i valori di STATE nella tabella sono “NY” (forse perché il database è per un’azienda di noleggio di attrezzature di Albany), potrebbe decidere che la scansione della tabella è effettivamente più economica rispetto alla scansione dell’indice e vedrai di nuovo FIFO.

È una buona idea apprendere alcune nozioni di base su come il database pianifica le sue query. È ansible utilizzare l’istruzione EXPLAIN per vedere in che modo il DBMS eseguirà una determinata query e quindi utilizzarlo per ottimizzare la query, in alcuni casi per ordine di grandezza. Questa è un’area affascinante e utile da imparare.

Se vuoi che i dati escano coerentemente ordinati, sì – devi usare ORDER BY .

Sì. Non esiste un “ordine predefinito” senza un ordine BY, e non vi è alcuna garanzia che i dati vengano recuperati in FIFO / LIFO o in qualsiasi altro ordine.

Per quanto riguarda gli sviluppatori che usano “SELECT id, nome FROM table”, sono inetti o non si preoccupano di quale ordine apparirà qualcosa.

Nessun RDBMS serio garantisce alcun ordine a meno che non si specifichi un ORDER BY esplicito.

Qualunque altra cosa è solo pura fortuna o aneddotica – se vuoi ordinare, devi specificare ORDINA PER – assolutamente no.

Se si desidera che i dati ordinati siano l’unico modo per garantire qualsiasi cosa (con tutti i principali sistemi RDBMS di cui sono a conoscenza, sicuramente Sql Server e Oracle) è necessario includere una clausola ORDER BY. FIFO non ha assolutamente nulla a che fare con i dati dell’ordine vengono restituiti senza una clausola ORDER BY, e non esiste un concetto di alcun tipo di ordine di classificazione DEFAULT. Il cosiddetto ordine di ordinamento DEFAULT è fondamentalmente comunque il motore riceve i dati, che potrebbero essere letteralmente in qualsiasi ordine in base a indici, dati memorizzati nella cache, query di esecuzione simultanee, carico sul server, ecc., Ecc.

Questo altro thread di stackoverflow copre fondamentalmente lo stesso concetto in relazione a Sql Server, AlexK ha bloggato un repository per dimostrare il comportamento.

Anche una semplice query come SELECT ... FROM table può restituire dati in vari ordini. So che questo è vero in teoria, so che questo è vero in pratica, e ho visto molti casi quando l’ordine cambia tra le esecuzioni successive, anche quando non si verificano cambiamenti di dati nella tabella.

Un esempio tipico di modifiche degli ordini tra le esecuzioni è quando la query viene eseguita utilizzando un piano parallelo. Poiché gli operatori paralleli restituiscono i dati mentre i thread sottostanti li producono, l’ordine delle righe nel risultato varia tra ogni esecuzione. Questa situazione rende anche la semplice SELECT nell’esempio restituisce risultati molto diversi ogni volta che viene eseguita.

Nella mia esperienza con SQL, la maggior parte delle volte non specifica un ORDER BY in SQL, perché i set di record sono visualizzati in un controllo di tipo grid “sul lato client ” ecc. Dove l’ordinamento dinamico è supportato – in questo caso l’ordine da SQL è inutile in quanto verrà comunque controllato dal lato client.

Anche questo viene fatto dal lato client perché la stessa query potrebbe essere utilizzata per visualizzare i dati in posizioni diverse in ordini diversi.

Pertanto, è solo la procedura migliore per inserire un ordine, quando

  • L’ordine dei dati è importante; e
  • L’ordinamento è più efficiente a livello di DB.

vale a dire se lo sviluppatore front-end sta “riordinando” lo stesso, quindi non ha senso, poiché è improbabile che risparmi tempo complessivo di elaborazione.

Forse gli autori di quelle query SQL che stai leggendo non si preoccupano dell’ordine dei dati restituiti. La migliore pratica è quella di usarlo dove è necessario per garantire l’ordine dei risultati restituiti!

Sto scrivendo questo nel caso in cui qualcuno vorrebbe usare questo come ho fatto io.

Bene, sto ottenendo un ordinamento di default soddisfacente, diciamo per le tabelle di log, con l’ordinamento su Index. Ad esempio, di solito sono interessato alle ultime righe della tabella dei registri (LIFO), quindi eseguo DateTime DESC come ordine. Ho anche provato a divertirmi ad aggiungere Index sull’altro campo (numero intero) accanto alla chiave primaria e ha funzionato.

 CREATE TABLE [dbo].[tableA]([DateTime] [datetime] NOT NULL, CONSTRAINT [PK_tableA] PRIMARY KEY CLUSTERED ([DateTime] DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] 

O in SSMS …

inserisci la descrizione dell'immagine qui