Christoph Ingenhaag

Trennzeichen getrennten String sortieren

Manchmal braucht man Dinge...

if object_id('dbo.OrderStringparts') is not NULL drop function dbo.OrderStringparts

create function dbo.OrderStringparts

(

@String nvarchar(max),

@Delimiter nvarchar(max)

)

returns nvarchar(max)

begin

declare @out nvarchar(max);

with tab(Part) as -- http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

(

select

substring(@String, n, charindex(@delimiter, @String + @delimiter, n) - n)

from dbo.Numbers(1, len(@String)) -- http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly

where

substring(@delimiter + @String, n, len(replace(@delimiter, ' ', '_'))) = @delimiter

string(string) as

(

select

@Delimiter + Part

from tab

order by

Part

for xml path('')

)

select

@out = substring(string, 2, 2147483647)

from string

return @out

end

Viele Grüße

Christoph Ingenhaag

Fortlaufende Summe

Eine Möglichkeit, eine fortlaufende Summe zu erzeugen ist die Nachfolgende.

Voraussetzung ist eine fortlaufende Nummer ohne Lücken und aus Gründen der Performance ein Index auf die fortlaufenden Nummer.

Da man diese Voraussetzungen selten vorfindet, lege ich eine entsprechende temporäre Tabelle an, die diesen Voraussetzungen genügt.

Aus den Testdaten

begin try drop table ##test, #test end try begin catch end catch

select

n as ID,

cast(abs(checksum(newid())) * 1.0 / 10000000 as int) as Betrag

into ##test

from dbo.numbers(1,100000) -- http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly

where

n % 3 = 1 or

n % 5 = 1

erzeuge ich die temporäre Tabelle:

select

row_number() over (order by ID) as RNR,

ID,

Betrag

into #test

from ##test

create unique clustered index cuidx on #test(RNR)

Und hier die Abfrage

;with posten as

(

select

RNR,

ID,

Betrag,

1 as MinRNR,

count(*) over (partition by 1) as MaxRNR

from #test

zeilenweise as

(

select

RNR,

ID,

Betrag,

Betrag as Summe,

MinRNR,

MaxRNR

from posten

where

RNR = MinRNR

union all

select

z.RNR + 1,

p.ID,

p.Betrag,

z.Summe + p.Betrag,

z.MinRNR,

z.MaxRNR

from zeilenweise z

inner join posten p

p.RNR = z.RNR + 1

where

z.RNR < z.MaxRNR

)

select

ID,

Betrag,

Summe

from zeilenweise

option (maxrecursion 0)

Die Ausführungsgeschwindigkeit ist bis ca. 100.000 Datensätze durchaus annehmbar...

Viele Grüße

Christoph Ingenhaag

Table Value Parameters (TVP) und kein passendes Client API. Was nun?

Grundsätzlich sind in den Artikel von Erland Sommarskog die Vorgehensweisen beschrieben:

http://www.sommarskog.se/arrays-in-sql-2008.html#Workarounds

http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-length

Aber es wird dort nur beispielhaft auf eine Liste von ganzen Zahlen eingegangen. Hier im Beispiel aber von ganzen Datensätzen.

Im nachfolgenden Beispiel wird nur ein TVP verwandt. Ziel im Beispiel ist es, Personendatensätze in eine Tabelle zu schreiben. Damit es nicht allzu langweilig ist, sollen vorhandene Sätze geändert und nicht vorhandene Sätze eingefügt werden.Das wird mittels MERGE und dem übergebenen TVP in einer Stored Procedure realisiert, für die eine weitere Stored Procedure als Wrapper verwendet wird, in der wiederum eine Funktion aufgerufen wird, die den BLOB als Parameter erhält und daraus den TVP zurück gibt, der dann an die aufzurufende Stored Procedure weitergegeben wird.

Achtung, man benötigt man die Numbers Funktion von Steve Kass:

http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly/

bzw. eine Numbers Tabelle (siehe z.B. http://www.glorf.it/blog/2010/08/17/sql-talk/number-helper-table )

Nun zu erst einmal die Tabelle und den passenden TVP erzeugen:

if object_id('dbo.Persons') is not NULL drop table dbo.Persons

if object_id('dbo.tvpTest') is not NULL drop procedure dbo.tvpTest

if object_id('dbo.fn_get_tvpPerson') is not NULL drop function dbo.fn_get_tvpPerson

if object_id('dbo.tvpTestWrapper') is not NULL drop procedure dbo.tvpTestWrapper

begin try drop type dbo.tvpPersons end try begin catch end catch

create table dbo.Persons

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

create type dbo.tvpPersons as table

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

Die Frage, ob ein TVP einen Primary Key benötigt und ggf. sogar unique constraints, muss man anhand des Verwendungszweckes klären. Hier im Beispiel sollen Datensätze bei Vorhandensein aktualisiert werden, sonst eingefügt werden. Nachfolgend die entsprechende Stored Procedure:

create proc dbo.tvpTest

@tvp as dbo.tvpPersons readonly

set nocount on

set xact_abort on

merge into dbo.Persons t

using @tvp s

on s.ID = t.ID

when matched then

update set

t.ID = s.ID,

t.Vorname = s.Vorname,

t.Name = s.Name,

t.GebDat = s.GebDat

when not matched then

insert

(

ID,

Vorname,

Name,

GebDat

)

values

(

s.ID,

s.Vorname,

s.Name,

s.GebDat

);

go

da die Client API nicht mit TVPs umgehen kann, nun die Wrapper Prozedur, die knapp gehalten ist, da die Funktionalität in der aufzurufenden Funktion liegt (der Parameter @p ist hier als output definiert um sich im SQL Server Profiler den Inhalt des Parameters als Textdata anschauen zu können)

create proc dbo.tvpTestWrapper

@p varbinary(max) output

set nocount on

set xact_abort on

declare @tvp as dbo.tvpPersons

insert into @tvp

select * from dbo.fn_get_tvpPerson(@p)

exec dbo.tvpTest @tvp

Die Funktion hat die Aufgabe, den BLOB in die entsprechenden Häppchen zu schneiden und in die entsprechenden Datentypen zu konvertieren. Was der Client da binär anliefert, lässt sich nicht immer einfach von binary zu dem gewünschten Datentypen konvertieren. Hier im Beispiel muss die Byte Reihenfolge beim INT umgedreht werden und das Datum als String übertragen werden. Natürlich könnte man alles als String übertragen, würde damit aber auch entsprechend mehr Bandbreite benötigen. Da bekannt ist, wie viele Bytes ein Datensatz im BLOB hat, ist mit Hilfe der Numbers Funktion das Teilen in einzelne Sätze kein Problem.

create function dbo.fn_get_tvpPerson

(

@p varbinary(max)

)

returns @tvp table

(

ID int primary key clustered,

Vorname nvarchar(30),

Name nvarchar(30),

GebDat datetime

)

begin

if datalength(@p) % 140 = 0

begin

insert into @tvp

select

convert

(

int,

substring(@p, 4 + (n -1) * 140, 1) +

substring(@p, 3 + (n -1) * 140, 1) +

substring(@p, 2 + (n -1) * 140, 1) +

substring(@p, 1 + (n -1) * 140, 1)

convert(nvarchar(30), substring(@p, 5 + (n -1) * 140, 60)),

convert(nvarchar(30), substring(@p, 65 + (n -1) * 140, 60)),

convert(nvarchar(16), substring(@p, 125 + (n -1) * 140, 16))

from dbo.Numbers(1, datalength(@p)/140)

end

return

end

Als Test-Client fungiert hier VBA mit ADO 2.8.

Nachfolgend die Klasse TVP4ADO28

Private adoStream As ADODB.stream

Private Type typRecord
ID As Long              '4 Byte
Vorname As String * 30 '60 Byte
Name As String * 30     '60 Byte
GebDat As String * 8    '16 Byte
End Type

Private Type typBinRecord
record(1 To 140) As Byte
End Type

Private Sub Class_Initialize()

Set adoStream = New ADODB.stream
adoStream.Type = adTypeBinary
adoStream.Mode = adModeReadWrite
adoStream.Open

End Sub

Public Sub AddRecord(ID As Long, Vorname As String, Name As String, GebDat As Date)

Dim t As typRecord
Dim r As typBinRecord
Dim s() As String

s() = Split(CStr(GebDat), ".")

t.ID = ID
t.Vorname = Vorname
t.Name = Name
t.GebDat = s(2) & s(1) & s(0)

LSet r = t

adoStream.Write r.record

End Sub

Public Property Get GetStream() As ADODB.stream

adoStream.Position = 0
Set GetStream = adoStream

End Property

Private Sub Class_Terminate()

adoStream.Close
Set adoStream = Nothing

End Sub

und hier der Aufruf (Der Application Name im Connection String dient zum einfachen Filtern im SQL Server Profiler)

Public Sub Test()

Dim conn As New ADODB.Connection
Dim com As New ADODB.Command
Dim i As Long

Set tvp = New TVP4ADO28

For i = 1 To 10000

tvp.AddRecord i, "Klaus", "Müller", "09.06.1957"

Next i

With conn

.ConnectionString = _
"Provider=SQLNCLI10;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=Test;" & _
"Data Source=MeinServer\MeineInstanz;" & _
"Application Name=MeineApplikation"

.CursorLocation = adUseClient
.IsolationLevel = adXactReadCommitted
.Mode = adModeReadWrite

.Open

End With

With com

.CommandType = adCmdStoredProc
.CommandText = "dbo.tvpTestWrapper"
.ActiveConnection = conn
.NamedParameters = False

.Parameters.Append .CreateParameter("@p", adVarBinary, adParamInputOutput, _
tvp.GetStream.Size, tvp.GetStream.Read)

Dim t As Single

t = Timer

.Execute Options:=adExecuteNoRecords

t = Timer - t

End With

Debug.Print t

conn.Close

Set com = Nothing
Set conn = Nothing
Set tvp = Nothing

End Sub

Viele Grüße

Christoph Ingenhaag

Rückgaben von Stored Procedures ad hoc weiterverarbeiten

Wenn man die Rückgabe eines Resultsets einer Stored Procedure ad hoc in einer neuen Tabelle oder direkt weiterverarbeiten möchte, kann man mit Hilfe von OPENROWSET (siehe Hilfe, Ad Hoc Distributed Queries müssen erlaubt sein) ein wenig tricksen:

Der Einfachheit halber habe ich für das Beispiel die Stored Procedure sp_help verwendet:

Rückgabe in eine Tabelle schreiben

select *

into #t1

from openrowset

(

'SQLNCLI',

'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',

'exec sp_help;'

) as a;

select * from #t1

drop table #t1

oder einfach als Funktion weiterverwenden

create function dbo.fn_help()

returns table

return

(

select *

from openrowset

(

'SQLNCLI',

'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',

'exec sp_help;'

) as a

);

select * from dbo.fn_help()

drop function dbo.fn_help

Dieses Vorgehen sollte aber die Ausnahme bleiben, ad hoc eben.

Ergänzend siehe auch

http://www.insidesql.org/blogs/uricken/2010/08/20/verwendung-von-udf-auf-linked-server

Viele Grüße

Christoph Ingenhaag

In IN ist mehr drin

IN Operator

test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)

Bekannt ist, das man mit dem IN Operator prüfen kann, ob Werte in einer Spalte vorhanden sind:

where Spalte1 in (1,2,3)

das es auch andersherum geht, ist eher unbekannt. Obwohl nach Onlinehilfe die test_expression "nur" ein gültiger Ausdruck sein muss:

where 1 in (Spalte1, Spalte2, Spalte3)

siehe hierzu auch:

http://blogs.technet.com/wardpond/archive/2009/09/04/database-programming-did-you-know-in-can-do-this.aspx

http://sqlblog.com/blogs/denis_gobo/archive/2009/04/09/13186.aspx

Gültige Ausdrücke sind

{ constant | scalar_function | [ table_name. ] column | variable 

        | ( expression ) | ( scalar_subquery ) 

    | { unary_operator } expression 

    | expression { binary_operator } expression 

    | ranking_windowed_function | aggregate_windowed_function

}

also where (Ausdruck) in (Spalte1, Spalte2, Spalte3)

Aber der Ausdruck darf nicht mehr als eine Zeile und Spalte zurückgeben (skalarer Ausdruck). Mit einem kleinen Trick kann man aber trotzdem mehrere Werte übergeben. Dazu ein Beispiel mit Testdaten:

begin try drop table #t end try begin catch end catch;

select

identity(int, 1, 1) id,

into #t

from

(

values

(0,0,0,0,0,0), (1,0,0,0,0,0), (0,1,0,0,0,0),

(0,0,1,0,0,0), (0,0,0,1,0,0), (0,0,0,0,1,0),

(0,0,0,0,0,1), (2,0,0,0,0,0), (0,2,0,0,0,0),

(0,0,2,0,0,0), (0,0,0,2,0,0), (0,0,0,0,2,0),

(0,0,0,0,0,2), (3,0,0,0,0,0), (0,3,0,0,0,0),

(0,0,3,0,0,0), (0,0,0,3,0,0), (0,0,0,0,3,0),

(0,0,0,0,0,3)

) t(sp1, sp2, sp3, sp4, sp5, sp6);

Ein Crossjoin mit den zu überprüfenden Werten funktioniert:

select distinct id

from #t,

(

select arg

from (values (1), (2)) a(arg)

) t

where

arg in (sp1, sp2, sp3, sp4, sp5, sp6);

Noch ein anderes Beispiel, mit dem man alle Lottozahlen 6 aus 49 inkl. Zusatzzahl erzeugen kann (vielleicht weiß ja jemand, wie man damit den Jackpot knacken kann ;-) )

;with n as

(

select 1 n

union all

select n + 1

from n

where

n < 49

z as

(

select 1 z

union all

select z + 1

from z

where

z < 10

)

,lotto as

(

select

n1.n as Zahl_1,

n2.n as Zahl_2,

n3.n as Zahl_3,

n4.n as Zahl_4,

n5.n as Zahl_5,

n6.n as Zahl_6,

z.z as Zusatzzahl

from n n1, n n2, n n3, n n4, n n5, n n6, z

where

n1.n not in (n2.n, n3.n, n4.n, n5.n, n6.n) and

n2.n not in (n1.n, n3.n, n4.n, n5.n, n6.n) and

n3.n not in (n1.n, n2.n, n4.n, n5.n, n6.n) and

n4.n not in (n1.n, n2.n, n3.n, n5.n, n6.n) and

n5.n not in (n1.n, n2.n, n3.n, n4.n, n6.n) and

n6.n not in (n1.n, n2.n, n3.n, n4.n, n5.n) and

n2.n > n1.n and

n3.n > n2.n and

n4.n > n3.n and

n5.n > n4.n and

n6.n > n5.n

)

select top(1) * -- aus 139838160 Zeilen

from lotto

order by

newid();

Viele Grüße

Christoph Ingenhaag

Beispiel zur Zusammenfassung

In diesem Artikel wird wie angekündigt ein Anwendungsbeispiel gezeigt, welches mittels Table Valued Parameters und MERGE ein als Tabelle gespeichertes Ergebnis von GROUPING SETS erweitert und aktuell hält. Selbstverständlich wird mit INSERT over DML die Aktion protokolliert.

Hiermit soll nicht aufgezeigt werden, dass dies der richtige Weg ist, eine solche Aufgabenstellung zu lösen, sondern die Möglichkeit der gemeinsamen Anwendung der neuen Features in T-SQL. Quasi als Ideengeber. Wer tatsächlich solche Problemstellungen hat, sollte sich mit dem Themenkreis rund ums Datawarehouse auseinandersetzen.

Für das Beispiel wird die Beispieldatenbank AdventureWorks2008 OLTP benötigt (http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040)

Zuerst einmal der Code, um aufzuräumen:

use AdventureWorks2008;
set nocount on;
go

if object_id('Production.Refresh', 'P') is not NULL drop proc Production.Refresh;
go

if object_id('Production.MatProd_data', 'U') is not NULL drop table Production.MatProd_data;
go

if object_id('Production.MrgAlertLog', 'U') is not NULL drop table Production.MrgAlertLog;
go

if object_id('Production.Prod_data', 'V') is not NULL drop view Production.Prod_data;
go

if 
    not exists(select * from sys.dm_sql_referencing_entities('dbo.grpset', 'type'))
    and exists(select * from sys.types where name = 'grpset' and is_table_type = 1)

drop type dbo.grpset;
go

Ich stelle solchen Code gerne an den Beginn und an das Ende von Beispiel-/Testcode, damit ich auch bei Fehlern das Beispiel nach Korrektur ohne weiteres Ausführen von Bereinigungscode starten kann und in jedem Fall nach vollständiger Ausführung alle Spuren beseitigt habe.
Was hier auffällt ist die neue DMV im untersten IF-Konstrukt um den Type zu löschen, wenn vorhanden. Diese DMV sys.dm_sql_referencing_entities sowie die DMV sys.dm_sql_referenced_entities sind neu in SQL Server 2008 und geben verlässliche(!) Auskunft über Abhängigkeiten zwischen Objekten zurück. Hierüber könnte man auch einen kurzen Artikel schreiben, ein Blick in die BOL tut‘s aber locker auch.

So, leicht vom Thema abgekommen - nun wieder zurück zur Aufgabenstellung:
Zuerst brauchen wir Testdaten, daher auch der Zugriff auf die AdventureWorks2008.

create view Production.Prod_data 
with schemabinding
as
select
     PC.Name                            Category
    ,PS.Name                            Subcategory
    ,P.Name                             Product
    ,datepart(yy, T.TransactionDate)    TA_year
    ,datepart(qq ,T.TransactionDate)    TA_quarter
    ,datepart(mm, T.TransactionDate)    TA_month
    ,datepart(dw, T.TransactionDate)    TA_weekday
    ,T.Quantity                         Qty
    ,T.ActualCost                       Cost
from Production.Product P
inner join Production.ProductSubcategory PS
on
    P.ProductSubcategoryID = PS.ProductSubcategoryID
inner join Production.Productcategory PC
on
    PC.ProductCategoryID = PS.ProductCategoryID
inner join Production.TransactionHistoryArchive T
on    
    T.ProductID = P.ProductID;
go

Ob diese Daten in diesen Verknüpfungen Sinn machen, ist unerheblich. Hauptsache Beispieldaten.

Mit 2 kombinierten Rollups erfüllen wir einfach mal die Anforderung, „die man sinnbildlich uns für die Auswertung herangetragen hat“:

select
     grouping_id(TA_weekday, TA_month, TA_quarter, TA_year, Product, Subcategory, Category) Grpid
    ,cast(hashbytes('MD5',
         isnull(cast(TA_weekday as varchar), '')    +
         isnull(cast(TA_month as varchar), '')      +
         isnull(cast(TA_quarter as varchar), '')    +
         isnull(cast(TA_year as varchar), '')       + 
         isnull(Product, '')                        +
         isnull(Subcategory, '')                    +
         isnull(Category, '')) as varbinary(100)) HashVal
    ,Category
    ,Subcategory
    ,Product
    ,TA_year
    ,TA_quarter
    ,TA_month
    ,TA_weekday
    ,sum(Qty)        sumQty
    ,avg(Cost)       avgCost
into Production.MatProd_data
from Production.Prod_data
group by
    rollup(Category, Subcategory, Product),
    rollup(TA_year, TA_Quarter, TA_month, TA_weekday);

Damit wir nicht nur die einzelnen Gruppierungen wieder finden, sondern auch die unterschiedlichen Werte der einzelnen Gruppen eindeutig wieder erkennen können ohne alle Spalten miteinander vergleichen zu müssen, wird zusätzlich ein Hashwert über die Spalten gebildet (ohne Aggregate). Ob Hashwert oder Checksum oder ein ähnliches Verfahren entscheidet sich danach, ob die Daten mit GROUPING_ID und dem berechneten Wert eindeutig unterscheidbar sind. Nicht nur um dies sicherzustellen erzeugen wir einen geclusterten eindeutigen Index.

create unique clustered index cuiProductionMatProd_data_GrpidHashVal
on Production.MatProd_data(Grpid, HashVal);

Ein zusammengesetzter Primärschlüssel auf diese beide Spalten ist auch eine gute Idee, ich aber verzichte in solchen Szenarien grundsätzlich darauf, da es sich hierbei ja „nur“ um Auswertungen handelt. HashVal ist zwar der eindeutig selektivere Wert (im Idealfall eindeutig), da aber nicht über HashVal zugriffen wird, sondern über Grpid, die oben angegebene Reihenfolge im Index.

So, die Daten liegen jetzt also vor. Weiter geht’s:
Jetzt brauchen wir noch einen Type, der die Daten enthalten wird, die zur Änderung der Auswertung erstellt werden.

create type dbo.grpset as table
(
     Grpid          int
    ,HashVal        varbinary(100)
    ,Category       nvarchar(100)
    ,Subcategory    nvarchar(100)
    ,Product        nvarchar(100)
    ,TA_year        int
    ,TA_quarter     int
    ,TA_month       int
    ,TA_weekday     int
    ,sumQty         int
    ,avgCost        money
    ,primary key clustered(GrpID, HashVal)
);

Da man bei einem Type keinen Index definieren kann, hier der Umweg über die Definition eines Primärschlüssels.

Da wir nach Änderungen im Anschluss einfach feststellen wollen, schreiben wir ein Protokoll über die Änderungen. Dazu legen wir eine entsprechende Tabelle an:

create table Production.MrgAlertLog
(
     Grpid          int
    ,HashVal        varbinary(100)
    ,Action         nvarchar(10)
    ,I_sumQty       int
    ,I_avgCost      money
    ,D_sumQty       int
    ,D_avgCost      money
);

Der Name der Tabelle zeugt von Wichtigkeit und beeindruckt vielleicht den einen oder anderen Kollegen. Zumindest kann man damit glänzen, wenn man sich damit brüstet, wertvolle Informationen darin entdeckt zu haben ;-)

Damit der Join zu den materialisierten Daten zügig durchgeführt wird, erzeugen wir einen dazu analogen Index.

create unique clustered index cuiProductionMrgAlertLog_GrpidHashVal
on Production.MrgAlertLog(Grpid, HashVal);
go

So, jetzt fehlt ja noch die Prozedur, die die eigentliche Arbeit erledigen soll und die Arbeit, die man ihr gibt.

Fangen wir mit dem Arbeiter an:

create proc Production.Refresh
    @GrpSet dbo.GrpSet readonly
as
set nocount on

begin try

insert into Production.MrgAlertLog
select 
     Grpid
    ,HashVal
    ,Action
    ,I_sumQty
    ,I_avgCost
    ,D_sumQty
    ,D_avgCost
from
(
    merge into Production.MatProd_data T
    using @GrpSet S
    on
        T.Grpid     = S.GrpID   and
        T.HashVal   = S.HashVal
    when matched and 
        (
            T.sumQty    <> S.sumQty or
            T.avgCost   <> S.avgCost    
        )
    then
        update set
            T.sumQty    = S.sumQty,
            T.avgCost   = S.avgCost
    when not matched by target then
        insert
        (
             Grpid      
            ,HashVal    
            ,Category   
            ,Subcategory
            ,Product    
            ,TA_year    
            ,TA_quarter 
            ,TA_month   
            ,TA_weekday 
            ,sumQty     
            ,avgCost            
        )
        values        
        (
             S.Grpid      
            ,S.HashVal    
            ,S.Category   
            ,S.Subcategory
            ,S.Product    
            ,S.TA_year    
            ,S.TA_quarter 
            ,S.TA_month   
            ,S.TA_weekday 
            ,S.sumQty     
            ,S.avgCost    
        )
    output
         S.Grpid
        ,S.HashVal
        ,$Action
        ,inserted.sumQty
        ,inserted.avgCost
        ,deleted.sumQty
        ,deleted.avgCost
) O (Grpid, HashVal, Action, I_sumQty, I_avgCost, D_sumQty, D_avgCost)
where
    I_sumQty    < isnull(D_sumQty, 0)  + 10 or
    I_avgCost   > isnull(D_avgCost, 0) + 10 or 
    Action      = 'INSERT';

end try

begin catch

    return error_number();

end catch

return 0;
go

Nehmen wir hier einmal an, dass wir nur dann Logeinträge wünschen, wenn sich die Aggregierungen um einen angegebenen Wert geändert haben (+10) oder neue Gruppierungen hinzugefügt wurden. Einfach haben wir es uns mit der Fehlerbehandlung gemacht, die die Arbeit dem Aufrufer aufhalst.

Nun noch die Arbeit für unsere Prozedur erzeugen, an die Prozedur übergeben und schauen was herauskommt. Zuerst müssen wir natürlich wieder Testdaten erzeugen. Wer kein Problem damit hat, die AdventureWorks zu verändern kann die Transaktion auch weglassen. Das hier Nützliche am TABLE TYPE ist, dass er wie auch Variablen vom Typ Table von der Transaktion unbeinflusst bleibt und somit das ROLLBACK nur die Änderung der Daten in der AdventureWorks zurückrollt.

begin tran

insert into Production.TransactionHistoryArchive 
select
     TransactionID + 100000
    ,ProductID
    ,ReferenceOrderID
    ,ReferenceOrderLineID
    ,dateadd(qq, 1, TransactionDate) TransactionDate
    ,TransactionType
    ,Quantity + 2
    ,ActualCost
    ,ModifiedDate 
from Production.TransactionHistoryArchive T 
where 
    datepart(yy ,T.TransactionDate) = 2003 and
    datepart(qq ,T.TransactionDate) = 3

update Production.TransactionHistoryArchive set 
    ActualCost = ActualCost + 10
where 
    datepart(yy ,TransactionDate) = 2003 and
    datepart(qq ,TransactionDate) = 3
    
declare @grpset as dbo.grpset;


insert into @grpset
select
     grouping_id(TA_weekday, TA_month, TA_quarter, TA_year, Product, Subcategory, Category) Grpid
    ,cast(hashbytes('MD5',
         isnull(cast(TA_weekday as varchar), '')    +
         isnull(cast(TA_month as varchar), '')      +
         isnull(cast(TA_quarter as varchar), '')    +
         isnull(cast(TA_year as varchar), '')       + 
         isnull(Product, '')                        +
         isnull(Subcategory, '')                    +
         isnull(Category, '')) as varbinary(100)) HashVal
    ,Category
    ,Subcategory
    ,Product
    ,TA_year
    ,TA_quarter
    ,TA_month
    ,TA_weekday
    ,sum(Qty)        sumQty
    ,avg(Cost)       avgCost
from Production.Prod_data
group by
    rollup(Category, Subcategory, Product),
    rollup(TA_year, TA_Quarter, TA_month, TA_weekday);

rollback

Hiermit wurde das 3. Quartal 2003 geändert und das 4. Quartal 2003 hinzugefügt.
Der Aufruf der Prozedur der mit obigem Code zusammenerfolgen muss, da sonst @grpset nicht mehr im Scope ist, folgt hier:

declare @r int;
exec @r = Production.Refresh @grpset;
if @r <> 0 select 'Error:', @r;

Dann schauen wir uns mal an, was alles aktualisiert wurde:

select 
     L.Action
    ,M.Category
    ,M.Subcategory
    ,M.Product
    ,M.TA_year
    ,M.TA_quarter
    ,M.TA_month
    ,M.TA_weekday
    ,L.I_sumQty
    ,L.I_avgCost
    ,L.D_sumQty
    ,L.D_avgCost
from Production.MrgAlertLog L
inner join Production.MatProd_data M
on
    L.Grpid     = M.Grpid   and
    L.HashVal   = M.HashVal
where
     Action = 'UPDATE'

Zum Schluss wieder aufräumen und falls durch Fehlerfall eine Transaktion noch offen sein sollte, ein Rollback machen:

if object_id('Production.Refresh', 'P') is not NULL drop proc Production.Refresh;
go

if object_id('Production.MatProd_data', 'U') is not NULL drop table Production.MatProd_data;
go

if object_id('Production.MrgAlertLog', 'U') is not NULL drop table Production.MrgAlertLog;
go

if object_id('Production.Prod_data', 'V') is not NULL drop view Production.Prod_data;
go

if 
    not exists(select * from sys.dm_sql_referencing_entities('dbo.grpset', 'type'))
    and exists(select * from sys.types where name = 'grpset' and is_table_type = 1)

drop type dbo.grpset;
go

if @@trancount > 0 rollback

Wie bereits geschrieben: dieses Beispiel soll nur als Denkanstoss oder Anreiz dienen, sich mit den neuen Features von T-SQL auseinander zu setzen. Ob dies als Lösung Sinn macht, ermisst sich aus den Anforderungen und für die Anforderung eines Beispiels mag ich die Lösung ;-).

MERGE

MERGE geisterte unter dem Begriff „Upsert“, eine Wortschöpfung aus Update und Insert, durch die SQL Server Community. MERGE ist aber mehr als eine Kombination aus INSERT und UPDATE. MERGE ist quasi eine Super DML Klausel, die INSERT, UPDATE und DELETE mit bedingter Ausführung in sich vereint.

Ganze Geschichte »

INSERT over DML

INSERT over DML ist eine Vereinfachung im Gebrauch der in SQL Server 2005 eingeführten OUTPUT Klausel, die es zusätzlich erlaubt Daten zu filtern.

Zur Erinnerung: Die OUTPUT Klausel gibt die einem DML Statement (UPDATE, INSERT, DELETE, MERGE) Einträge der systemeigenen Tabellen inserted und deleted zurück (Besonderheiten bei MERGE im nächsten Artikel). Diese können direkt an den Datenkonsumenten zurückgegeben werden oder in eine Variable vom Typ Table geschrieben werden, um dort weiterverarbeitet zu werden, beispielsweise um Logs zu schreiben.

INSERT over DML kürzt den letzteren Weg ab. Statt die Daten in eine Variable vom Typ Table zu schreiben und von dort aus in eine Logging Tabelle, kann mittels INSERT over DML direkt in die Logging Tabelle geschrieben werden. Zusätzlich können die Daten mit WHERE gefiltert werden. Dies ist eine atomare Operation.

Zuerst einmal Testdaten erzeugen

use tempdb

create table dbo.products
(
    id      int,
    price   money
)

create table dbo.prodlog
(
    id      int,
    diff    money,
    
)

declare @prodlog table
(
    id      int,
    diff    money
)

insert into dbo.products values
(1, 1),
(2, 1.23),
(3, 2.05),
(4, 1.99),
(5, .5)

--Die Transaktion dient dazu, die Daten wieder im Ursprungszustand wiederherzustellen
begin tran

--SQL Server 2005 Vorgehensweise
update dbo.products set
    price *= 1.25 
output
     inserted.id id
    ,inserted.price - deleted.price diff
into @prodlog

insert into dbo.prodlog
select 
     id
    ,diff
from @prodlog
where
    diff > .5

select * from dbo.prodlog

rollback

--SQL Server 2008 Vorgehensweise
insert into dbo.prodlog
select
      id
     ,diff
from
(
    update dbo.products set
        price *= 1.25 
    output
         inserted.id id
        ,inserted.price - deleted.price diff
) U
where
    diff > .5

 
select * from dbo.prodlog

--Aufräumen
drop table 
     dbo.products
    ,dbo.prodlog

Hieraus lassen sich gedanklich die interessantesten Anwendungsmöglichkeiten konstruieren, aber leider erlauben die Einschränkungen, außer den Anwendungsfälle des Loggings/Auditing/Change Managements sowie die vereinfachte Bearbeitung von „Slowly changing dimensions“ im Zusammenhang mit MERGE als BI Thema, eigentlich nichts. (Da diese Einschränkungen sehr umfangreich sind, bitte in den BOL nachschlagen).

Der nächsten Artikel handelt von MERGE.