Dit is het vierde artikel in een serie van het leren van het statement VIEW SQL. Tot nu toe, we hebben veel gedaan van het creëren en wijzigen van standpunten met behulp van T-SQL. In dit laatste deel wil ik een grote blik werpen op hoe te werken met geïndexeerde weergaven.

zoals altijd, om samen met de serie te volgen, is het sterk aanbevolen om eerst de vorige delen te lezen en dan deze., Dit komt vooral omdat we onze eigen voorbeelddatabase en objecten erin vanaf nul hebben gemaakt die we ook in dit deel zullen gebruiken, maar ook omdat het veel gemakkelijker zal zijn om het grote plaatje te zien.

Hier zijn de vorige drie stukken van de Create VIEW SQL series:

• weergaven aanmaken in SQL Server
• weergaven wijzigen in SQL Server
• gegevens invoegen via weergaven in SQL Server

dus, hoofd over en lees deze voordat u verder gaat met deze.

Inleiding

het eerste wat we zullen doen is een geïndexeerde weergave maken., We zullen, natuurlijk, gebruik maken van de CREATE VIEW SQL statement voor dit zoals we vele malen hebben gedaan door de serie. Maar het algemene idee, zoals de titel zegt, is om te zien hoe te werken met geïndexeerde weergaven, te zien wat de vereisten zijn voor het toevoegen van een index aan een weergave, en hoe het programmatisch te doen. Bovendien, om de voordelen van geïndexeerde weergaven uit te leggen, zullen we kijken naar uitvoeringsplannen in SQL Server. Ze zijn een geweldig hulpmiddel voor DBA ‘ s en ontwikkelaars als het gaat om het vinden en de vaststelling van een knelpunt in de langzaam lopende query.,

Zonder verdere omhaal, we maken een weergave met de WEERGAVE MAKEN in SQL-instructie hieronder en zie wat het doet:

merk op dat deze weergave een met SCHEMABINDING optie heeft. De reden waarom het deze optie is ingeschakeld is omdat bij het maken van indexen op weergaven, ze eigenlijk fysiek worden opgeslagen in de database. Met andere woorden, alles waar deze weergave op steunt, voor zover het de tabellen betreft, kan de structuur niet veranderen van waar we naar verwijzen.

daarom moet het gebonden zijn aan de onderliggende tabellen, zodat we ze niet kunnen wijzigen op een manier die de beelddefinitie zou beïnvloeden., Als we proberen om ze op welke manier dan ook te veranderen, zal SQL Server een fout maken die zegt dat deze weergave ergens van afhangt. Dus, zie het als een harde eis voor het maken van een index op een uitzicht.

zodra het commando succesvol is uitgevoerd, ziet u de vemploeesalesorders-weergave in de map weergaven in Objectverkenner zoals hieronder getoond:

de definitie van de weergave is een iets complexere query. We kregen een aggregaat in het SELECT statement gevolgd door de groep door clausule., Vergeet niet dat wanneer we een aggregaat in de query hebben, het de getallen bij elkaar optelt, dus we moeten de groep per clausule hebben.

in principe, als er een groep door in een query, moeten we groeperen door alles dat in de select lijst behalve het aggregaat.,

nu heb ik de weergave al gemaakt, maar onthoud dat het altijd een goed idee is om de definitie van de weergave uit te testen door alleen het SELECT deel van het create VIEW SQL statement uit te voeren om te zien wat het retourneert:

Doorgaan, hier is hoe u kunt controleren of de Schemagebonden optie is ingeschakeld of uitgeschakeld., Ga naar Objectverkenner, vouw weergaven uit, klik met de rechtermuisknop op de weergave en selecteer Eigenschappen:

naast alle andere informatie in het venster Weergaveeigenschappen, zult u zien of de Schemagebonden optie is ingesteld op True of False onder de Algemene pagina.

geïndexeerde weergaven aanmaken

laten we verder gaan en een index maken in onze weergave., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:

Als we op de knop Uitvoeren in SSMS, SQL Server genereert een foutbericht met de melding dat de index kan niet worden gemaakt:

Hier is het volledige bericht dat niet kan worden gezien in de opname hierboven:

Cannot create index op zicht ‘SQLShackDB.dbo.v employeeesalesorders ‘ omdat de select lijst geen goed gebruik van COUNT_BIG bevat. Overweeg om COUNT_BIG(*) toe te voegen om de lijst te selecteren.,

we hebben COUNT_BIG nodig in dit geval gezien het feit dat we GROUP BY gebruiken in onze visie.

in het algemeen, als we aggregaten gebruiken zoals COUNT, SUM, AVG, enz. in de selectielijst van de index moeten we ook COUNT_BIG opnemen om er een index op te maken.

dat is precies wat we gaan doen.,met behulp van de WEERGAVE MAKEN in SQL-instructie en voeg COUNT_BIG aan de lijst selecteren met behulp van het script hieronder:

Als u zich afvraagt waarom dit in dit geval gebeurt, is het antwoord omdat SQL Server een manier nodig heeft om het nummer van het record dat we draaien voor de index te volgen en dit is ook een van de vele beperkingen bij het maken van geïndexeerde weergaven.

tot nu toe ziet alles er goed uit. We hebben met succes de definitie van onze weergave gewijzigd:

nu kunnen we terug gaan naar het maken van een index op de weergave door het eerder gebruikte script nog een keer uit te voeren., Deze keer zal de operatie soepel verlopen. Als we naar Object Explorer gaan en de map indexen van onze weergave uitvouwen, zullen we de nieuw aangemaakte index zien:

vanaf hier, als we met de rechtermuisknop op de index klikken en Eigenschappen selecteren, kunt u onder de Algemene pagina weergavenaam, indexnaam, type, sleutelkolommen, enz.,:

Als we overschakelen naar de Opslag, zie je dat het een Bestandsgroep omdat het fysiek opgeslagen in een database:

Verder als we overschakelen naar Fragmentatie, moet zeggen dat de Totale fragmentatie is nul procent, want wij hebben nog maar een paar records in onze tabellen:

op Zoek naar een gedetailleerde maar leuk en makkelijk om te lezen primer op het behoud en monitoring SQL-indexen? Check out SQL index onderhoud.,

indexen verwijderen

voordat we verder gaan, laten we eens kijken hoe we een index kunnen verwijderen. De makkelijkste manier is om met de rechtermuisknop op de index in Object Explorer en gebruik de optie Verwijderen. Maar in het geval dat je nodig hebt om meerdere indexen tegelijk te laten vallen, de DROP INDEX statement is handig. Dat is wat we gaan doen, want dit is tenslotte een T-SQL serie over het leren van de CREATE VIEW SQL statement.,

Use the script from below to drop the CAK_vEmployeesSalesOrders index:

1 2 3 4 5

USE SQLShackDB; GO DROP INDEX CAK_vEmployeesSalesOrders ON dbo.,v employeeesalesorders; GO

wanneer u meerdere indexen wilt laten vallen, specificeer dan alle namen gescheiden door een komma.

het genereren van willekeurige gegevens

nu, dat we verlost zijn van de index, laten we wat willekeurige gegevens genereren in onze tabel, zodat we kunnen kijken naar het uitvoeringsplan en zien hoe SQL Server Data ophaalt onder de motorkap. Het analyseren van het uitvoeringsplan toont het verschil in hoe de prestaties worden beïnvloed door het uitvoeren van de query met en zonder een index op de weergave.,

gebruik het script van onderen om 50000 willekeurige records in te voegen in de Sales tabel:

Ik ga je niet in details door het script lopen, maar het is in principe een lus die 50000 keer zal uitvoeren en willekeurige gegevens in de Sales tabel zal invoegen., om te controleren of de records werden ingevoegd met succes, het uitvoeren van de volgende instructie die tellen alle records uit de tabel Verkoop:

1 2 3 4

GEBRUIK SQLShackDB; GO SELECT COUNT(*) UIT de Verkoop

Het aantal geretourneerde laat zien dat je er 50006 rijen in de tabel Verkoop., Dit is het aantal records dat we net gegenereerd hebben + 6 dat we in eerste instantie hadden:

analyseren uitvoeringsplannen

nu we enkele gegevens in onze tabel hebben, kunnen we echt het gebruik van een index in de weergave demonstreren. Laten we de vEmployeeSalesOrders bekijken en zien hoe SQL Server haalt de gegevens., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:

1 2 3 4

USE SQLShackDB; GO SELECT * FROM dbo.,v employeeesalesorders;

Dit script gaf 23814 rijen terug, maar wat belangrijker is, het genereerde het uitvoeringsplan van de query. Vergeet niet dat we eerder liet de index op onze mening. Dus, op dit moment is er geen index op onze mening. Daarom zal SQL Server een paar tabelscans doen zoals hieronder getoond:

Dit is vrijwel het ergste in de databasewereld, vooral op tabellen met een grote hoeveelheid data., Het is goed om tafel scans met een kleine hoeveelheid gegevens, bijvoorbeeld de zaak met onze medewerkers en producten tabellen, maar het is slecht voor de verkoop tabel omdat het heeft 50K+ records.

De makkelijkste manier om van de tabelscans af te komen is door er een index op aan te maken omdat het de dingen dramatisch versnelt. Dus, wat we zullen doen om dit probleem op te lossen is het opnieuw uitvoeren van het script voor het maken van de unieke geclusterde index op de vemployeesalesorders uitzicht.

als we nu het SELECT statement opnieuw uitvoeren, zullen er geen verschillen zijn, ook al hebben we net de index aangemaakt. Waarom is dat?, Omdat ik gebruik van de SQL Server Express edition voor het doel van deze serie, en alleen in Enterprise en ontwikkelaar edities van SQL Server zal de Query Optimizer daadwerkelijk rekening houden met de index.

geen zorgen omdat we SQL Server daadwerkelijk kunnen dwingen om een index te gebruiken bij het genereren van uitvoeringsplannen. Dit wordt gedaan met behulp van de NOEXPAND optie., NOEXPAND is alleen van toepassing op geïndexeerde weergaven:

1	SELECTEER * VAN dbo.v employeeesalesorders WITH (NOEXPAND)

net zo hebben we SQL Server gedwongen om de geclusterde index te gebruiken, wat in principe betekent dat de onderliggende tabellen niet gebruikt worden bij het ophalen van gegevens., Zoals hieronder te zien is, hebben we enige vooruitgang geboekt door het elimineren van een aantal operaties:

In feite kunnen we tegelijkertijd statements selecteren en de resultaten vergelijken door naar de uitvoeringsplannen te kijken:

zou je dat willen bekijken? Als we de query kosten van de eerste SELECT statement (w/o index 95%) vergelijken met de tweede SELECT statement (w/ index 5%), zou ik zeggen dat is een enorme prestatiewinst met behulp van een enkele index.,

conclusie

indexen zijn geweldig omdat ze de prestaties versnellen en met een index op een weergave zou het echt de prestaties moeten versnellen omdat de index in de database is opgeslagen. Het indexeren van zowel weergaven en tabellen is een van de meest efficiënte manieren om de prestaties van query ‘ s en applicaties die ze gebruiken te verbeteren.

Ik wil graag afronden en deze serie van het leren van het CREATE VIEW SQL statement met dit artikel afronden. We hebben vrijwel alles besproken over het creëren en wijzigen van weergaven met T-SQL., We begonnen met de CREATE VIEW SQL statement, creëerden een paar weergaven, wijzigden ze, verwijderd, en nog veel meer.

Ik hoop dat deze serie van het leren van de CREATE VIEW SQL statement informatief voor u is geweest en ik dank u voor het lezen ervan.,

het MAKEN van SQL WEERGEVEN: het Invoegen van gegevens door middel van het uitzicht in SQL Server

het MAKEN van SQL WEERGEVEN: Werken met geïndexeerde weergaven in SQL Server

• Auteur
• Laatste Berichten

Bojan aka “Boksi”, een AP afgestudeerd in de IT-Technologie gericht op Netwerken en elektronische technologie van de Copenhagen School of Design en Technologie, is een software-analist met ervaring in kwaliteitszorg, software, ondersteuning, product evangelisatie en betrokkenheid van gebruikers.,
hij heeft uitgebreid geschreven over zowel de SQL Shack als het ApexSQL Solution Center, over onderwerpen variërend van klanttechnologieën zoals 4K-resolutie en themering, foutafhandeling tot indexstrategieën en prestatiebewaking.Bojan werkt bij ApexSQL in Nis, Servië als een integraal onderdeel van het team dat zich richt op het ontwerpen, ontwikkelen en testen van de volgende generatie databasetools, waaronder MySQL en SQL Server, en zowel stand-alone tools als integraties in Visual Studio, SSMS en VSCode.,
Zie voor meer informatie over Bojan op LinkedIn
Bekijk alle berichten van Bojan Petrovic

Laatste berichten door Bojan Petrovic (zie all)

• Visual Studio Code voor MySQL en MariaDB ontwikkeling – 13 augustus 2020
• SQL UPDATE-syntaxis uitgelegd – juli 10, 2020
• het MAKEN van SQL WEERGEVEN: Werken met geïndexeerde weergaven in SQL Server – 24 Maart 2020