Kronester

SQL Injection – Schnelltest

admin — Tue, 15 Dec 2009 11:11:38 +0000

Um einen evtl. gefährlichen String zu testen, habe ich nachfolgend einen Schnelltest in Form einer Scalar-Funktion entwickelt.

Diese Funktion übernimmt einen potenziell gefährlichen String und prüft ihn auf die wichtigsten Merkmale.

Es dürfen keine String-Hochkommas vorkommen die nicht paarig sind.
Desweiteren sind DDL-Kommandowörter als potzenziell gefährlich einzustufen.

Wenn ein “Problem” gefunden wurde, wird eine Fehlermeldung ausgegeben. Im anderen Fall wird der Originalstring als Rückgabewert präsentiert.

Natürlich erhebt diese Funktion keinen Anspruch auf Vollständigkeit, jedoch ist sie gerade bei der Entwicklung generischer (metadatengesteuerter) Prozeduren für den schnellen Test gerade dieser Metatabellen sehr nützlich.

Wenn noch jemand eine Ergänzung hierfür einfällt … bitte einfach einen kurzen Kommentar posten. Damit bekommen auch andere Besucher dieses Blogs diese Infos mit.

CREATE FUNCTION dbo.SQLInjection_Quicktest(@Wert VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
	IF (LEN(@Wert)-LEN(REPLACE(@Wert,'''',''))) % 2 <> 0
	BEGIN
		SET @Wert = ''
		SET @Wert = '** ERROR - String-Delimiter nicht paarig! **'
	END
 
	SET @Wert = LOWER(@Wert)
 
	IF  @Wert Like '%[^a-z0-9_]delete[^a-z0-9_]%'       or
		@Wert Like '%[^a-z0-9_]update[^a-z0-9_]%'   or
		@Wert Like '%[^a-z0-9_]truncate[^a-z0-9_]%' or
		@Wert Like '%[^a-z0-9_]drop[^a-z0-9_]%'     or
		@Wert Like '%[^a-z0-9_]exec[^a-z0-9_]%'     or
		@Wert Like '%[^a-z0-9_]execute[^a-z0-9_]%'  or
		@Wert Like '%[^a-z0-9_]create[^a-z0-9_]%'   or
 
		@Wert Like '%delete[^a-z0-9_]%'             or
		@Wert Like '%update[^a-z0-9_]%'             or
		@Wert Like '%truncate[^a-z0-9_]%'           or
		@Wert Like '%drop[^a-z0-9_]%'               or
		@Wert Like '%exec[^a-z0-9_]%'               or
		@Wert Like '%execute[^a-z0-9_]%'            or
		@Wert Like '%create[^a-z0-9_]%'
	BEGIN
		SET @Wert = ''
		SET @Wert = '** ERROR - Unzulässiges Schlüsselwort! **'
	END
 
	RETURN @Wert
END

have fun

TSQL Fehler bei Case (simple Version)

admin — Thu, 03 Dec 2009 09:35:03 +0000

Die Case-Anweisung von T-SQL verhält sich sehr interessant (fehlerhaft?) bei Ausdrücken die in der einfachen Version des Statements in der Case-Expression angegeben werden.

Die einfache Syntax siehe wie folgt aus:

CASE input_expression
     WHEN when_expression THEN result_expression
    [ ...n ]
     [
    ELSE else_result_expression
     ]
END

Um das Problem zu demonstrieren, möchte ich einmal das folgende Statement zur Diskussion stellen:

DECLARE @i INT
 
SET @i = 0
 
WHILE @i<10
BEGIN
	PRINT
	CASE CONVERT(INT,RAND()*5+1)
		WHEN 1 THEN 10
		WHEN 2 THEN 20
		WHEN 3 THEN 30
		WHEN 4 THEN 40
		WHEN 5 THEN 50
		WHEN 6 THEN 60
		ELSE 0
	END
	SET @i = @i + 1
END

Das CASE-Statement verwendet die Random-Funktion RAND().

Diese Funktion liefert Werte zwischen 0 und 1 zurück. Multipliziert man nun den Rückgabewert mit 5 sollten also bis dahin Zahlen zwischen 0 und 5 zurückgeliefert werden. Um 0-Werte auszuschließen wird abschließend noch 1 dazu addiert und der endgültige Wertebereich liegt zwischen 1 und 6.

Soweit so gut.

Lässt man jedoch die oben beschriebene Schleife laufen, erhält man als Ergebnis aus dem CASE-Austruck auch den ELSE-Wert 0 zurückgeliefert. Dies darf jedoch aufgrund der Berechnung eigentlich gar nicht geschehen!

Das Problem liegt hier in der Art wie die CASE-Funktion die input_expression behandelt. Anscheinend wird der Ausdruck trotz CAST/CONVERT nicht korrekt in seinem Datentyp interpretiert und liefert als Ergebnis wahrscheinlich einen FLOAT-Wert zurück.

(Zur Behandlung von Typkonvertierungen siehe “T-SQL CASE und die implizite Datentypkonvertierung”)

Dieser Wert ist durch die Random-Funktion allerdings nicht im Einzelfall zu bestimmen und kann, wenn überhaupt, nur durch aufwändige Tests ermittelt werden.

Als Regel können wir hier also festhalten:

Wenn eine CASE-Inputexpression ein nicht deterministisches Ergebnis liefert, ist das Verhalten der Funktion auf das genaueste zu testen.

Als Workaround muss man dafür sorgen, dass der Wert für die Inputexpression nicht im CASE berechnet wird, sondern ggf. vorher. Sei es in einer CTE, wenn man den Wert innerhalb einer Abfrage benötigt, oder vorher in einer Variablen.

Ein Beispiel für die Version mit der Variablen findet man nachfolgend:

DECLARE @i INT
DECLARE @w INT
 
SET @i = 0
 
WHILE @i<10
BEGIN
            SET @w = CONVERT(INT,RAND()*5+1)
            PRINT
            CASE @w
                 WHEN 1 THEN 10
                 WHEN 2 THEN 20
                 WHEN 3 THEN 30
                 WHEN 4 THEN 40
                 WHEN 5 THEN 50
                 WHEN 6 THEN 60
                 ELSE 0
            END
            SET @i = @i + 1
END

Wenn jemand einen anderen Weg, oder eine Erklärung für dieses absonderliche Verhalten hat, ist er/sie aufgefordert dies in den Kommentaren der interessierten Öffentlichkeit mitzuteilen.

SQL Server: Index Selektivität und UnUsed Indizes

admin — Wed, 11 Nov 2009 16:29:29 +0000

In einer Datenbank können sich über die Zeit sehr viele Tabellen, und damit verbunden auch sehr viele Indizes ansammeln. Möchte man nun wissen welche dieser Indizes noch in Gebrauch sind, kann man dies mittels der Abfrage , wie sie im Beispiel unten gezeigt wird, ermitteln.

In der Dynamic-Management-View sys.dm_db_index_usage_stats sammelt SQL Server Statistikdaten über die Verwendung eines jeden Indexes der auf der SQL Server Instanz angesprochen wird.

Dies ist dann auch der Grund warum man die Datenbank im Beispiel (s.u.) explizit im CTE-Block der Abfrage angeben muss (@Database).

Die Statistik-Daten werden mit jedem Neustart des MSSQL-Service zurückgesetzt und zeigen daher nur die seit dem letzten Start aufgelaufenen Statistiken an. Vorsicht also mit voreiligen Schlüssen was das Löschen von diesen “unnützen” Indizes angeht!

Grundsätzlich kann man sich jedoch mal eine Liste ausgeben lassen und die enthaltenen Indizes einer genaueren Prüfung unterziehen.

Selektivität oder warum wird mein Index in einer Abfrage nicht verwendet?

Unter Selektivität eines Index versteht man die Anzahl von Datensätzen die von einem Index-Eintrag angesprochen werden. Je mehr Datensätze über einen Indexschlüssel selektiert würden, je schlechter ist die Selektivität. Der Optimierer von SQL Server bewertet, neben einigen anderen Kriterien, auch die Selektivität eines Index bei der Entscheidung ob dieser Index für eine Abfrage in Betracht gezogen werden muss. Hintergrund ist die triviale Tatsache dass ein Index im Verhältnis zum Scan der gesamten Tabelle ggf. mehr I/O-Aufkommen produzieren würde als der direkte Zugriff auf die Tabelle. Wenn dies der Fall ist, ist ein Table-Scan oft schneller (weil nicht andauernd zwischen Index und Tabelle hin und her gesprungen werden muss um selektierte Daten zu erhalten) als der Zugriff über einen Index.

Grundsätzlich kann man sich die Faustregel, wie im Beispiel dargestellt, zu Herzen nehmen. Da die Selektivität nicht das einzige Kriterium bei der Optimierung ist, kann es durchaus sein das ein Index verwendet wird obwohl die Selektivität schlecht ist.

z.B. wenn der Index die Abfrage komplett abdeckt ist dies meist am schnellsten (schmales Dataset) über den Scan des gesamten Index zu machen.

Also wie immer: den Einzelfall prüfen!

USE AdventureWorks
go
 
DECLARE @DATABASE sysname
 
SET @DATABASE = 'AdventureWorks'
 
-- ----------------------------------------------------------------
-- unbenutzte Indizes feststellen
-- ----------------------------------------------------------------
 
;with NotUsed AS
(
    SELECT OBJECT_ID,index_id
    FROM sys.indexes
    EXCEPT
    SELECT s.OBJECT_ID,s.index_id
    FROM sys.dm_db_index_usage_stats s
    WHERE database_id=DB_ID(@DATABASE)
)
SELECT s.name AS SchemaName
     , o.name AS TabellenName
     , i.name AS IndexName
FROM sys.objects o
    join sys.schemas s ON  s.schema_id = o.schema_id
    join NotUsed n     ON  o.OBJECT_ID = n.OBJECT_ID
    join sys.indexes i ON  i.index_id  = n.index_id
                       and i.OBJECT_ID = n.OBJECT_ID
WHERE o.type = 'U'
 
-- ----------------------------------------------------------------
-- Faustregel
-- ----------------------------------------------------------------
 
-- Selektivität eines Index feststellen (excl. Covering-Index)
 
-- 0 bis 0.05     = Hohe Selektivität 
--                  (Indexverwendung wahrscheinlich)
 
-- 0.05 bis 0.10  = Mittlere Selektivität 
--                  (IndexVerwendung fraglich)
 
-- 0,10 und höher = Schlechte Selektivität 
--                  (Indexverwendung unwahrscheinlich)
 
 
-- ----------------------------------------------------------------
-- Beispiel: Schlechter selektiver Index
-- ----------------------------------------------------------------
 
-- select EmailPromotion,count(*) 
-- from Person.Contact 
-- group by EmailPromotion 
-- order by count(*)
 
CREATE INDEX IX_Non_Selective ON Person.Contact (EmailPromotion)
 
DBCC SHOW_STATISTICS ('Person.Contact','IX_Non_Selective')
     WITH NO_INFOMSGS , DENSITY_VECTOR 
 
SELECT * FROM Person.Contact WHERE EmailPromotion = 2
 
DROP INDEX IX_Non_Selective ON Person.Contact 
 
-- ----------------------------------------------------------------

have fun

SET OPTIONs abfragen und SSIS-Probleme vermeiden

admin — Thu, 22 Oct 2009 08:14:34 +0000

Im vorliegenden Fall hatte ich ein Problem mit einem SSIS-Paket dass mit einer Fehlermeldung abgebrochen wurde. Die Fehlermeldung besagte, dass es eine Warnung beim Aufruf der Datenquelle (View) gegeben hätte.

Diese Warnung sollte nach meiner Meinung auch als Warnung und nicht als Fehler behandelt werden. Das Problem war leicht zu beheben, ich musste nur ein Set-Kommando vor der View platzieren dass die ANSI_WARNINGS abschaltet.

SET ANSI_WARNINGS OFF

Der View Aufruf liefert jetzt den Returncode 0 zurück und das Paket tut das was es immer hätte tun sollen.

Im Nachhinein stellte sich mir jedoch die Frage, welche SET-Options denn eigentlich für eine aktuell ausgeführte Session gerade aktiv sind und wie diese abgefragt werden können?

Für diesen Zweck stellt SQL-Server die Variable @@OPTIONS zur Verfügung. Hier kann man über eine bitweise UND-Verknüpfung den Status der verschiedenen SET-Optionen bestimmen.

Da mir dies jedoch für den wiederholten Gebrauch zu unhandlich erschien, habe ich mal eben eine kleine Funktion geschrieben die den Status aller Optionen in einer Tabelle zurückliefert:

CREATE FUNCTION fn_Get_SetOptions()
RETURNS TABLE
AS RETURN
WITH Options AS
(
    SELECT    CONVERT(BIT,(@@OPTIONS & 0x0001) / 0x0001) AS DISABLE_DEF_CNST_CHK
             ,CONVERT(BIT,(@@OPTIONS & 0x0002) / 0x0002) AS IMPLICIT_TRANSACTIONS
             ,CONVERT(BIT,(@@OPTIONS & 0x0004) / 0x0004) AS CURSOR_CLOSE_ON_COMMIT
             ,CONVERT(BIT,(@@OPTIONS & 0x0008) / 0x0008) AS ANSI_WARNINGS
             ,CONVERT(BIT,(@@OPTIONS & 0x0010) / 0x0010) AS ANSI_PADDING
             ,CONVERT(BIT,(@@OPTIONS & 0x0020) / 0x0020) AS ANSI_NULLS
             ,CONVERT(BIT,(@@OPTIONS & 0x0040) / 0x0040) AS ARITHABORT
             ,CONVERT(BIT,(@@OPTIONS & 0x0080) / 0x0080) AS ARITHIGNORE
             ,CONVERT(BIT,(@@OPTIONS & 0x0100) / 0x0100) AS QUOTED_IDENTIFIER
             ,CONVERT(BIT,(@@OPTIONS & 0x0200) / 0x0200) AS NOCOUNT
             ,CONVERT(BIT,(@@OPTIONS & 0x0400) / 0x0400) AS ANSI_NULL_DFLT_ON
             ,CONVERT(BIT,(@@OPTIONS & 0x0800) / 0x0800) AS ANSI_NULL_DFLT_OFF
             ,CONVERT(BIT,(@@OPTIONS & 0x1000) / 0x1000) AS CONCAT_NULL_YIELDS_NULL
             ,CONVERT(BIT,(@@OPTIONS & 0x2000) / 0x2000) AS NUMERIC_ROUNDABORT
             ,CONVERT(BIT,(@@OPTIONS & 0x4000) / 0x4000) AS XACT_ABORT
)
SELECT * FROM 
(
    SELECT * FROM Options 
    unpivot 
    (
        IsSet FOR SetOption in 
        (
             DISABLE_DEF_CNST_CHK
            ,IMPLICIT_TRANSACTIONS
            ,CURSOR_CLOSE_ON_COMMIT
            ,ANSI_WARNINGS
            ,ANSI_PADDING
            ,ANSI_NULLS
            ,ARITHABORT
            ,ARITHIGNORE
            ,QUOTED_IDENTIFIER
            ,NOCOUNT
            ,ANSI_NULL_DFLT_ON
            ,ANSI_NULL_DFLT_OFF
            ,CONCAT_NULL_YIELDS_NULL
            ,NUMERIC_ROUNDABORT
            ,XACT_ABORT
        )
    ) unpvt
) x
GO
 
-- ---------------- So wird die Funktion aufgerufen ------------------
SELECT * FROM [AdventureWorks].[dbo].[fn_Get_SetOptions] ()

Hier noch die Values für die verschiedenen SET-Optionen zum selber verknüpfen:

Werte für die Bestimmung von SET-Optionen aus der @@Options-Variable
1	DISABLE_DEF_CNST_CHK
2	IMPLICIT_TRANSACTIONS
4	CURSOR_CLOSE_ON_COMMIT
8	ANSI_WARNINGS
16	ANSI_PADDING
32	ANSI_NULLS
64	ARITHABORT
128	ARITHIGNORE
256	QUOTED_IDENTIFIER
512	NOCOUNT
1024	ANSI_NULL_DFLT_ON
2048	ANSI_NULL_DFLT_OFF
4096	CONCAT_NULL_YIELDS_NULL
8192	NUMERIC_ROUNDABORT
16384	XACT_ABORT

have fun

Funktionsergebnisse mit cross/outer apply einbinden

admin — Wed, 21 Oct 2009 08:59:58 +0000

Ich möchte heute eine interessante Methode vorstellen die es ermöglicht die Ergebnisse von Funktionen in SQL-Abfragen zu verwenden.

Die sogenannten Table-Values-Functions (Funktionen die eine Tabelle als Return-Wert haben) können bei Abfragen wie eine Tabelle in ein Select-Statement eingebunden werden. Parameterwerte dürfen jedoch bei einem normalen Join nur von außerhalb des Select-Statements versorgt werden.

Als Beispiel könnte die in der AdventureWorks enthaltene Funktion dbo.ufnGetContactInformation in einer Abfrage verwendet werden. Als Parameterwert kann die ContactID einer Person übergeben werden und die TVF (Table-Valued-Function) liefert ein paar Kontaktdaten (Name, Vorname, Position, KontaktArt) zurück.

Möchte man diese Funktionalität nun in einem Select verwenden, ist man versucht die TVF, die ja wie eine Tabelle behandelt werden kann, wie folgt einzusetzen:

Nicht Korrekt!

WITH Liste AS
(
    SELECT   SalesPersonID
            ,SUM(TotalDue) AS TotalDue
    FROM Sales.SalesOrderHeader
    GROUP BY  SalesPersonID
)
SELECT   ci.FirstName
        ,ci.LastName
        ,ci.JobTitle
        ,ci.ContactType
        , l.TotalDue
FROM Liste l
join HumanResources.Employee e 
      ON e.EmployeeID = l.SalesPersonID
join dbo.ufnGetContactInformation(e.ContactID) ci 
      ON ci.ContactID = e.ContactID

In dem obigen Beispiel wird man eine Fehlermeldung bekommen, da hier versucht wird einen Parameterwert aus dem laufenden Select zu verwenden bzw. die TVF als JOIN-Tabelle genutzt wird.

Um eine TVF zu einem Select-Bestand zu joinen, muss stattdessen die Variante mittels APPLY gewählt werden. Hier gibt es zwei Möglichkeiten die einem INNER bzw. OUTER JOIN entsprechen.

Mittels CROSS APPLY wird das Ergebnis der TVF an das bestehende Resultset angehängt. Das Verhalten entspricht hier einem INNER JOIN, da Datensätze die kein Ergebnis über die Funktion liefern, nicht im Ausgabeset enthalten sind.

Möchte man, wie bei einem OUTER JOIN, alle Datensätze der Abfrage erhalten so muss die OUTER APPLY Variante angewendet werden. Spalten die nicht durch die TVF gefüllt werden können, werden hierbei mit NULL-Werten gefüllt.

Hier jetzt das korrekte Statement. Es zeigt die Umsätze aller Vertriebsmitarbeiter an. Direkte Kundenumsätze werden hierbei durch den CROSS APPLY unterdrückt.

Korrekte Anwendung

WITH Liste AS
(
    SELECT   SalesPersonID
            ,SUM(TotalDue) AS TotalDue
    FROM Sales.SalesOrderHeader
    GROUP BY  SalesPersonID
)
SELECT   ci.FirstName
        ,ci.LastName
        ,ci.JobTitle
        ,ci.ContactType
        , l.TotalDue
FROM Liste l
    LEFT join HumanResources.Employee e ON e.EmployeeID = l.SalesPersonID
    cross apply dbo.ufnGetContactInformation(e.ContactID) ci
 
ORDER BY LastName

Man kann ja mal das selbe Statement mit OUTER APPLY probieren

have fun

Parent-Child Tabellen für Reports sortieren

admin — Tue, 13 Oct 2009 16:14:09 +0000

Wenn man eine Parent-Child-Tabelle für einen Report vorbereiten soll, so ist das nicht immer ganz einfach. Die Parent-Child-Hierarchy kann man zwar noch mit einer rekursiven CTE (common table expression) abfragen, jedoch ist die anschließende Sortierung des Resultsets ein nicht zu unterschätzendes Hindernis.

Die CTE-Abfrage liefert zwar die richtigen Datensätze, gibt sie aber nicht in einer, für den Report geeigneten Weise, zurück.

Beispiel eines Ergebnis-Sets für eine Abfrage der Personal-Hierarchy aus der AdventureWorks:

Title                           EmployeeID  ManagerID    Level
------------------------------- ----------- -----------  ------
Chief Executive Officer         109         NULL         0     
Marketing Manager               6           109          1     
Vice President of Engineering   12          109          1     
Information Services Manager    42          109          1     
Chief Financial Officer         140         109          1     
Vice President of Production    148         109          1     
Vice President of Sales         273         109          1     
North American Sales Manager    268         273          2     
European Sales Manager          284         273          2     
Pacific Sales Manager           288         273          2     
Sales Representative            290         288          3     
Sales Representative            285         284          3     
Sales Representative            286         284          3     
Sales Representative            289         284          3     
Sales Representative            275         268          3

Wie man hier sehen kann, werden die unterschiedlichen Hierarchy-Level sortiert dargestellt.

Für einen Bericht müsste man dieses Ergebnis umsortieren bzw. in der folgenden Reihenfolge zurückgeliefert bekommen:

Title                              EmployeeID  ManagerID   LoginID                      Level
---------------------------------- ----------- ----------- ---------------------------- ------
Chief Executive Officer            109         NULL        adventure-works\ken0         0     
Vice President of Engineering      12          109         adventure-works\terri0       1     
Engineering Manager                3           12          adventure-works\roberto0     2     
Design Engineer                    11          3           adventure-works\jossef0      3     
Research and Development Manager   158         3           adventure-works\dylan0       3     
Research and Development Engineer  114         158         adventure-works\gigi0        4     
Research and Development Manager   217         158         adventure-works\michael6     4     
Research and Development Engineer  79          158         adventure-works\diane1       4     
Senior Tool Designer               263         3           adventure-works\ovidiu0      3     
Tool Designer                      265         263         adventure-works\janice0      4     
Tool Designer                      5           263         adventure-works\thierry0     4     
Senior Design Engineer             267         3           adventure-works\michael8     3

Hier ist die Reihenfolge, beginnend mit einem Manager ab Level 1 für diesen einen Manager bis zum Boden definiert. Danach wird zur nächst höheren Ebene zurückverwiesen die wiederum weitere Untergebene hat. Das Ergebnis wird also so angezeigt, wie dies in einem Bericht normalerweise stattfinden sollte.

Diese Sortierung kann man bereits bei der Generierung der Hierarchy als extra Feld mit in die rekursive Abfrage einbauen. Man benötigt hierfür ein Feld dass sortierbar ist und für den individuellen Zweck auch geeignet scheint.

Ich hab für das Beispiel einfach die EmployeeID verwendet. Wenn man diese ID je Ebene jeweils mit den IDs der Vorgesetzten versieht dann erhält man in etwa die folgende Spalte:

Hierarchy
--------------
109
1096
10912
10942
109140
109148
109273
109273268
109273284
109273288
109273288290
109273284285
109273284286
109273284289
109273268275

Sortiert man das Ergebnis nach dieser Hierarchy-Spalte und rückt das Resultset je nach Level noch ein wird die Hierarchy schnell sichtbar.

| Chief Executive Officer (109)
————— Vice President of Engineering (12)
————————— Engineering Manager (3)
————————————— Design Engineer (11)
————————————— Research and Development Manager (158)
————————————————— Research and Development Engineer (114)
————————————————— Research and Development Manager (217)
————————————————— Research and Development Engineer (79)
————————————— Senior Tool Designer (263)
————————————————— Tool Designer (265)
————————————————— Tool Designer (5)
————————————— Senior Design Engineer (267)
————————————— Design Engineer (270)
————————————— Senior Tool Designer (4)
————————————— Design Engineer (9)
————— Chief Financial Officer (140)
————————— Assistant to the Chief Financial Officer (103)
————————— Accounts Manager (139)
————————————— Accounts Receivable Specialist (130)
————————————— Accounts Payable Specialist (166)

Das Ergebnis enthält ja die EmployeeID und kann somit mit weiteren Tabellen verjoint werden und für einen Bericht sozusagen Mundgerecht aufbereitet werden. Die Sortierung innerhalb der Ebenen erfolgt im Moment noch über die EmployeeID, könne jedoch ggf. noch durch die JobBezeichung ergänzt werden. Dies würde die einzelnen Tätigkeiten auf den inneren Ebenen noch ein wenig gruppieren.

Wie immer hier das komplette Listing:

USE AdventureWorks
 
SET NOCOUNT ON
 
DECLARE @EmployeeID INT
 
-- ------------------------------------------------------------------
-- CEO bestimmen (Start der Hierarchy)
-- ------------------------------------------------------------------
 
SELECT @EmployeeID = EmployeeID
FROM HumanResources.Employee
WHERE ManagerID IS NULL
 
-- ------------------------------------------------------------------
-- Parent-Child Struktur parsen und einen sortierbaren Schlüssel
-- aufbauen.
-- In diesem Beispiel ist dies die EmployeeID, könnte auch jedes
-- andere Feld sein das eine Sortierung möglich machen könnte.
-- ------------------------------------------------------------------
 
;WITH ParentChildHierachy AS
(
        SELECT   emp.Title
                ,emp.EmployeeID
                ,emp.ManagerID
                ,emp.LoginID
                ,0 AS LEVEL
                ,CONVERT(VARCHAR(MAX),
                 CONVERT(VARCHAR(10),emp.EmployeeID)) AS Hierarchy
        FROM HumanResources.Employee emp
        WHERE emp.EmployeeID = @EmployeeID  
 
        UNION ALL
 
        SELECT   emp.Title
                ,emp.EmployeeID
                ,emp.ManagerID
                ,emp.LoginID
                ,pch.LEVEL+1
                ,CONVERT(VARCHAR(MAX),pch.Hierarchy
                +CONVERT(VARCHAR(10),emp.EmployeeID)) AS Hierarchy
        FROM HumanResources.Employee emp
        join ParentChildHierachy pch ON pch.EmployeeID = emp.ManagerID
)
SELECT   Title
        ,EmployeeID
        ,ManagerID
        ,LoginID
        ,LEVEL
        ,Hierarchy
INTO #TempTable
FROM ParentChildHierachy
ORDER BY Hierarchy
 
-- ------------------------------------------------------------------
-- Ausgabe der Hierarchie in Druckbarer Form
-- ------------------------------------------------------------------
 
 
DECLARE @Title VARCHAR(1000)
DECLARE @LEVEL INT
 
DECLARE c1 CURSOR FOR
SELECT LEVEL,Title,EmployeeID FROM #TempTable
 
OPEN c1
 
FETCH NEXT FROM c1 INTO @LEVEL, @Title, @EmployeeID
 
WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT CASE @LEVEL WHEN 0 THEN '|' ELSE CHAR(151) END
            + REPLICATE(CHAR(151),@LEVEL*4)
            + ' '
            + @Title
            + ' (' + CONVERT(VARCHAR(10),@EmployeeID) + ')'
 
        FETCH NEXT FROM c1 INTO @LEVEL, @Title, @EmployeeID
END
 
CLOSE c1
DEALLOCATE c1
 
DROP TABLE #TempTable

have fun

T-SQL Stringhandling (Extraktion von Token)

admin — Fri, 25 Sep 2009 14:03:38 +0000

Heute möchte ich einmal ein kleines Script bzw. eine kleine Funktion vorstellen die zum Zerlegen von Strings sehr nützlich sein kann.

Wenn man strukturierte Texte vorliegen hat und daraus bestimmte Teile extrahieren soll, so kann man dies sicher mit den Stringfunktionen von T-SQL erledigen. Gerade die Verarbeitung von Listen (Kommasepariert oder sonst irgendwie) bedingt aber die wiederholte Extraktion von Textblöcken (Token) die durch verschiedenste Begrenzungszeichen voneinander getrennt sind.

Als Token verstehe ich in diesem Zusammenhang Teile eines Textes die durch Begrenzer (Delimiter) voneinander logisch getrennt werden. In einem Satz wären das die Worte, in einer Liste die durch z.B. Komma getrennten Artikelnummern oder einfach Bestandteile eines Tabellennamens usw.

Sehen wir uns hierzu mal ein paar Beispiele an:

String: ‘Hello World, how are you?’
String: ‘123,2345,231234,3456′
String: ‘[AdventureWorks].[Person]. [Contact]‘

Extrahieren wir mal das dritte Wort aus Beispiel 1, den zweiten Parameterwert aus Beispiel 2 und den Tabellennamen aus Beispiel 3!

Mit Standardmitteln sicherlich ebenfalls zu realisieren, jedoch mit einer Funktion viel eleganter und Übersichtlicher zu gestalten:

DECLARE @String1 VARCHAR(1000)
DECLARE @String2 VARCHAR(1000)
DECLARE @String3 VARCHAR(1000)
 
-- --------------------------------------------------------------------
-- Single Delimiter ' ' Leerzeichen
-- --------------------------------------------------------------------
 
SET @String1 = 'Hello World, how are you?'
 
PRINT @String1 + '  ==>  ' + dbo.fn_GetToken(' ',3,@String)
 
-- --------------------------------------------------------------------
-- Single Delimiter ',' Komma
-- --------------------------------------------------------------------
 
SET @String2 = '123,2345,231234,3456'
 
PRINT @String2 + '  ==>  ' + dbo.fn_GetToken(',',2,@String)
 
-- --------------------------------------------------------------------
-- Paariger Delimiter []
-- --------------------------------------------------------------------
 
SET @String3 = '[AdventureWorks].[Person].[Contact]'
 
PRINT @String3 + '  ==>  ' + dbo.fn_GetToken('[<-*->]',3,@String)

Die Syntax für den Fuktionsaufruf sieht dann wie folgt aus:

dbo.fn_GetToken(single_delimiter|delimiter_pair, position, source_string)

single_delimiter: Beliebige Zeichenkette die Token voneinander trennt.
delimiter_pair: Beliebige Zeichenkette, danach ein Trennblock <-*-> und dann wieder eine Zeichenkette.
position: Das n-te Token beginnend mit 1.
Wenn position = 0 dann wird der String unverändert returniert.
source_string: Der Quellstring.
Als Rückgabewert wird das Token ohne die Delimiter geliefert oder NULL im Fehlerfall.

… und hier wie immer das Script dazu:

CREATE FUNCTION dbo.[fn_GetToken]
(
    @Delim NVARCHAR(100),
    @TokenNr INT,
    @Zeile NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
 
    DECLARE @RetWert        NVARCHAR(MAX)
    DECLARE @Line           NVARCHAR(MAX)	
 
    DECLARE @End_Delim      NVARCHAR(100)
    DECLARE @End_Delim_Len  INT			
 
    DECLARE @Delim_Pos_S    INT
    DECLARE @Delim_Pos_E    INT			
 
    DECLARE @Delim_S NVARCHAR(100)
    DECLARE @Delim_E NVARCHAR(100)	
 
    DECLARE @Pos        INT
    DECLARE @LEN        INT
    DECLARE @Delim_Len  INT
    DECLARE @TokenCnt   INT		
 
    -- Initialisierung der Parameter auf Defaults
 
    IF @Zeile   IS NULL SET @Zeile   = N''
    IF @Delim   IS NULL SET @Delim   = N';'
    IF @TokenNr IS NULL SET @TokenNr = 1
 
    SET @Line = @Zeile
 
    -- Anfangs- und Ende-Delimiter bestimmen wenn die Zeichenfolge
    -- '<-*->' im Delimiter vorhanden ist.
    -- Im anderen Fall wird die Zeichenfolge als Einzeldelimiter verwendet.
 
    IF CHARINDEX(N'<-*->',@Delim) > 0
    BEGIN
        SET @End_Delim = SUBSTRING(@Delim,
                         CHARINDEX(N'<-*->',@Delim)
                         +5,datalength(@Delim)/2)
 
        SET @Delim     = SUBSTRING(@Delim,1,
                         CHARINDEX(N'<-*->',@Delim)-1)
    END
    ELSE
    BEGIN
        SET @End_Delim = @Delim
    END
 
    -- Die Länge des Begrenzers muss halbiert werden (Unicode)
 
    SET @Delim_Len     = datalength(@Delim)/2
    SET @End_Delim_Len = datalength(@End_Delim)/2
 
    -- Start und Ende setzen. Wenn nur ein Begrenzer definiert ist,
    -- dann sind Start und Ende gleich.
 
    SET @Delim_S = @Delim
    SET @Delim_E = @End_Delim
 
    -- Die eckige Klammer muss als regulärer Ausdruck gecastet werden.
    -- Aus [ wird [[]
 
    SET @Delim     = REPLACE(@Delim    ,N'[','[[]')
    SET @End_Delim = REPLACE(@End_Delim,N'[','[[]')
 
    -- Wenn es nur einen Delimiter gibt, muss die ganze Zeile auf eine
    -- Zwei-Delimiter-Logik erweitert werden
    -- Dies bedeutet, dass die Zeile von einem Start und Enddelimiter
    -- umschlossen sein muss. Diese Werte werden ggf. hinzugefügt.
 
    IF @Delim_S = @Delim_E
    BEGIN
        IF SUBSTRING(@Zeile,1,@Delim_Len    ) <> @Delim_S
            SET @Zeile = @Delim_S + @Zeile
 
        IF RIGHT    (@Zeile  ,@End_Delim_Len) <> @Delim_E
            SET @Zeile = @Zeile + @Delim_E
    END
 
    -- Delimiter erweitern 
 
    IF SUBSTRING(@Delim,1,1) <> N'%' SET @Delim = N'%'+ @Delim
    IF RIGHT    (@Delim,1)   <> N'%' SET @Delim = @Delim + N'%'
 
    IF SUBSTRING(@End_Delim,1,1) <> N'%' SET @End_Delim = N'%'+ @End_Delim
    IF RIGHT    (@End_Delim,1)   <> N'%' SET @End_Delim = @End_Delim + N'%'
 
    -- Initial-Position setzen
 
    SET @Pos       = 1
    SET @TokenCnt  = 1
 
    SET @Delim_Pos_S = PATINDEX(@Delim,@Zeile)
    SET @Delim_Pos_E = PATINDEX(@End_Delim,
                            SUBSTRING(@Zeile,@Delim_Pos_S+@Delim_Len ,
                            datalength(@Zeile)/2))
                     + @Delim_Pos_S
                     + @Delim_Len-1
 
    -- Delimiter und Token suchen
 
    IF @TokenNr > 0
    BEGIN
        WHILE (@Delim_Pos_S > 0 and @Delim_Pos_E > 0)
        BEGIN
 
            -- Ermittlung des Token ...
 
            SET @LEN     = @Delim_Pos_E - @Delim_Pos_S - @Delim_Len
            SET @RetWert = SUBSTRING(@Zeile,@Delim_Pos_S + @Delim_Len,@LEN)
 
            -- Vorbereitung auf den nächsten Token ...
 
            IF @TokenCnt = @TokenNr BREAK ELSE SET @TokenCnt = @TokenCnt + 1;
 
            SET @Zeile       = SUBSTRING(@Zeile,@Delim_Pos_E + @End_Delim_Len,
                                                datalength(@Zeile)/2 - @Delim_Pos_E)
 
            IF @Delim_S = @Delim_E and datalength(@Zeile)/2 > 0
            BEGIN
                IF SUBSTRING(@Zeile,1,@Delim_Len    ) <> @Delim_S
                    SET @Zeile = @Delim_S + @Zeile
 
                IF RIGHT    (@Zeile  ,@End_Delim_Len) <> @Delim_E
                    SET @Zeile = @Zeile + @Delim_E
            END
 
            SET @Delim_Pos_S = PATINDEX(@Delim,@Zeile)
 
            SET @Delim_Pos_E = PATINDEX(@End_Delim,
                                   SUBSTRING(@Zeile,@Delim_Pos_S+@Delim_Len,
                                   datalength(@Zeile)/2))
                             + @Delim_Pos_S
                             + @Delim_Len-1
 
            IF @Delim_Pos_E <= @Delim_Len SET @RetWert = NULL
        END
    END
 
    -- Returncodes setzen
 
    SET @RetWert = CASE WHEN @TokenCnt <  @TokenNr	THEN NULL
                        WHEN @TokenNr  <  0			THEN NULL
                        WHEN @TokenNr  =  0			THEN @Line
                        ELSE @RetWert
                   END
 
    RETURN @RetWert
END

Tip: Man kann die Funktionsaufrufe natürlich auch ineinander verschachteln!

have fun

T-SQL CASE und die implizite Datentypkonvertierung

admin — Wed, 23 Sep 2009 09:33:41 +0000

Wenn man in einem Ausdruck einen CASE-Block verwendet, so ist dies auf den ersten Blick noch keine Besonderheit. Vergisst man jedoch dabei, dass SQL Server eine implizite Datentypkonvertierung vornimmt, so kann dies zu fehlerhaften Ergebnissen führen die noch dazu sehr schwer zu finden sind.

Betrachten wir hierfür einmal folgendes Beispiel:

DECLARE @AlteKndNr VARCHAR(15)
DECLARE @Von       VARCHAR(15)
DECLARE @Auf       VARCHAR(15)
DECLARE @Regel     INT
 
SET @AlteKndNr = 'K011110999-VIP'
 
SET @Regel = 1
SET @Von   = CASE @Regel WHEN 1 THEN '099' WHEN 2 THEN '0111' ELSE 0 END
SET @Auf   = CASE @Regel WHEN 1 THEN '033' WHEN 2 THEN '0000' ELSE 0 END
 
SELECT NeueKndNr = REPLACE(@AlteKndNr, @Von, @Auf), @Von, @Auf
 
SET @Regel = 2
SET @Von   = CASE @Regel WHEN 1 THEN '099' WHEN 2 THEN '0111' ELSE 0 END
SET @Auf   = CASE @Regel WHEN 1 THEN '033' WHEN 2 THEN '0000' ELSE 0 END
 
SELECT NeueKndNr = REPLACE(@AlteKndNr, @Von, @Auf), @Von, @Auf
 
-- -------------------------------------------------------------------------
-- Ergebnisse:
--
-- NeueKdNr bei @Regel = 1: K011110339-VIP = OK
-- NeueKdNr bei @Regel = 2: K0010999-VIP   = FALSCH
--                          K000010999-VIP = Das währe richtig!
-- -------------------------------------------------------------------------

Wie man sehen kann wird bei @Regel=2 eine falsche Konvertierung durchgeführt. Dies liegt an der oben besprochenen impliziten Konvertierungsregel von SQL Server. Wenn mehrere Datentypen in einem Ausdruck vorkommen so werden diese in einen Zieldatentyp konvertiert.

Der Typ des Zieldatentyps ist der Typ des Wertes der in folgender Tabelle den höchsten Prioritätswert (1) aufweist.

In unserem Fall wird im CASE-Block in den When-Zweigen ein String zurückgegeben, im Else-Zweig hingegen ein Integerwert! Obwohl der Else-Zweig nicht angesprochen wird, ist der Gesamtdatentyp des CASE-Blocks gem. Konvertierungstabelle ein Integerwert, da dieser die höhere Priorität gegenüber den varchar-Werten der When-Zweige aufweist.

Wenn man also den Else-Zweig des Case-Blocks mit Else ‘0′ definiert (man beachte die Hochkommas), dann wird auch das richtige Ergebnis ausgegeben.

Hier die Tabelle die die Prioritäten bei der impliziten Typkonvertierung aufzeigt:

**Datentyp-Prioritäten**
Priorität	Datentyp
1	Benuter-Definierter Typ
2	sql_variant
3	xml
4	datetime
5	smalldatetime
6	float
7	real
8	decimal
9	money
10	smallmoney
11	bigint
12	int
13	smallint
14	tinyint
15	bit
16	ntext
17	text
18	image
19	timestamp
20	uniqueidentifier
21	nvarchar
22	nchar
23	varchar
24	char
25	varbinary
26	binary

have fun

‘virtuelle’ Arrays mit T-SQL

admin — Tue, 22 Sep 2009 09:33:13 +0000

In T-SQL sind per Definition keine Arrays vorgesehen. Es gibt jedoch Fälle in denen der Einsatz eines Arrays von Vorteil wäre.

In einem konkreten Fall soll eine Prozedur einen Datenwert als Parameter erhalten. Dieser Wert wird jedoch in den, für die Abfrage zugrundeliegenden Tabellen nicht direkt verwendet. Vielmehr wird auf den Parameterwert nur indirekt über den Primärschlüssel (IdentityFeld) der Schlüsseltabelle verwiesen.

In der Prozedur müsste jetzt eine Abfrage in der Form:

SELECT <feldliste>
FROM <komplexe Abfrage>
    join Schlüsseltabelle ON Code =
<parameterwert>

erstellt werden.

Grundsätzlich ist dagegen ja nichts einzuwenden. Wenn der Join gegen die Schlüsseltabelle jedoch die Laufzeit des Gesamtstatements spürbar verlängert und zusätzlich in der Prozedur dieser Join mehrfach verwendet wird, dann sollte man darüber nachdenken ob es vielleicht eine performantere Lösung gibt.

Da es kein Array in T-SQL gibt kann man dieses als String simulieren. Dieses virtuelle Array trennt die einzelnen Werte durch einen definierten Delimiter (in meinem Fall ‘|’ der senkrechte Strich). Bei der Abfrage muss jetzt nur der Abfragewert in einen String mit umgebenden Delimitern convertiert werden und über CHARINDEX mit der virtuellen Array-Variablen verglichen werden.

Eine Lösung könnte damit in etwa so aussehen:

<erstelle ARRAY mit IdentWerten die durch parameterwert definiert sind>
SELECT <feldliste>
FROM <komplexe Abfrage>
WHERE CHARINDEX('|'+CONVERT(VARCHAR(10),<quellfeld>)+'|',@ARRAY) > 0

Ein Beispiel hierfür findet man in nachfolgenden Script:

SET NOCOUNT ON 
 
-- -----------------------------------------------------------------
-- Script in Variable @Cmd erstellt Array-String
-- -----------------------------------------------------------------
 
DECLARE @Cmd NVARCHAR(MAX)
 
SET @Cmd = N'
    use <@Datenbank>
 
    declare @Feld   nvarchar(1000)
    declare @Result nvarchar(max)
    declare @Delim  nvarchar(10)
 
    set @Delim = ''|''
    set @Result = @Delim
 
    declare FieldListCur cursor for
    select <@QuellFeld> from <@Quelltabelle> where <@Where>
 
    open FieldListCur
 
    fetch next from FieldListCur into @Feld
 
    while @@fetch_status = 0
    begin
        set @Result = @Result + @Feld + @Delim
        fetch next from FieldListCur into @Feld
    end
 
    close FieldListCur
    deallocate FieldListCur
 
    select @Result as Result
    '
 
-- -----------------------------------------------------------------
-- Script konfigurieren
-- -----------------------------------------------------------------
 
SET @Cmd = REPLACE(@Cmd,'<@Datenbank>','AdventureWorks')
SET @Cmd = REPLACE(@Cmd,'<@QuellFeld>','ContactID')
SET @Cmd = REPLACE(@Cmd,'<@Quelltabelle>','Person.Contact')
SET @Cmd = REPLACE(@Cmd,'<@Where>','FirstName like ''Fernan%''')
 
-- -----------------------------------------------------------------
-- Script ausführen und Ergebnis in Variable @Array schreiben
-- Die @Tabelle wird nur zur temporäran Übernahme des Ergebnisses benötigt.
-- -----------------------------------------------------------------
 
DECLARE @ARRAY NVARCHAR(MAX)
DECLARE @TArray TABLE (wert NVARCHAR(MAX))
 
INSERT INTO @TArray EXEC SP_EXECUTESQL @Cmd
 
SELECT @ARRAY = wert FROM @TArray
 
DELETE @TArray
 
-- -----------------------------------------------------------------
-- Anwendungsbeispiel:
-- Auslesen von Datensätzen die eine ContactID haben die in
-- unserem Array enthalten ist.
-- -----------------------------------------------------------------
 
SELECT *
FROM AdventureWorks.Person.Contact
WHERE CHARINDEX('|'+CONVERT(VARCHAR(10),ContactID)+'|',@ARRAY) > 0

Hinweis: Wer Fragen zu den Beispielen bzw. Themen an mich hat soll doch bitte über die Kommentarfunktion am Ende jedes Artikels seine Fragen einstellen. Direkte Mails gehen zwar auch, jedoch können andere in einem solchen Fall nicht an den Ergebnissen bzw. Verbesserungen teilhaben.

Viel Spaß beim ausprobieren

Datensatzlänge einer Abfrage schätzen

admin — Tue, 08 Sep 2009 16:16:19 +0000

Gerade wenn man ein bestehendes Datenbanksystem optimieren möchte, könnte die Frage nach der durchschnittlichen Breite einer Abfrage auftauchen. Speziell wenn man keine Tabelle zur Hand hat die in ihren Properties zumindest den verbrauchten Speicherplatz anzeigt, ist die nachfolgende Prozedur vielleicht ganz nützlich.

Es werden für eine gegebene Abfrage die Ergebnisse in eine temporäre Tabelle geschrieben und dann mittels der Funktion DATALENGTH die tatsächliche Länge eines Feldes bestimmt. Die durchschnittliche Summe der Gesamtlänge eines Datensatzes wird in der Rückgabe angezeigt.

Wenn man diese Durchschnittslänge mit der Anzahl der Datensätze multipliziert, erhält man den Nettowert des Datenvolumens das transferiert wird. (zumindest annähernd;))

Wenn ein Feld einen NULL-Wert enthält, setzt die Prozedur eine Feldlänge von 4 Byte ein. Dies wird als Mittelwert einer leeren Feldlänge verwendet und könnte in einer weiteren Ausbaustufe ggf. noch aus den System-Views für die Feldtypen genauer bestimmt werden.

Die Abfrage sollte eine repräsentative Menge an Daten zurückliefern. Es ist vor allem bei sehr großen Ergebnismengen ein wenig Vorsicht angesagt, um nicht die Temp-Datenbank in Bedrängnis geraten zu lassen

CREATE PROCEDURE dbo.GetAvgRecordWidth
  @Datenbank sysname
, @SourceCmd NVARCHAR(MAX)
AS
BEGIN
    DECLARE @TempName NVARCHAR(1000)
    DECLARE @IntoTemp NVARCHAR(1000)
 
    SET @TempName = '##TempCntSource_' + REPLACE(newid(),'-','')
 
    SET @IntoTemp = ' into ' + @TempName + ' FROM '
 
    SET @SourceCmd = STUFF(@SourceCmd,CHARINDEX('from',LOWER(@SourceCmd)),4,@IntoTemp)
 
    -- ---------------------------------------------------------------------------
    DECLARE @Cmd NVARCHAR(MAX)
 
    SET @Cmd = 'USE ' + @Datenbank + '; ' + @SourceCmd + N'
    declare  @SQL nvarchar(max)
            ,@Feld sysname
            ,@Delim varchar(100)
 
    set @Delim = N''Select avg(''
 
    set @SQL   = N''''
 
    declare Colcur cursor for
    select c.name
    from tempdb.sys.objects o
        join tempdb.sys.columns c on c.object_id = o.object_id
    where o.name like ''<##TempCntSource>%''
 
    open Colcur
 
    fetch next from  Colcur into @Feld
 
    while @@fetch_status = 0
    begin
        set @SQL = @SQL + @Delim + ''isnull(DATALENGTH('' + @Feld + ''),4)''
        set @Delim = ''+''
 
        fetch next from  Colcur into @Feld
    end
 
    close Colcur
    deallocate Colcur
 
    set  @SQL = @SQL + '') from <##TempCntSource>''
 
    exec sp_executesql @SQL
 
    drop table <##TempCntSource>
    '
 
    SET @Cmd = REPLACE(@Cmd,'<##TempCntSource>',@TempName)
 
    EXEC SP_EXECUTESQL @Cmd
END

Der Aufruf sieht dann wie folgt aus:

DECLARE @Stmt NVARCHAR(MAX)
DECLARE @Datenbank sysname
 
SET @Datenbank = N'AdventureWorks'
SET @Stmt      = N'select * from HumanResources.Employee'
 
EXEC dbo.GetAvgRecordWidth @Datenbank,@Stmt

have fun