Archiv der Kategorie: MS SQL Server

relationale Datenbank SQL Server 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017

Datumsdimension in SSAS (ab 2016) / Azure

In meinen Projekten kommt es natürlich oft vor, dass man Datumsdimensionen benötigt.

Dazu verwende ich Stored Functions im SQL Server, um die Datumswerte mit allen gewünschten Attributen (wie Wochentag, Monat, Kalenderwoche, etc.) an den Analysis Services-Dienst weiter zu geben. Ich will das hier beschreiben, weil seit SSAS 2017 der Aufruf von Stored Functions gar nicht mehr so offensichtlich ist (wenn man die depracted Data Sources nicht verwenden will).

Das Vorgehen ist wie folgt:

  • Ermittle die Datumswerte, die die Datumsdimension enthalten soll. In der Regel ist das entweder ein Zeitraum oder distinkte Werte (z.B. bei einer Dimension, in der der Datenstand ausgewählt werden kann)
  • Liefere zu den ermittelten Datumswerten alle Attribute wie Wochentag, Monat, Kalenderwoche, etc.)
  • Gib diese Daten an den SSAS weiter

Wir benutzen hier bewusst einen getrennten Ansatz, erst die anzeigenden Datumswerte zu ermitteln und dann dazu die Attribute zu erzeugen. Dadurch erreichen wir die höchst mögliche Flexibilität. Man könnte zum Beispiel folgendes machen:

  • Wir nehmen alle Datumswerte vom Minimum der auftretenden Werte bis zum Maximum
  • Da aber Fehleingaben enthalten sind, und wir nicht alle Datumswerte vom 1.1.2000 bis 1.1.3000 in unserer Dimension haben wollen, nehmen wir nur die Datumswerte vom Minimum bis zum Ende nächsten Jahres und alle weiteren DISTINCTen Werte, die vorkommen.

Wir gehen so vor:

Als erstes legen wir einen Type an, der eine Liste von Datumswerten halten kann:

CREATE TYPE [dbo].[typ_Datumswerte] AS TABLE(
	[datum] [date] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[datum] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

In eine Variable dieses Datentyps können wir nun beliebige Datumswerte speichern. Wenn man zum Beispiel eine distinkte Menge an Datumswerten aus einer Faktentabelle speichern will, kann man das so machen:

DECLARE @d AS typ_Datumswerte
INSERT INTO @d SELECT DISTINCT Datum FROM Fakt_Auftragsbestand

Meistens benötigt man aber einen ganzen Zeitraum an allen möglichen Datumswerten. Dafür habe ich eine triviale Stored Function geschrieben:

-- =============================================
-- Author:		Martin Cremer
-- Create date: 07.07.2015
-- Description:	liefert alle Tage eines bestimmten Zeitraums
-- =============================================
CREATE FUNCTION [dbo].[f_alle_Datumswerte] 
(
	@von date, 
	@bis date
)
RETURNS 
@tab TABLE 
(
	datum date	 
)
AS
BEGIN
	while @von <= @bis
	begin
		insert into @tab select @von

		set @von = dateadd(d, 1, @von)
	end
	
	RETURN 
END
GO

Um einen ganzen Zeitraum nun in eine Variable des oben definierten Typs zu schreiben, geht man so vor:

DECLARE @d AS typ_Datumswerte
INSERT INTO @d 
SELECT * FROM dbo.f_alle_Datumswerte( 
	(SELECT min(Datum) FROM Fakt_Auftragsbestand),
	(SELECT max(Datum) FROM Fakt_Auftragsbestand)
)

Damit hätten wir den ersten Punkt erledigt. Kommen wir nun dazu, alle benötigten Attribute zu ermitteln. Dafür gibt es ein paar Hilfsfunktionen, deren 1. Version ich sogar am 1.1.2009 hier schon im Blog veröffentlicht hatte, und eine Funktion, die alles zusammenfasst. Zunächst die Hilfsfunktionen (auf die heutige Zeit angepasst, in der der SQL Server nativ die deutsche ISO-Woche ermitteln kann):

-- =============================================
-- Author:		Martin Cremer
-- Create date: 16.8.2006
-- Description:	ermittelt aus dem Datum den Wochentag (unabhängig von SET DATEFIRST): 1=Montag - 7=Sonntag
-- =============================================
CREATE FUNCTION [dbo].[getWochentag] 
(
	@dat date
)
RETURNS int
AS
BEGIN
	DECLARE @Result int
	SELECT @Result = (datepart(dw, @dat) - 1 + @@datefirst -1) % 7 +1
	RETURN @Result
END
GO
CREATE FUNCTION [dbo].[getKW_Jahr](@h as date)
returns int
as
begin
    return case 
		when datepart(isowk, @h)>50 and month(@h) = 1 then year(@h)-1 
		when datepart(isowk, @h)=1 and month(@h) = 12 then year(@h)+1
		else year(@h)
	end 
end
GO
CREATE FUNCTION [dbo].[getKW_Woche](@h as date)
returns int
as
begin
	return  datepart(isowk, @h)
end
GO

Die geradlinige zusammenfassende Funtkion sieht dann so aus:

-- =============================================
-- Author:		Martin Cremer
-- Create date: 07.07.2015
-- Description:	liefert zu den übergebenen Datumswerten alle Attribute
-- =============================================
CREATE FUNCTION [dbo].[f_Datumsattribute] 
(
	@datumswerte as dbo.typ_Datumswerte readonly
)
RETURNS 
@Ergebnis TABLE 
(
	Datum date NOT NULL, 
	[KW_ID] INT NOT NULL, 
	[KW_Jahr] SMALLINT NOT NULL,
	[KW] NVARCHAR(10) NOT NULL,
	[KW_Nr] SMALLINT NOT NULL,
	[Monat_ID] INT NOT NULL,
	[Monat_OhneJahr_ID] TINYINT NOT NULL,
	[Monat] NVARCHAR(8) NOT NULL,
	[Monat_OhneJahr] NVARCHAR(3) NOT NULL,
	[Jahr] SMALLINT NOT NULL,
	[Quartal_ID] SMALLINT NOT NULL,
	[Quartal_OhneJahr_ID] TINYINT NOT NULL,
	[Quartal_OhneJahr] NVARCHAR(50) NOT NULL,
	[Quartal] NVARCHAR(50) NOT NULL,
	[Wochentag_ID] int NOT NULL,
	[Wochentag] nvarchar(20) NOT NULL,
	[Wochentag_Kürzel] nvarchar(2) NOT NULL
)
AS
BEGIN
	INSERT INTO @Ergebnis
	(Datum, [KW_ID], [KW_Jahr], [KW], [KW_Nr], [Monat_ID], [Monat_OhneJahr_ID], [Monat], [Monat_OhneJahr], [Jahr], 
		 [Quartal_ID], [Quartal_OhneJahr_ID], [Quartal_OhneJahr], [Quartal], [Wochentag_ID], [Wochentag], [Wochentag_Kürzel])
	SELECT 
		   x.datum,
		   x.KW_Jahr * 100 + x.KW /* 201501 für KW 01/2015*/,
		   x.KW_Jahr /*2015*/,
		   'KW ' + RIGHT('0' + CONVERT(NVARCHAR(2), x.KW), 2) + '/' + CONVERT(NVARCHAR(4), x.KW_Jahr) /* KW 01/2015*/,
		   x.KW /*1*/,
		   x.jahr * 100 + x.Monat /* 201501 für Jan 2015 */,
		   x.monat /* 1 */,
		   monate.monatsname + ' ' + CONVERT(NVARCHAR(4), x.jahr) /* Jan 2015 */,
		   monate.monatsname /* Jan */,
		   x.jahr,
		   x.jahr * 10 + x.quartal /* 20151 für Q1 2015 */,
		   x.quartal /* 1 */,
		   'Q' + CONVERT(NVARCHAR(1), x.quartal) /* Q1 */,
		   'Q' + CONVERT(NVARCHAR(1), x.quartal) + ' ' + CONVERT(NVARCHAR(4), x.jahr),
		   x.wochentagID,
		   Wochentage.Wochentagname, 
		   Wochentage.Wochentagkurz
		FROM
			(SELECT [dbo].[getKW_Jahr](d.datum) AS KW_Jahr, [dbo].[getKW_Woche](d.datum) AS KW, 
			MONTH(d.datum) AS monat,
			datepart(QUARTER, d.datum) AS quartal,
			year(d.datum) AS jahr,
			[dbo].[getWochentag](d.datum) as wochentagID,
			d.datum
			FROM @datumswerte as d) AS x
		LEFT JOIN 
			(SELECT 1 AS monat, 'Jan' AS Monatsname UNION ALL
			 SELECT 2, 'Feb' UNION ALL
			 SELECT 3, 'Mär' UNION ALL
			 SELECT 4, 'Apr' UNION ALL
			 SELECT 5, 'Mai' UNION ALL
			 SELECT 6, 'Jun' UNION ALL
			 SELECT 7, 'Jul' UNION ALL
			 SELECT 8, 'Aug' UNION ALL
			 SELECT 9, 'Sep' UNION ALL
			 SELECT 10, 'Okt' UNION ALL
			 SELECT 11, 'Nov' UNION ALL
			 SELECT 12, 'Dez' ) AS monate
		ON x.monat = monate.monat
		LEFT JOIN 
			(SELECT 1 as WochentagID, 'Montag' Wochentagname, 'Mo' Wochentagkurz UNION ALL
			 SELECT 2, 'Dienstag', 'Di' UNION ALL
			 SELECT 3, 'Mittwoch', 'Mi' UNION ALL
			 SELECT 4, 'Donnerstag', 'Do' UNION ALL
			 SELECT 5, 'Freitag', 'Fr' UNION ALL
			 SELECT 6, 'Samstag', 'Sa' UNION ALL
			 SELECT 7, 'Sonntag', 'So' ) as Wochentage
		ON x.wochentagID = Wochentage.WochentagID
	RETURN 
END
GO

Somit kann man nun obige Ermittlung der gewünschten Datumswerte um die Ausgabe der Attribute erweitern, also zum Beispiel so:

declare @tage as [dbo].[typ_Datumswerte]
INSERT INTO @tage select * from dbo.f_alle_Datumswerte(convert(date, '1.1.2020', 104), convert(date, '31.12.' + convert(nvarchar(4), year(getdate())), 104))
select * from dbo.f_Datumsattribute(@tage)

Das Ergebnis sieht dann so aus:

Ergebnis Datumswerte

Nun müssen wir dieses SQL nur noch im SSAS ausführen lassen. Seit viele Neuerungen aus PowerBI in das Produkt SSAS einfließen, hat sich auch die Art und Weise geändert, wie man das Ergebnis von SQL-Statements im SSAS einbinden kann. Früher war es ja ganz einfach ein SQL-Statement anstelle einer Tabelle (bzw. View) zu verwenden, heute muss man wie folgt vorgehen [Die Idee dazu fand ich in Chris Webbs sehr gutem BI-Blog (hier).]:

Wenn man in Visual Studio eine neue Tabelle hinzufügt, wird im Hintergrund M-Code erzeugt, der in etwa so aussieht:

let
    Source = #"SQL/<host>;<Datenbank>",
    meineTabelle = Source{[Schema="dbo",Item="Beispiel"]}[Data]
in
    meineTabelle

Über die Properties der Tabelle > Quelldaten > (Zum Bearbeiten klicken) kann man diesen Code einsehen.

Zu verstehen ist der Code ja ganz leicht: Über Source wird die SQL-Connection auf dem Host <host> und Datenbank <Datenbank> geöffnet. Daraus wird im obigen Beispiel die Tabelle dbo.Beispiel geladen.

Wenn wir nun obiges SQL ausführen wollen, fügen wir zunächst auf den normalen Weg im UI eine neue Tabelle hinzu (welche ist vollkommen egal). Danach bearbeiten wir dieses M-Statement zu

let
    Source = #"SQL/<Host>;<Datenbank>",
    Entlassungsdatum = Value.NativeQuery(
        Source,
        "declare @tage as [dbo].[typ_Datumswerte]
INSERT INTO @tage select * from dbo.f_alle_Datumswerte(convert(date, '1.1.2020', 104), convert(date, '31.12.' + convert(nvarchar(4), year(getdate())), 104))
select * from dbo.f_Datumsattribute(@tage)"
    )
in
    Entlassungsdatum

Entscheidend ist also die Änderung von Source{}[Data] zu Value.NativeQuery().

SQL Server: spezifische Rollen für z.B. ETL-Verarbeitung

Motivation

In diesem Artikel möchte ich beschreiben, wie man eigene Datenbank-Rollen anlegt und diesen bestimmte Rechte gibt.

Dies wird zum Beispiel benötigt, wenn man ETLs im SQL Server Agent ausführen lässt, die natürlich gewisse Rechte auf der Datenbank benötigen. Ich sehe es oft, dass die ausführenden User dann dbo-Rechte bekommen, da man (oder der Integrator) bei der Installation der Jobs nicht weiß, welche Rechte sie genau benötigen. Best practice ist natürlich, dass die ausführenden User möglichst wenig Rechte bekommen.

Ich sehe es als Entwickler-Aufgabe an, die Rollen zu erstellen und mit den nötigen Rechten auszustatten.
Es ist dann die Aufgabe des Integrators, die entsprechenden User anzulegen und der Rolle zuzuordnen.

Den User kennt nämlich der Entwickler in der Regel nicht, welche Rechte er aber braucht, weiß der Integrator in der Regel nicht.

Umsetzung

In meinem Beispiel verwende ich eine Datenbank namens „Faktura“ und einen User namens „HOGWARTS\Tobias“

Das Anlegen einer Rolle ist ganz einfach – hier heißt sie „db_ETL_Verarbeitung

use Faktura
 go
 CREATE ROLE [db_ETL_Verarbeitung]
 GO

Nun müssen wir definieren, welche Rechte die Rolle haben soll.
In unserem Beispiel soll sie

  • aus allen Tabellen des Schemas dbo lesen können (SELECT)
  • die Tabelle dbo.Spesensaetze aktualisieren dürfen (UPDATE)
  • und die Funktion dbo.getDatumDate ausführen dürfen (EXECUTE)

Das sind natürlich nur Beispiele, aber wenn man die Syntax kennt, findet man im Internet noch alle möglichen Beispiele.

 GRANT SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]
 GO
 GRANT UPDATE ON dbo.Spesensaetze TO [db_ETL_Verarbeitung]
 GO
 GRANT EXECUTE ON [dbo].[getDatumDate] TO [db_ETL_Verarbeitung]
 GO 

Nun müssen wir nur noch den User dieser Rolle zuweisen:

ALTER ROLE [db_ETL_Verarbeitung] ADD MEMBER [HOGWARTS\Tobias]
 GO

Man kann Rollen auch verschachteln. So könnte man statt obigen GRANT SELECT auf dem Schema dbo die Rolle auch zum data reader machen. Deswegen entfernen wir erst das SELECT-Recht und fügen dann die Rolle hinzu:

REVOKE SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]
 GO
 ALTER ROLE [db_datareader] ADD MEMBER [db_ETL_Verarbeitung]
 GO

Vorteil

Dieses Vorgehen hat auch den Vorteil, dass man die Berechtigungen an den einzelnen Objekten (z.B. Execute auf Stored Procedures) nicht auf User-Ebene sondern auf Rollen-Ebene definiert. Das kann zum Beispiel bei Verwendung der Redgate-Tools mit eingecheckt werden. Beim Deployment auf die Produktivserver wird diese Berechtigung dann mit bereitgestellt. Nur die User-Zuordnung zur Rolle muss der Integrator/Administrator dann noch machen.

Somit ist die Entwickler- und Administratoren/Integratoren-Tätigkeit sauber getrennt.

Testen

Eine schöne Möglichkeit zu testen, ob die Rolle die richtigen Rechte hat, ist „EXECUTE AS LOGIN„.

Damit impersoniert man sich als der betreffende User (hier HOGWARTS\Tobias) und kann die Statements ausführen und sehen, ob die Rechte entsprechend vorhanden sind.

Das folgende Beispiel

execute as login = 'hogwarts\tobias'

 select * from Rechnungen

 update Spesensaetze
 set Gruppe_id = Gruppe_id
 where 1=0

 update Rechnungen
 set RechnungsJahr = RechnungsJahr
 where 1=0

 select [dbo].getDatumDate(20200101)

liefert als Ergebnis, dass die Statements 1, 2 und 4 ausgeführt werden, Statement 3 liefert

Meldung 229, Ebene 14, Status 5, Zeile 9
The UPDATE permission was denied on the object ‚Rechnungen‘, database ‚Faktura‘, schema ‚dbo‘.

Sichere Logging-Tabellen trotz @@identity

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man  könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier]  NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

Scope_Identity statt @@Identity

Beides sind Funktionen, um den Wert der Identity-Column nach dem Insert zu erhalten. Als ich SQL Server gelernt hatte (1997), hatte ich nur @@identity gekannt.

@@Identity hat aber einen entscheidenden Nachteil, aber dazu später mehr.

Nehmen wir an, wir haben eine Kunden-Tabelle mit Identity-Column KundeID:

CREATE TABLE [dbo].[Kunden](
    [KundeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Kunden] PRIMARY KEY CLUSTERED
(
    [KundeID] ASC
)
)

Wenn wir in diese Tabelle einen Kunden eintragen und dann das Ergebnis mit @@Identity oder SCOPE_Identity() abfragen, gibt es keinen Unterschied:

INSERT INTO Kunden SELECT ‚Martin‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

Beides liefert den Wert 1.

Wenn wir aber nun eine Logging-Tabelle hinzufügen, die ebenfalls eine Identity-Column hat:

CREATE TABLE [dbo].[Logging](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Und der Tabelle Kunden einen Insert-Trigger geben, der in diese Tabelle schreibt:

CREATE TRIGGER dbo.tr_i_Kunden
   ON  dbo.Kunden
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
   
    INSERT INTO Logging (LogText) VALUES (‚Kunde angelegt‘)
    INSERT INTO Logging (LogText) VALUES (‚und dann habe ich noch überprüft, ob er schon da ist‘)
    INSERT INTO Logging (LogText) VALUES (‚und noch irgendwas anderes auch gemacht‘)

END

Wenn wir jetzt einen Kunden anlegen, werden automatisch noch 3 Zeilen in die Logging-Tabelle geschrieben.

Da @@identity die letzte Identity-Column der aktuellen Connection zurückliefert, liefert dies den Identity-Wert aus der Logging-Tabelle.

Da SCOPE_Identity() die letzte Identity-Column des aktuellen Scopes (also der betrachteten Tabelle) in der aktuellen Connection zurückliefert, gibt das den Identity-Wert der Kunden-Tabelle.

Wir sehen das hier:

INSERT INTO Kunden SELECT ‚mein Schatz‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

liefert die Werte 3 für @@identity (da in Logging als letztes die Zeile 3 eingefügt wurde) bzw. 2 für Scope_identity().

Man sollte deshalb grundsätzlich SCOPE_IDENTITY() verwenden, da dies das in der Regel gewünschte Verhalten ist.

Es gibt noch eine Funktion IDENT_CURRENT(‘Tabellenname’). Diese liefert den letzten Identity-Wert für diese Tabelle – egal ob aus meiner Connection oder nicht. Damit sollte man also nicht versuchen, den gerade eingefügten Identity-Wert zu ermitteln. Wenn nämlich parallel eine andere Connection schreibt, hat man Pech gehabt.

Dynamisches Top n in SQL 2000

Ab SQL Server 2005 kann man TOP n über SELECT TOP (@n) … erledigen:

declare @n integer
set @n = 2

select top (@n) a
from
(select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t
order by a desc

Das liefert als Ergebnis

a
———–
6
5

(2 row(s) affected)

In früheren Versionen geht das leider nicht. Da hilft die Verwendung von set rowcount, das die Anzahl der zurückgegebenen Zeilen definiert:

declare @n integer
set @n = 2

set rowcount @n

select distinct  a
from
(select 1 as a
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t
order by a desc

Das liefert das gleiche Ergebnis wie oben.

Allerdings muss man danach wieder

set rowcount 0

absetzen, damit alle nachfolgenden SQL-Statements in dieser Session wieder alle Datensätze zurückliefern.

begin try in SQL-Batch-Statements

Es kommt öfter vor, dass man im Batch mehrere SQL-Statements ausführen möchte, sei es in einer Stored Procedure oder im Execute SQL-Task von SSIS.

Meistens hat man folgende Anforderung:

Läuft ein Statement auf einen Fehler, soll ein Rollback der Statements gemacht werden. Außerdem soll natürlich dem aufrufenden System der Fehler gemeldet werden.

Lässt man einen Batch einfach so laufen, wird dieses Ziel nicht erreicht, da im Fehlerfall auch die Statements nach dem Statement, das den Fehler verursacht, ausgeführt werden.

Beispiel:

set nocount on
select 1
select 1/0
select 2

liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

———–
2

In Versionen vor SQL Server 2005 musste man die Error-Variable auslesen, etwa so:

set nocount on
declare @fehler as int
set @fehler = 0
select 1
set @fehler = @fehler + @@error
select 1/0
set @fehler = @fehler + @@error
select 2
set @fehler = @fehler + @@error
if @fehler>0 begin
print ‚Ein Fehler ist aufgetreten‘
end

was folgendes Ergebnis liefert:

———–
1

———–
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.

———–
2

Ein Fehler ist aufgetreten

Das Problem ist, man muss die Zeile „set @fehler = @fehler + @@error“ nach jedem Statement schreiben, da sie nach jedem (!) Statement zurückgesetzt wird.

Leichter geht das in SQL 2005 mit begin try … end try – angelehnt an Konstrukte aus Programmiersprachen wie C#:

set nocount on
begin try

select 1
select 1/0
select 2

end try
begin catch
print ‚Ein Fehler aufgetreten‘
end catch

Am Ergebnis

———–
1

———–

Ein Fehler aufgetreten

sieht man, dass nach dem fehlerhaften Statement die Bearbeitung beendet wird.

Nun fehlen nur noch 2 Anforderungen:

Dass keine Datenmanipulation statt findet, erreicht man über eine Transaktion, die im catch-Block zurückgerollt (rollback) wird.

Dass der Aufruf dennoch den Fehler mitbekommt, erreicht man über einen raiserror.

Das fertige Skript sieht dann so aus:

set nocount on

begin tran
begin try

/* hier die eigentlichen SQL-Statements schreiben */
select 1
select 1/0
select 2

end try
begin catch
if @@trancount > 0 begin
rollback tran
end
declare @fehler_text nvarchar(4000)
set @fehler_text = ERROR_MESSAGE()
declare @fehler_severity int
set @fehler_severity = ERROR_SEVERITY()
declare @fehler_state int
set @fehler_state = ERROR_STATE()
RAISERROR (@fehler_text, — Message text.
@fehler_severity, — Severity.
@fehler_state — State.
)

end catch
if @@trancount > 0 begin
commit tran
end

Verhalten von Views bei Strukturänderungen der zugrundeliegenden Tabellen

Ein View, der mittels * alle Spalten einer verwendeten Tabelle zurückgibt, wie z.B.

CREATE VIEW dbo.testView
AS
SELECT u.*, k.name as Kunden_Name, k.AnzahlKinder
FROM umsatz u
INNER JOIN kunde k
ON k.kunde_id = u.kunde_id

verhält sich überaschend, wenn sich die Tabellenstruktur der zugrunde liegenden Tabellen ändert. Die Metainformationen aktualisieren sich nämlich nicht automatisch, was sogar zu Datentypsverletzungen führen kann.

Nehmen wir an, die Tabelle umsatz aus obigen Beispiel habe folgenden Inhalt:

Inhalt der Tabelle Umsatz

Damit liefert die Abfrage des Views

SELECT * FROM testView

folgende Daten:

Inhalt des TestView original

Wird nun in der Tabelle umsatz eine Spalte Rabatt hinzugefügt (und mit Werten gefüllt), liefert das SELECT überaschender Weise:

nach Hinzunahme der Spalte Inhalt des Views

Dabei fällt auf, dass die Spaltentitel die ursprünglichen Inhalte haben, d.h. Rabatt ist nirgends zu sehen, die Werte der Spalten sind aber falsch: In der Spalte Kundenname steht nun der Rabatt und im Feld AnzahlKinder (Datentyp ist eigentlich int!) steht nun der Kundenname.

Das Aktualisieren der Views geht einfach: Nach

exec sp_refreshview 'testview'

sieht wieder alles richtig aus.

Hat man mehrere Views, in einer Datenbank, kann man über

select 'exec sp_refreshview ''' + name + '''' from sysobjects
where xtype = 'V'

sich die notwendigen SQL-Statements automatisch generieren lassen, die man dann einfach ausführt.

Einige Hinweise:

  • Views können natürlich verschachtelt sein, d.h. ein View greift auf einen anderen View zu. Dann müssen die Refresh-View-Statements in der richtigen Reihenfolge oder einfach mehrfach ausgeführt werden.
  • Clustered / Indexed Views können nicht refresht werden – das Statement liefert einen Fehler und muss aus der Liste der auszuführenden Statements gelöscht werden. Das Aktualisieren eines indizierten Views ist aber auch unnötig, da die zugrunde liegenden Tabellen nicht geändert werden können (da der View mit Schema Binding angelegt wurde)

SQL: Dateiname extrahieren

Hier ein schönes Beispiel, in der man die SQL-Server-String-Funktion reverse zum Umdrehen eines Strings (Hallo –> ollaH) sinnvoll einsetzen kann.

Wir nehmen an, dass in einer Tabelle Dateinamen voll qualifiziert stehen, also z.B. c:tempblogtest.txt.

Nun sei die Aufgabe, den Dateinamen (hier test.txt) zu ermitteln. Dazu muss das letzte Auftreten von gefunden werden und der String rechts davon ermittelt werden.

Dies geschieht so:

case when charindex(“, Dateiname) > 0 then
substring(Dateiname, len(dateiname)-charindex(“, reverse(Dateiname))+2, len(dateiname)) else Dateiname end

Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.

SQL Server: zufällige Auswahl von n Zeilen einer Tabelle

Bei einer Aufgabe zur Performance-Steigerung einer SQL Server 2008-Applikation (über die ich später berichten werde), war die erste Aufgabe, ein Test-Szenario aufzubauen, anhand dessen die Abfragezeiten verglichen werden konnten.

Der erste Schritt war dazu die Auswahl 100 beliebiger Sätze aus einer Tabelle. Dabei zeigte mir ein Kollege folgende super einfache Möglichkeit:

select top 100 * from sysobjects order by newid()

[Natürlich muss sysobjects durch die entsprechende Applikations-Tabelle ersetzt werden 🙂 ]