Flera gånger har jag sett och svarat på en fråga om sortering i SQL Server. Frågan har formulerats på olika sätt, men I princip är det samma fråga som ställts:

När jag gör SELECT mot min tabell får jag tillbaka raderna i “fel” ordning, jag lade till rad A först, sen rad B, men SQL Server ger mig rad B före rad A.

Det är en vanlig missuppfattning att SQL Server ger oss rader i någon särskild ordning. Men det kan vi inte på något sätt förvänta oss att SQL Server gör. SQL Server försöker att ge oss det vi frågar om så snabbt som möjligt. Om vi ber om rader genom SELECT * FROM tabell så försöker SQL Server att så snabbt som möjligt ge oss alla rader i tabellen tabell. Om det snabbaste sättet är att ge oss datat I samma ordning som det lades in i tabellen så får vi det i den ordningen. Men om det går snabbare att få raderna på något helt annat sätt så får vi raderna sorterade på ett helt annat sätt. Eller snarare: Vi får alltid raderna osorterade.

Det är ganska vanligt att man använder en IDENTITY-kolumn som primärnyckel i en tabell. Då vet vi att datat på disk fysiskt är sorterat i den ordning det har lagts in I tabellen. Detta eftersom en primärnyckel som default skapar ett klustrat index och ett klustrat index lagras ihop med själva datat för tabellen. Alltså: Klustrat index sorterar själva tabellen istället för att lagras vid sidan om tabellen i ett index-träd.

I diskussionerna om sortering på data från SQL Server brukar klustrat index dyka upp och någon brukar påstå att ett klustrat index garanterar att SQL Server ger oss data I en viss ordning. Men det är också fullständigt fel. Ett klustrat index garanterar ingenting annat än att datat är fysiskt sorterat. Men det har ju ingenting att göra med hur SQL Server hämtar data från en tabell att göra. Om SQL Server hittar ett snabbare sätt att ge oss rader i en tabell än att bara läsa från början till sut så kommer ju SQL Server att göra det.

Alltså: Om vi inte har något klustrat index så är datat i en tabell bara en hög med tabellrader, helt utan inbördes ordning. Det är omöjligt att veta vilken ordning vi kommer att få raderna om vi inte ber SQL Server att sortera raderna med en ORDER BY-klausul. Om det finns ett klustrat index så är raderna prydligt ordnade på disk. Men det är precis lika omöjligt att veta i vilken ordning vi kommer att få raderna om vi inte använder en ORDER BY-klausul.

Jag påmindes om det här ämnet i en diskussion på Ask SQL Server Central idag. Just den diskussionen handlade om hur man kan sortera data vid en INSERT. Men det är egentligen samma sak – frågan bygger på missuppfattningen att SQL Server sorterar data i den ordning raderna lagts till.

Det kan ibland finnas skäl att försöka lägga in rader i en viss ordning – nämligen om det finns ett klustrat index som INTE är en IDENTITY-kolumn, eller en datetime-kolumn med CURRENT_TIMESTAMP som default-värde. Ta till exempel ett personregister. Där skulle jag vilja ha ett klustrat index över efternamn, förnamn. Om jag ska lägga in massor av rader i tabellen så är jag lite intresserad av att försöka lägga in dem i “naturlig ordning”, för att undvika fragmentering av data. Men det är ett helt annat ämne…

Tänk dig att du får följande uppgift:

“Vi behöver göra en liten ändring i den här databasen, som har hundratals tabeller och tusentals kolumner. Vi behöver ett bättre stöd för olika språk i applikationen så alla kolumner måste ändras till unicode (dvs varchar blir nvarchar, char blir nchar osv).”

Med små datamängder kan man alltid använda SQL Server Management Studio och göra ändringarna där. Men det är knappast ett alternativ när man har utvecklingsmiljöer, test-miljöer och produktionsmiljöer med flera installerade instanser av liknande databaser osv. När produktionssättningen av en sådan här ändring ska göras vill man inte behöva stänga sina system två veckor för att manuellt ändra kolumner.

En andra tanke skulle vara att just göra manuellt ändringar i en test-miljö och via Script-knappen i Management Studio få det SQL-script som används för att göra förändringen och spara ändringen till en text-fil. Men det skulle också vara extremt tidsödande. Jag skrev ovan om produktionsmiljöer med liknande databaser. Det är ett problem med ändra-manuellt-i-Management-Studio-och-spara-undan-scriptet-metoden. Är inte databaserna identiska måste samma procedur utföras för varje databas.

En tredje idé är ju förstås att scripta ut databaserna och göra ändringar med Sök/Ersätt i valfri text-editor. Så tänkte jag först när jag ställdes inför uppgiften. Men ganska snabbt slog jag idén ur hågen. I mitt fall har flera tabeller kolumnnamn som är datatyper (“Text” till exempel). Det gjorde enkel Sök/Ersätt i text ganska svårt. Dessutom skulle scriptet behöva omstruktureras en hel del för att saker och ting skulle göras i rätt ordning (droppa constraints och index, ändra tabellerna, återskapa constraints och index).

Därför testade jag mig fram med Microsoft.SqlServer.Management.Smo i VB.NET och kom fram till ganska lite kod som gör ett väldigt stort jobb. Jag älskar verkligen SMO! På första försöket blev jag 95% färdig. Två-tre försök senare hade jag en lösning som fungerade “universellt” för alla databaser.

Jag har även använt SMO och reguljära uttryck för att ändra alla stored procedures i en databas till att använda unicode-ekvivalenter, men den funktionen har så mycket buggar än så länge, så jag väntar lite med den. De två största problem jag haft med den är: Jag har inte ett korrekt reguljärt uttryck för att känna igen strängar. Det blir fel när det finns fyra eller sex enkla citattecken invid varandra i koden. Jag har inte heller fått till igenkänning av alla variabler och parametrar. Parametrarna är OK, det är rätt enkelt att matcha “@någonting<ett eller flera blanksteg>variabeltyp”. Men det är inte alls lika enkelt att hitta alla förekomster av varchar, text eller char i löpande SQL-kod (exempelvis “SELECT CAST(kolumnen AS varchar(10)) FROM tabellen” osv. Men jag kanske finslipar lite, och återkommer när den är “fulländad” (as if..). Tills vidare körde jag min funktion och blev sedan sittande i nästan två dagar med rättning av koden. Men det sparade ändå massor av tid, jämfört med att helt manuellt göra förändringarna.

Här är VB.NET-koden för att ändra tabeller till unicode. En klass, med en publik och en privat metod:

Imports Microsoft.SqlServer.Management
Public Class TableUnicodyfier 
    'Sex globaler, två för att lagra fel och constraints, två som är namn på SMO-objekt 
    'och två som är självaste SMO-objekten. Inga publika variabler. 
    Private clConstraints As Collection 
    Private clErrors As Collection 
    Private s As Smo.Server 
    Private d As Smo.Database 
    Private sDBName As String 
    Private sInstanceName As String 
    Public Sub New(ByVal DBName As String, ByVal InstanceName As String) 
        sDBName = DBName 
        sInstanceName = InstanceName 
    End Sub 
    'Publik metod som returnerar ett SQL-script för att ändra alla text-kolumner till Unicode. 
    Public Function ReplaceAllTablesWithUnicode() As String 

        'Variabler 
        Dim sError As String = "" 
        Dim j As Integer = 0 
        Dim i As Integer = 0 
        Dim str As String = "" 

        'Initiera object 
        clConstraints = New Collection() 
        clErrors = New Collection() 
        s = New Smo.Server(sInstanceName) 
        d = s.Databases(sDBName)

        'Viktigt!! Jag föredrar att kunna granska alla ändringar i text innan de genomförs (jag kanske är feg?). 
        'Om inte raden nedan finns med görs alla ändringar "på riktigt". Finns den med sparas bara alla SQL-anrop 
        'och kan hämtas sedan. 
        s.ConnectionContext.SqlExecutionModes = Common.SqlExecutionModes.CaptureSql 
 

        'Loopa igenom alla tabeller och kör replaceTableWithUnicode 
        For Each t As Smo.Table In d.Tables 
            If Not t.IsSystemObject Then 
                replaceTableWithUnicode(t.Name, True) 
            End If 
            i += 1 
            Application.DoEvents() 
        Next 
 

        'Eftersom replaceTableWithUnicode bara droppar Foreign Keys men inte återskapar dem så gör vi det sist av allt. 
        'Anledningen är förstås att alla tabeller måste ändras innan vi kan återskapa FK:n igen. Annars riskerar vi 
        'att försöka skapa en FK mellan en varchar-kolumn i tabell A och en nvarchar-kolumn i tabell B. 
        'Därför loopar vi igenom clConstraints och kör Create på varje FK. 
        For Each f2 As Object In clConstraints 
            f2.create() 
        Next 
 

        'Loopa igenom all SQL-text som SMO sparat åt oss istället för att genomföra förändringarna på riktigt. 
        'Bygg upp en resultat-sträng 
        For Each str2 As String In s.ConnectionContext.CapturedSql.Text 
            str &= str2 & vbCrLf & "GO" & vbCrLf 
        Next 
 

        'Om några fel uppstod under körningen så har de objekten lagrats i clErrors. Loopa igenom den och 
        'lägg till även den SQL-koden sist i resultat-strängen. 
        If clErrors.Count > 0 Then 
            str &= "--Errors occured while saving scripts" & vbCrLf 
            For Each o As Object In clErrors 
                For Each strScript As String In o.script() 
                    str &= strScript & vbCrLf 
                Next 
            Next 
        End If 

        'str innehåller nu: 
        '- Drop statements för alla Foreign Key constraints 
        '- Drop-statements för alla index, primärnycklar, defaults och triggers 
        '- Alter statements för alla tabeller 
        '- Create-statements för alla index, primärnycklar, defaults och  triggers 
        '- Create-statements för alla Foreign Key constraints 
        Return str 
    End Function

    Private Function replaceTableWithUnicode(ByVal tableName As String, ByVal commit As Boolean) As String 
        Dim cl As New Collection 
        Dim bChange As Boolean 
        Dim t As Smo.Table = d.Tables(tableName) 
        Dim c As Smo.Column 
        Dim str As String = "" 


 
        'Loopa igenom alla kolumner. Ändra dem till respektive Unicode-ekvivalent 
        For Each c In t.Columns 
            bChange = False 
            Select Case c.DataType.Name 
                Case "varchar" 
                    bChange = True 
                    If c.DataType.MaximumLength > 4000 Then 
                        c.DataType = Smo.DataType.NVarCharMax 
                    Else 
                        c.DataType = Smo.DataType.NVarChar(c.DataType.MaximumLength) 
                    End If 
                Case "char" 
                    bChange = True 
                    c.DataType = Smo.DataType.NChar(c.DataType.MaximumLength) 
                Case "text" 
                    bChange = True 
                    c.DataType = Smo.DataType.NVarCharMax 
            End Select 
            'Notera att vi inte har gjort Alter på tabellen ännu. Än så länge har vi bara ändrat i kolumn-objekten. 
            'Om någon kolumn ska ändras så är det säkrast att återskapa alla default-constraints, annars kommer 
            'SQL Server att gnälla. Vi loopar igenom dem, lägger till dem i cl och droppar dem sedan. På så sätt 
            'genererar ett DROP-statement till vår SQL-kod, och vi kan göra Create senare när tabellen är ändrad. 
            If bChange Then 
                If Not c.DefaultConstraint Is Nothing Then 
                    cl.Add(c.DefaultConstraint) 
                    c.DefaultConstraint.Drop() 
                End If 
            End If 
        Next 

         Try 
            'Nu ska vi hitta alla FK-constraints som refererar till den här tabellen. 
            'För varje FK som refererar till den här tabellen - lägg till den i både funktionens interna 
            'cl2 och i klassvariablen clConstraints 
            'Sist - loopa igenom alla constraints i cl2 och droppa. Resultatet blir att alla FK kommer att droppas 
            'men själva objekten kommer att finnas kvar i clConstraints, för att anropande funktion ska kunna återskapa dem 
            For Each t2 As Smo.Table In d.Tables 
                Dim cl2 As New Collection 
                For Each f2 As Smo.ForeignKey In t2.ForeignKeys 
                    If f2.ReferencedTable.ToUpper() = tableName.ToUpper() Then 
                        cl2.Add(f2) 
                        clConstraints.Add(f2) 
                    End If 
                Next 
                For Each f2 As Smo.ForeignKey In cl2 
                    f2.Drop() 
                Next 
            Next 
            'Droppa även alla FK som är skapade i aktuell tabell och som refererar andra tabeller 
            Do Until t.ForeignKeys.Count = 0 
                clConstraints.Add(t.ForeignKeys(0)) 
                t.ForeignKeys(0).Drop() 
            Loop 
            'Droppa alla index. Lägg först till indexet i den privata variabeln cl 
            Do Until t.Indexes.Count = 0 
                'Om inte den här raden är med funkar inte koden för Primärnycklar. Raden gör ingenting mer än att 
                'läsa indextypen, men tydligen görs något i själva Index-objektet som initierar den korrekt för primärnycklar. 
                Dim it As Smo.IndexKeyType = t.Indexes(0).IndexKeyType 
                cl.Add(t.Indexes(0)) 
                t.Indexes(0).Drop() 
            Loop
            'Och slutligen samma sak med alla triggers i tabellen 
            Do Until t.Triggers.Count = 0 
                cl.Add(t.Triggers(0)) 
                t.Triggers(0).Drop() 
            Loop 

            'Sådärja, då är alla beroende objekt droppade. Då gör vi Alter på tabellen för att ge genomslag 
            'åt våra förändringar av kolumn-datatyper 
            Try 
                t.Alter() 
            Catch ex As Exception 
                'Och det här kan ju gå fel om vi glömt något, så då sparar vi i sådant fall undan tabellen i clErrors 
                MsgBox("Error when altering table " & t.Name) 
                clErrors.Add(t) 
            End Try

            'Nu är tabellen ändrad, men den har varken index, primärnycklar defaults eller triggers. 
            'Tur att vi sparat undan de objekten i privata variabeln cl. Loopa och återskapa. 
            For Each o As Object In cl 
                Try 
                    o.Create() 
                Catch ex As Exception 
                    'Det här är fult, men jag vill direkt få reda på när något går fel, och få se en Stacktrace. 
                    'I normala fall ska naturligtvis inte en sådan här klass försöka sig på att kommunicera interaktivt. 
                    'Den som orkar fixar självklart en ErrorMessages-collection som public property i klassen. 
                    MsgBox("Error when creating object(s)" & vbCrLf & ex.ToString()) 
                    clErrors.Add(o) 
                End Try 
            Next 
        Catch ex As Exception 
            MsgBox(ex.ToString()) 
        End Try 
        Return str 
    End Function
End Class

Andra intressanta blogginlägg om: Unicode, SQL Server, Microsoft, Microsoft.SqlServer.Management.Smo

I mitt förra blogginlägg skrev jag bland annat om spDeleteBackupRestoreHistory, som är en Stored Procedure som rensar backup- och restore-historik i MSDB. Den gick VERKLIGEN långsamt i sitt ursprungsutförande. Därför började jag kolla indexeringen i MSDB och upptäckte att den är minst sagt sparsam. Det kan säkert göras massor för att snabba upp både det ena och det andra i MSDB. Men jag hade två specifika problem:

1) Tabellen backupset har ett enda index – backupsetuuid. Men det finns en Foreign Key-constraint som refererar kolumnen media_set_id till tabellen backupmediaset. I SPn spDeleteBackupRestoreHistory görs en sökning i just den kolumnen, vilket innebär en table-scan. Eftersom den sökningen görs väldigt många gånger innebär det väldigt många tablescans och därmed väldigt dålig prestanda.

2) I spDeleteBackupRestoreHistory läses kolumnerna backup_set_id och media_set_id i tabellen backupset. Det finns ett index (primärnyckel, klustrad) på backup_set_id, men inget index på media_set_id.

För att slå två flugor i en smäll skapade jag ett sammansatt index över kolumnerna media_set_id och backup_set_id. Då fick jag dels sökbarhet på media_set_id vilket gör att table_scan undviks, dels fick jag ett täckande index över de två kolumner i tabellen backupset som spDeleteBackupRestoreHistory använder.

Alltså:

CREATE 
  INDEX [backupset_mediaset_id]
ON
  [dbo].[backupset]([media_set_id],
  [backup_set_id])

Jag testkörde genom att:

• Först göra exec spDeleteBackupRestoreHistory 100 utan att mäta tiden.
• Sedan gjorde jag exec spDeleteBackupRestoreHistory 90 med tidsmätning innan jag skapat mitt nya index. Resultatet: 12 minuters körning.
• Sedan skapade jag indexet och gjorde exec spDeleteBackupRestoreHistory 90. Resultat: 5 sekunders körning.

Sensmoral: Ha alltid index på kolumner som refereras i främmande nycklar. Försök att skapa täckande index.

Jag har upptäckt att MSDB i en server jag hanterar är ALLDELES för välfylld. Att droppa en databas har tagit flera timmar om jag valt att radera backup-historik samtidigt. Därför googlade jag och fann en användbar SP: spDeleteBackupRestoreHistory hos DBA and SysAdmin workd. Det är en procedur som rensar backuphistorik ändre än X dagar. Mycket användbar SP som redan hamnat som ett veckojobb på en testmaskin och som jag körde över helgen på en produktionsmaskin, för att den skulle rensa bort all historik som är äldre än två år (jag hade sex år gammal historik sparad).

Det hade gått alldeles utmärkt om inte nedanstående “trevliga” lilla felmeddelande uppenbarat sig i Query Analyzer strax efter att jag lämnat datorn att jobba över helgen:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).Server: Msg 11, Level 16, State 1, Line 0
Generalnetwork error. Check your network documentation.Connection Broken.

Jobbigt tänkte jag, men det är säkert något tillfälligt – någon som i helgen ryckte och drog i någon sladd. Så jag testade att köra igång den igen, och fick direkt samma fel. Jag bytte databas och testade lite olika queries utan att få något felmeddelande. Jag gick tillbaka till MSDB och körde igen, och fick samma fel. Andra queries gick utmärkt att köra i MSDB, det var bara spDeleteBackupRestoreHistory som ballade ur.

Jag loggade in direkt på servern och öppnade en anslutning mot Localhost och körde spDeleteBackupRestoreHistory och fick åter samma fel. Ganska säker på att det verkligen inte är ett nätverksproblem körde jag DBCC CHECKDB på databasen och upptäckte ett fel på indexet backupsetuuid i tabellen backupset.

Nu när jag visste vad felet var kunde jag åtgärda det genom att bygga om indexet. Tabellen innehåller ungefär en miljon rader, och att bygga om indexet tog fem sekunder. “Förarbetet” – det vill säga att hitta vad som faktiskt var fel – tog däremot ungefär en timme. Nu är felet åtgärdat, och spDeleteBackupRestoreHistory är igång igen. Återstår att söka efter orsaken till att ett index gått sönder från början.

CREATE
  INDEX [backupsetuuid]
ON backupset ([backup_set_uuid])
  WITH DROP_EXISTING

Det har varit tyst härifrån ett tag. Det beror på att min fritid ägnas i större utsträckning till att umgås med min bebis och min sambo (kloka val) och att jag begränsar min fritid genom att arbeta mycket (inget val..). Snart har jag semester följt av föräldraledighet. 10 veckor tillsammans med sambon, följt av ett halvår med 80% föräldraledighet. Skönt!

Men lite bloggande blir det nog under den här tiden. Jag håller på att installera SQL Server 2008 och Visual Studio Express 2008, och återkommer med lite rapporter om både SQL Server 2008 och om interaktionen mellan VS2008 Express och SQL Server.

Jag har ett projekt i huvudet, som i korthet går ut på att skriva om Bloggvänstern så att den BARA körs under SQL Server. Det är vansinnigt, men kunde vara kul att prova på. Självklart blir det CLR för en stor del av slanten. Det blir inte Bloggvänstern som sådant i första hand som ska köras i SQL Server, utan det blir en mer generell Bloggportal-applikation. Om det här projektet verkligen blir av beror till stor del på tillgång till tid. Jag återkommer löpande, och kommer också att - när/om det blir klart - skriva fullständig dokumentation för sajten, och avslöja eventuella "hemligheter".

Intressant?
Andra bloggar om SQL Server, Optimering, SQL Server 2008, Metabloggande

Nyligen stötte jag på en fråga som lyder:

Hur skriver jag en stored procedure för att göra en insert bara om det inte redan finns en rad med de värden jag försöker göra insert med.

Mitt svar är:

Använd IF NOT EXISTS(SELECT * FROM tabellen where kolumn1 = @kolumn1 AND kolumn2 = @kolumn2 [..] AND kolumnN = @kolumnN

Den som ställde frågan stötte på syntaktiska problem, och återkom med beskedet:

Jag har löst det. Jag skapade ett icke-klustrat unikt index med alternativet WITH IGNORE_DUP_KEY

Såhär alltså:

CREATE UNIQUE NONCLUSTERED INDEX UQI_tabellen_all ON tabellen(kolumn1, kolumn2, [..], kolumnN) WITH IGNORE_DUP_KEY

Fiffigt. Då behöver man inte programmera sin kontroll. Man kan bara göra en insert, och om en identisk rad redan finns så händer ingenting. Eller nästan ingenting iallafall. Sanningen är att SQL Server kastar ifrån sig en varning: "Duplicate key was ignored". Men det är ingenting jag i de flesta fall märker i min ADO.NET-kod. Där tror jag att allt gått som det ska, och kör vidare till nästa rad.

Frågan är om det verkligen är vad jag vill. Eller rättare sagt. Det här är INTE vad jag vill. Jag vill verkligen inte att SQL Server ska låtsas som att den har gjort en INSERT när den inte har gjort det. Försöker jag göra insert i en tabell där det finns en unique-constraint så vill jag veta att jag bryter mot databas-regler. Jag vill ha ett stort fett ERROR kastat när jag försöker bryta mot en unique-constraint.

Ett STORT problem med lösningen är också att den som frågade hade gjort ett ICKEKLUSTRAT index istället för ett KLUSTRAT. Det innebär att allt data dubbellagras, dels i tabellen själv, dels i indexträd för tabellen. Ska man använda sig av heltäckande index så ska de vara klustrade!

Jag vill alltså göra kontrollen med EXISTS-funktionen som jag beskriver ovan. Till min STORA glädje upptäcker jag också att det är mycket effektivare att göra så. Exakt varför vet jag inte, men troligen är det så att det snor en del resurser från SQL Server att konstatera att ett brott mot en constraint håller på att begås, och därför ge ifrån sig en varning och ignorera den fysiska inserten, istället för att "bara" göra en EXISTS-kontroll (som ju SQL Server ändå måste göra för att upprätthålla UNIQUE-constrainten).

Jag testade genom att först skapa en tabell:

create table t (id int identity(1,1) primary key nonclustered, c1 varchar(10), c2 varchar(10), c3 varchar(10))

Sedan skapade jag ett klustrat unique-index med WITH IGNORE_DUP_KEY:

create unique clustered index uqi_t_all ON t(c1,c2,c3) WITH IGNORE_DUP_KEY
go

Slutligen ett script som loopar 100000 gånger för att göra en INSERT:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 12 sekunder

Jag testar igen, denna gång med ett unique index UTAN WITH IGNORE_DUP_KEY

drop index t.uqi_t_all
go
create unique clustered index uqi_t_all ON t(c1,c2,c3)
go

Jag kör sedan mitt insert-script igen, den här gången med en EXISTS-kontroll:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
IF NOT EXISTS(SELECT * FROM t where c1=@c1 AND c2=@c2 AND c3=@c3)
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 1-2 sekunder

6-12 gånger snabbare alltså att göra kontroll i T-SQL istället för att låta SQL Server ignorera en insert genom IGNORE_DUP_KEY. Dessutom betydligt tydligare - alla kan se i SQL-koden att ingen insert kommer att göras om det skulle innebära skapande av dubletter. Med IGNORE_DUP_KEY är det betydligt mer otydligt.

Jag är nästan lite småsur över att IGNORE_DUP_KEY överhuvudtaget finns som ett alternativ för unique index. Varför gör man så? Det skapar risk för missförstånd och förvirring, och det är långsammare än att göra kontrollen i Transact-SQL.

För mer information om klustrade och icke-klustrade index: Vaddå klusterindex?

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