Via en bloggpost från Adam Machanic har jag deltagit i en tävling om ett exemplar av Expert SQL Server 2005 Development. Frågan från Adam är:

Givet att du har två tabeller, som skapats med scriptet nedan:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Hur kan man skriva om nedanstående fråga så att den blir snabbare (den tar ungefär sex minuter på min PC):

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

Inga objekt-ändringar är tillåtna, dvs ingen indexering av tabellerna får göras.

Lösningen är att göra exakta jämförelser istället för ungefärliga. Dvs att använda "="-operatorn istället för "LIKE"-operatorn. Varför? Jo, därför att "LIKE"-operatorn gör att vi måste använda oss av en "NESTED LOOP" för att jämföra raderna. Dvs SQL Servers Query Processor (QP) måste titta på varje rad i den yttre tabellen i JOIN-operationen, och för varje rad i den yttre tabellen måste QP titta på varje rad i den inre tabellen för att jämföra data. Både b1 och b2 innehåller 19614 rader. Det är inga extrema datamängder, långt därifrån. För att köra ovanstående fråga måste QP titta på 19614 i b1, och för varje av dessa rader måste 19614 rader i b2 undersökas. Dvs 384 708 996 rader måste undersökas.

Lyckas vi istället använda en "="-operator kan QP använda en "MERGE" eller "HASH MATCH", vilket väsentligen reducerar antalet rader att titta på.

Min omskrivna fråga blir:

SELECT *
FROM b1
JOIN b2 ON
LEFT(b2.blat2,5) = b1.blat1

Det funkar därför att vi vet att b1.blat2 innehåller de första fem tecknen från b2.blat2. Tack vare den vetskapen kan frågan skrivas om, och köras på två sekunder. QP utför en "HASH MATCH" och antalet rader som måste undersökas för att utföra JOIN-operationen reduceras väsentligt.

Nedan ser du bilder av de estimerade planerna för de båda frågorna, och kan själv se skillnaden i estimerat antal rader för "NESTED LOOP" jämfört med "HASH MATCH" (Jämför "Estimated Number of Rows")

 
SELECT * FROM b1 JOIN b2 ON b2.blat2 like b1.blat1 +'%'

SELECT * FROM b1 JOIN b2 ON left(b2.blat2,5) = b1.blat1

Fler än en gång har jag försökt att grundläggande förklara lite grann om index i SQL Server (och egentligen alla andra databaser, det funkar ju faktiskt rätt lika). Det som flest har svårt att förstå är det här med klustrade och icke-klustrade index. Många undrar vad som är skillnaden, och många undrar vilket index som är bästa kandidaten att använda som klustrat index. Här tänkte jag försöka sammanfatta vad jag brukar svara.

Vad är det?
Ett klustrat index är ett index som lagras tillsammans med datat för en tabell. Det gör att datat självt är sorterat, vilket kan vara användbart emellanåt.

Den bästa jämförelsen jag känner till är en telefonkatalog. I en telefonkatalog finns sidor, som vi kan jämföra med data-pages i SQL Server. Varje telefonnummer kan jämföras med en rad i en tabell i SQL Server. Telefonnumren är sorterade efter Efternamn, Förnamn. På samma sätt funkar ett klustrat index. Datat på varje data-page är sorterat efter det klustrade indexet som finns på tabellen. Tabellens data-pages är sorterade inbördes enligt samma princip.

I SQL Server finns dessutom ett index-träd utanför tabellen som gör att det blir lite lättare att hitta till den första raden som uppfyller ett sökvillkor i det klustrade indexet. Det kan jämföras med att högerkanten på sidorna i en telefonkatalog ofta innehåller ett första-bokstaven-register, som ger genvägar till alla efternamn som börjar på en viss bokstav.

Har du en tabell med telefonnummer, Efternamn och Förnamn, där Efternamn, Förnamn är ett klustrat index, och du vill söka ut alla telefonnummer tillhörande någon som heter Andersson, så lägger du in "WHERE efternamn = 'Andersson'" som WHERE-klausul i din fråga. SQL Server börjar då leta i sitt index-träd efter 'Andersson', och när den hittar 'Andersson' så går den via en adress-pekare direkt till den data-page där den första förekomsten av 'Andersson' finns. Eftersom datat är sorterat efter Efternamn, Förnamn finns nu ingen anledning att gå tillbaka till något ytterligare index för att hitta alla rader för med Efternamn='Andersson', utan SQL Server kan börja läsa data-page efter data-page, tills den stöter på ett efternamn som INTE är 'Andersson'.

Icke-klustrat då?
Icke-klustrade index lagras helt och hållet utanför själva datat för en tabell. De lagras också i trädstrukturer, men innehåller till skillnad från klustrade index ett index-löv för varje rad i tabellen, och en pekare till adressen där raden lagras. Vill du läsa alla som heter 'Andersson' enigt exemplet ovan får SQL Server jobba lite mer för att hitta allt data. Den måste göra följande fler-stegs-raket:

1. Leta upp första lövet/nästa löv med efternamn='Andersson' i indexträdet.
2. Följ pekaren till den data-pagen där raden som index-lövet motsvarar lagras.
3. Stoppa in raden i en temporär "Spool-tabell"
4. Upprepa steg 1-3 tills alla 'Andersson' är funna
5. Läs "Spool-tabellen" och returnera.

Alltså något mer arbete än att bara leta reda på den första raden och sen läsa och returnera rad för rad.

Vad ska jag välja som klustrat index?
När man börjar diskutera kandidater för klustrade index ändras ofta diskussionen till att bli av religiös karaktär.

SQL Server skapar alltid ett klustrat index, oavsett om vi vill det eller inte. Om vi inte själva bestämmer vilket index som ska vara klustrat skapas ett "osynligt" klustrat index som vi inte har varken nytta av eller kontroll över. Därför är det hyfsat självklart att vi ska välja ett klustrat index. Som standard skapar SQL Server ett klustrat index över kolumnen eller kolumnerna som ingår i primärnyckeln, om det inte redan finns ett klustrat index när primärnyckeln skapas.

Vissa hävdar att det här alltid är helt rätt och att man aldrig ska använda sig av annat än primärnyckeln som klustrat index. Det är snick-snack i mina öron. Tänk dig återigen fallet med telefonkatalogen. Man kan tänka sig att telefonnummer är primärnyckel i en telefonkatalog, eller hur? Varför skulle vi vilja ha telefonnummer som klustrat index i telefonkatalogen? Din Del skulle förmodligen ha betydligt färre nöjda mottagare av katalogen om den var sorterad efter telefonnummer..

Vissa hävdar att man ska ha HÖG selektivitet på det index som är klustrat. Dvs att man får träff på väldigt få rader om man söker i indexet efter ett visst värde. Varför det skulle vara en fördel förstår jag ärligt talat inte. Har man hög selektivitet på ett index, till exempel en primärnyckel, så kostar det ju väldigt lite att först söka reda på index-lövet och sen besöka en eller ett fåtal rader via pekare. Har man däremot LÅG selektivitet (exempelvis efternamn i en telefonkatalog) så kostar det ganska mycket att gå via ett icke-klustrat index.

Jag tycker att främmande nycklar ofta utgör bra kandidater för klustrade index. Tänker dig en order-applikation, med en Order-tabell och en Order-rad-tabell. Där vill man väldigt ofta selektera ut alla orderrader som tillhör ett visst ordernummer. Då är Ordernummer-kolumnen i orderrad-tabellen en perfekt kandidat för klustrat index. Nu skulle jag i och för sig designa min tabell så att primärnyckeln bestod av Order-nummer och Orderrad-nummer. Men många SQL Server-utvecklare använder sig ALDRIG av naturliga nycklar, utan skapar istället en IDENTITY-kolumn som primärnyckel. Den kolumnen är precis värdelös att använda som klustrat index, ur sökbarhetssynpunkt.

Sammanfattningsvis
Tänk dig för innan du bara accepterar SQL Servers förslag att skapa klustrat index av primärnyckeln. Använder du dig av normaliserade tabeller, med bara naturliga nycklar är det förmodligen bra kandidater för klustrade index. Men om du tillhör de 99% SQL Server-utvecklare som istället skapar en surrogat-nyckel och en UNIQUE-constraint (VARFÖR GÖR FÖRRESTEN SÅ MÅNGA DET, DET ÄR VERKLIGEN DUBBELARBETE!!!) över det som kunde varit naturlig nyckel - ja då ska du fundera både en och två gånger på vilket klustrat index du bör använda. Tänk telefonkatalog!

När man installerar SQL Server 2005 kan man få problem med att installationen verkar "hänga sig" medan den gör "Setting File Security". Tittar man i loggen är det sista den gör något i stil med "Write_sqlFileSDDL".

I själva verket har inte installationen hängt sig. Den tar bara EXTREMT lång tid på sig att hoppa till nästa steg. Problemet beskrivs av Microsoft i en KB-artikel (http://support.microsoft.com/kb/910070) som Active Directory-relaterat. Om domänen har många trust-relationer med andra domäner kan det ta lång tid att göra uppslag i katalogen. Det verkar stämma med min erfarenhet. Jag har stött på problemet då jag installerat SQL Server 2005 på maskiner som ingår i ett stort Active Directory, spritt över flera kontor, med ibland så långsam uppkoppling som 128kbit/s.

Det finns en Hotfix att hämta från Microsoft. Det finns också en "Workaround" listad i samma KB-artikel, som funkade bra för mig. Den går ut på att helt enkelt inaktivera nätverkskortet en stund, så att SQL Server-installationen kan förstå att den inte kommer att hitta en AD-resurs, och istället gå vidare med livet och slutföra installationen.

Ett problem med att inaktivera ett nätverkskort kan förstås vara att man gör sin installation på en server som man inte har fysisk tillgång till. Det kan upplevas som lite svårt att logga in med Remote Desktop om man har inaktiverat nätverket på servern... Det kan man lösa genom att köra ett command-script som inaktiverar nätverkskortet, pausar en stund och sedan aktiverar nätverkskortet.

Syntaxen för att inaktivera och aktivera nätverkskort varierar från version till version av Windows. I Windows Server 2003 ser det ut såhär:

netsh interface set interface "Local Area Connection" DISABLED

och

netsh interface set interface "Local Area Connection" ENABLED

Då återstår alltså bara att lägga till "pausa en stund" så har vi en färdig bat- eller cmd-fil som gör vad vi vill. Men det är dessvärre inte "bara" att pausa ett script. PAUSE-kommandot som finns i Windows pausar i väntan på att användaren ska trycka på en tangent. Det är inte dirket användbart när vi vill använda vårt script för att inaktivera ett nätverkskort, pausa och sen aktivera. Det blir svårt att trycka på en tangent när ens fjärranslutning kopplats ned så att man inte längre kommer åt maskinen som scriptet körs på... Lyckligtvis finns det andra saker man kan göra för att pausa ett script. Jag har använt mig av "ping 127.0.0.1", dvs att pinga "loopback interfacet" (svensk översättning någon?).

Ett helt script kan alltså se ut såhär:

REM Inaktivera nätverkskort
netsh interface set interface "Local Area Connection" DISABLED
REM Pausa cirka 10 sekunder
ping -n 10 127.0.0.1
REM Aktivera nätverkskort
netsh interface set interface "Local Area Connection" ENABLED

När du programmerar mot en databas ifrån .NET - undvik att skicka in parametrar i koden på det här viset:

Dim s As String = TextBox1.Text
Dim cmd As New SqlClient.SqlCommand("SELECT * FROM tabell WHERE kolumn='" & s & "'", cn)
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader()

Det kan nämligen leda till SQL Injection. Vad det innebär? Jo, att en illvillig användare anropar din sida med skadlig SQL-kod, genom att i TextBox1 skriva in SQL-kommandon. Tänk dig att en användare skriver in följande i TextBox1:

'; DROP DATABASE minDatabas;SELECT 'You suck!

Vad skulle hända då? Jo, det som skulle hända är att det du egentligen vill göra - nämligen att välja från tabellen "tabell" körs, med en tom sträng som WHERE-villkor för kolumnen "kolumn". Därefter skulle kommandot "DROP DATABASE minDatabas" köras, dvs databasen minDatabas skulle tas bort ifrån SQL Server. Slutligen skulle kommandot "SELECT 'You suck!'" köras. Och det är ju INTE vad vi vill ska hända...

Det absolut enklaste sättet att undvika SQL Injection är att använda parmeteriserade frågor. Såhär kan det se ut:

Dim s As String = TextBox1.Text
Dim cmd As New SqlClient.SqlCommand("SELECT * FROM tabell WHERE kolumn = @kolumn", cn)
cmd.Parameters.Add(New SqlClient.SqlParameter("@kolumn", s))
Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader() 

Ytterligare en kodrad, och BETYDLIGT säkrare kod. ADO.NET tar hand om att parametern som jag skickar in omvandlas på ett sådant sätt att den är säker för SQL Server att ta emot. Ingen SQL Injection alltså.

Hade IDG förstått vikten av att använda parametrar i sin kod hade DET HÄR aldrig hänt :)

En för mig välkommen nyhet som introducerades i SQL Server 2005 är felhantering genom TRY/CATCH.

Innan TRY/CATCH såg felhantering typiskt ut något i den här stilen (lite förenklat):

 

--create table tabell (fält varchar(10))

DECLARE @error int
SET @error=0
BEGIN TRAN
INSERT INTO tabell (fält) VALUES('abcde12345abcde12345')
SET @error=@@error
IF @error>0
BEGIN
      PRINT @error
      ROLLBACK TRAN
      GOTO PROC_EXIT
END

INSERT INTO tabell (fält) VALUES('abcdefghijabcdefghij')
SET @error=@@error
IF @error>0
BEGIN
      PRINT @error
      ROLLBACK TRAN
      GOTO PROC_EXIT
END

INSERT INTO tabell (fält) VALUES('12345678901234567890')
SET @error=@@error
IF @error>0
BEGIN
      PRINT @error
      ROLLBACK TRAN
      GOTO PROC_EXIT
END

COMMIT TRAN

PROC_EXIT:

Med TRY/CATCH är det kortare och i min mening betydligt mer lättförståligt:

BEGIN TRY
BEGIN TRAN

INSERT INTO tabell (fält) VALUES('12345678901234567890')
      INSERT INTO tabell (fält) VALUES('abcdefghijabcdefghij')
      INSERT INTO tabell (fält) VALUES('abcde12345abcde12345')
COMMIT TRAN
END TRY
BEGIN CATCH
      IF @@TRANCOUNT>0
            ROLLBACK TRAN
      PRINT ERROR_MESSAGE()
END CATCH

En avgörande skillnad finns mellan "gamla" och "nya" sättet att felhantera: Med TRY/CATCH kastas inga fel till klienten, så det kan definitivt vara läge att slänga in en RAISERROR i sitt CATCH-block.

En fråga jag får ganska ofta är om det är möjligt att lagra binära filer i SQL Server, och i så fall hur det går till.

Ja, det är möjligt att lagra binära filer. Det är till och med ganska lätt. Därmed inte sagt att det alltid är en bra idé. Diskussioner om HUR man lagrar bilder i SQL Server brukar spåra ur i en religiös diskussion om det är en bra idé eller inte. Vissa hävdar att det alltid är bättre att lagra en sökväg i SQL Server, och själva filen i filsystemet.

Själv försöker jag vara lite mer pragmatisk, och lagrar filer direkt i en SQL Server-databas när det är en bra idé att göra det. Vill man ha versionshantering av sina filer är det perfekt att lagra dem direkt i databasen. För att nämna ett användningsområde.

Jag har i mitt jobb byggt en bild-databas, med cirka 12000 högupplösta bilder, som även lagras i ett antal lågupplösta format. Versionshantering är inbyggd, så varje gång en bild laddas upp sparas den som en ny version, och alla gamla versioner finns kvar. Det blir rätt stora datamängder - 80 GB närmare bestämt. Och tidsmätningar visar att det är perfekt att lagra de här bilderna i SQL Server. Ett av de lågupplösta formaten läses nämligen mycket regelbundet, eftersom de bilderna visas i en webb-katalog som öppnas flera tusen gånger om dagen. Det formatet utgör mindre än 1 GB data. SQL Server är bra på att cacha data. Faktum är att SQL Server cachar allt data den läser. Data som läses ofta behåller SQL Server i sin cache hellre än data som läses sällan. Det innebär att de lågupplösta bilderna som läses flera tusen gånger om dagen nästan helt säkert redan finns i SQL Servers cache-minne när de ska visas. Vid en jämförelse mellan lagring i filsystem och lagring i SQL Server, blir just det exemplet snabbare vid lagring i SQL Server, eftersom bilderna nästan varje gång levereras som IO-läsning direkt från SQL Servers primärminne istället för att levereras som en IO-läsning från disk.

Hur gör man då?

Mitt exempel är gjort i VB.NET 1.1 och SQL Server 2000. Att göra det i .NET 2.0 eller 3.0, och SQL Server 2005 är ingen större skillnad.

Till att börja med behöver vi en tabell där vi kan lagra våra filer i databasen:

CREATE TABLE files(
filename nvarchar(255),
version int,
blob image)

Jag föredrar (nästan) alltid att jobba med Stored Procedures för SELECT och INSERT i databaser. Därför skapar jag också två Stored Procedures:

CREATE PROC GetFile (@filename nvarchar(255))
AS
SELECT TOP 1 *
FROM     files
WHERE     filename = @filename
ORDER BY version DESC

CREATE PROC InsertFile (@filename nvarchar(255), @file image)
AS
SET NOCOUNT ON
INSERT INTO files (filename,version,blob)
SELECT @filename, COALESCE((SELECT MAX(version) FROM files WHERE filename = @filename),0)+1 ,@file

Sådär. Då har vi en Stored procedure för att spara filer, och en för att lägga in nya filer. Klart förenklad datamodell, men den räcker för att visa mitt exempel. Jag använder en sub-select i InsertFile för att få fram det högsta versionsnumret som redan finns i databasen för den fil jag ska spara. Finns ingen sådan fil sparad redan returnerar sub-selecten NULL. Därför använder jag COALESCE för att istället returnera 0. Slutligen lägger jag till 1 till resultatet. Det gör att jag får versionsnummer 1 första gången en fil sparas, och MAX(versionsnummer)+1 för kommande versioner.

Så till VB.NET-koden. I mitt exempel visar jag en metod för att läsa ut en fil från databasen och lägga den i en katalog i filsystemet, och en metod för att ta en fil från filsystemet och spara den i databasen.

' Filename är ett filnamn. path är sökvägen till katalogen där filen ligger.
' Först öppnas filen, och läses in i en array. Därefter anropas vår Stored Procedure InsertFile, med filnamnet och arrayen med filinnehållet.
Private Sub saveInDB(filename As String, path As String, connString As String)
Dim cn As New SqlClient.SqlConnection(sConnString)
Dim fs As System.IO.FileStream = System.IO.File.OpenRead(path & filename)
Dim a(fs.Length - 1) As Byte
fs.Read(a, 0, fs.Length - 1)
fs.Close()
Dim cmd As New SqlClient.SqlCommand("InsertFile", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@file", SqlDbType.Image, a.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, a))
cmd.Parameters.Add(New SqlParameter("@filename", filename))
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub

' filename är filen som ska läsas ut, path är katalogen där den ska läggas.
' Först anropas proceduren getFile. Därefter, om proceduren returnerar några rader, läses blob-kolumnen ut till en array,
' som skrivs till disk med hjälp av en FileStream.
'Vill man streama ut en fil till en webbsida kan man göra Response.BinaryWrite med arrayen istället.
Private Sub getFile(filename As String, path As String, connString As String)
 Dim cn As New SqlClient.SqlConnection(connString)
 Dim cmd As New SqlClient.SqlCommand("getFile", cn)
 cmd.CommandType = CommandType.StoredProcedure
 cmd.Parameters.Add(New SqlClient.SqlParameter("@filename", filename))
 Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 Dim b() As Byte
 Dim f As System.IO.FileStream
 If dr.Read() Then
  f = New System.IO.FileStream(path & filename, IO.FileMode.Create)
  b = dr("blob")
  f.Write(b, 0, b.Length)
  f.Close()
  f = Nothing
 End If
 dr.Close()
End Sub

Inte alltför mycket kod alltså, och inte alltför komplicerat. Självklart behövs mer än såhär för att bygga en lagringsfunktion som är användbar, men principerna ändras inte - man behöver en tabell för lagring, man behöver kunna läsa och lägga in i tabellen, och man behöver applikationskod för att använda databaskoden.

Image-datatypen är alltså vad jag har använt för lagringen - populärt kallad BLOB (Binary Large OBjects).

Är koden användbar - skriv gärna en kommentar och berätta det. Är något fel i koden, berätta självklart det också!

Jag har haft lite problem med pingar från bloggen, och testar därför ett inlägg för att se om Nyligen, Twingly, Technorati och de andra bloggsökmotorerna blir pingade.

Jag brukar då och då läsa och svara på frågor om Microsoft SQL Server i olika nätforum. Nu har jag startat en blogg där jag kommer att skriva om SQL Server på svenska. Det finns gott om både bloggar och andra resurser om SQL Server, men (nästan) allt är på engelska, samtidigt som det finns väldigt många systemutvecklare i Sverige, som sysslar med bland annat SQL Server. Jag tror att jag kan ha en hel del att dela med mig av, eftersom jag de senaste 8-9 åren jobbat med applikationsutveckling mot SQL Server, och med drift av SQL Server.

Bloggen använder sig av dasBlog som motor (www.dasblog.info)

Intressant?
Andra bloggar om SQL Server, Bloggar, Databaser