code it

Martins Tech Blog

SQL Server 2012 und die Format-Funktion

Eigentlich ist Formatierung von Daten in meinen Augen nicht wirklich eine Aktion, die zwingend von der Datenbank durchgeführt werden muss. Aber wie es häufig ist, so gibt es auch hier Anwendungsszenarien in denen dies ein durchaus sinnvoller Weg ist.

In den bisherigen Versionen von SQL Server gestaltete sich dies aber immer etwas schwierig, denkt man nur an verschiedene Datumsformatierungen. Die Funktion CONVERT bot zwar schon begrenzt die Möglichkeit, hier Einfluss zu nehmen, aber wenn man bedenkt, dass für Datumsformate die Auswahlmöglichkeit auf "mit Jahrhundert oder ohne" begrenzt war, so schaute man schon etwas neidisch auf die Möglichkeiten, die beispielsweise die .NET CLR mit string.Format bot.

Mehr...

Temporäre Stored Procedures in SQL-Server

Um sehr rechenintensive Operationen im SQL-Server durchzuführen, bietet es sich an, zu geeigneten Zeitpunkten Zwischenergebnisse zu generieren, diese zu speichern und dann auf Basis dieser Ergebnisse weiterzuarbeiten. Nun besitzt aber nicht jeder Benutzer Berechtigungen, in Datenbanken Tabellen nach Lust und Laune zu erstellen oder in bestehenden Tabellen Datensätze anzufügen, zu löschen oder zu manipulieren.

Zur Lösung dieses Problems bieten sich temporäre Tabellen an. Mit Hilfe des Zeichens # vor dem Tabellennamen wird dem Server signalisiert, dass keine physische Tabelle in der Datenbank erstellt werden soll, sondern vielmehr eine temporäre. Natürlich hat diese auch eine physische Repräsentation (in der tempdb) aber das soll hier keine Rolle spielen. Vorteil dieser Tabellen ist, dass sie nur im aktuellen Kontext gültig sind und über keinerlei Berechtigungseinschränkungen verfügen. Über die Anzahl der # wird definiert, ob sie nur in der aktuellen Session oder global gültig sein soll.

SELECT [Id], [Firstname], [Lastname], [Street], [Zipcode], [City]
INTO #MyAddressTable
FROM MyComplexLongRunningAddressView

/* do further calculations */

Doch dieses Feature gibt es nicht nur Tabellen. Nach dem gleichen Muster können auch nur für den aktuellen Kontext gültige Stored Procedures erstellt werden.

CREATE PROCEDURE #DoSomeComplexCalculations(@id as int)
AS
BEGIN
 /*complex calculations*/
END
GO

EXEC #DoSomeComplexCalculations 1
EXEC #DoSomeComplexCalculations 2

Die Prozedur wird automatisch wieder gelöscht, wenn die Session beendet wird. Sicher: In den meisten Fällen wird es am sinnvollsten sein, richtige Stored Procedures zu erstellen, die dann auch allen Benutzern zur Verfügung stehen und nicht ständig wieder neu erstellt werden müssen - aber ich bin mir sicher, dass es auch den ein oder anderen Anwendungsfall gibt, in dem es sinnvoll ist, auf diese temporären Objekte zurückzugreifen.

Spatial Results View

Für die Verwendung der sogenannten "spatial datatypes" geometry und geography gibt es im SQL-Server 2008 eine weitere Ergebnisanzeige. Diese ist in meinen Augen sehr sinnvoll, da man sich hiermit die Ergebnisse visualisieren kann und damit eine bessere Vorstellung von den Werten bekommt.

Probiert man das Ganze mit dem geography-Datentyp aus, wird man feststellen, dass die Anzeige analog zum geometry-Datentyp vor einem weißen Hintergrund ohne Referenzwert ist. Das macht die Vorstellung hier etwas komplizierter. Auf Beginning Spatial hab ich einen interessanten Eintrag gefunden. Hier gibt es eine Dataset mit den Ländergrenzen, das man hinter sein Ergebnis legen kann.

Verwaiste SQL-Logins auf einem SQL-Server ermitteln

Auf einem SQL-Server kann es vorkommen, dass es Logins gibt, zu denen es gar keine User-Zuordnung mehr in Datenbanken gibt. Das kann mehrere Ursachen haben. Erste Ursache kann sein, dass dem Login einfach der Zugriff auf eine Datenbank entzogen wurde. Zweite Ursache kann sein, dass die Datenbanken auf die der Nutzer mal Zugriff hatte, nun vom Server gelöscht wurden - entweder weil sie obsolet waren oder weil sie nun auf einem anderen Server liegen. Diese verwaisten Logins dümpeln nun noch auf dem Server herum (Security -> Logins).

Nun kann es ganz interessant und auch wichtig sein, herauszufinden, wie es um den Server in Bezug auf dieses Thema bestellt ist, um ggf. Aufräumaktionen zu starten. Besonders bei vielen Logins oder bei vielen Datenbanken kann es dann aber schon zeitaufwändig werden, bei jedem Login in die User-Mappings zu schauen und dort zu prüfen, ob eine Datenbank angehakt ist.

Für SQL-Logins kann das folgende Skript schnell eine Übersicht generieren:

CREATE TABLE #OrphanedLogins (sid varbinary(85) NOT NULL)

-- get all sql logins except of sa
INSERT INTO #OrphanedLogins (sid)
SELECT sid FROM sys.server_principals WHERE type = 'S' AND sid <> 0x01

-- iterate trough all databases and determine if one of the logins exists there
-- delete found sids from temp table
exec sp_msforeachdb 'DELETE #OrphanedLogins
FROM sys.database_principals dp INNER JOIN #OrphanedLogins ol 
ON dp.sid = ol.sid'

-- get the result
SELECT * FROM sys.server_principals sp
INNER JOIN #OrphanedLogins ol ON
sp.sid = ol.sid

Die Lösung für NT-Nutzer sieht so ähnlich aus. Aber Achtung: Logins können auch sysadmin-Funktionen haben und dann kann es gerechtfertigt sein, dass diese keine Datenbankzuordnung haben. Aus diesem Grund sollte anhand des Ergebnisses nicht blind gelöscht werden.

Duplikate in SQL-Server-Tabellen löschen

Ich bin grad über einen sehr interessanten Blog-Eintrag gestolpert, der sich mit dem Thema Löschen von Datensatzduplikaten aus SQL-Server-Tabellen beschäftigt. Weil ich den Ansatz wirklich cool finde, besonders weil er überraschend einfach ist, möchte ich ihn euch nicht vorenthalten.

Zunächst wird mal eben schnell eine Demo-Tabelle mit doppelten Datensätzen angelegt.

Und nun kommt der Trick: Mittels PARTITION und ROW_NUMBER() werden die jeweils identischen Datensätze durchnummeriert.

SELECT Column1,Column2,
ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1) AS DuplicateCount
FROM DuplicateRecordTable

Mit diesem Wissen können nun alle die Datensätze gelöscht werden, deren laufende Nummer größer als 1 ist:

WITH CTE (Column1,Column2, DuplicateCount)
AS
(
SELECT Column1,Column2,
ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1) AS DuplicateCount
FROM DuplicateRecordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1

Alle Tabelleninhalte einer Datenbank löschen

Während der Entwicklung von Anwendungen kommt es gelegentlich vor, dass man die eingegebenen Test- oder Demodaten wieder löschen möchte. Hier stellt sich häufig das Problem, dass referentielle Integrität ein stupides "DELETE FROM ..." für jede Tabelle verhindert. Das Skript sollte also die referentielle Integrität beibehalten, alle Daten aus den Tabellen löschen und Identity-Spalten wieder zurücksetzen.

Während der Entwicklung von Anwendungen kommt es gelegentlich vor, dass man die eingegebenen Test- oder Demodaten wieder löschen möchte. Hier stellt sich häufig das Problem, dass referentielle Integrität ein stupides "DELETE FROM ..." für jede Tabelle verhindert. Das Skript sollte also die referentielle Integrität beibehalten, alle Daten aus den Tabellen löschen und Identity-Spalten wieder zurücksetzen.

Schlüssel für die Lösung ist die Systemprozedur sp_MSForEachTable, mit der man ein SQL-Statement für jede Tabelle einer Datenbank ausführen kann: 

  1. Zunächst wird für alle Tabellen die Prüfung der referentiellen Integrität deaktiviert. 
  2. Danach wird ein DELETE-Statement ausgeführt. Dabei ist es wichtig die Option "Quoted Identifiers" zu deaktivieren, sonst würde es bei der Ausführung zu folgender Meldung kommen:
    DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
  3. Das dritte Statement aktiviert die Prüfung der referentiellen Integrität wieder.
  4. Im Anschluss daran werden die Identity-Spalten noch zurückgesetzt.
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
GO  

EXEC sp_MSForEachTable '
 SET QUOTED_IDENTIFIER ON  
 DELETE FROM ?  
'  
GO  
  
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
GO  
  
EXEC sp_MSForEachTable '   
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1   
DBCC CHECKIDENT (''?'', RESEED, 0)   
'   
GO
 

Mehrere Datensätze mit nur einem INSERT-Statement einfügen

Hier noch ein kleines Feature von SQL Server 2008, das ich unter dem Punkt syntaktischer Zucker ablegen möchte: Mit nur einem INSERT-Statement können mehrere Datensätze eingefügt werden. Dazu werden einfach mehrere Sätze im VALUES-Teil angegeben.

-- create demo table
CREATE TABLE Mitarbeiter(
  Id INT IDENTITY(1,1),
  Vorname NVARCHAR(35),
  Nachname NVARCHAR(35),
  Abteilung  NVARCHAR(50)
)
-- insert statement with multiple value clauses
INSERT INTO Mitarbeiter(Vorname, Nachname, Abteilung) VALUES 
('Meier','Klaus','HR'),
('Schmidt','Dirk','R&D'),
('Schneider','Tobias','R&D')

Textdateien mit SQL-Server schreiben

Ruft man Inhalte einer NVARCHAR(MAX)- oder NTEXT-Spalte im Management-Studio mit einem SELECT-Query ab, so wird früher oder später der Text abgeschnitten. Das ist insbesondere dann nervig, wenn sich die relevante Information am Ende befindet oder der Inhalt ein XML ist, das dadurch nicht mehr verwendbar wird. Aus diesem Grund hab ich nach einer Möglichkeit gesucht, solche Inhalte in Textdateien abzuspeichern, um den Inhalt dann in einem Texteditor anschauen zu können. Ein recht komfortabler Weg hierzu sind OLE Automation Procedures, die allerdings zuerst im SQL-Server aktiviert werden müssen:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Zur Vereinfachung leg ich mir dann eine Gespeicherte Prozedur an, die das Schreiben der Texte in die Textdatei übernimmt:

CREATE PROCEDURE spWriteStringToFile (@String NVARCHAR(MAX), @Path VARCHAR(255), @Filename VARCHAR(100))
AS
DECLARE @objFileSystem int,
@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
GO

Den Aufruf der Prozedur ist recht trivial: Der Gespeicherten Prozedur werden als Parameter der zu schreibende Text, der Pfad und der Dateiname übergeben:

DECLARE @message nvarchar(max)
SELECT @message = CAST([Field] as nvarchar(max)) FROM [dbo].[Table] WHERE [Id] = 1
exec spWriteStringToFile @message, 'C:\', 'output.txt'

Hierarchische Daten löschen

Möchte oder kann man den mit SQL Server 2008 eingeführten Datentyp hierarchyid nicht verwenden, nutzt man meist eine Tabellenstruktur mit den Spalten Id (Identifier des aktuellen Datensatzes) und ParentId (Identifier des Eltern-Datensatzes), um hierarchische Daten abzubilden. Dabei werden diese beiden Spalten durch eine Selbstreferenz miteinander verbunden.

 

Da es sich hier um eine klassische Eltern-Kind-Beziehung handelt, ist man nun versucht, die Referenz mit einer Löschweitergabe (ON DELETE CASCADE) zu erzeugen.

CREATE TABLE [Test].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentId] [int] NULL,
	[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
),
CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentId])
REFERENCES [Test].[Category] ([Id]) ON DELETE CASCADE
)
GO

Um es vorweg zu nehmen: Dieses Statement schlägt fehl mit der Meldung:

Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_Category_Category' on table 'Category' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Es bleibt also erstmal nichts anderes übrig als die Tabelle ohne CASCADE zu erstellen, in dem Wissen, dass ein Löschversuch eines Elements mit Kindelementen zwangsläufig zu einem Fehler führt, weil dadurch die Kindelemente verwaisen würden.

Die Lösung für dieses Problem ist ein INSTEAD-OF-DELETE-Trigger mit einer rekursiven Abfrage:

CREATE TABLE [Test].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentId] [int] NULL,
	[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
),
CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentId])
REFERENCES [Test].[Category] ([Id])
)
GO

CREATE TRIGGER CategoryDeleteTrigger ON [Test].[Category]
INSTEAD OF DELETE
AS
IF @@ROWCOUNT = 0
RETURN;

WITH x
AS
(
	SELECT Id 
	FROM deleted
	UNION ALL
	SELECT c.Id 
	FROM [Test].[Category] c
	INNER JOIN x on x.Id = c.ParentId
)

DELETE [Test].[Category]
WHERE Id IN (SELECT Id FROM x)
GO

Ein kleiner Test zeigt, dass es wie erwartet funktioniert:

Das DELETE-Statement löscht sowohl den betroffenen Datensatz als auch die 5 durch Rekursion ermittelten Datensätze.