Tags:    databaser

Stored Procedures i MS SQL Server 2000

Denne artikel gennemgår de teoretiske aspekter i at benytte Stored Procedures. Stored Procedures giver database udvikleren utrolig mange fordele med hensyn til at styre performance, sikkerhed. SQL Serveren tilbyder et programmeringsmiljø i hvilket det er muligt at programmere Stored Procedures der kan udføre komplekse opgaver for brugeren.

Performance forbedringer

Hver gang en Transact SQL kommando sendes afsted til serveren, skal serveren først evaluere afsenderen om denne har tilstrækkelige rettigheder til at eksekvere denne kommando og om kommandoen er gyldig. Når disse to ting er valideret bygger SQL Serveren en execution plan over Transact SQL kommandoen, det vil sige hvordan den skal afvikles. Stored Procedures er mere effektive end almindelige Transact SQL kommandoer da de, når de bliver lavet bliver gemt på SQL Serveren. Dette betyder at når brugeren eksekverer en Stored Procedure fra eksempelvis en klient applikation, skal brugeren kun udstede en enkelt linie Transact SQL, der aktiverer den Stored Procedure hvorefter denne afvikles på serveren. Dette gør blandt andet at der ikke skal sendes store mængder Transact SQL over netværket, der hver gang skal valideres og kontrolleres for brugerrettigheder. Før en stored procedure bliver gemt på serveren bliver dens syntaks valideret, hvis ikke der er fejl i syntaksen bliver den Stored Procedures navn gemt i SysObjects tabellen mens teksten på den Stored Procedure bliver gemt i SysComments tabellen. Første gang en Stored Procedure bliver eksekveret en execution plan bliver genereret og den Stored Procedure bliver compilet. Efterfølgende vil eksekveringen af den Stored Procedure være hurtigere da den Stored Procedure ikke skal igennem alle opgaverne med at validere syntaks, lave execution plan og compile koden.

Programmeringsmiljø

Når først en Stored Procedure er oprettet kan den kaldes lige så ofte udvikleren ønsker det. Denne feature tilskynder kodegenbrug samt modularitet. Kodegenbrugen gør det også nemmere at vedligeholde databasen ved at isolere databasen fra blandt andet ændrede forretningsregler, og så videre der ellers kan påvirke brugen af en database, disse skal nu kun ændres eet sted og ikke overalt i den Transact SQL kode man har lavet i sit data warehouse. Som ethvert andet programmeringssprog kan Stored Procedures acceptere input parametre, returnere output parametre samt give eksekverings feedback i form af status codes og tekst, samt kalde andre Stored Procedures. Eksempelvis kan en Stored Procedure eksekvere en vilkårlig anden Stored Procedure ud fra en statuskode en anden Stored Procedure har returneret. Software udviklere kan skrive et program i eksempelvis C++ og så kalde en bestemt type Stored Procedure (Extended Stored Procedure) og så eksekvere dette C++ program fra denne Stored Procedure. Som database udvikler er det en god ide at lave en Stored Procedure således at den kun udfører en enkelt opgave. Jo mere generisk en Stored Procedure er jo bedre kan den bruges i forbindelse med størstedelen af de databaser man arbejder med. Eksempelvis bruges den Stored Procedure sp_rename til at ændre navnet på et bruger-oprettet objekt som eksempelvis en tabel eller en kolonne. Da denne Stored Prcedure kun lave een opgave kan den i en database ændre tabelnavnet, mens den i en anden database bruges til at ændre kolonnenavnet.

Sikkerhed

En anden vigtig del at Stored Procedures er sikkerheden forbedres gennem isolering og kryptering. Databasebrugere kan gives adgang til den Stored Procedure uden at have adgang til de tabeller. Stored Procedures kan endvidere krypteres således det ikke er muligt for brugere at læse den Transact SQL der skal eksekveres.

Kategorier af Stored Procedures

Der er fem overordnede klasser af Stored Procedures: System Stored Procedures, Local Stored Procedures, Temporary Stored Procedures, Extended Stored Procedures samt Remote Stored Procedures. Der er andre måder at opdele Stored Procedures på, ovenstående opdeling gør det let at opverskue den Stored Procedures placering, formål og evne.

System Stored Procedures

System Stored Procedures findes i Master databasen og har ofte et sp_ prefix. De udfører typisk en stor vifte af forskellige opgaver for at understøtte Server funktioner, der understøtter en lang række Server specifikke opgaver. Nogle af de måske mest typiske System Stored Procedures er eksempelvis sp_table_privileges Stored Proceduren, den tager et parameter med ind der er et tabelnavn og udskriver så rettighederne på denne tabel.

use northwind
go
execute sp_table_privileges customers

Denne Stored procedure giver et resultatsæt der beskriver den sikkerhed der for nuværende findes på 'Customer' tabellen i Northwind databasen. En anden ofte brugt System Stored Procedure er sp_who Stored Proceduren, der givet en gyldigt windows login viser hvilke processer denne bruger for nuværende har kørende.

use northwind
go
execute sp_who @loginame='domæne\\brugernavn'

Ofte når man arbejder med data warehousing har man et datawarehouse i hvilket der dagligt bliver indsat en mængde nye records, der eksempelvis vidner om dagens salgstransaktioner (Det kan godt være en del, eksempelvis genererer Amazon.com hver dag 1Tb data fra besøgende). Med tiden bliver serveren statistik ukurrant og passer meget dårligt med de aktuelle data, derfor er det ofte en god ide at køre sp_updatestats System Stored Proceduren, da denne gendanner statistikken i databasen. Hvis man oven i købet kører med en multidimensionel (kube) løsning ovenpå databasen vil denne stored procedure skære væsentlig ned på procestiden for kuben. For en komplet listning af alle System Stored Procedurer til rådighed i SQL Serveren kan man i "Books Online" søge på "System Stored Procedure", så vil alle disse blive vist.

Local Stored Procedures

Disse Stored Procedures bliver gemt i den database hvor de hører til, deres opgave er ofte at fuldføre en forretningsmæssig opgave i denne database. Det er samtidig muligt at skrive en Local Stored Procedure, der udfører samme opgave, enten i en modificeret udgave eller i ren form. Dette gøres ved først at kopiere teksten fra den ønskede System Stored Procedure ud og indsætte den i den ønskede Local Stored Procedure, herefter kan den tilpasses den enkelte opgave og gemmes.

Temporary Stored Procedures

En Temporary Stored Procedure er i princippet det samme som en Local Stored Procedure, blot eksisterer den kun indtil enten forbindelsen der oprettede den bliver lukket eller SQL Serveren lukkes ned. Denne flygtighed eksisterer da den Temporary Stored Procedure der er oprettet bliver gemt i TempDB databasen. TempDB databasen bliver gen-oprettet hver gang SQL Serveren startes. Temporary Stored Procedures er nyttige hvis man tilgår ældre versioner af SQL Serveren der ikke tillader genbrug af execution plans, eller hvis ikke man ønsker at gemme den stored procedure. Der findes også tre slags Temporary Stored Procedures: lokale (også kaldet private), globale samt Temporary Stored Procedures oprettet direkte i TempDB. En lokal Temporary Stored Procedure starter altid med et # en global Temporary Stored Procedure starter altid med to ##. Eksekverings scopet ved en lokal Temporary Stored Procedure er begrænset til den connection der har oprettet den, alle brugere der har adgang til SQL Serveren kan se denne Lokale Temporary Stored Procedure. På grund af restriktionen i scope er der ingen fare for navne sammenfald med andre Stored Procedures. Enhver forbindelse til databasen kan eksekvere en global Temporary Stored Procedure, denne slags Stored Procedure skal have et unikt navn

Extended Stored Procedures

En extended Stored Procedure benytter et eksternt program der er kompileret som et 32-bit dynamic link library (DLL), til at udvidde mulighederne i en stored procedure. Et antal af System Stored Procedurerne er også defineret som extended Stored Procedures. Eksempelvis er sp_sendmail Stored Proecduren, der sender en mail med en vedhæftning af et resultatsæt til en e-mail konto. Denne Stored Procedure er både en System Stored Procedure og en Extended Stored Procedure. De fleste Extended Stored Procedures benytter xp_ prefikset som en navngivnings metode, dette er blot en hovedregel og der er flere tilfælde i praksis, hvor dette ikke er tilfældet.

Remote Stored Procedures

Som navnet antyder eksekverer en remote Stored Procedure en Stored Procedure på en anden SQL Server installation. Remote Stored Procedurer er blevet erstattet at Distributed Queries, men findes stadig af hensyn til bagud kompatabilitet.

Afslutningsvis

Denne artikel gennemgår rent teoretisk hvordan de forskellige typer af Stored Procedurer virker. Da Stored Procedurer i SQL Serveren er meget kompleks er denne artikel ment som baggrundsstof til at forstå og komme videre med Stored Procedurer i SQL Serveren.


Hvad synes du om denne artikel? Giv din mening til kende ved at stemme via pilene til venstre og/eller lægge en kommentar herunder.

Del også gerne artiklen med dine Facebook venner:  

Karma barometer (30 dage)

IT jobs | Kontakt | Om Udvikleren.dk | Om karma | IT nyheds arkiv | Annoncering

X