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 ;-).