Microsoft SQL Server

Kopieren einer Tabelle aus einer anderen Datenbank

      SELECT * INTO AdventureWorks.dbo.CustomersTemp
      FROM Northwind.dbo.Customers

Anzeigen von Werten einer Spalte vom Typ Text

Spalten vom Typ Text sind wie Blobs. Man kann Texte belieber Länge bis zu einer Kapazität von 1 GB darin speichern. Allerdings werden diese Typen in neuen Versionen vom SQL Server nicht mehr unterstützt und sollten somit nicht mehr verwendet werden. Zusätzlich kann man nur bestimmte Funktionen darauf anwenden und bei Abfragen wird der Inhalt nach einer bestimmten Länge abgeschnitten.

Um den gesamten Inhalt einer Spalte vom Typ Text auf dem SQL-Server zu ermöglichen muss man den SQL Server Management Studio 2005 öffnen und dort folgendes Select-Statement absetzen:

      select SUBSTRING(<textfeld>, 0, datalength(<textfeld>))
      from <tabelle> where <Bedingung>

Für neue Projekte sollten stattdessen die Datentypen varchar(max) und nvarchar(max) verwendet werden. (Gemäß der Information von dieser Microsoft Seite, Stand 2013.11.14)

DB-Datei verkleinern

Um das Transaction-Log-File zu verkleinern (shrinken) kann man folgendermaßen vorgehen:

rechte Maus auf Datenbank - Eigenschaften - Optionen - Modell auf Einfach (Simple) stellen
Query-Analyzer der betreffenden Datenbank öffnen und den Befehl
```
dbcc shrinkfile('<Logischer Filename der Transaction-Log-Datei>',50)
```
ausführen.
Danach in Eigenschaften der DB die Optionen wieder auf Vollständig (Full) stellen.

Das ganze kann auch gescripted und in einem Job aufgerufen werden:

      use <Datenbank>
      alter database <Datenbank> set recovery simple
      dbcc shrinkfile('<Logischer Filename der Transaction-Log-Datei>',50)
      alter database <Datenbank> set recovery full

Datenbank offline nehmen

Normalerweise kann man im SQL Server Management Studio in der Tree-Ansicht im Kontextmenü einer Datenbank unter Tasks einfach den Eintrag "Take Offline" auswählen. Wenn aber noch eine Verbindung von einer Applikation zur DB besteht, dann funktioniert das nicht.

Mit dem befehl EXEC sp_who2 werden alle Verbindungen angezeigt. Um eine Verbindung zu lösen kann KILL <SPID> (zb. KILL 64) eingegeben werden. Danach kann die DB offline genommen werden.

Alternativ kann man auch den Activity Monitor starten. (Im SQL Server Management Studio in der Tree-Ansicht das Kontextmenü vom Root-Eintrag öffnen, der einen SQL Server repräsentiert.) Dort kann man unter Processes nach Datenbanken filtern und die einzelnen Verbindungen mit rechter Maustaste -> Kill Process schließen.

In folgendem Fall hat diese Methode allerdings nicht geholfen: Eine Openquery Abfrage wurde von einem Job auf einen Linked Server abgesetzt. Der Linked Server wurde während der Abfrage offline genommen. Der Job wartete immer auf ein Feedback vom Linked Server und ließ sich nicht mehr stoppen. In diesem Fall musste das Service des SQL Servers neu gestartet werden. Danach konnte der Job wieder wie gewohnt ausgeführt werden.

Unicode Daten einfügen

Zuerst ist für den Spaltentype nchar bzw. nvarchar zu wählen, damit Unicodezeichen darin gespeichert werden können.

Beim Insert muss vor dem Unicode-Wert ein N stehen. Beispiel:

      INSERT INTO TranslationHelp (Label, Chinese)
      VALUES ('Order acknowledgement', N'订单确认');

Zufällige Datensätze wählen

Wenn man einen zufälligen Wert einer Tabelle abfragen will, dann habe ich das so gelöst, indem ich die Tabelle mit einer Id vom Type Identity(1,1) versehen habe. Somit sollte jeder Eintrag eine Id beginnend mit 1 aufweisen. Danach kann die Funktion rand() verwendet werden, um eine beliebige Id zu berechnen und diese dann in der WHERE Bedingung zu vewenden.

      SELECT <Fields>
      FROM <Table>
      WHERE id = (
        SELECT cast(max(Id)*rand() as int)+1 FROM <Table>
      )

Zeilenumbrüche ersetzen

Eine Sache, die man beim Export von Textfeldern ins Excel immer wieder braucht:

      SELECT
        REPLACE(
          REPLACE(
            <Field>, CHAR(13), '<br/>'
          ), CHAR(10), '<br/>'
        )
      FROM <Table>

Ich ersetze dabei die Zeilenumbrüche meist durch ein anderes Zeichen, damit die Information nicht verloren geht. Die Lösung habe ich hier gefunden.

Manchmal hat man es auch noch mit dem Datentyp text zu tun, der die replace Funktion nicht erlaubt. In diesem Fall führe ich dann noch eine cast Operation durch. Am Ende sieht das ganze dann so aus:

      SELECT
      REPLACE(
        REPLACE(cast(<Field> as varchar(8000)), CHAR(13), '<br/>'),
        CHAR(10), '<br/>'
      )
      FROM <Table>

Auf Oracle Datenbanken muss man hier statt CHAR den Tag CHR verwenden. Greift man z.B. über Openquery auf eine Oracle Datenbank zu, dann könnte die Abfrage wie folgt aussehen:

      SELECT * FROM OPENQUERY(<LinkedServer>, '
        SELECT
          REPLACE(
            REPLACE(
              <Field>, CHR(13), ''<br/>''
            ), CHR(10), ''<br/>''
          )
        FROM <Table>
      ')

Das folgende Skript ersetzt Zeilenumbrüche auf dem Linked Server durch <br/> Tags und Tabulator-Schritte durch <tab/> Tags. Dabei wird aber die Kombination von CHR(13) und CHR(10) zuerst ersetzt um unnötige Verdoppelungen von Zeilenumbrüchen zu vermeiden.

      SELECT * FROM OPENQUERY(<LinkedServer>, '
        SELECT
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  <Field>, CHR(13) || CHR(10), ''<br/>''
                ), CHR(13), ''<br/>''
              ), CHR(10), ''<br/>''
            ), CHR(9), ''<tab/>''
          )
        FROM <Table>
      ')

SQL Skripte immer im SVN halten

Es gibt für mich mehrere Gründe, warum man sich nicht darauf verlassen sollte, SQL-Skripte nur auf dem SQL-Server zu speichern. Ich halte die Skripte immer im SVN, was folgende Vorteile für mich gebracht hat:

Zum einen hat man die Historie, wie sich das Skript geändert hat. (Und man ist nicht gezwungen die Historie manuell in etwaigen Kommentaren zu warten.)
Manchmal wirft der SQL-Server einen Fehler, wenn man das Skript für eine Stored Procedure in einem Abfragefenster öffnen will. Beispielsweise:

Syntaxfehler in 'TextHeader' von Stored Procedure '...'. (Microsoft.SqlServer.Smo)

Anstatt sich dann damit zu beschäftigen, wie man das Skript wieder anzeigen kann, greift man einfach auf den Code im SVN zurück. (Wenn man googelt findet man auch eine Lösung dafür - zum Beispiel hier - aber warum soll man sich das antun, wenn man es vermeiden kann?)
In Unternehmen, bei denen die Kommunikation anscheinend nicht 100%ig klappt, kann auch folgendes passieren (Ein kurzer Erfahrungsbericht!):
- Der Programmierer arbeitet an neuen Skripten auf der Datenbank.
- Gleichzeitig wird eine Datenbank-Migration auf einen neuen Server durchgeführt. Dabei wurden aber die Funktionen schon einige Tage vor der Migration eingespielt, ohne den Programmierer darauf hinzuweisen. Nur die Daten wurden am Tag der Migration kopiert. D.h. die Daten sind aktuell, aber Stored Procedures und Sichten waren danach veraltet oder fehlten.
- Der Programmierer, der die Sourcen im SVN hat, hat kein großes Problem. Der Programmierer, der sich auf den SQL-Server verlässt, sollte sich mit dem Migrations-Team in Kontakt setzen.

Zeilennummern und Alias in WHERE Bedingung verwenden

Meine Aufgabenstellung war, mit einer Applikation jeweils nur 20 Datensätze anzuzeigen und dann eine Seitenauswahl zu erstellen, um im Suchergebnis blättern zu können. Bevor ich dies durchgeführt hatte, wurden lediglich die ersten 20 Zeilen mit TOP angezeigt.

        SELECT TOP 20
          Feld1, Feld2, Feld3
        FROM Tabelle1
        LEFT JOIN Tabelle2 ON ...
        WHERE Bedingung1 AND Bedingung2 AND ...
        ORDER BY SortierFeld1, SortierFeld2

Im Endeffekt mussten an der Abfrage nur folgende Änderungen vorgenommen werden:

Die Sortier-Bedingung und die TOP Anweisung musste entfernt werden.
Mit die Funktion ROW_NUMBER() OVER (ORDER BY ...) wird eine Zeilennummer, die mit 1 beginnt über das gewünschte Sortierkriterium definiert. (Leider kann man diese Funktion nicht gleich direkt im WHERE verwenden. Und leider liefert eine Abfrage auf dem SQL Server nicht gleich automatisch eine Rownum-Spalte wie bei einer Oracle Datenbank.)

Danach musste die Abfrage in ein Subselect geschachtelt werden. So konnte ich dann über eine WHERE-Bedingung die neu erstellte Spalte Rownum direkt ansprechen.

        SELECT * FROM (
          SELECT
            Feld1, Feld2, Feld3,
            ROW_NUMBER() OVER (ORDER BY SortierFeld1, SortierFeld2) as RowNum
          FROM Tabelle1
          LEFT JOIN Tabelle2 ON ...
          WHERE Bedingung1 AND Bedingung2 AND ...
        ) AS MyTable
        WHERE RowNum >= 1 AND RowNum <= 20
        ORDER BY RowNum

Das Blättern habe ich dann im C# Code realisiert, in dem ich die Start Id und die End Id für die Rownum mit folgender Berechnung gesetzt habe. Im Folgenden sieht man die Berechnung dieser Ids und das Verschachteln der ursprünglichen Abfrage, die in der Variable pQuery enthalten ist. m_iPageId enthält die Seiten-Id, die angezeigt werden soll, mit 1 beginnend.

        int iStartEntry = (m_iPageId-1)*20 + 1 ;
        int iEndEntry = m_iPageId*20;

        string pNewQuery = @"
          SELECT *
          FROM (" + pQuery + @") as MyTable
          WHERE
            RowNum >= " + iStartEntry + " AND
            RowNum <= " + iEndEntry + @"
          ORDER BY RowNum
        ";

Mit dieser Verschachtelung kann man also auch Spalten, die durch Funktionen erstellt wurden, in WHERE-Bedingungen über den Spaltennamen ansprechen.

Auf diese Lösung bin ich durch folgende Webseiten gestoßen:

Werten von mehreren Zeilen miteinander verbinden

Ich hatte schon mehrmals den Fall, wo für Benutzer eine 1 zu n Zuordnung von einem bestimmten Datentyp vorlag.

Im folgenden Beispiel sind Benutzer mehreren Bereichen (Areas) zugeordnet. Die Daten liegen in folgenden Tabellen vor:

Tabelle Areas
(speichert die Bereiche)

AreaId	AreaName
1	Interner Bereich
2	Dokumentation
3	Projekt 1

Tabelle AreaPermissions
(speichert die Zuordnung der Benutzer zu Bereichen)

UserName	AreaId
wanderinformatiker	1
wanderinformatiker	2
wanderinformatiker	3
guest	2

In einer Übersicht will ich dann folgende Ausgabe anzeigen:

Benutzer	Bereiche
wanderinformatiker	Interner Bereich, Dokumentation, Projekt 1
guest	Dokumentation

Das kann mit folgender Abfrage auch im SQL auf dem SQL Server erreicht werden:

      select distinct
        t3.UserName, ltrim(stuff((
          select
            ', ' + t2.AreaName
          from AreaPermissions t1
          inner join Areas t2 on t1.AreaId = t2.AreaId
          where t1.UserName = t3.UserName
          For XML PATH ('')
        ), 1, 1,'')) as AreasAssignedTo
        from AreaPermissions t3

Wie kommt man da drauf?

- 1 -

Zunächst sollte man sich mal die Abfrage zusammenstellen, welche die Inhalte der beiden Spalten beinhält.

      select t1.UserName, t2.AreaName
      from AreaPermissions t1
      inner join Areas t2 on t1.AreaId = t2.AreaId

UserName	AreaName
wanderinformatiker	Interner Bereich
wanderinformatiker	Dokumentation
wanderinformatiker	Projekt 1
guest	Dokumentation

- 2 -

Im nächsten Schritt sollte man die Werte der Zeilen verknüpfen. Das wird mit folgender Abfrage erreicht. (Man fügt ein Trennzeichen ein und verwendet nur mehr jene Spalten, die verbunden werden sollen. For XML PATH ('') verbindet alle Zeilen zu einem Wert.)

      select ', ' + t2.AreaName
      from AreaPermissions t1
      inner join Areas t2 on t1.AreaId = t2.AreaId
      For XML PATH ('')

Dies ergibt folgenden String-Wert:
, Interner Bereich, Dokumentation, Projekt 1, Dokumentation

- 3 -

Danach verwendet man diese Abfrage als Subquery, um einen Spaltenwert anzuzeigen. Der zweite Spaltenwert ist der Benutzername. Die Abfrage wird dann auf die Tabelle abgesetzt, in der die Zuordnung durchgeführt wurde. Zusätzlich wird dann in der Subquery der Benutzername als Bedingung gesetzt.

      select distinct
        t3.UserName, (
          select ', ' + t2.AreaName
          from AreaPermissions t1
          inner join Areas t2 on t1.AreaId = t2.AreaId
          where t1.UserName = t3.UserName
          For XML PATH ('')
        ) as AreasAssignedTo
      from AreaPermissions t3

Benutzer	Bereiche
wanderinformatiker	, Interner Bereich, Dokumentation, Projekt 1
guest	, Dokumentation

- 4 -

Im letzten Schritt muss man nur noch den Beistrich vor dem Wert loswerden. Dies wird mit der Funktion stuff erreicht. Damit werden Zeichen beginnend an einer bestimmten Position mit einer bestimmten Länge ersetzt. Hier wird das erste Zeichen (Länge 1) mit dem Leerstring ersetzt. Zusätzlich wird der Wert noch getrimmt, um das Leerzeichen loszuwerden. (Alternativ könnte man auch nur stuff(Subquery, 1, 2, '') aufrufen, ohne die Trim-Funktionalität.)

      select distinct
        t3.UserName, ltrim(stuff((
          select
            ', ' + t2.AreaName
          from AreaPermissions t1
          inner join Areas t2 on t1.AreaId = t2.AreaId
          where t1.UserName = t3.UserName
          For XML PATH ('')
        ), 1, 1,'')) as AreasAssignedTo
        from AreaPermissions t3

oder

      select distinct
        t3.UserName, stuff((
          select
            ', ' + t2.AreaName
          from AreaPermissions t1
          inner join Areas t2 on t1.AreaId = t2.AreaId
          where t1.UserName = t3.UserName
          For XML PATH ('')
        ), 1, 2,'') as AreasAssignedTo
        from AreaPermissions t3

Auffinden der langsamsten Abfragen

Um herauszufinden, welche Abfragen auf einem SQL Server am längsten brauchen, kann man folgende Abfrage ausführen:

        SELECT
          creation_time
          ,last_execution_time
          ,total_physical_reads
          ,total_logical_reads
          ,total_logical_writes
          , execution_count
          , total_worker_time / 100000 as total_worker_time_in_sec
          , total_elapsed_time / 100000 as total_elapsed_time_in_sec
          , total_elapsed_time / execution_count / 100000
              as avg_elapsed_time_in_sec
          ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
           ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
              - qs.statement_start_offset)/2) + 1) AS statement_text
        FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        where total_elapsed_time / execution_count / 100000 > 100
        order by total_elapsed_time / execution_count desc

Man muss dabei die WHERE Bedingung so anpassen, dass eine entsprechende Anzahl an Abfragen angezeigt wird. Danach kann man sich daran machen und diese Abfragen optimieren.

Um eine genauere Auskunft über die Laufzeit in Millisekunden zu erhalten, kann man folgendes Script anwenden.

      set statistics time on

      -- Die Abfrage

      set statistics time off

Die Lösung für das Auffinden der langsamsten Abfragen habe ich hier gefunden. Den Ansatz für das genaue Stoppen der Abfragedauer habe ich hier gefunden.

Erstellen einer neuen GUID

Mit folgendem Skript kann man eine neue GUID erstellen, um sie beispielsweise als neuen Unique Key Wert zu verwenden. (Die Lösung dazu habe ich hier gefunden.)

        SELECT NEWID()
      
        --Mögliches Ergebnis: CA2367C2-3BC3-46BE-B9F4-4385B7F4CF40

Einen BLOB in ein DB-Feld einfügen

Mit folgendem Skript kann man die Datei C:\Test\Test1.pdf als BLOB in ein Tabellenfeld übernehmen. (Die Lösung dazu habe ich hier bzw. auch hier gefunden.)

        UPDATE <tableName>
        SET <blobField> =
          (SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a) 
        WHERE <condition>

Einen BLOB als Text anzeigen lassen

Gibt es in einer Tabelle eine BLOB Spalte so wird mit einer normalen Abfrage auf diese Spalte der binäre Inhalt angezeigt, auch wenn es sich um einen XML-Text handelt:

        SELECT <blobField> FROM <tableName>

Mit folgender Abfrage kann man sich dann den BLOB-Inhalt als Text anzeigen lassen:

        SELECT convert(varchar(max), convert(varbinary(max),<blobField>)) FROM <tableName>

Die Lösung dazu habe ich in diesem Stackoverflow-Artikel gefunden.

Eine Datenbank unter anderem Namen ins MS SQL Studio einhängen

Ausgehend von einem Datenbank-Backup (bak-Datei) soll eine Datenbank unter anderem Namen eingespielt werden. Unter der Annahme, dass die gegebene Datenbank den Namen DB hat und die Datei C:\DB.bak zum einspielen vorliegt, geht man dabei wie folgt vor:

Man findet zunächst heraus, wie die Datenbank-Dateien heißen:

        Restore filelistonly from DISK = N'C:\DB.bak'

        -- Ergebnis: 
        -- DB
        -- DB_log

Im nächsten Schritt spielt man die Datenbank unter dem Namen DB_new in das MS SQL Management Studio ein:

        USE [master]
        GO
        RESTORE DATABASE [DB_new] FROM  DISK = N'C:\DB.bak'
        WITH  FILE = 1, 
        MOVE N'DB' TO N'C:\DB_new.mdf', 
        MOVE N'DB_log' TO N'C:\DB_new_log.ldf',
        NOUNLOAD,  STATS = 10

Dabei werden als neue Datenbank-Dateien C:\DB_new.mdf und C:\DB_new_log.ldf angelegt.

Die Lösung dazu habe ich hier gefunden.

Einen Foreign Key anhand seines Namens finden

Wenn bei einem Programm die Verletzung eines Foreign Keys ausgegeben wird, so wird oft nur dessen Namen angegeben. Mit folgender Abfrage kann man herausfinden, auf welcher Tabelle der Foreign Key gesetzt ist.

        SELECT
            FK_Table = FK.TABLE_NAME,
            FK_Column = CU.COLUMN_NAME,
            PK_Table = PK.TABLE_NAME,
            PK_Column = PT.COLUMN_NAME,
            Constraint_Name = C.CONSTRAINT_NAME
        FROM
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
            ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
            ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
            ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
        INNER JOIN (
                    SELECT
                        i1.TABLE_NAME,
                        i2.COLUMN_NAME
                    FROM
                        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                    WHERE
                        i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                  ) PT
            ON PT.TABLE_NAME = PK.TABLE_NAME
        WHERE C.CONSTRAINT_NAME = '<Name des ForeignKeys>'