Category Archives: Miscellaneous

Manipulera XML utan att använda T-SQL

I dagens bloggpost tänkte jag skriva lite om XQuery och hur man kan använda det för att lösa att knivigt problem. Jag gjorde en proof-of-concept häromveckan där det krävdes att det gjordes ett hash-värde på en XML-kolumn i databasen för att upptäcka förändringar och dessutom använda den som en unik nyckel. Jag skriver unik nyckel eftersom den inte skulle användas om primärnyckel.

Bakgrunden är ett användargränssnitt där användarna fritt kan kombinera olika urval. Dessa användarväl modelleras i ett XML-dokument och sparas i databasen. Så här långt är det inga konstigheter, förutom att användargränssnittet inte hade ett deterministiskt sätt att gå tillväga med att spara urvalen. Den sparade helt enkelt ner urvalen i samma ordning som användaren markerade i kryssrutor osv. Det innebär att du får olika hash-värden för samma urval eftersom de i XML-dokumenten hamnar på olika ställen.

I första versionen av PoC gjorde jag urvalet med T-SQL och det blev en ganska klumpig lösning. Sedan inför PoC v2 tittade jag på XQuery och hittade en perfekt kandidat för att lösa kundens problem, nämligen funktionen Index-Of. Tyvärr visade det sig ganska snabbt att Microsoft inte har implementerat denna standardfunktion, så jag fick göra det på annat sätt.

Titta på det här XML-dokumentet

<filter>

  <parameter name=“def”>
    <value>200</value>
    <value>100</value>
  </parameter>
 
<parameter name=“abc”>
    <value>12</value>
    <value>23</value>
    <value>9</value>
  </parameter>
  <parameter name=“abc”>
    <value>23</value>
    <value>45</value>
  </parameter>
  <parameter name=“xyz”>
    <value>200</value>
    <value>40</value>
  </parameter>
  <parameter name=“abc”>
    <value>56</value>
  </parameter>
</filter>

Vad jag vill ha i databasen är en deterministiskt XML-dokument som ser ut så här

<filter>
  <parameter name=“abc”>
    <value>9</value>
   
<value>12</value>
    <value>23</value>
    <value>45</value>
    <value>56</value>
  </parameter>
  <parameter name=“def”>
    <value>100</value>
    <value>200</value>
  </parameter>
  <parameter name=“xyz”>
    <value>40</value>
    <value>200</value>
  </parameter>
</filter>

Det som kommer att hjälpa oss här mest är en funktion DISTINCT-VALUES som Microsoft har implementerat. Jag startar med vanlig FLWOR-semantik och skriver

<filter>
{
    for $c in distinct-values((
/filter/parameter/@name))
        order by string(lower-case($c))
        return
    <parameter name=”{lower-case($c)}” />
}
</filter>

Vad denna kod gör är att ta fram alla unika namn-attribut och returnera dem i en sorterad ordning.

<filter>
  <parameter name=“abc” />
  <parameter name=“def” />
  <parameter name=“xyz” />
</filter>

Inga konstigheter så här långt. Nästa steg blir att Hämta fram alla värden som är kopplade till just sin parameter. Vi vill t.ex inte ha värdet 40 kopplat till parametern abc. Tänker man sig inte för här är det lätt att få till en kartesisk produkt där du fram fram en unik lista på alla värden och jämför dem med alla parametrar. Det behövs inte!
I XQuery kan vi filtrerar elementen ganska lätt och få fram enbart de värde som är relevanta genom att filtrera den innersta loopen med de attributvärden som den yttre loopen genererat!

Vad vi behöver göra är att skriva en loop i loopen, så här.

<filter>
{
     for $c in distinct-values((
/filter/parameter/@name))
          order by string(lower-case($c))
          return 
     <parameter name=”{lower-case($c)}”>
     {
          for $v in distinct-values((/filter/parameter[@name = $c]/value))
              order by floor($v)
              return 
          <value>{$v}
          </value>
     }
     </parameter>
}
</filter>

Och där har vi lösningen!

<filter>
  <parameter name=“abc”>
    <value>9</value>
   
<value>12</value>
    <value>23</value>
    <value>45</value>
    <value>56</value>
  </parameter>
  <parameter name=“def”>
    <value>100</value>
    <value>200</value>
  </parameter>
  <parameter name=“xyz”>
    <value>40</value>
    <value>200</value>
  </parameter>
</filter>

Alla element är sorterade enligt attributet namn och sedan är alla värden sorterade numeriskt. Hur får vi då till detta som en funktion som vi kan använda oss av i T-SQL?

CREATE FUNCTION dbo.fnMakeSelectionDeterministic
(
   
@Content XML
)
RETURNS TABLE
RETURN  (

            SELECT  @Content.query(
                                    <filter>
                                      {
                                          for $c in distinct-values((/filter/parameter/@name))
                                              order by string(lower-case($c))
                                              return 
                                      <parameter name=”{lower-case($c)}”>
                                        {
                                            for $v in distinct-values((/filter/parameter[@name = $c]/value))
                                                order by floor($v)
                                                return 
                                        <value>{$v}
                                        </value>
                                        }
                                      </parameter>
                                      }
                                    </filter>
                                   ) AS Content
        )

Faran med att använda default-värden

Jag hade nyligen en kund som hade fått problem med en rapport. Jag åkte dit och tittade och kunde konstatera att denna kund, liksom många andra, förlitar sig på default-värden i T-SQL och de funktioner som erbjuds.

I just det här fallet var det en s.k Running Total som hade ställt till det för kunden. I versioner före SQL Server 2012 så har det varit omständigt att göra detta på ett bra sätt men från och med version 2012 finns det inbyggt i T-SQL.

Det är genom att används Partitioned Functions som detta gör att göra. Nu är inte detta en sak som Microsoft gör på egen hand utan dessa är definierade i ANSI SQL. Däremot är de olika tillverkarna olika snabba på att implementera funktionaliteten. Microsoft är inte en av de snabbare.

image 

Från och med SQL Server 2005 introducerades PARTITION BY. Och nu finns det ORDER BY samt WINDOW FRAMING.

Denna kund har ca en miljon rader i en tabell som det ska göras en running total på. Eftersom de har en kolumn med Avdelning, så partitionerar de på denna kolumn så att varje avdelning hanteras separat. För att få en konsekvent running total så gör de en ORDER BY på en annan kolumn. Det gör att alla rader inom partitionen sorteras på det önskade sättet. Men sedan händer något…

I min kunds fall skrev de inte ut ROWS eller RANGE utan förlitade sig på att RANGE ska inträffa, vilket det också gör. Men det är farligt som det skulle visa sig. Se detta exempel

image 

Som ni kan se så ska de två original-kolumnerna visas, samt tre ytterligare kolumner för att demonstrera faran i att använda default-värden.
Det här är resultatet av ovanstående kod

image

Som ni kan se blir running total inte som man kanske förväntar sig, när det finns dubbletter i sorteringen. Varför händer detta?
Jo, eftersom RANGE enbart hanterar UNBOUNDED PRECEDING, CURRENT ROW och UNBOUNDED FOLLOWING, dvs ett intervall, eller RANGE.
Då rad 2 processas ser SQL Server att Co1 (som det sorteras på) har värdet 2. RANGE träder i kraft och alla rader fram till och med 2 tas med.
Då kommer även rad 3 med!

Skillnaden blir tydlig när man ser hur ROWS hanterar samma data. Den uppträder som man kan förvänta sig. Dessutom finns det en klar prestandaskillnad med mellan ROWS och RANGE. Se följande bild på samma data

image

Om du tittar på exekveringsplanen så introduceras en Window Spool som har två olika sätt att hantera datat som flödar igenom.

  • Inmemory worktable
  • Diskbased worktable

Som du kan se på resultet av SET STATISTICS IO ON är det en stor skillnad på prestanda. In-memory worktable används när du definierar framingen med ROWS och antalet rader är mindre än 10 000 rader. Om framingen är fler än 10 000 rader kommer Windows Spool automatiskt att slå över till disk-based worktable.
Default är Disk-based worktable (RANGE, som alltid använder disk-based worktable) och det är det som hände min kund.

Ni som är riktigt observanta la säkert också märke till att de två raderna som har Col1 = 2 bytte ordning mellan hur datasetet såg ut i min Common Table Expression och i det slutgiltiga datasetet.

Hjälpmedel för felsökning

Idag tänkte jag skriva om hur en enkel lagrad procedur samt en inställning i Management Studio kan hjälpa dig med felsökning i framtiden.

Det finns massor med inställningar som ändrar beteendet på hur en fråga beter sig. T.ex SET ANSI_NULLS och XACT_ABORT. Det är ganska tidsödande att ta fram alla inställningar och dessutom tillkommer det ibland några efterhand.

Den procedur jag har på min laptop använder sig av tabellen master.dbo.spt_values. Den kanske ni känner igen sedan tidigare då den är flitigt använd som en Tally-table, en nummertabell, för att förenkla massor med programmeringsuppgifter. Vad ni kanske inte visste är att den innehåller massor med andra uppgifter. En av dessa uppgifter är just dina connection settings.

Börja med att skriva följande kod i Management Studio


USE
master
GO

CREATE PROCEDURE dbo.uspConnectionSettings
AS

SELECT Name AS Setting,
       CASE
              WHEN @@OPTIONS & Number = Number THEN ‘ON’
              ELSE ‘OFF’
       END AS Value
FROM   master.dbo.spt_values
WHERE  [Type] = ‘SOP’
       AND Number > 0;

Sedan går ni till inställningarna i Management Studio genom att klicka på Tools –> Options –> Environment –> Keyboard –> Query Shortcuts

I rutan för CTRL-3 (eller vilket kortkommando ni önskar), skriver du in


master
.dbo.uspConnectionSettings

Sedan behöver du tyvärr starta om Management Studio. När det är gjort behöver du bara trycka CTRL-3 för att få fram en lista med alla connection settings som finns och deras värde. Du behöver inte bekymra dig för att det ska tillkomma någon inställning i framtiden eftersom Microsoft lägger till denna i master.dbo.spt_values åt dig.

Lycka till!

En snabb beräkning av veckonummer enligt ISO

Med SQL Server 2008 och senare kan man enkelt ta fram veckonumret enligt ISO med hjälp av DATEPART(ISO_WEEK, @Date).

Tyvärr finns det många produktionsmiljöer kvar som använder SQL Server 2005 eller till och med SQL Server 2000 och SQL Server 7. För dessa miljöer är det enda sättet att få fram veckonumret genom att skriva en egen funktion. Som alltid finns det bättre sätt att göra det på och det finns sämre sätt att göra det på. De flesta jag sett är dessutom beroende på användarinställningar som SET DATEFIRST eller SET LANGUAGE. Detta vill jag ha bort.

Jag tänkte idag gå igenom en ny algoritm som börjat sprida sig på nätet ganska nyligen. Det är en av de smartaste implementeringarna jag sett och som bygger på en idé jag bloggade om 2009. Den enkla lösningen ser ut såhär

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7 + 3, ‘17530101’)) + 6) / 7

Tyvärr är den begränsad till datatypen DATETIME då Microsoft inte har implementerat DATE i alla deras datumfunktioner ännu. Är du intresserad av att se en komplett veckoberäkningsrutin för alla år mellan 1 och 9999 och som saknar alla beroenden av inbyggda funktioner ska du kolla här. Men eftersom DATE inte finns tillgänglig i SQL Server 2005 och tidigare så klarar vi oss bra med ovanstående kod.

Vecka nummer enligt ISO kan definieras på olika sätt. Antingen den vecka (måndag till söndag) som innehåller den första torsdagen vilket är den formella beskrivningen, eller den vecka som har minst fyra dagar på det nya året eller den vecka som innehåller den 4 januari. Vi kommer att använda oss av den formella beskrivningen och då har vi en utgångspunkt. Denna beräkning är känd och det gäller bara att vi hittar ett känt utgångsdatum som är en måndag. Datumet 17530101 är en måndag och det är också det äldsta datum som DATETIME hanterar, så detta passar oss alldeles utmärkt. Vad vi också måste passa oss för är att vi väljer ett utgångsdatum som är en måndag den 1 januari som INTE är ett skottår. Jag går inte in på matematiken här, det skulle ta alldeles för mycket plats. Således skulle inte 17540101 fungera. Återigen passar datumet 17530101 in eftersom 1753 inte är ett skottår.

Vi börjar med att räkna fram hur många dagar som passerat sedan 17530101 genom att skriva

DATEDIFF(DAY, ‘17530101’, @Date)

Då får vi ett heltal som är 95170 för datumet 20130727. Det har alltså passerat 95 170 dagar sedan 17530101. Efter detta förlitar vi oss på heltalsdivision i SQL Server. En vecka är som bekant 7 dagar och vi dividerar antalet passerade dagar med 7 och behåller heltalsdelen. Sedan multiplicerar vi med 7 igen för att få fram måndagen i den innevarande veckan genom att skriva följande

DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7

Då får vi fram ett värde 95165 som är måndagen den 22 juli 2013. Här använder jag mig inte av

DATEDIFF(WEEK, ‘17530101’, @Date)

eftersom denna är beroende av SET DATEFIRST. Prova med ett datum som är en söndag så får du se skillnaden mellan de två formlerna. Men nu skulle vi koncentrera oss på den första torsdagen så vi behöver addera tre dagar till måndagen för att komma till torsdag och detta gör vi enkelt genom att skriva

DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7 + 3

Då har vi värdet 95168 som motsvarar datumet torsdag 20130725. Detta antal dagar adderar vi nu till vårt utgångsdatum 17530101 genom att använda DATEADD för att få fram vårt torsdagsdatum i innevarande vecka

DATEADD(DAY, DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7 + 3, ‘17530101’)

Så här långt är det egentligen inget märkvärdigt med algoritmen. Det är med DATEPART vi börjar se hur smart algoritmen är. Vad denna gör är att ta fasta på “Veckonumret kan beräknas genom att räkna antalet torsdagar; vecka 12 innehåller den tolfte torsdagen i året.” och det är här som DATEPART kommer till vår räddning!

Det som vi först måste komma överens om är att alla torsdagar infinner sig på sju dagars intervall. Den torsdag som infaller mellan den 1 januari och 7 januari (1 till 7) enligt DATEPART, kan beräknas med följande och ska få veckonummer 1.

DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7 + 3, ‘17530101’))

Detta är också sant för 20130726 (vecka 30), som har den den trettionde torsdagen i året. För att få fram veckonumret kan vi antingen förlitar vi oss på heltalsdivision igen genom att först dra ifrån 1 (0 till 6), dividerar med 7 och lägger till 1 (veckor startar alltid med 1) vilket blir tre operationer, eller så adderar vi 6 (7 till 13) och dividerar med 7 vilket bara är två operationer.

Då kommer den kompletta algoritmen att se ut som

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7 + 3, ‘17530101’)) + 6) / 7

och den fungerar för alla datum som ryms i en DATETIME.

Vill man spara in ännu en operation i algoritmen kan man ta bort “+ 3” genom att istället addera antalet sjudagarsintervaller till en torsdag direkt, såhär

(DATEPART(DAYOFYEAR, DATEADD(DAY, DATEDIFF(DAY, ‘17530101’, @Date) / 7 * 7, ‘17530104’)) + 6) / 7

Lycka till!

Kuriosa om en snabb primtalsberäkning

Primtal. Vem kan inte älska dem? De är matematikens särlingar och ändå så användbara.
Utan primtal skulle vi inte kunna kryptera känslig information.

I mars i år deltog jag i en uppmaning att skriva kod så att korrekta primtal upp till 10 000 skulle produceras så fort som möjligt. Jag visste att jag hade en bra chans då jag ganska nyligen deltagit i Celkos tävling (http://www.sqlservercentral.com/articles/T-SQL/67666/) om precis samma uppgift. Men istället för att bara posta samma lösning (http://www.sqlservercentral.com/Forums/Topic759697-1604-1.aspx) igen så ville jag tänka igenom uppgiften om det fanns en annan algoritm som var snabbare än den jag kom tvåa med.

Jag började med att rita upp alla tal upp till 120 på ett papper.

image

Jag segmenterade talen om 30, då de tre första primtalen 2, 3, och 5 har en produkt som blir 30. Detta gjorde jag eftersom att efter 30 så skulle mönstret upprepa sig. Detta är en viktig insikt, att mönstret kommer att upprepa sig.

Som ni kan se i bilden ovan, markerar de röda blocken de tal som omöjligen är ett primtal då de är delbara med antingen 2, 3, eller 5. Kvar är de gröna eller blå talen som är möjliga primtal. Som du kan se finns det som mest 8 möjliga primtal i varje segment. I realiteten finns det bara sju primtal då ett av de möjliga primtalen är falskt.
1 är per definition inte ett primtal, 47 är produkten av sju och sju, 77 är produkten av sju och elva, 91 är produkten av sju och tretton samt 119 som är produkten av sju och sjutton. Denna insikt innebär att jag bara behöver testa 8 av 30 tal i varje segment dvs 26.67% av talen, en avsevärd prestandaförbättring!

Jag bestämde mig för att det skulle räcka med att producera ett visst antal segment upp till det önskade primtalet. Dessutom behöver jag inte kontrollera 2, 3 och 5 då dessa redan har använts för att bestämma segmentets storlek, benämnt x. Det första tal jag behöver kontrollera är 7, dvs x – 23. De följande talen blir 11 (x – 19), 13 (x – 17), 17 (x – 13), 19 (x- 11), 23 (x – 7) samt 29 (x – 1). Men eftersom jag jag skjutit på sekvensserien med sju steg så måste jag även ta med 31 (x + 1). Då har jag mina åtta primtalskandidater och dessa sparar jag i en temporär tabell. Om jag nu ska visa alla primtal upp till 1 000 000 så behöver jag bara lagra 266 665 tal.

Nu när jag har mina primtalskandidater så behöver jag bara kontrollera om det för varje primtalskandidat finns ett lägre tal som ingår i primtalskandidatens faktorisering. Och det är känt sedan tidigare att man bara behöver testa faktorisering upp till roten av primtalskandidaten.

Nu började koden ta form. Jag skulle producera lika många 30-talssegment upp till det önskade primtalet och bara de möjliga kandidaterna. Sedan skulle jag kontrollera för alla primtalskandidater om det finns ett lägre tal (upp till roten av primtalskandidaten) som ingår i faktoriseringen av primtalskandidaten.

Så här blev det till slut.

CREATE TABLE    #Numbers
                (
                        PrimeCandidate INT NOT NULL,
                        PrimalityTest BIGINT PRIMARY KEY CLUSTERED
                );

DECLARE @MaxPrime BIGINT = 1000000;

WITH n0(p)
AS (
        SELECT  1 UNION ALL
        SELECT  1
), n1(p)
AS (
        SELECT          1
        FROM            n0 AS a
        CROSS JOIN      n0 AS b
), n2(p)
AS (
        SELECT          1
        FROM            n1 AS a
        CROSS JOIN      n1 AS b
), n3(p)
AS (
        SELECT          1
        FROM            n2 AS a
        CROSS JOIN      n2 AS b

), n4(p)
AS (
        SELECT          1
        FROM            n3 AS a
        CROSS JOIN      n3 AS b
), n5(p)
AS (
        SELECT          1
        FROM            n4 AS a
        CROSS JOIN      n4 AS b
)
INSERT          #Numbers
                (
                        PrimeCandidate,
                        PrimalityTest
                )
SELECT          f.PrimeCandidate,
                f.PrimeCandidate * f.PrimeCandidate AS PrimalityTest
FROM            (
                        SELECT  TOP (1 + @MaxPrime / 30)
                                30 * ROW_NUMBER() OVER (ORDER BY p) AS Segment
                        FROM    n5
                ) AS v(Segment)
CROSS APPLY     (
                        VALUES  (v.Segment 23),
                                (v.Segment 19),
                                (v.Segment 17),
                                (v.Segment 13),
                                (v.Segment 11),
                                (v.Segment   7),
                                (v.Segment   1),
                                (v.Segment +  1)
                ) AS f(PrimeCandidate)
WHERE           f.PrimeCandidate <= @MaxPrime;

SELECT  Prime
FROM    (
                VALUES  (2),
                        (3),
                        (5)
        ) AS v(Prime)
WHERE   Prime <= @MaxPrime

UNION ALL

SELECT  n.PrimeCandidate AS Prime
FROM    #Numbers AS n
WHERE   NOT EXISTS      (
                                SELECT  *
                                FROM    #Numbers AS p
                                WHERE   p.PrimalityTest <= n.PrimeCandidate
                                        AND n.PrimeCandidate % p.PrimeCandidate = 0
                        );

DROP TABLE      #Numbers;

Denna kod producerar alla primtal (78 498) upp till 1 000 000 på cirka 1,5 sekund på min laptop. Jag har provat med att använda 7 som basprimtal med, för att utöka segmentstorleken till 210 men det verkar som de 48 primtalskandidaterna i dessa segment (22,86%) inte ger så stor förbättring.

CREATE TABLE    #Numbers
                (
                        PrimeCandidate INT NOT NULL,
                        PrimalityTest BIGINT PRIMARY KEY CLUSTERED
                );

DECLARE @MaxPrime BIGINT = 1000000;

WITH n0(p)
AS (
        SELECT  1 UNION ALL
        SELECT  1
), n1(p)
AS (
        SELECT          1
        FROM            n0 AS a
        CROSS JOIN      n0 AS b
), n2(p)
AS (
        SELECT          1
        FROM            n1 AS a
        CROSS JOIN      n1 AS b
), n3(p)
AS (
        SELECT          1
        FROM            n2 AS a
        CROSS JOIN      n2 AS b

), n4(p)
AS (
        SELECT          1
        FROM            n3 AS a
        CROSS JOIN      n3 AS b
), n5(p)
AS (
        SELECT          1
        FROM            n4 AS a
        CROSS JOIN      n4 AS b
)
INSERT          #Numbers
                (
                        PrimeCandidate,
                        PrimalityTest
                )
SELECT          f.PrimeCandidate,
                f.PrimeCandidate * f.PrimeCandidate AS PrimalityTest
FROM            (
                        SELECT  TOP (1 + @MaxPrime / 210)
                                210 * ROW_NUMBER() OVER (ORDER BY p) AS Segment
                        FROM    n5
 
              ) AS v(Segment)
CROSS APPLY     (
                        VALUES  (v.Segment 199),
                                (v.Segment 197),
                                (v.Segment 193),
                                (v.Segment 191),
                                (v.Segment 187),
                                (v.Segment 181),
                                (v.Segment 179),
                                (v.Segment 173),
                                (v.Segment 169),
                                (v.Segment 167),
                                (v.Segment 163),
                                (v.Segment 157),
                                (v.Segment 151),
                                (v.Segment 149),
                                (v.Segment 143),
                                (v.Segment 139),
                                (v.Segment 137),
                                (v.Segment 131),
                                (v.Segment 127),
                                (v.Segment 121),
                                (v.Segment 113),
                                (v.Segment 109),
                                (v.Segment 107),
                                (v.Segment 103),
                                (v.Segment 101),
                                (v.Segment   97),
                                (v.Segment   89),
                                (v.Segment   83),
                                (v.Segment   79),
                                (v.Segment   73),
                                (v.Segment   71),
                                (v.Segment   67),
                                (v.Segment   61),
                                (v.Segment   59),
                                (v.Segment   53),
                                (v.Segment   47),
                                (v.Segment   43),
                                (v.Segment   41),
                                (v.Segment   37),
                                (v.Segment   31),
                                (v.Segment   29),
                                (v.Segment   23),
                                (v.Segment   19),
                                (v.Segment   17),
                                (v.Segment   13),
                                (v.Segment   11),
                                (v.Segment    1),
                                (v.Segment +   1)
                ) AS f(PrimeCandidate)
WHERE           f.PrimeCandidate <= @MaxPrime;

SELECT  Prime
FROM    (
                VALUES  (2),
                        (3),
                        (5),
                        (7)
        ) AS v(Prime)
WHERE   Prime <= @MaxPrime

UNION ALL

SELECT  n.PrimeCandidate AS Prime
FROM    #Numbers AS n
WHERE   NOT EXISTS      (
                                SELECT  *
                                FROM    #Numbers AS p
                                WHERE   p.PrimalityTest <= n.PrimeCandidate
                                        AND n.PrimeCandidate % p.PrimeCandidate = 0

                        );

DROP TABLE      #Numbers;

Omvandla text i UTF-8 till standard ANSI

Idag hade jag tänkt visa en funktion som omvandlar text kodat i UTF-8 till vanlig ANSI, så att det går att spara utan förlust i SQL Server. Nu har SQL Server 2012 stöd för UTF-8 men inte alla har investerat i SQL Server 2012 ännu.

Wikipedia har denna förklaring till vad UTF-8 är (http://sv.wikipedia.org/wiki/UTF8):

UTF-8 (åtta-bitars Unicode transformationsformat) är en längdvarierande teckenkodning som används för att representera text kodad i Unicode, som en sekvens av byte (oktetter). Unicode använder upp till 21 bitar per tecken, vilket inte får plats i en byte, och därför används till exempel i textfiler vanligen en av metoderna UTF-8 eller UTF-16 för att få en serie bytes. UTF-8 har valts som huvudsaklig teckenkodning i internetprotokoll: nya protokoll måste stöda denna teckenkodning, om det inte av speciella skäl är olämpligt.

För inte så länge sedan hade jag en kund som ville spara ner svar från webbformulär ner i deras databas. Då den externa samarbetspartnern använde UTF-8 visade det sig snabbt ohanterbart att låta tecken försvinna i databasen för att kunna lagra informationen.

För att få en så snabb kod som möjligt tittade jag på problemet i den andra änden, dvs vilka ansikoder har en motsvarighet i UTF-8 och inte tvärtom. Det visade sig ganska snabbt att det bara är 0x60 koder som har en motsvarighet och det är alla koder för ANSI 0xA0 och upp till och med 0xFF. Alla dessa koder har ett prefix i UTF-8 som antingen är 0xC2 eller 0xC3.

Med detta i minne började jag skriva en iterativ skalär funktion. Jag vet att det inte är det optimala men i detta fallet är en skalär funktion det mest användbara.


CREATE
FUNCTION dbo.fnConvertUtf8Ansi
(
    @Source VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Value SMALLINT = 160,
            @Utf8 CHAR(2),
            @Ansi CHAR(1)

    IF @Source NOT LIKE ‘%[ÂÃ]%’
        RETURN  @Source

    WHILE @Value <= 255
        BEGIN
            SELECT  @Utf8 = CASE
                                WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value)
                                WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value 64)
                                ELSE NULL
                            END,
                    @Ansi = CHAR(@Value)

            WHILE CHARINDEX(@Source, @Utf8) > 0
                SET    @Source = REPLACE(@Source, @Utf8, @Ansi)

            SET    @Value += 1
        END

    RETURN  @Source
END

Vad funktionen gör är att för alla koder i ansi som har en motsvarighet, ta fram motsvarigeten i UTF-8 och göra en replace på bara dessa koder om de verkligen finns i texten.

Funktion för att testa om en händelse inträffar inom ett givet tidsintervall

Det finns flera fall där man vill ta reda på om någonting inträffar t.ex nästa dag, vecka eller månad. Det kan vara en födelsedag eller en annan högtidsdag.

För att kunna göra denna jämförelse behöver man tre datum; ett datum för när någonting inträffade och två datum som anger inom vilket tidsintervall man vill undersöka om händelsen kommer att inträffa igen.

Det finns en inbyggs funktion i SQL Server för att ta reda på en tidsdifferens mellan två datum och den heter DATEDIFF. Var bara beredd på att den inte uppför sig om man kan förvänta sig. Läs mer här http://www.sqlteam.com/article/datediff-function-demystified.

Kontentan är att om dagen för händelsen ligger senare i månaden än vad dagens datum är får man lägga till en extra månad för att kompensera för hur hjärnan fungerar och hur DATEDIFF fungerar.

Börja med att deklarera tre datum

DECLARE @DoB DATETIME,
        @FromDate DATETIME,
        @ToDate DATETIME;

För att kunna kontrollera om händelsen kommer att inträffa inom det givna tidsintervallet behöver vi en referenspunkt och två händelsedatum att jämföra med varandra. I vårt fall blir referenspunkten @DoB. Sedan får vi helt enkelt undersöka hur många hela månader som inträffat till den ena händelsedatumet @FromDate och dessutom hur många hela månader som inträffat till det andra händelsedatumet @ToDate.

CASE
        WHEN DATEPART(DAY, @DoB) >= DATEPART(DAY, @FromDate) THEN DATEDIFF(MONTH, @DoB, @FromDate) – 1
        ELSE DATEDIFF(MONTH, @DoB, @FromDate)
END / 12

Sedan gör du likadant, nästan, med det andra datumet. När jag säger nästan så är det för att om händelsedatumet ligger exakt på det andra datumet så får vi hantera det lite annorlunda.

CREATE FUNCTION dbo.fnHasAnniversary
(
    @DoB DATETIME,
    @FromDate DATETIME,
    @ToDate DATETIME
)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @From INT,
            @To INT

    IF @FromDate > @ToDate
        SELECT  @From = CASE
                            WHEN DATEPART(DAY, @DoB) >= DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @DoB, @ToDate) – 1
 
                           ELSE DATEDIFF(MONTH, @DoB, @ToDate)
 
                       END / 12,
 
               @To = CASE
                          WHEN DATEPART(DAY, @DoB) > DATEPART(DAY, @FromDate) THEN DATEDIFF(MONTH, @DoB, @FromDate) – 1
                          ELSE DATEDIFF(MONTH, @DoB, @FromDate)
                      END / 12
    ELSE
        SELECT  @From = CASE
                            WHEN DATEPART(DAY, @DoB) >= DATEPART(DAY, @FromDate) THEN DATEDIFF(MONTH, @DoB, @FromDate) – 1
 
                           ELSE DATEDIFF(MONTH, @DoB, @FromDate)
 
                       END / 12,
 
               @To = CASE
                          WHEN DATEPART(DAY, @DoB) > DATEPART(DAY, @ToDate) THEN DATEDIFF(MONTH, @DoB, @ToDate) – 1
                          ELSE DATEDIFF(MONTH, @DoB, @ToDate)
                      END / 12

   
RETURN  NULLIF(@To, @From)
END

Det blir inte svårare än så här. Jag tänkte istället berätta om två kanske nya funktioner som inte är så kända. Den första är “RETURNS NULL ON NULL INPUT”. Det gör att funktion själv känner av att om en av parametrarna är NULL så exekveras ingen kod i funktionen alls, utan funktionen returnernerar NULL direkt.

Den andra funktionen är NULLIF. Den funktionen returnerar NULL om båda parametrarna är lika med varandra. I vårt fall passar det det, för om den första åldern är lika med den andra åldern så har ingen födelsedag inträffat och då returnerar funktionen NULL. Om de två variablerna däremot är olika varandra, t.ex om @From är 39 och @To är 40, så har personen fyllt 40 under det angivna tidsintervallet och då vill vi returnera 40 istället för 39. Då sätter vi @From före @To.

Funktionen är enkel att använda.

SELECT  Name,
        DateOfBirth,
        dbo.fnHasAnniversary(DateOfBirth, ‘20130603’, ‘20130609’) AS Age
FROM   dbo.Employees

Lycka till!

Hur matematikkunskaper kan hjälpa till med optimering

Idag fick jag tillfället att hjälpa en kollega med att optimera en fråga för en kunds räkning. Originalfrågan såg i princip ut så här

— Original
SELECT      t.*
FROM        dbo.Table1 AS t
CROSS JOIN  (
                SELECT  ChildID
               
FROM    dbo.Table1
           
) AS w
WHERE       (
                t.ParentID = w.ChildID AND t.ParentID <> t.ChildID
               
OR
               
t.ChildID = t.ParentID AND t.ChildID = w.ChildID
           
)
           
AND t.CategoryID IN (’11’, ’22’, ’33’)

 

Det som jag reagerade först på var användandet av OR i WHERE-satsen. Det gör det mycket svårare för Query Optimizer att välja ett bra index och att skapa en bra exekveringsplan. När jag sedan tittat lite närmare på den för att se vad som egentligen ska hända, vilken affärsregel som ska uppfyllas, ser jag att uttrycket är överflödigt.

Låt oss börja med uttrycket efter OR.

t.ChildID = t.ParentID
t.ChildID = w.ChildID

Av detta kan vi sluta oss till att även uttrycket t.ParentID = w.ChildID är sant. Dessutom är det sant att om t.ChildID = t.ParentID så är det dessutom sant att t.ParentID = t.ChildID. Låt oss skriva om uttrycket med vår härledning och dessutom byta plats på uttrycket efter OR.

— Härledning
SELECT      t.*
FROM        dbo.Table1 AS t
CROSS JOIN  (
                SELECT  ChildID
               
FROM    dbo.Table1
           
) AS w
WHERE       (
                t.ParentID = w.ChildID AND t.ParentID <> t.ChildID
               
OR
                t.ParentID = w.ChildID
AND t.ParentID = t.ChildID
           
)
           
AND t.CategoriID IN (’11’, ’22’, ’33’)

Vad vi nu kan se är att vi har två exklusiva uttryck i vår OR. I det ena fallet ska t.ParentID = t.ChildID och i det andra utttycket ska t.ParentID <> t.ChildID.
Av detta vet vi att alla rader ska med, för antingen så har de två kolumnerna lika värde eller så har de det inte. Vi behöver heller inte heller ta hänsyn till NULL här då de båda kolumnerna inte tillåter NULL. Om vi plockar bort det onödiga uttrycket från frågan får vi följande query.

— Bortplockning
SELECT      t.*
FROM        dbo.Table1 AS t
CROSS JOIN  (
                SELECT  ChildID
               
FROM    dbo.Table1
           
) AS w
WHERE       (
                t.ParentID = w.ChilID
               
OR
               
t.ParentID = w.ChildID
           
)
           
AND t.CategoryID IN (’11’, ’22’, ’33’)

Nu börjar det likna något! Ett uttryck ska vara sant eller så ska samma uttryck igen vara sant. Det är en överflödig kontroll, så det slutade här med att jag skrev om frågan så den såg ut så här istället.

— Optimerad
SELECT        t.*
FROM          dbo.Table1 AS t
INNER JOIN    dbo.Table1 AS w ON w.ChildID = t.ParentID
WHERE         t.CategoryID IN (’11’, ’22’, ’33’)


Vad gjorde det då för prestandan? Med hjälp av SET STATISTICS TIME, IO ON fick jag följande resultat.

— Original
(391 row(s) affected)
Table ‘Worktable’. Scan count  8, logical reads 151599, physical reads 0.
Table ‘Table1’.    Scan count 17, logical reads   1466, physical reads 0.
Table ‘Worktable’. Scan count  0, logical reads      0.

SQL Server Execution Times:
   CPU time = 11389 ms, elapsed time = 1873 ms.

— Omskrivning
(391 row(s) affected)
Table ‘Worktable’. Scan count  0, logical reads      0, physical reads 0.
Table ‘Table1’.    Scan count  2, logical reads   1256, physical reads 0.

SQL Server Execution Times:
   CPU time =   733 ms, elapsed time =  732 ms.

Detta lilla matematiska knep gjorde att omskrivningen använder 15 ggr färre CPU samt slutförs 2,5 ggr snabbare än tidigare. När jag tittade närmare i exekveringsplanen såg jag att omskrivningen bara behöver 1 core medan originalfrågan la beslag på alla 16 cores.

Om det nu varit så att kolumnerna tillåtit NULL, hade jag varit tvungen att lägga till en extra kontroll som den här för att efterlikna originalfrågan till 100%.

— Optimerad med NULL
SELECT        t.*
FROM          dbo.Table1 AS t
INNER JOIN    dbo.Table1 AS w ON w.ChildID = t.ParentID
WHERE         t.CategoryID IN (’11’, ’22’, ’33’)
              AND t.ChildID IS NOT NULL

Behöver du Performance Tuning eller Query Tuning?

Det är den stora frågan. Dessutom en aktuell sådan. Idag hade jag inte tänkt beskriva någon smart kod utan istället berätta om skillnader i sätt att angripa prestandaproblem.

Det senaste året har jag på konferenser, seminarier och utbildningstillfällen ofta blivit hejad på och hamnat i en diskussion om vad som behövs göras i ett system för att snabba upp applikationen som använder SQL Server som lagringsplattform. Mitt första svar blir oftast “Det beror på om du är DBA eller utvecklare.”.
Nästan undantagslöst ser jag då ett frågetecken hos frågeställaren som nu undrar om jag är riktigt klok i huvudet. För han eller hon vill ju bara ha ett svar på hur deras applikation ska gå snabbare!

Jag ställer ofta en uppföljningsfråga i stil med “Vad är det du upplever som ett bekymmer?”. Och efter ett litet tag med halmstrån som långa väntetider, deadlocks och IO-problem brukar det utkristallisera sig vad egentligen är som upplevs som ett problem. De flesta professionella DBA-er och utvecklare vet om att det som brukarna upplever som ett bekymmer oftast bara är symptomet på det verkligen problemet medan de med mindre erfarenhet koncentrerar sig på fel saker. De sätter helt enkelt bara ett plåster på tummen när det egentligen hade behövts gips på armen. Eller i värsta fall en full hjärt-, lever-, lung- och njur-transplantation. Där slutar mina liknelser för i den verkliga världen går det ännu inte att transplantera en hjärna men i vår digitala värld går det att åstadkomma.

För enkelhetens skull påstår jag att DBA-er vill ha Performance Tuning och utvecklare vill ha Query Tuning. När man arbetar med SQL Server behöver man ta hänsyn både till hårdvara och mjukvara och som överallt annars är det svårt att se allt i svart eller vitt. Det finns nästan alltid en gråzon någonstans där mittemellan. Det finns en stark koppling mellan Performance Tuning och hårdvara samt mellan Query Tuning och mjukvara.

Jag ska fortsätta med en annan liknelse för att göra det enklare att förklara den egentliga skillnadan mellan de både metoderna att angripa ett prestandaproblem. För det är just prestandan som i nästan alla fall är den gemensamma nämnaren på de problem som brukarna rapporterar.

Vi börjar med Performance Tuning eller hårdvaran. Låt oss säga att vi ärver en gammal Bubbla från Volkswagen. Det finns folk som verkligen vet hur man ska pimpa den till att bli så kraftfull som möjligt inom ramen för en budget. Dessa personer är DBA-er. De är ansvariga för att hålla kolla på så att det finns olja i motorn, kylvätska och spolarvätska. De kollar lufttryck i däcken och inställningarna för tändstiften. De servar och reparerar bubblan för att den ska vara i så bra trim som möjligt.

De kan byta ut motorn till en starkare motor om de tycker det behövs eller sätta på vinterdäcken när det är påkallat. De kollar mätarställningen och tvättar bilen. Vad jag vill illustrera är att DBA-er har en stor uppgift att underhålla och se till så att bubblan uppför sig som man kan förvänta sig. DBA-er är experter på detta.

Då uppkommer den logiska frågan, vad gör då en utvecklare eller Query Tuning? Jo, dessa är experter på mjukvaran. Det är utvecklarna som vet hur man ska få bubblan att köra så fort som möjligt inom ramen för den hårdvara som existerar. Det är de som väljer körväg och hur mycket som behövs lutas i kurvorna. Pressar de mjukvaran för mycket så kommer hårdvaran inte att hinna med. Det är detta som händer är oerfarna utvecklare skriver dålig kod som i sin tur genererar alldeles för mycket IO eller använder för många processorer eller cores än nödvändigt. Även om det är DBA-erna som bestämt hur stor tanken ska vara, är det utvecklarna som kan se till så att bilen bara drar 0,1 liter per mil istället för 13 liter per mil. Det är utvecklarnas kod som avgör om bilen kommer att gå i 700 km/h eller i 15 km/h.
Jag har arbetat med databaser i över 20 år. En enda gång har lösningen på ett prestandaproblem varit att köpa mer hårdvara, eller uppgradera den befintliga hårdvaran. All gånger, utom den gången, har lösningen varit att skriva bättre kod eller skapa bättre design i applikationen. Det är också så att det är den lösningen som ger mest valuta för pengarna. Dubblar du antalet processorer, mängden minne och dubblar IO på ditt SAN, kan du förvänta dig att applikationen går dubbelt så snabbt, till en hög kostnad. Men spendera någon eller några timmar med kod och design, och du får en prestandaökning som kan räknas i 100 elller 10 000 000 gånger bättre prestanda.

Och någonstans här mittemellan ska dessa två yrkesgrupper komma överens. Utvecklarna kommer med önskemål om att “Servern är lite långsam, kan ni sätta i mer minne?” på vilken DBA-n antingen svarat “Javisst kan jag göra det” eller “Nej, det behövs inte. Du har lika mycket minne som Facebook och Google tillsammans. Använd det du har på ett bättre sätt.”.

Det är inte helt enkelt att få dessa två yrkesgrupper att samarbeta då de har olika fokus. Men en sak har de trots allt gemensamt och det är att göra brukarna nöjda inom ramen för det ledningen tillhandahåller. Och det är därför jag fortsätter med att undervisa, presentera och prata om prestandaförbättringar för att underlätta för brukaren, för det är ändå de som ser till så att vi alla har ett jobb att sköta.

Ha det kul tills nästa gång vi ses!

//Peter

Enkel funktion för att dela upp en sträng

Det finns flera fall där man behöver dela upp en sträng i flera delar. En anledning kan vara att man behöver en flexibel parameter till en lagrad procedur. En annan kan vara att man arbetar med en tabell som inte är normaliserad.

Jag påträffade en annan sorts problem i vintras när jag arbetade med en stor kund. Deras applikation lämnade ifrån sig en sträng till databasen och strängens innehåll var beroende på vad användarna gjort för val i klienten med hänsyn till checkboxar, fritextrutor och annat. Dessa värden skulle sedan in till en lagrad procedur för att bygga ihop en dynamisk fråga. Jag lyckades till slut att göra proceduren 10 000 000 gånger snabbare och en av faktorerna (förutom omdesign av datamodellen) var att hantera den sträng som kom från klienten på ett effektivt sätt som möjligt. Vill du veta mer om listor i SQL Server kan du läsa här http://www.sommarskog.se/arrays-in-sql.html.

Som de flesta vet är just hantering av strängar inte en av SQL Servers styrkor. Men det finns sätt att göra det effektivt på! Vad jag kommer att visa nu är en funktion som hanterar multipla attribut med flerval samt wildcard.

Ett typisk strängvärde kan se ut som “PersonID=201304061234“. Vad den informationen betyder är att kolumnen PersonID ska vara lika med det personnummer som skickades med. Det här är ganska trivialt att dela upp med T-SQL.  Strängen kan också se ut så här “Utbildningsnivå=Kandidat|Stad=Malmö”. De olika attributen kan alltså vara uppdelade med ett pipe-tecken.

I min kunds fall visade det sig dessutom att det finns flera olika klienter som anropade den aktuella proceduren. Från en klient kan strängen se ut så här “Medlemskap=Guld|Medlemskap=Silver|Inkasso=Nej” medan den kan se ut så här “Medlemskap=Guld,Silver|Inkasso=Nej” från en annan klient. Till en början innebar det naturligtvis en utmaning men när du tittar närmare på de kombinationer som finns, uppdagar ett mönster sig.

  • De olika attributen är uppdelade med “|”
  • Attribut och värde är uppdelat med “=”
  • Flervalsmöjligheter är uppdelade med “,”

Finns det ett samband mellan de fyra olika stränginnehållen ovan? Ja, det finns det faktiskt. Om vi börjar i andra ändan, dvs med det slutresultat vi vill ha, så syns det ganska tydligt.

Attribut

Värde

PersonID 201304061234

Attribut

Värde

Utbildningsnivå Kandidat
Stad Malmö

Attribut

Värde

Medlemskap Guld
Medlemskap Silver
Inkasso Nej

Aha! Det som ska göras är en normalisering av stränginnehållet. När denna insikt lagt sig startade jag att fundera på hur man på enklast möjliga sätt ska skriva en rutin för att dela på strängen på det önskade viset. Redan i planeringsstadiet föll en iterativ process bort, mest med tanke på komplexiteten att hantera komma eller inte komma och när kommer pipen. Likamedtecknet är det enklaste i detta fallet.

Precis om jag redovisade ovan gjorde jag också på papper hos kunden. När jag brottades med hur jag skulle likställa attributet Medlemskap mellan de olika klienterna insåg jag att en OUTER APPLY skulle göra hela skillnaden! Och då föddes tanken med att skriva funktionen som en Inline Table-Valued Function (IVTF) för absolut bästa prestanda. För att underhållet av funktionen skulle krävas ett minimum av specialkunskaper valde att jag att arbeta med XML.

Första åtgärden var att dela upp strängen med pipetecknet för att separera de olika attributen. Vad jag ville åstadkomma var ett XML-dokument som såg ut så här

<r>
  <c>Medlemskap=Guld,Silver</c>
</r>
<r>
  <c>Inkasso=Nej</c>
</r>

Den biten är ganska lätt. Jag gjorde en REPLACE på “|”till de olika xml-elementen med hjälp av den här statementet

SELECT CAST(‘<r><c>’ + REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’) + ‘</c></r>’ AS XML)

Men det är inte tillräckligt. Jag vill ju även dela upp strängen i attribut och värden som olika kolumner. Det kan vi göra genom att lägga till en extra REPLACE i samma statement! Då ser statementet ut så här

SELECT CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML)

och producerar ett XML-dokument som ser ut så här

<r>
  <c>Medlemskap</c>
  <c>Guld,Silver</c>
</r>
<r>
  <c>Inkasso</c>
  <c>Nej</c>
</r>

Vid en första anblick kan det se konstigt ut att jag benämner de båda kolumnerna som “c”. Tänk då på att vi arbetar med XML och vi kan särskilja dessa med deras inbördes positioner. Detta är i normala fall inget som ska ha en betydelse men för den här funktionen måste det ha en betydelse då vi inte vet om det ens finns ett värde för attributet.

Sätter vi ihop statementet till en fungerande fråga som ska fungera som bas för funktionen får vi skriva en wrapper och hantera utdatat som XML.

SELECT d.Data
FROM   (
              VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
       ) AS d(Data)

I detta läge vill vi omvandla XML-dokumentet i mindre dokument, eftersom varje nod “r” ska bli en minst en rad i resultatsetet. Det gör vi genom att dela upp XML-dokumentet med en CROSS APPLY. Här kan vi använda CROSS APPLY eftersom det måste finnas minst en nod “r” för annars är XML-dokumentet tomt.

SELECT        r.n.query(‘.’)
FROM          (
                     VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
              ) AS d(Data)
CROSS APPLY   d.Data.nodes(‘(r)’) AS r(n)

Nu har vi två XML-dokument som ser ut så här

<r>
  <c>Medlemskap</c>
  <c>Guld,Silver</c>
</r>

<r>
  <c>Inkasso</c>
  <c>Nej</c>
</r>

Nu kommer det att bli lite svårare eftersom vi ska kontrollera om värdet är ett flerval genom att dela upp nod “c” i två delar igen. Och här får vi ju inte dela upp attributet med hjälp av komma! För att särskilja de två noderna “c” mellan attribut och värde använder vi deras inbördes ordning med hjälp av xquery-noteringen “[]”.

SELECT        r.n.query(‘.’),
              f.n
FROM          (
                     VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
              ) AS d(Data)
CROSS APPLY   d.Data.nodes(‘(r)’) AS r(n)
CROSS APPLY   (
                     VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
              ) AS f(n)

Detta statement ger ett resultatset som har två kolumner

<r><c>Medlemskap</c><c>Guld,Silver</c></r>      <r>Guld</r><r>Silver</r>
<r><c>Inkasso</c><c>Nej</c></r>                 <r>Nej</r>

Vad vi nu behöver är en sista uppdelning på den andra kolumnen och dela upp noderna “r”. Denna gång använder vi OUTER APPLY eftersom vi inte vet om det ens finns ett värde för attributet.

SELECT        r.n.query(‘.’),
              f.n,
              x.n.query(‘.’)
FROM          (
                     VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
              ) AS d(Data)
CROSS APPLY   d.Data.nodes(‘(r)’) AS r(n)
CROSS APPLY   (
                     VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
              ) AS f(n)
OUTER APPLY   f.n.nodes(‘(r)’) AS x(n)

OUTER APPLY gör att vi nu får tre rader i resultatsetet (Andra och tredje kolumnen)

<r>Guld</r><r>Silver</r>   <r>Guld</r>
<r>Guld</r><r>Silver</r>   <r>Silver</r>
<r>Nej</r>                 <r>Nej</r>

Nu är det klart och vi kan skriva funktionen så här

CREATE FUNCTION dbo.fnTokenizeAttributes
(
    @AttributeList VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN  (
            WITH cteAttributes(KeyType, KeyValue)
            AS (
                    SELECT          ISNULL(r.n.value(‘(c[1])’, ‘VARCHAR(48)’), ) AS KeyType,
                                    ISNULL(x.n.value(‘(text()[1])’, ‘VARCHAR(512)’), ) AS KeyValue
                    FROM            (
                                        VALUES (CAST(‘<r><c>’ + REPLACE(REPLACE(@AttributeList, ‘|’, ‘</c></r><r><c>’), ‘=’, ‘</c><c>’) + ‘</c></r>’ AS XML))
                                    ) AS d(Data)
                     CROSS APPLY    d.Data.nodes(‘(r)’) AS r(n)
                     CROSS APPLY    (
                                         VALUES (CAST(‘<r>’ + REPLACE(r.n.value(‘(c[2])’, ‘VARCHAR(MAX)’), ‘,’, ‘</r><r>’) + ‘</r>’ AS XML))
                                    ) AS f(n)
                     OUTER APPLY    f.n.nodes(‘(r)’) AS x(n)
               )
               SELECT DISTINCT LTRIM(RTRIM(KeyType)) AS KeyType,
                               LTRIM(RTRIM(KeyValue)) AS KeyValue
               FROM            cteAttributes
               WHERE           LTRIM(RTRIM(KeyType)) >
        )
GO

Vad jag också gjorde i funktionen var att sätta saknade kolumner med NULL-värde till tomsträng. Detta gör jag eftersom det kan finnas attribut utan värden. Dessutom kontrollerar jag att inga rader med tomma attribut returneras samt tar bort alla dubbletter.

Ge mig datumet för den tredje lördagen i maj 2013

I många fall har man nytta av att ta reda på vilket datum den en viss veckodag infaller i en viss månad, t.ex den tredje lördagen i maj 2013. Det kanske är för att ta fram mötesdagar eller att planera återkommande besök.

Det finns många som tycker att man ska använda sig av en kalendertabell och skapa en sju nya kolumner för detta. Jag tycker inte det. Dels för att det skapar onödigt redundant data i tabellen samt att det kommer att generera mycket mer IO i databasen än vad som behövs.

Alternativet är då att beräkna sig fram till vilken datum den tredje lördagen i maj 2013 är. Och hur gör man det på ett effektivt sätt?

Lösningen ligger i att veta att den första lördagen, eller vilken veckodag som helst, infaller mellan den 1 och 7 i varje månad. Den andra lördagen infaller mellan den 8 och 14, den tredje lördagen mellan den 15 och 21, den fjärde lördagen mellan den 22 och 28 samt till slut den eventuellt femte lördagen som infaller mellan den 29 och 29/30/31 i den önskade månaden.

För att effektivt kunna göra en beräkning på detta behöver vi totalt tre parametrar till vår funktion.

  1. Ett datum (vilket som helst) som infaller den månad som önskas.
  2. Ett datum med samma veckodag som det datum som önskas. Denna parameter skulle kunna ändras till ett hårdkodat värde, men då är det absolut viktigt att det dokumenteras vilket värde som används till vilken veckodag.
  3. Ett värde för första, andra, tredje, fjärde eller femte veckodagen som önskas.

Först beräknar vi den förste i den önskade månaden med följande algoritm.

DATEADD(MONTH, DATEDIFF(MONTH, ‘19000101’, @WantedDate), ‘19000101’)

Ovanstående algoritm är ganska enkel. Först beräknar man antalen kalendermånader som passerat sedan ett fixerat datum (i det här fallet 1900-01-01) och den månad som önskas. Det blir 1 360 månader för maj 2013.

Dessa månader läggs sedan till ett annat fixerat datum (för enkelhetens skull är även detta 1900-01-01). Då blir resultatet av algoritmen 2013-05-01. En ganska bra början.

Och nu till tricket. Jag har valt att inte göra en CASE för alla sju datumen som kan komma i fråga, utan skapar en virtuell tabell med sju rader med alla de datum som ligger inom det önskade intervallet.

VALUES      (DATEADD(DAY, 7 * @Nth 7, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 6, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 5, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 4, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 3, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 2, b.FirstDateOfWantedMonth)),
            (DATEADD(DAY, 7 * @Nth 1, b.FirstDateOfWantedMonth))

  

Nu vet vi att det önskade datumet är något av ovanstående. Så hur ska vi validera resultatet? Det behövs totalt tre kontroller för detta

  1. Kontrollera så att det önskade intervallet är 1, 2, 3, 4 eller 5.
  2. Kontrollera vilket av ovanstående datum som har den önskade veckodagen.
  3. Kontrollera så att datumen ovan verkligen är i samma månad som önskas. Det kan bli problem annars om man önskar den femte lördagen och det visar sig att den egentligen ligger i efterföljande månad.

Kontrollerna kan se ut som dessa

WHERE       DATEDIFF(DAY, f.theDate, @WeekDay) % 7 = 0
            AND DATEDIFF(MONTH, f.theDate, @WantedDate) = 0
            AND @Nth BETWEEN 1 AND 5

Den första kontrollen ser till så att det datum vi vill ha tillbaka har samma veckodag som det önskade datumet genom att beräkna differens i dagar mellan dessa och ta det datumet som är en multipel av sju dagar. Den andra kontrollen ser till så att inget datum spiller över till följande månad. Den tredje kontrollen ser till så att värdet på parametrarna ligger inom ett giltigt intervall.

Och svårare än så här blir det inte. Sätter vi ihop allting till en funktion så kommer den att se ut så här

CREATE FUNCTION dbo.fnGetNthWeekday
(
            @WantedMonth DATE,
            @WantedWeekday DATE,
            @Nth TINYINT
)
RETURNS DATE
AS
BEGIN
        RETURN  (
                       
SELECT          f.theDate
                        FROM            (
                                                VALUES  (DATEADD(MONTH, DATEDIFF(MONTH, ‘19000101’, @WantedMonth), ‘19000101’))
                                        ) AS b(FirstDateOfWantedMonth)
                        CROSS APPLY     (
                                                VALUES  (DATEADD(DAY, 7 * @Nth 7, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 6, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 5, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 4, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 3, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 2, b.FirstDateOfWantedMonth)),
                                                        (DATEADD(DAY, 7 * @Nth 1, b.FirstDateOfWantedMonth))
                                        ) AS f(theDate)
                        WHERE           DATEDIFF(DAY, f.theDate, @WantedWeekday) % 7 = 0
                                        AND DATEDIFF(MONTH, f.theDate, @WantedMonth) = 0
                                        AND @Nth BETWEEN 1 AND 5
                )
END

Hur gör man sedan för att ta fram den tredje lördagen i maj 2013?

SELECT      dbo.fnGetNthWeekday(‘20130501’, ‘19690906’, 3)

Så nu vet du att den 18 maj 2013 är den tredje lördagen i maj 2013.