SQL Magazine 7 article – SQL Server: Turbinera dina frågor med indexerade vyer

Use NorthWind go create view vi_vendas_mes As Select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity) from Orders o inner join od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate), datepart(mm,o.OrderDate) go select * from vi_vendas_mês go ano mes qtde_total contador ----------- ----------- ----------- -------------------- 1996 7 1462 59 1996 8 1322 69 1996 9 1124 57 1996 10 1738 73 1996 11 1735 66

En av fördelarna med att använda vyer är följande:

• Förenkling av koden: Du kan skriva komplexa SELECTs en gång, kapsla in dem i vyn och trigga dem från den, som om det vore vilken tabell som helst;
• Säkerhetsfrågor: Anta att du har konfidentiell information i vissa tabeller och att du vill att endast vissa användare ska ha tillgång till dem. Vissa kolumner i dessa tabeller måste dock vara tillgängliga för alla användare. Ett effektivt sätt att lösa problemet är att skapa en vy och dölja de känsliga kolumnerna. På så sätt kan man undertrycka åtkomsträttigheterna till den ursprungliga tabellen och frigöra åtkomst till vyn;
• Möjlighet till optimering av frågor genom att implementera indexerade vyer.

Indexerade vyer i praktiken

Vyer kapslar in SELECT-utsagor, vilket innebär att SELECT-utsagorna som är associerade med dem exekveras närhelst de utlöses. Vyer skapar inte arkiv för de data de returnerar (vilket tabellen gör). Det skulle vara bra om vi kunde ”materialisera” resultatet av SELECT-kommandot som finns i vyn i en tabell och skapa index för att underlätta åtkomsten till det. De indexerade vyerna gör just det. Att utföra ett SELECT i en indexerad vy har samma effekt som att utföra ett select i en konventionell tabell.

Det huvudsakliga syftet med indexerade vyer är att öka prestandan, och fördelen med SQL Server är att låta exekveringsplaner ta hänsyn till den indexerade vyn som ett sätt att få tillgång till data, även om vyns namn inte var explicit i frågan. Detta är möjligt i Enterprise Edition av SQL Server 2000, där kommandoptimeraren kan välja data direkt i den indexerade vyn (i stället för att välja de rådata som finns i tabellen), vilket vi kommer att se härnäst.

Skapa en indexerad vy steg för steg

1. Det första steget i konfigureringen av miljön är att ställa in tillståndet för vissa parametrar i sessionen där du vill skapa och använda vyn, eftersom den indexerade vyn ”materialiseras” i en tabell och inget kan störa dess resultat. Tänk dig till exempel följande scenario:
2. En viss inställning, som påverkar resultatet av en SELECT (t.ex. concat_null_yelds_null), ställs in innan den indexerade vyn skapas;
3. Den indexerade vyn skapas; observera att resultatet av vyn kommer att ”materialiseras” på disken i enlighet med inställningen som ställdes in i det föregående steget;
4. Därefter inaktiveras inställningen och den indexerade vyn körs av användaren. Eftersom vyn har materialiserats kommer vi att få ett resultat som inte överensstämmer med den aktuella konfigurationen.

I listning 2 visas till exempel skillnaden i resultatet av ett kommando när egenskapen concat_null_yields_null ändras.

set concat_null_yields_null ON print null + 'abc' -------------------------------------- Set concat_null_yields_null OFF print null + 'abc' -------------------------------------- abc

Föreställ dig vad som skulle hända om den indexerade vyn skapades med egenskapen concat_null_yields_null aktiverad, men den aktuella sessionen var med den egenskapen inaktiverad – samma SELECT skulle leda till olika resultat!

Detta problem löstes på ett enkelt sätt – för att skapa och använda indexerade vyer är det obligatoriskt att konfigurera miljön enligt en lista med standardvärden. På så sätt är det omöjligt att få olika resultat, eftersom vyn helt enkelt inte fungerar om någon av inställningarna är inställd på ett avvikande värde.

Tabell 1 visar dessa inställningar och deras respektive standardvärden.

(*) Id används i kommandot sp_configure. Läs avsnittet ”Krävande inställningar för indexerade vyer” för att kontrollera vad varje inställning gör. Det finns två sätt att ändra värdet på en konfiguration:

• Direkt i sessionen: kör kommandot set ON | OFF
• Ändra det befintliga standardvärdet för servern: kör sp_Configure ’user options’, . Id-numret för varje konfiguration framgår av tabell 1.
  Notera: AritHabort har id 64 och Quoted_Identifier har id 256. För att länka till exempel Quoted_Identifier + AritHabort skulle vi utföra sp_cofigure och som parameter skicka resultatet av 64+256 (=320): sp_configure ’user options’, 320. En fullständig lista över de id som tilldelats varje konfiguration finns på http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_9b1q.asp.

För att bekräfta tillståndet för var och en av parametrarna i tabell 1 använder du funktionen SessionProperty(’parameter name’) eller kommandot DBCC UserOptions.

Sätt alla parametrar enligt kolumnen ”required state for indexed views” i tabell 1 – om du inte gör detta kommer SQL Server inte att tillåta dig att skapa/utföra den indexerade vyn.

Skapa den indexerade vyn

Vi kommer att skapa en vy för att summera det dagliga beloppet som sålts i tabellen Order Details, som finns i NorthWind-databasen. Se Listing 3.

use NorthWind go create view vi_vendas_mes with SchemaBinding as select ano = datepart(yyyy,OrderDate), mes = datepart(mm,OrderDate), qtde_total = sum(Quantity), contador = count_big(*) from dbo.Orders o inner join dbo. od on o.OrderId = od.OrderId group by datepart(yyyy,o.OrderDate),datepart(mm,o.OrderDate) go

Vi måste observera vissa särdrag när vi skapar indexerade vyer:

• Vyn måste vara deterministisk. En vy är deterministisk om vi endast använder deterministiska funktioner i dess kod. Samma SELECT-kommando som utförs upprepade gånger på en indexerad vy (med en statisk bas) kan inte ge olika resultat. Deterministiska funktioner garanterar att resultatet av en funktion förblir oförändrat oavsett hur många gånger den utförs. Funktionen DatePart är till exempel deterministisk, eftersom den alltid ger samma resultat för ett visst datum. Funktionen getdate() returnerar ett annat värde varje gång den utförs. En fullständig lista över SQL Server 2000-deterministiska funktioner finns på http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_95v7.asp
• Kontrollera att det inte finns några syntaxbegränsningar. De klausuler, funktioner och frågetyper som anges nedan kan inte integrera schemat för en indexerad vy:
  • MIN, MAX,TOP
  • VARIANCE,STDEV,AVG
  • COUNT(*)
  • SUMMA på kolumner som tillåter nollvärden
  • DISTINCT
  • ROWSET-funktionen
  • Avledda tabeller, self joins, subqueries, outer joins
  • DISTINCT
  • UNION
  • Float, text, ntext och image
  • COMPUTE och COMPUTE BY
  • HAVING, CUBE och ROLLUP

Det är nödvändigt att skapa de indexerade vyerna med SchemaBinding. För att innehållet i vyn ska vara konsekvent kan du inte ändra strukturen på de tabeller som ligger till grund för vyn. För att undvika den här typen av problem är det obligatoriskt att använda SchemaBinding när du skapar indexerade vyer, eftersom du med det här alternativet inte kan ändra tabellstrukturen utan att först ta bort vyn.

För att använda GROUP BY-klausulen är det obligatoriskt att inkludera funktionen COUNT_BIG(*). Funktionen count_big(*) gör samma sak som count(*), men returnerar ett värde av typen bigint (8 bytes).

Informera alltid ägaren av de objekt som refereras i den indexerade vyn. Använd select * from dbo.Orders istället för select * from Orders, eftersom det är möjligt att ha tabeller med samma namn men med olika ägare. Eftersom schemabindningsalternativet är obligatoriskt behöver SQL Server den exakta objektspecifikationen för att förhindra ändringar i schemat.

Skapa ett klusterindex i vyn (materialisering)

Vyn som skapades i punkt 2 fungerar ännu inte som en indexerad vy, eftersom resultatet av select-kommandot inte har materialiserats till en tabell. Du kan bekräfta detta genom att köra kommandot sp_spaceused i Query Analyzer, som returnerar antalet rader och det utrymme som används av tabellerna (Listing 4).

sp_SpaceUsed vi_vendas_mes -------------------------------------------------------------------------------------- Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91 Views do not have space allocated.

Set statistics io ON Select * from vi_vendas_mesgo---------------------------------------------------------ano mes qtde_total contador ----------- ----------- ------------- -------------------- 1996 7 1462 591996 8 1322 691996 9 1124 57.....(23 row(s) affected)Table 'Order Details'. Scan count 830, logical reads 1672, physical reads 0, read-ahead reads 0.Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.

För att kontrollera om vyn kan indexeras (materialiserad), det vill säga om den skapades enligt de standarder och inställningar som krävs för indexerade vyer, bör resultatet av följande SELECT vara lika med 1.

select ObjectProperty(object_id('vi_vendas_mes'),'IsIndexable')

Vid bekräftelse av förutsättningarna kan vi nu skapa indexet. Syntaxen har samma format som när du skapar index i konventionella tabeller:

create unique clustered index pk_ano_mes on vi_vendas_mes (ano,mes)