Kategorie: "SQL Server"
Determinismus von CHARINDEX und PATINDEX
In BOL werden CHARINDEX() und PATINDEX() als nichtdeterministische Stringfunktionen aufgelistet. Warum eigentlich? Man sollte meinen, das ceteris paribus auch bei diesen Funktionen stets ein identisches Ergebnis herauskommt. Richtig, und gleichzeitig nicht! Der Grund, warum beide Funktionen als nichtdeterministisch geführt werden, findet sich dann in den Erklärungen zu PATINDEX():
Daten aus Excel abfragen
In fast allen Online Communities sieht man solche Fragen mit schöner Regelmässigkeit auftauchen. Die vielleicht einfachste Methode, diese Daten abzufragen, besteht in der Verwendung von OPENROWSET:
Zahl rechts-oder linksbündig mit 0 formatieren
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)
N-te Wurzel einer Zahl
Tja, wieder so ein Beispiel, dass man mal in der Schule gelernt hat, aber immer genau dann vergisst, wenn man es braucht.
DECLARE @My1 FLOAT
DECLARE @My2 FLOAT
SELECT @My1 = 16, @My2 = 4
SELECT POWER(@My1, 1/@My2)
-----------------------------------------------------
2.0
(1 row(s) affected)
Oder als UDF-Version
CREATE FUNCTION nthroot(@My1 FLOAT, @My2 FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN POWER(@My1,1/@My2)
END
GO
SELECT dbo.nthroot(16,4)
DROP FUNCTION dbo.nthroot
-----------------------------------------------------
2.0
(1 row(s) affected)
Kleinste gemeinsame Vielfache zweier Zahlen
CREATE FUNCTION dbo.kgv(@zahl1 int, @zahl2 int )
RETURNS INT
AS
BEGIN
RETURN (@zahl1 * @zahl2) / dbo.ggt(@zahl1, @zahl2)
END
GO
SELECT dbo.kgv(24,36)
DROP FUNCTION dbo.kgv
-----------
72
(1 row(s) affected)
Der Vollständigkeit halber hier noch einmal die Funktion zur Ermittlung des grösssten gemeinsamen Teilers:
CREATE FUNCTION dbo.ggt(@zahl1 int, @zahl2 int)
RETURNS INT
AS
BEGIN
DECLARE @zahl3 INT
SET @zahl3=1
WHILE (@zahl3 <> 0)
BEGIN
SET @zahl3=@zahl1 % @zahl2
SET @zahl1=@zahl2
SET @zahl2=@zahl3
END
RETURN @zahl1
END
GO
Dies ist dies Adaption der Excel Funktion KGV.
Größte gemeinsame Teiler zweier Zahlen
Dies ist die Adaption der Excel Funktion GGT().
CREATE FUNCTION dbo.ggt(@zahl1 int, @zahl2 int)
RETURNS INT
AS
BEGIN
DECLARE @zahl3 INT
SET @zahl3=1
WHILE (@zahl3 <> 0)
BEGIN
SET @zahl3=@zahl1 % @zahl2
SET @zahl1=@zahl2
SET @zahl2=@zahl3
END
RETURN @zahl1
END
GO
SELECT dbo.ggt(24,36)
DROP FUNCTION dbo.ggt
-----------
12
(1 row(s) affected)
Dynamisches SQL innerhalb einer Funktion
Dynamisches SQL kann nicht innerhalb einer Funktion ausgeführt werden. Genausowenig können Stored Procedures aufgerufen werden. Der einzige Workaround hier ist, eine andere Logik anzuwenden, um um den dynamischen Teil herumzukommen. So führt z.B. folgendes zu einem Fehler:
Sequentielle Nummerierung eines Resultsets
Eine beliebte Frage mit unzähligen Antworten. Meine Lieblingsantwort darauf ist, dies in der Präsentationsschicht seiner Anwendung zu machen. IMHO ist dies Aufgabe des Clients, nicht des Servers. Wenn es aber unbedingt in T-SQL gemacht werden soll, kann man folgendes machen:
USE PUBS
GO
SELECT
(
SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id
) AS Lfd_Nr
, au_lname, au_fname
FROM
Authors AS A
ORDER BY
Lfd_Nr
GO
Lfd_Nr au_lname au_fname
----------- ---------------------------------------- --------------------
1 Hello World Johnson
2 Green Marjorie
3 Carson Cheryl
4 O'Leary Michael
5 Straight Dean
6 Smith Meander
7 Bennet Abraham
8 Dull Ann
9 Gringlesby Burt
10 Locksley Charlene
11 Greene Morningstar
12 Blotchet-Halls Reginald
13 Yokomoto Akiko
14 del Castillo Innes
15 DeFrance Michel
16 Stringer Dirk
17 MacFeather Stearns
18 Karsen Livia
19 Panteley Sylvia
20 Hunter Sheryl
21 McBadden Heather
22 Ringer Anne
23 Ringer Albert
(23 row(s) affected)
Bei umfangreichen Resultsets kann die obige Methode schon mal einige Zeit in Anspruch nehmen. Ist also nur bedingt empfehlenswert. Eine Alternative ist vielleicht die Verwendung der IDENTITY Funktion zusammen mit einer temporären Tabelle, so wie hier:
SELECT
IDENTITY(INT,1,1) AS Lfd_Nr
, au_lname
, au_fname
INTO #authors
FROM authors
ORDER BY au_id
SELECT *
FROM #authors
ORDER BY Lfd_Nr
DROP TABLE #authors
Lfd_Nr au_lname au_fname
----------- ---------------------------------------- --------------------
1 Hello World Johnson
2 Green Marjorie
3 Carson Cheryl
4 O'Leary Michael
5 Straight Dean
6 Smith Meander
7 Bennet Abraham
8 Dull Ann
9 Gringlesby Burt
10 Locksley Charlene
11 Greene Morningstar
12 Blotchet-Halls Reginald
13 Yokomoto Akiko
14 del Castillo Innes
15 DeFrance Michel
16 Stringer Dirk
17 MacFeather Stearns
18 Karsen Livia
19 Panteley Sylvia
20 Hunter Sheryl
21 McBadden Heather
22 Ringer Anne
23 Ringer Albert
(23 row(s) affected)
In SQL Server 2005 ist dies übrigens einfacher durch die Implementierung von ROW_NUMBER().
