BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Ett till användningsområde för nummertabeller - att räkna antalet arbetsdagar mellan två datum. Det finns naturligtvis fler sätt att göra detta, genom att räkna dagarna med DATEDIFF och dra av för lördag och söndag. Men jag tycker att det blir mer intuitivt med Nummertabell.

--Först skapar vi nummertabellen, om den inte redan finns
SELECT TOP 11000 IDENTITY(int,0,1) AS N INTO dbo.Numbers
FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2

--Index på nummertabellen är ett måste
CREATE CLUSTERED INDEX ixc_n ON dbo.Numbers(N)

--Se till att måndag räknas som veckans första dag.
SET DATEFIRST 1

--Deklarera datum-variabler för Från och Till.
DECLARE @d1 datetime, @d2 datetime
SET @d1 = '20110101 12:00:00.000'
SET @d2 = '20110115 12:00:00.000'

--Också själva beräkningen
SELECT COUNT(*) FROM dbo.Numbers
WHERE N < DATEDIFF(day,@d1, @d2)
AND DATEPART(weekday,dateadd(day,n,@d1)) not in (6,7)

Ganska enkelt och rättfram va?

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Transact SQL ska användas för att skriva queries. Transact SQL ska INTE användas för att skriva loop-konstruktioner. Tyvärr är det så att det finns massor med verksam kod där Transact SQL används för att skriva loopar eller andra konstruktioner som skulle kunna lösas med mängdfunktioner.

Ett ganska vanligt scenario är kalender-problem. En tabell innehåller transaktioner och en datumkolumn. Som exempel använder jag en evenemangskalender:

CREATE TABLE Evenemang( ID int IDENTITY(1,1) PRIMARY KEY,
Beskrivning nvarchar(MAX), TidOchDatum datetime)

Någon vill skapa en rapport, där alla dagar i februari finns med som rubriker, och om det finns evenemang ska info om dessa också finnas med.

Många skulle välja att lösa det i applikationskoden, i ASP.NET eller annat språk. Det skulle bli ineffektivt och troligen skulle en query för varje dag köras.

Jag skulle lösa det ungefär såhär:

SELECT TOP 11000 IDENTITY(int,1,1) as N INTO dbo.Nummer

FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2

ALTER TABLE dbo.Nummer
ADD CONSTRAINT PK_Nummer
PRIMARY KEY(N)

GO
CREATE TABLE Evenemang( ID int IDENTITY(1,1) PRIMARY KEY,
Beskrivning nvarchar(MAX), TidOchDatum datetime)
GO
INSERT INTO Evenemang(Beskrivning,TidOchDatum)
SELECT 'Här är lite evenemangsinfo','20110205 00:00:30.000'

SELECT e.Beskrivning,
CONVERT(varchar(10),DATEADD(day,N-1,'20110201 00:00:00.000'),120) 
FROM dbo.Nummer n
LEFT JOIN dbo.Evenemang e
ON 
CONVERT(varchar(10),DATEADD(day,N-1,'2011-02-01 00:00:00.000'),120) = 
CONVERT(varchar(10),TidOchDatum,120)
WHERE DATEDIFF(month,'20110201 00:00:00.000',DATEADD(day,N-1,'20110201 00:00:00.000'))=0
order by CONVERT(varchar(10),DATEADD(day,N-1,'2011-02-01 00:00:00.000'),120) 

Med en nummertabell alltså, mot vilken jag joinar evenemangstabellen. Jag använder en rad tal i ordningsföljd för att - för varje rad - skapa ett datum-fält. Jag joinar mot Evenemangstabellen. Med en LEFT JOIN löser jag detta att alla dagar inte har evenemang, men jag vill returnera alla dagar.

Prestandan med att använda en Nummertabell för att sådant scenario som ovan kan omöjligt slås med loopar, varken i T-SQL-koden eller i .NET.

Att Nummer-tabellen skapades med just 11.000 rader beror på att ungefär så många dagar har förflutit sedan 1900-01-01, som är SQL Servers första giltiga datum för datum-datatyperna det motsvarar ungefär 30 års datum.

Det här var det första inlägget om Nummer-tabellen. Den kan användas till en massa saker och jag ser alltid till att skapa en Nummer-tabell det första jag gör när jag installerat en ny SQL Server-instans. Stort tack till Jeff Moden, som redan 2008 i ett pedagogiskt inlägg på SQL Server Central skrev om Nummertabellen (Tally table som han kallar den). Det var inte Jeff Moden som "uppfann" konceptet, men han har beskrivit det mycket pedagogiskt. Jag ska inte göra det så enkelt för mig som att jag kopierar hans exempel och svenskifierar dem. Istället kommer jag att ge andra exempel på Nummertabellens användningsområden.

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

En vanlig funktion på webbsidor är att visa ett antal rader, N antal rader i taget. Ganska många gånger har jag sett det göras genom att alla rader laddas till ett DataSet- eller DataTable-objekt och sidhanteringen görs sedan på webbserver-sidan. Men vill man slippa minnesslukande stora objekt på webbserver-sidan finns det skäl att implementera sidhanteringen i databasen, genom att bara returnera rader en sida i taget.

Här är ett exempel på en Stored Procedure som gör just det:

CREATE PROC HämtaSida(
    @sidnummer int=1,
    @sidstorlek int=10
)
AS
WITH PagingTable AS(
    SELECT
        *,
        ROW_NUMBER() OVER(ORDER BY kolumn_att_sortera_på) AS RadNummer
    FROM
        EN_TABELL
    )
SELECT * FROM PagingTable
WHERE RadNummer BETWEEN (@sidnummer-1) * @sidstorlek +1 AND @sidnummer * @sidstorlek

Alltså:

• Skapa en Common Table Expression (CTE) – PagingTable –  som tar fram alla kolumner plus radnummer från en tabell. Radnummer tas fram med ROW_NUMBER()-funktionen, med angivelse av sorterings-kriterium.
• Gör select mot PagingTable, med radnumren mellan (@sidnummer –1) * @sidstorlek +1 och @sidnummer * @sidstorlek.

Som exempel: Om jag har en tabell EN_TABELL, med 100 rader, och en ID-kolumn som jag vill sortera på så skriver jag om min stored procedure så att ROW_NUMBER()-funktionen använder ORDER BY ID istället för ORDER BY kolumn_att_sortera_på. Skickar jag sedan in @sidnummer=1 och @sidstorlek=10 till proceduren så kommer raderna mellen (1-1) * 10 + 1 och 1*10 att selekteras, dvs de tio första raderna. Med @sidnummer=3 och @sidstorlek=15 blir det raderna mellan (3-1) * 15 +1 och 3 * 15 att returneras, dvs raderna mellan 31 och 45.

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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…

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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.

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

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?