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.

GROUPING SETS

Wer viel mit Auswertungen zu tun hat und GROUP BY sowie ROLLUP und CUBE bis hin zur Verzweiflung benutzen muss, der hat mit der bei SQL Server 2008 eingeführten Klausel GROUPING SETS sicherlich seine Freude.

Wer beispielsweise solche Auswertungen schreiben muss

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
     Anstellungsjahr
    ,Geschlecht
    ,Region
union all
select
     NULL
    ,Geschlecht
    ,Region
    ,sum(Verkauf)
from dbo.vVerk
group by
     Geschlecht
    ,Region
union all
select
     Anstellungsjahr
    ,NULL
    ,Region
    ,sum(Verkauf)
from dbo.vVerk
group by
     Anstellungsjahr
    ,Region
union all
select
     NULL
    ,NULL
    ,NULL
    ,sum(Verkauf)
from dbo.vVerk;

wird sich freuen, sein Augenmerk auf die Logik, statt auf mühselige Tipparbeit und fehleranfälliges Copy&Paste legen zu können. Mittels GROUPING SETS wird es deutlich übersichtlicher.

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Anstellungsjahr, Region)
        ,(Geschlecht, Region)
        ,()
    );

Auffällig an dieser Stelle ist das Paar leere Klammern. Dieses fasst innerhalb eines GROUPING SETS alle Gruppen zu einer Gruppe zusammen („Grand Total“).

In obigem Beispiel entspricht

()

folgendem Statement aus dem ersten Beispiel

select
     NULL
    ,NULL
    ,NULL
    ,sum(Verkauf)
from dbo.vVerk;

Also eine feine Sache. Mittels ROLLUP und CUBE kann man sich noch mehr Tipparbeit sparen.

ROLLUP

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    rollup(Anstellungsjahr, Geschlecht, Region);

entspricht

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Anstellungsjahr, Geschlecht)
        ,(Anstellungsjahr)
        ,()
    );

CUBE

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    cube(Anstellungsjahr, Geschlecht, Region);

entspricht

select
     Anstellungsjahr
    ,Geschlecht
    ,Region
    ,sum(Verkauf) Summe
from dbo.vVerk
group by
    grouping sets
    (
         (Anstellungsjahr, Geschlecht, Region)
        ,(Geschlecht, Region)
        ,(Region)
        ,(Anstellungsjahr, Region)
        ,(Anstellungsjahr)
        ,(Anstellungsjahr, Geschlecht)
        ,(Geschlecht)
        ,()
    ) ;

Natürlich gilt bei GROUPING SETS auch, dass jede Spalte in der SELECT Liste Argument einer Aggregatfunktion oder in der GROUP BY Klausel enthalten sein muss. Die Anordnung der Spalten innerhalb eines GROUPING SETS oder die Anordnung von mehreren GROUPING SETS (dazu später mehr) ist unerheblich.

Bitte dringend beachten, das die Verwendung von CUBE, ROLLUP und ALL gemäß früherer SQL Server Versionen abgekündigt ist.

Vorteil der GROUPING SETS ist nicht nur die vereinfachte Schreibeweise von komplexen Gruppierungen, sondern auch ein Geschwindigkeitsaspekt. Bei der früheren Schreibweise mit UNION ALL wurde jeweils auf die Tabelle(n) zugegriffen, bei GROUPING SETS kann der Optimizer hierfür auch Table Spools erzeugen, wenn dies kostengünstiger ist.

Besonders interessant ist, dass GROUPING SETS miteinander kombiniert werden können.
Da ROLLUP und CUBE nichts anderes als Abkürzungen für GROUPING SETS sind, kann man diese somit auch kombinieren.
Aber dem Ganzen sind natürlich Grenzen gesetzt: Maximale Anzahl an Gruppierungen ist 4096. Das entspricht beispielsweise GROUP BY CUBE mit 12 Ausdrücken. Maximal 32 eindeutige Ausdrücke in GROUPING SETS sind erlaubt.

Um das Ganze durchschaubarer und übersichtlicher darzustellen, erzeugen wir uns einfach virtuelle Daten (die folgenden Beispiele sind ohne Weiteres in jeder Datenbank lauffähig):

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows;

Als Ergebnis erhalten wir eine Tabelle mit 8 Spalten und einen Satz


1

2

3

4

5

6

7

8

1

2

3

4

5

6

7

8

Nehmen wir nun 2 GROUPING SETS hinzu

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
    grouping sets
    (
         ([1], [2])
        ,([3], [4])
    ),
    grouping sets
    (
         ([5], [6])
        ,([7], [8])
    );

1

2

3

4

5

6

7

8

NULL

NULL

3

4

NULL

NULL

7

8

1

2

NULL

NULL

NULL

NULL

7

8

NULL

NULL

3

4

5

6

NULL

NULL

1

2

NULL

NULL

5

6

NULL

NULL

Wir sehen hier, dass aus den beiden GROUPING SETS ein Kartesisches Produkt gebildet wurde:

(3,4) und (7,8),
(1,2) und (7,8),
(3,4) und (5,6),
(1,2) und (5,6)

Mit diesem Beispiel kann man auch sehr gut sehen, was () in einem GROUPING SET bewirkt

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
    grouping sets
    (
         ([1], [2])
        ,([3], [4])
        ,()
    ),
    grouping sets
    (
         ([5], [6])
        ,([7], [8])
    );

folgendes Ergebnis


1

2

3

4

5

6

7

8

NULL

NULL

3

4

NULL

NULL

7

8

NULL

NULL

NULL

NULL

NULL

NULL

7

8

1

2

NULL

NULL

NULL

NULL

7

8

NULL

NULL

3

4

5

6

NULL

NULL

NULL

NULL

NULL

NULL

5

6

NULL

NULL

1

2

NULL

NULL

5

6

NULL

NULL

Die fett markierten Zellen sind das Ergebnis des leeren Klammerpaares.

Also

(3,4) und (7,8),
() und (7,8),
(1,2) und (7,8),
(3,4) und (5,6),
() und (5,6),
(1,2) und (5,6)

Da es sich bei ROLLUP und CUBE wie bereits erwähnt um Abkürzungen von GROUPING SETS handelt, lassen sich diese natürlich auch beliebig mit anderen GROUPING SETS kombinieren:

with tstrows as
(
    select 
		[1], [2], [3], [4], [5], [6], [7], [8]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [3], [4], [5], [6], [7], [8])
)
select
    [1], [2], [3], [4], [5], [6], [7], [8]
from tstrows
group by
     rollup([1], [2], [3], [4], [5])
    ,cube([6], [7], [8]);

Das Ergebnis ist etwas größer


1

2

3

4

5

6

7

8

1

2

3

4

5

6

7

8

1

2

3

4

NULL

6

7

8

1

2

3

NULL

NULL

6

7

8

1

2

NULL

NULL

NULL

6

7

8

1

NULL

NULL

NULL

NULL

6

7

8

NULL

NULL

NULL

NULL

NULL

6

7

8

NULL

NULL

NULL

NULL

NULL

NULL

7

8

NULL

NULL

NULL

NULL

NULL

NULL

NULL

8

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

1

2

3

4

5

NULL

7

8

1

2

3

4

NULL

NULL

7

8

1

2

3

NULL

NULL

NULL

7

8

1

2

NULL

NULL

NULL

NULL

7

8

1

NULL

NULL

NULL

NULL

NULL

7

8

1

NULL

NULL

NULL

NULL

NULL

7

NULL

NULL

NULL

NULL

NULL

NULL

NULL

7

NULL

1

2

3

4

5

6

NULL

8

1

2

3

4

NULL

6

NULL

8

1

2

3

NULL

NULL

6

NULL

8

1

2

NULL

NULL

NULL

6

NULL

8

1

NULL

NULL

NULL

NULL

6

NULL

8

NULL

NULL

NULL

NULL

NULL

6

NULL

8

NULL

NULL

NULL

NULL

NULL

6

NULL

NULL

1

2

3

4

5

NULL

NULL

8

1

2

3

4

NULL

NULL

NULL

8

1

2

3

NULL

NULL

NULL

NULL

8

1

2

NULL

NULL

NULL

NULL

NULL

8

1

NULL

NULL

NULL

NULL

NULL

NULL

8

1

NULL

NULL

NULL

NULL

NULL

NULL

NULL

1

2

3

4

5

6

7

NULL

1

2

3

4

NULL

6

7

NULL

1

2

3

NULL

NULL

6

7

NULL

1

2

NULL

NULL

NULL

6

7

NULL

1

NULL

NULL

NULL

NULL

6

7

NULL

NULL

NULL

NULL

NULL

NULL

6

7

NULL

1

2

3

4

5

NULL

7

NULL

1

2

3

4

NULL

NULL

7

NULL

1

2

3

NULL

NULL

NULL

7

NULL

1

2

NULL

NULL

NULL

NULL

7

NULL

1

2

NULL

NULL

NULL

NULL

NULL

NULL

1

2

3

4

5

6

NULL

NULL

1

2

3

4

NULL

6

NULL

NULL

1

2

3

NULL

NULL

6

NULL

NULL

1

2

NULL

NULL

NULL

6

NULL

NULL

1

NULL

NULL

NULL

NULL

6

NULL

NULL

1

2

3

4

5

NULL

NULL

NULL

1

2

3

4

NULL

NULL

NULL

NULL

1

2

3

NULL

NULL

NULL

NULL

NULL

Solche Ergebnismengen bieten sich an, in einer Tabelle gespeichert zu werden. Um schnelle Abfragen auf bestimmte Gruppierungen zu ermöglichen, ist ein clustered Index auf einer Spalte, welche eine Gruppierung identifiziert, ideal. Um dieses Merkmal zu erstellen, kann die Funktion GROUPING_ID() verwendet werden.

GROUPING_ID() gibt eine Bitmap zurück, bei der für jedes übergebene Argument per nicht gesetztem Bit (0) angezeigt wird, ob das Argument Teil der Gruppierung ist.

Achtung, GROUPING_ID() ist insofern fehleranfällig, dass nicht alle gruppierten Spalten als Argument übergeben werden müssen und dass diese zudem mehrfach vorkommen dürfen. Copy&Paste kann hier also schnell zu einem spät zu erkennenden Fehler führen.

Am besten übergibt man die Argumente an die GROUPING_ID() Funktion in umgekehrter Reihenfolge der Spaltenordnung, um die zu den Spalten korrespondierenden Bits der Bitmap einfach identifizieren zu können (erste Spalte, niedrigstes Bit).

Hierfür ein Beispiel, bei dem ich zur verständlicheren Darstellung die 2er Potenz des entsprechenden Bits als Spaltenkopf gewählt habe (zum testen TOP Klausel entfernen, damit alle 256 Zeilen dargestellt werden):

with tstrows as
(
    select [1], [2], [4], [8], [16], [32], [64], [128]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8')
    ) E ([1], [2], [4], [8], [16], [32], [64], [128])
)
select top(16)
     grouping_id([128], [64], [32], [16], [8], [4], [2], [1]) grpid
    ,[1], [2], [4], [8], [16], [32], [64], [128]
from tstrows
group by
    cube([1], [2], [4], [8], [16], [32], [64], [128])
order by
    grpid;

grpid

1

2

4

8

16

32

64

128

0

1

2

3

4

5

6

7

8

1

NULL

2

3

4

5

6

7

8

2

1

NULL

3

4

5

6

7

8

3

NULL

NULL

3

4

5

6

7

8

4

1

2

NULL

4

5

6

7

8

5

NULL

2

NULL

4

5

6

7

8

6

1

NULL

NULL

4

5

6

7

8

7

NULL

NULL

NULL

4

5

6

7

8

8

1

2

3

NULL

5

6

7

8

9

NULL

2

3

NULL

5

6

7

8

10

1

NULL

3

NULL

5

6

7

8

11

NULL

NULL

3

NULL

5

6

7

8

12

1

2

NULL

NULL

5

6

7

8

13

NULL

2

NULL

NULL

5

6

7

8

14

1

NULL

NULL

NULL

5

6

7

8

15

NULL

NULL

NULL

NULL

5

6

7

8

Da SQL Server 2008 mehrfach vorkommende Gruppierungen zulässt, kann man dieses Verfahren auch dazu verwenden, mehrfach vorkommende Gruppierungen bei komplexen GROUPING SETS zu identifizieren. Bei 4096 möglichen Gruppierungen kann man sich ja mal vertun…

with tstrows as
(
    select 
		[1], [2], [4], [8], [16], [32], [64], [128], 
		[256], [512], [1024], [2048], [4096]
    from
    (
        values
        ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13')
    ) E ([1], [2], [4], [8], [16], [32], [64], [128], [256], [512], [1024], [2048], [4096])
)
, grps as
(
    select
         grouping_id([4096], [2048], [1024], [512], [256], [128], [64], [32], [16], [8], [4], [2], [1]) grpid
        ,[1], [2], [4], [8], [16], [32], [64], [128], [256], [512], [1024], [2048], [4096]
    from tstrows
    group by
        grouping sets
        (
            ([256], [512], [1024], [2048]),
            ([256], [512], [2048]),
            ([256], [2048], [4096]),
            ()
        ),
        rollup([1], [2], [4], [8], [16], [32]),
        cube([32], [64], [128], [2048])
)
select
    count(*) NbrOfSameGroups
   ,grpid
   ,cast(sign(grpid & 1)    as char(1)) +
    cast(sign(grpid & 2)    as char(1)) +
    cast(sign(grpid & 4)    as char(1)) +
    cast(sign(grpid & 8)    as char(1)) +
    cast(sign(grpid & 16)   as char(1)) +
    cast(sign(grpid & 32)   as char(1)) +
    cast(sign(grpid & 64)   as char(1)) +
    cast(sign(grpid & 128)  as char(1)) +
    cast(sign(grpid & 256)  as char(1)) +
    cast(sign(grpid & 512)  as char(1)) +
    cast(sign(grpid & 1024) as char(1)) +
    cast(sign(grpid & 2048) as char(1)) +
    cast(sign(grpid & 4096) as char(1)) bitvektor
from grps
group by
    grpid
having
    count(*) > 1

Die Möglichkeit, eine HAVING Klausel zu verwenden um bestimmte Gruppierungen mittels GROUPING_ID() herauszufiltern, ist kontraproduktiv: Entweder bildet man die Gruppierungen direkt so, wie sie benötigt werden, oder falls diese in einer Tabelle persistiert wurden, wird über den clustered Index auf dem durch die GROUPING_ID() Funktion erzeugten Wert auf die Gruppierungen zugegriffen.

Ein Weg mit dem man eine solche Tabelle aktuell halten und mit neuen Gruppierungen befüllen kann, ist Beispiel des letzten Artikels.

Table Valued Parameters

Wer sich jemals mit der Problematik beschäftigen musste, wie man ein Array bzw. Recordset an eine Stored Procedure (SP) oder Function (F) übergeben kann, wird hoffentlich bei Erland Sommarskog http://www.sommarskog.se/arrays-in-sql.html vorbeigeschaut und Lösung gefunden haben. Erland hat natürlich auch über TVP geschrieben wie über vieles andere auch, so dass ein Besuch seiner Seite mehr als empfehlenswert ist.

Vor einem Besuch besteht aber natürlich auch die Möglichkeit, diesen Artikel weiterzulesen…

Es ist keine große Angelegenheit, mit TVP zu arbeiten:

  • Einen Type vom Type Table erzeugen,
  • einer Variable mit diesem Type deklarieren,
  • diese Variable mit Daten befüllen (alle DML Befehle)
  • an eine SP oder F übergeben, die als INPUT-Parameter Definition den vorher definierten Table Type enthält

Hier wird der Table Type erzeugt (Beispiel lauffähig)

create type dbo.tabletype_param as table
(
ID int
,Product nvarchar(100)
,BBD date default(current_timestamp) not NULL
);

die Variable deklariert

declare @param dbo.tabletype_param;

ein paar Daten eingefügt, geändert und gelöscht

insert into @param(ID, Product) values
(1, 'Corn'), (2, 'Meat'), (3, 'Beef'), (4, 'Butter'), (5, 'Olive Oil');
update @param set 
BBD = dateadd(YY, 1, BBD);
delete @param
where
ID = 5;

und mal nachgeschaut, ob auch alles wie erwartet vorhanden ist

select ID, Product, BBD
from @param;

Die Übergabe an eine Stored Procedure (SP) / Function (F) ist ähnlich trivial, jedoch lassen sich hier Features vermissen, die man eigentlich erwartet hätte.

SP

create proc dbo.proc_Test
@Param dbo.tabletype_param readonly
as
...< code >;

F

create function dbo.func_Test
(
@Param_in dbo.tabletype_param readonly
)
returns @Param_out table
(
ID int
,Product nvarchar(100)
,BBD date default(current_timestamp) not NULL
)
as
begin
...< code >
end;

Der TVP ist readonly (muss mit diesem Schlüsselwort definiert werden) und der Table Type kann nicht als Definition der aus der Table Valued Function zurückgegebenen Variable von Typ Table verwendet werden. Der Begriff „halbherzig“ umgesetzt trifft hier meiner Meinung nach sehr gut…

So wird man bei Bedarf den Typen wieder los (Beispiel lauffähig)

if exists
(select * from sys.types where name = 'tabletype_param' and is_table_type = 1)
drop type dbo.tabletype_param;

Als Mengengerüste für die Verwendung von TVP empfiehlt Microsoft als Faustregel:

  • Bulk Insert für formatierte Daten auf dem Server mit Ausnahme weniger als 1000 Datensätze oder komplexer Weiterverarbeitung.
  • Weniger als 1000 Datensätze von einem Remote Client mit TVP.
    (siehe hierzu auch tabellarische Aufstellung BOL)

Jetzt geht‘s ein bisschen ans Eingemachte, man sollte sich mit dem Thema Optimierung schon mal beschäftigt haben…

Testen ist besser als jede Faustregel, so dass man durchaus auch mit mehreren Millionen Datensätzen experimentieren kann. Dies liegt daran, dass - im Gegensatz zu einer Variablen vom Typ Table - der Optimizer nicht mit der geschätzten Anzahl von 1 Datensatz, sondern beim TVP nach der Anzahl der enthaltenen Datensätze optimiert, aber nicht nach der Verteilung der Daten. Dies ist dadurch möglich, das der Optimizer bei der erstmaligen Ausführung der SP / F den TVP „sniffen“ kann, daher gilt dies nicht für eine Variable deklariert mit Table Type/Table.

Voraussetzung für eine schnelle Weiterverarbeitung ist ein unique clustered Index oder clustered Primary Key des Table Types, auf den in der weiteren Verarbeitung zugegriffen wird.

create type dbo.tabletype_param as table
(
ID int primary key clustered not NULL
,Product nvarchar(100) NULL
,BBD date default(current_timestamp) not NULL
);

Vorsicht ist jedoch geboten, wenn man versucht die Ausführung durch weitere Indizes - bei Variablen vom Typ Table und Table Types umgesetzt durch Unique Constraints - zu optimieren:

create type dbo.tabletype_param as table
(
ID int not NULL
,Product nvarchar(100) NULL
,BBD date default(current_timestamp) not NULL
,primary key clustered(ID)
,unique(Product, ID)
,unique(BBD, ID)
);

Da der Optimizer den TVP statistisch nur nach Anzahl der enthaltenden Datensätze betrachtet, nicht aber nach der Verteilung der Daten, wird ein Zugriff auf solche Indizes mit einem Wert, der oft im Index vorkommt, alles andere als optimal sein.

Immer mit set statistics io on und set statistics times on die Anzahl der Reads und insbesondere die Prozessorzeit prüfen und Bedenken, in welcher Verteilung die Daten im TVP bei mehrmaliger Verwendung enthalten sein werden. Mögliche Probleme des „Parameter Sniffings„ gelten auch hier!

Im geringsten Zweifel lieber auf solche „Optimierungen“ verzichten.

Weiter geht es im nächsten Artikel mit GROUPING SETS.

1 2 3