Tag: "entwicklung"

Trigger umgehen

Posted on Nov 17, 2004 von in SQL Server

Gleich vorwegschicken möchte ich, daß dieser Beitrag nicht zur Nachahmung oder zum Einsatz in Produktivsystemen gedacht ist. Vielmehr eher als reine Spielerei mit dem, was möglich ist.

Also, kann die Ausführung eines Triggers umgangen werden? Einfache Antwort: Ja! Und zwar auf mehreren Wegen, von denen ich hier nur einen anführen will.

Die Ausgangssituation:

CREATE TABLE dbo.test_trigger
(
c1 INT
, c2 CHAR(10)
)
GO
CREATE TRIGGER fool_me ON dbo.test_trigger FOR INSERT
AS
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO

Dieser Trigger soll also bei jedem INSERT den Wert 'Hallo Welt' in die Spalte c2 schreiben. Das das funktioniert, kann man einfach feststellen:

INSERT INTO dbo.test_trigger (c1) VALUES(1)
INSERT INTO dbo.test_trigger (c1, c2) VALUES(1, 'wtf')
SELECT * FROM dbo.test_trigger

c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt

So, egal,ob man nun explizit einen Wert in die Spalte c2 eintragen will oder nicht, der Wert wird durch den Trigger überschrieben.

Die Manipulation:

Und zwar nutzen wir die Spalte context_info in der master.dbo.sysprocesses Tabelle. Diese kann durch die Einstellung SET CONTEXT_INFO zusätzliche 128 Bytes pro Session oder Connection speichern. Zunächst ändern wir mal unseren Trigger ab.

ALTER TRIGGER fool_me ON dbo.test_trigger FOR INSERT AS
IF NOT (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@SPID) = 0xA
BEGIN
UPDATE dbo.test_trigger SET c2 = 'Hallo Welt'
END
GO

So, nun können wir SET CONTEXT_INFO verwenden:

SET CONTEXT_INFO 0xA
INSERT INTO dbo.test_trigger (c1, c2) VALUES(2, 'q.e.d')
SET CONTEXT_INFO 0

Das Ergebnis:

SELECT * FROM dbo.test_trigger

c1 c2
----------- ----------
1 Hallo Welt
1 Hallo Welt
2 q.e.d

Nochmals der Hinweis: Dies ist kein Skript, was in Produktionssystemen verwendet werden sollte. Die Manipulation von SET CONTEXT_INFO kann zu Nebeneffekten führen bei anderen DB Objekten, die ebenfalls auf diese Einstellung zugreifen.

Danke an Jonathan van Houtte!

In welcher Reihenfolge gibt SELECT * die Spalten zurück

Posted on Okt 26, 2004 von in SQL Server

Vielleicht hat sich der Eine oder Andere schon mal gefragt, wieso und warum SQL Server bei einem SELECT * Statement die Spalte in der Art und Weise zurückgibt, in der es geschieht. Zum Beispiel:

CREATE TABLE #test
(
col1 CHAR
, col2 INT
, col3 FLOAT
, col4 VARCHAR
)
GO
INSERT INTO #test VALUES('a',1,0.9,'A')
SELECT * FROM #test

col1 col2 col3 col4
---- ----------- ----------------------------------------------------- ----
a 1 0.90000000000000002 A

Die Reihenfolge der Spalten entspricht bei einem SELECT * immer stets der Reihenfolge, in der sie beim CREATE TABLE Statement angegeben wurden. Das heißt, im Grunde werden die Spalten in aufsteigender Reihenfolge der colid in der Tabelle syscolumns zurückgegeben. Hier ist ein Ausschnitt aus

EXEC sp_help #test

Column_name
------------
col1
col2
col3
col4

Und ist der entsprechende Ausschnitt aus syscolumns:

SELECT
CAST(name AS CHAR(30)) AS [name]
, colid
FROM
syscolumns
WHERE
id=OBJECT_ID('#test')

name colid
------------------------------ ------
col1 1
col2 2
col3 3
col4 4

Erwähnt werden sollte auch, daß ein SELECT * Statement nichts in Produktionscode zu suchen hat, IMHO. Und das die physikalische Speicherung der Storage Engine wiederum eine komplett andere ist.

Abfolge von Triggern ändern

Posted on Sep 28, 2004 von in SQL Server

Die nachfolgenden Informationen gelten nur für AFTER Trigger, nicht für INSTEAD OF Trigger. Grundsätzlich kann man (theoretisch) beliebig viele AFTER Trigger pro Tabelle definieren. Die Anzahl ist hierbei nur durch die maximalen Objekte pro Datenbank von 2.147.483.647 beschränkt. Wenn es nun auf eine gewisse Abfolge ankommt, in welcher die Trigger abgefeuert werden, so kann man auf diese einen gewissen Einfluß ausüben durch die Systemprozedur sp_settriggerorder. Diese hat folgende Syntax:

sp_settriggerorder[@triggername = ] 'triggername' 
    , [@order = ] 'value' 
    , [@stmttype = ] 'statement_type' 

Ein beispielhafter Aufruf sieht dann folgendermaßen aus:

Den Einfluß, den man ausüben kann beschränkt sich aber darauf, festzulegen, welcher Trigger als Erster und welcher Trigger als Letzter abgefeuert wird. Dies erfolgt durch Angabe von

@order='first'

oder

@order='last'

Dazwischen ist die Reihenfolge der Ausführung undefiniert.

@order='none'

Weitere Erklärungen stehen in BOL.

Spaß mit Zahlen in Transact-SQL Abfragen

Posted on Sep 25, 2004 von in SQL Server

Original von Narayana Vyas Kondreddi; deutsche Übersetzung von Frank Kalis

Neulich stellte jemand in den öffentlichen Microsoft Newsgroups die Frage, wie man Zeichen innerhalb eines Strings sortiert? Zum Beispiel enthielt der String 'CBA' und er wollte nun die Zeichen innerhalb des String sortieren, um daraus 'ABC' zu machen. Anstelle nun die prozedurale Lösung zu verwenden, habe ich versucht, rein aus Spaß, dies mit einem relationalen Ansatz (T-SQL spezifisch) zu lösen. In diesem Artikel zeige ich Ihnen, wie Sie eine Zahlentabelle verwenden können, um diese innovative Art von Abfragen zu schreiben.

Ganze Geschichte »

Spaltenwerte als kommaseparierte Liste zurückgeben

Posted on Sep 21, 2004 von in SQL Server

Wie so oft mag man sich fragen, ob dies eher die Aufgabe des Clients als die des Server ist, aber da man häufig derartige Fragestellungen beobachten kann, hier an dieser Stelle vielleicht ein paar Lösungsansätze zu folgendem Problem:

Ganze Geschichte »

Summenwert einer Reihe

Posted on Aug 18, 2004 von in SQL Server

Dies ist ein beliebtes Beispiel für Informatikstudenten im Anfangsstadium, um die Auswirkungen effizienter Algorithmen zu demonstrieren. Also auch hier nicht unbedingt etwas, was man zwingend in einer Datenbank machen müßte, das sich aber durchaus mengenbasiert lösen läßt. Zu diesem Algorithmus gibt es eine kleinen Anekdote:

Dem "Entdecker" Carl-Friedrich Gauß wurde in der Schule die Aufgabe gestellt, die Summe aller Zahlen von 1 bis 100 zu berechnen. Alle Kinder rechneten los, Gauß schrieb kurz etwas auf seine Tafel und riss nach kurzer Zeit seinen Lehrer aus dessen Ruhepause. Die Formel stimmte natürlich! Wer es genauer nachlesen möchte, kann sich mal hier umschauen.

DECLARE @n BIGINT
SET @n = 100
SELECT (@n+@n*@n)/2
                     
-------------------- 
5050

(1 row(s) affected)

oder als UDF

CREATE FUNCTION dbo.achtsieben(@n BIGINT) 
RETURNS BIGINT
	AS
		BEGIN
    	RETURN (@n+@n*@n)/2
		END
GO
SELECT dbo.achtsieben(100)
DROP FUNCTION dbo.achtsieben
                     
-------------------- 
5050

(1 row(s) affected)

Zu dem Namen, den ich der Funktion gegeben habe, gibt es auch eine Anekdote:

Als ich unsere Mathematiker nach der "richtigen" Bezeichnung für diese Formel gefragt habe, kam als Antwort:

"Wir nennen das die 78-er Regel, da die Summe der Monate eines Jahres 78 ist."

Ein kurzer Test ergibt:

DECLARE @n BIGINT
SET @n = 12
SELECT (@n+@n*@n)/2
                     
-------------------- 
78

(1 row(s) affected)

Stimmt!

Nachtrag 27.08.2004: Auch im SQL Server kann man damit die Notwendigkeit zum Einsatz von effizienten Algorithmen demonstrieren. Dazu bauen wir uns mal folgendes Testskript zusammen:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME
SET @start = GETDATE()
DECLARE @n BIGINT 
SET @n = 200000 
SELECT (@n+@n*@n)/2
SELECT GETDATE()-@start AS Zeit
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME
DECLARE @n BIGINT 
DECLARE @result BIGINT 
SET @start = GETDATE()
SET @n = 1
SET @result = 0
WHILE @n <= 200000
	BEGIN
		SET @result = @result + @n
		SET @n = @n + 1
	END
SELECT @result
SELECT GETDATE()-@start AS Zeit

Nach Ausführung erhält man folgendes Ergebnis:

...
                     
-------------------- 
20000100000

(1 row(s) affected)

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:00.010

(1 row(s) affected)

...
                     
-------------------- 
20000100000

(1 row(s) affected)

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:01.513

(1 row(s) affected)

Während der Gauß Algorithmus fast augenblicklich das Ergebnis zurückliefern, braucht die iterative Methode deutlich länger!

Vielleicht mag das nicht viel erscheinen, aber jetzt stelle ich mir die Auswirkungen auf ein System vor, in dem ein solcher algorithmus in einer stark frequentierten Prozedur implementiert wurde, die mehrere Tausend Male pro Stunde aufgerufen wird. Nun sieht die Sache schon etwas anders aus. Jetzt mag man natürlich mit Caching argumentieren, aber in diesem Fall wird die Ausführung ohne

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

zwischen zwei Läufen der iterativen Methode auch nicht wirklich schneller

Zeit                                                   
------------------------------------------------------ 
1900-01-01 00:00:01.403

(1 row(s) affected)

Dies ist natürlich kein repräsentativer Test unter sterilen Testbedingungen, aber verdeutlicht doch die Notwendigkeit effizienter Algorithmen, auch, und vielleicht gerade, in T-SQL. 

Unterschiede zwischen MONEY und DECIMAL

Posted on Aug 16, 2004 von in SQL Server

SQL Server MVP Steve Kass hat dieses Beispiel in den englischen Newsgroups gepostet. Es zeigt, daß der Einsatz der Datentypen zur Speicherung monetärer Daten sorgfältig durchdacht sein sollte. Man sollte stets bedenken, welche Operationen mit diesen Daten durchgeführt werden.

Ganze Geschichte »

Zahl rechts-oder linksbündig mit 0 formatieren

Posted on Aug 9, 2004 von in SQL Server

Die Präsentation von Informationen aus der Datenbank ist imho eher Sache des Clients als die des Servers. Solche Aufgaben gehören zum Handswerkzeug jedes Front-End und sind dort schnell und einfach erledigt.

Falls jedoch, aus welchen Gründen auch immer, dies auf dem Server erledigt werden muss, kann vielleicht folgendes Skript gute Dienst leisten:

DECLARE @MeineZahl INT
SET @MeineZahl = 99
SELECT 
	RIGHT(REPLICATE('0',10) + CAST(@MeineZahl AS VARCHAR(10)),10) AS Rechtsbündig
	, LEFT(CAST(@MeineZahl AS VARCHAR(10)) + REPLICATE('0',10) ,10) AS Linksbündig

Rechtsbündig Linksbündig 
------------ ----------- 
0000000099   9900000000

(1 row(s) affected)

Für den Lazycoder könnte das rechtbündige Auffüllen auch noch folgendermaßen aussehen:

DECLARE @MeineZahl INT
SET @MeineZahl = 99
SELECT REPLACE(STR(@MeineZahl,10), ' ', '0')

Rechtsbündig          
---------------------- 
0000000099

(1 row(s) affected)