Questo è il quarto articolo di una serie di apprendimento dell’istruzione CREATE VIEW SQL. Finora, abbiamo fatto una grande quantità di creazione e modifica delle viste utilizzando T-SQL. In questa ultima parte, voglio dare un’occhiata a come lavorare con le viste indicizzate.

Come sempre, per seguire la serie, si consiglia vivamente di leggere prima le parti precedenti e poi questa., Questo è principalmente perché abbiamo creato il nostro database di esempio e gli oggetti in esso da zero che useremo anche in questa parte, ma anche perché sarà molto più facile vedere il quadro generale.

Ecco i tre pezzi precedenti della serie CREATE VIEW SQL:

• Creazione di viste in SQL Server
• Modifica delle viste in SQL Server
• Inserimento di dati attraverso le viste in SQL Server

Quindi, vai e leggi quelli prima di continuare con questo.

Introduzione

La prima cosa che faremo è creare una vista indicizzata., Noi, naturalmente, utilizzare l’istruzione CREATE VIEW SQL per questo come abbiamo fatto molte volte attraverso la serie. Ma l’idea generale, come dice il titolo, è vedere come lavorare con le viste indicizzate, vedere quali sono i requisiti per aggiungere un indice a una vista e come farlo a livello di programmazione. Inoltre, per spiegare i pro delle viste indicizzate, esamineremo i piani di esecuzione in SQL Server. Sono un ottimo strumento per i DBA e gli sviluppatori quando si tratta di trovare e correggere un collo di bottiglia nella query a esecuzione lenta.,

Senza ulteriori indugi, andiamo a creare una vista utilizzando l’istruzione SQL per la CREAZIONE della VISTA da sotto e vedere cosa fa:

Si noti che questa vista ha un’opzione CON SCHEMABINDING su di essa. Il motivo per cui questa opzione è attivata è perché quando si creano indici sulle viste, vengono effettivamente memorizzati fisicamente nel database. In altre parole, tutto ciò su cui si basa questa vista, per quanto riguarda le tabelle, la struttura non può cambiare da ciò che stiamo facendo riferimento.

Pertanto, deve essere associato alle tabelle sottostanti in modo che non possiamo modificarle in un modo che influenzerebbe la definizione della vista., Se proviamo a cambiarli in qualsiasi modo, SQL Server genererà un errore dicendo che questa vista dipende da qualcosa. Quindi, guardalo come un requisito difficile per creare un indice su una vista.

Una volta eseguito correttamente il comando, dovresti vedere la vista vEmployeeSalesOrders nella cartella Views in Object Explorer come mostrato di seguito:

La definizione della vista è una query un po ‘ più complessa. Abbiamo ottenuto un aggregato nell’istruzione SELECT seguito dalla clausola GROUP BY., Ricorda, quando abbiamo un aggregato nella query, aggiunge i numeri insieme, quindi dobbiamo avere la clausola GROUP BY.

Fondamentalmente, quando c’è un GROUP BY in una query, dobbiamo raggruppare per tutto ciò che è nell’elenco select tranne l’aggregato.,

Ora, ho già creato la vista, ma ricordate che è sempre una buona idea per testare la definizione della vista eseguendo solo SELEZIONARE la parte di VISUALIZZAZIONE CREATE istruzione SQL per vedere cosa restituisce:

passare, ecco come si può verificare se lo Schema associato opzione è abilitata o disabilitata., Oltre al capo Object Explorer, espandere viste, fare clic destro sulla vista, e selezionare Proprietà:

Tra tutte le altre informazioni nella finestra Visualizza proprietà, vedrete se l’opzione Schema associato è impostato su True o False sotto la pagina Generale.

Creazione di viste indicizzate

Andiamo avanti e creiamo un indice sulla nostra vista., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:

Se si ha colpito il pulsante di Esecuzione in sql server management studio, SQL Server restituirà un errore dicendo che l’indice non può essere creata:

Ecco il messaggio di errore completo che non può essere visto nella foto sopra:

Non è possibile creare un indice sulla vista ‘SQLShackDB.dbo.vEmployeeSalesOrders ‘ perché il suo elenco di selezione non include un uso corretto di COUNT_BIG. Considerare l’aggiunta di COUNT_BIG (*) per selezionare l’elenco.,

Abbiamo bisogno di COUNT_BIG in questo caso dato che stiamo usando GROUP BY nella nostra vista.

In generale, se stiamo usando aggregati come COUNT, SUM, AVG, ecc. nell’elenco di selezione dell’indice, dobbiamo anche includere COUNT_BIG per creare un indice su di esso.

Questo è esattamente quello che faremo.,utilizzando l’istruzione SQL per la CREAZIONE della VISTA e aggiungere COUNT_BIG elenco di selezione, utilizzando lo script di seguito:

Se vi state chiedendo perché questo sta accadendo in questo caso, la risposta è perché SQL Server ha bisogno di un modo di individuare il numero del record che si sta girando per l’indice, e questo è anche uno di un sacco di limitazioni con la creazione di viste indicizzate.

Finora, tutto sembra a posto. Abbiamo cambiato con successo la definizione della nostra vista:

Ora, possiamo tornare a creare un indice sulla vista eseguendo ancora una volta lo script utilizzato in precedenza., Questa volta, l’operazione si completerà senza intoppi. Se andiamo a Object Explorer ed espandiamo la cartella Indici della nostra vista, vedremo l’indice appena creato:

Da qui, se facciamo clic destro sull’indice e selezioniamo Proprietà, sotto la pagina Generale, puoi vedere nome vista, nome indice, tipo, colonne chiave, ecc.,:

Se si passa a Stoccaggio, vedrai che ha un Filegroup perché è fisicamente memorizzati in un database:

Inoltre, se si passa a Frammentazione, si dovrebbe dire che la Totale frammentazione è pari a zero per cento, perchè abbiamo solo un paio di record nelle tabelle:

Cerca un dettagliato ma divertente e facile da leggere di primer sulla manutenzione e monitoraggio di SQL indici? Controlla la manutenzione dell’indice SQL.,

Eliminazione degli indici

Prima di andare oltre, vediamo come possiamo eliminare un indice. Il modo più semplice è fare clic destro sull’indice in Esplora oggetti e utilizzare l’opzione Elimina. Ma nel caso in cui sia necessario eliminare più indici contemporaneamente, l’istruzione DROP INDEX è utile. Questo è quello che faremo, perché, dopo tutto, questa è una serie T-SQL sull’apprendimento dell’istruzione CREATE VIEW SQL.,

Use the script from below to drop the CAK_vEmployeesSalesOrders index:

Quando è necessario eliminare più indici, è sufficiente specificare tutti i nomi separati da una virgola.

Generazione di dati casuali

Ora, che ci siamo sbarazzati dell’indice, generiamo alcuni dati casuali nella nostra tabella in modo da poter guardare il piano di esecuzione e vedere come SQL Server recupera i dati sotto il cofano. L’analisi del piano di esecuzione mostrerà la differenza nel modo in cui le prestazioni sono influenzate dall’esecuzione della query con e senza un indice nella vista.,

Usa lo script dal basso per inserire 50000 record casuali nella tabella delle vendite:

Non ti illustrerò lo script nei dettagli, ma è fondamentalmente un ciclo che eseguirà 50000 volte e inserirà dati casuali nella tabella delle vendite., per verificare se i record sono stati inseriti correttamente, eseguire la seguente istruzione SELECT che potrà contare tutti i record della tabella Vendite:

Il numero restituito mostra che ci sono 50006 le righe della tabella Vendite., Questo è il numero di record che abbiamo appena generato + 6 che inizialmente avevamo:

Analizzando i piani di esecuzione

Ora che abbiamo alcuni dati nella nostra tabella, possiamo davvero dimostrare l’uso di un indice sulla vista. Interroghiamo la vista vEmployeeSalesOrders e vediamo come SQL Server recupera i dati., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:

Questo script ha restituito 23814 righe, ma la cosa più importante, ha generato il piano di esecuzione della query. Ricorda che in precedenza abbiamo lasciato cadere l’indice sulla nostra vista. Quindi, al momento non c’è nessun indice sulla nostra vista. Pertanto, SQL Server eseguirà alcune scansioni di tabelle come mostrato di seguito:

Questa è praticamente la cosa peggiore nel mondo del database, specialmente su tabelle con una grande quantità di dati., Va bene avere scansioni di tabelle con una piccola quantità di dati, ad esempio il caso con le tabelle dei nostri dipendenti e dei prodotti, ma è negativo per la tabella di vendita perché ha 50K+ record.

Il modo più semplice per sbarazzarsi delle scansioni della tabella è creare un indice su di esso perché accelera drasticamente le cose. Quindi, quello che faremo per risolvere questo problema è rieseguire lo script per creare l’indice cluster univoco nella vista vEmployeeSalesOrders.

Ora, se rieseguiamo l’istruzione SELECT, non ci saranno differenze anche se abbiamo appena creato l’indice. Perche ‘ mai?, Poiché sto usando SQL Server Express edition per lo scopo di questa serie, e solo nelle edizioni Enterprise e Developer di SQL Server l’ottimizzatore di query prenderà effettivamente in considerazione l’indice.

Non preoccuparti perché possiamo effettivamente forzare SQL Server a utilizzare un indice durante la generazione di piani di esecuzione. Questo viene fatto utilizzando l’opzione NOEXPAND., NOEXPAND si applica solo per le viste indicizzate:

Proprio così, abbiamo costretto SQL Server a utilizzare l’indice cluster che in pratica significa non utilizzare le tabelle sottostanti durante il recupero dei dati., Come si può vedere qui sotto, abbiamo fatto qualche progresso, eliminando una serie di operazioni:

In effetti, siamo in grado di executeboth istruzioni SELECT contemporaneamente e confrontare i risultati di ricerca presso i piani di esecuzione:

vuoi vedere che? Se confrontiamo il costo della query della prima istruzione SELECT (senza indice 95%) con la seconda istruzione SELECT (con indice 5%), direi che è un enorme guadagno di prestazioni usando un singolo indice.,

Conclusione

Gli indici sono ottimi perché accelerano le prestazioni e con un indice su una vista dovrebbe davvero accelerare le prestazioni perché l’indice è memorizzato nel database. L’indicizzazione sia delle viste che delle tabelle è uno dei modi più efficienti per migliorare le prestazioni delle query e delle applicazioni che le utilizzano.

Mi piacerebbe concludere le cose e finire questa serie di apprendimento dell’istruzione CREATE VIEW SQL con questo articolo. Abbiamo praticamente coperto tutto ciò che riguarda la creazione e la modifica delle viste con T-SQL., Abbiamo iniziato con l’istruzione CREATE VIEW SQL, creato alcune viste, alterate, eliminate e molto altro.

Spero che questa serie di apprendimento dell’istruzione CREATE VIEW SQL sia stata informativa per te e ti ringrazio per averla letta.,

CREARE una VISTA SQL: Inserimento di dati attraverso viste in SQL Server

CREARE una VISTA SQL: Lavorare con le viste indicizzate in SQL Server

• Autore
• Post Recenti

Bojan aka “Boksi”, un AP laureato in informatica focalizzata sulle Reti e tecnologie elettroniche da Copenaghen Scuola di Design e Tecnologia, è un software analyst con esperienza nel controllo qualità, assistenza software, prodotto di evangelizzazione, e il coinvolgimento degli utenti.,
Ha scritto molto sia su SQL Shack che su ApexSQL Solution Center, su argomenti che vanno dalle tecnologie client come risoluzione 4K e tematizzazione, gestione degli errori, strategie di indicizzazione e monitoraggio delle prestazioni.
Bojan lavora presso ApexSQL a Nis, Serbia come parte integrante del team concentrandosi sulla progettazione, lo sviluppo e il test della prossima generazione di strumenti di database tra cui MySQL e SQL Server, e sia strumenti stand-alone e integrazioni in Visual Studio, SSMS, e VSCode.,
Vedi di più su Bojan a LinkedIn
Visualizza tutti i messaggi di Bojan Petrovic

Ultimi messaggi di Bojan Petrovic (vedi tutti)

• Visual Studio Codice per MySQL e MariaDB sviluppo – agosto 13, 2020
• AGGIORNAMENTO SQL sintassi – ha spiegato il 10 luglio 2020
• CREARE una VISTA SQL: Lavorare con le viste indicizzate in SQL Server – Marzo 24, 2020