Microsoft SQL Server
Diese Seite beschreibt einige nützliche Tipps und Tricks im Umgang mit dem 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.