SQL Server & ASP .NET Blog

Interessantes und Wissenswertes

SQL Server Tools

Hier eine Liste von Tools für den SQL Server und rings herum, die sich bei mir im Laufe der Zeit angesammelt haben. Ich werde die Liste im Laufe der Zeit erweitern…

SQL Server Database Copy Tool
Das beste ;-) Tool um Datenbanken von einem SQL Server zu einem anderen zu kopieren. Mehr dazu habe ich in diversen Posts hier geschrieben: http://www.sql-asp-blog.de/category/SQL-Server-Database-Copy-Tool.aspx

Download hier: http://dbcopytool.codeplex.com/

SSMS Toolpack
Nützliches Add-In für das Management Studio. Tools wie Query Execution History etc. Mehr dazu habe ich hier geschrieben: http://www.sql-asp-blog.de/post/SSMS-Toolpack.aspx

Download hier: http://www.ssmstoolspack.com/

SQL Inform
JAVA-Applikation um SQL Code (nicht nur T-SQL) mit etlichen Optionen zu formatieren. Läuft direkt im Browser oder Stand-Alone.

Download hier: http://www.sqlinform.com/

Idera SQL check
kostenloses Tool um einige wichtige Performance Counter zu überwachen.

Download hier: http://www.idera.com/Products/Free-Tools/SQL-check/

Idera SQL job manager
Dieses Tool bietet eine grafische Übersicht (wie ein Outlook-Kalender) mit den gelaufenen und geplanten Agent-Jobs. Außerdem können damit Zeitpläne bearbeitet werden etc.

Download hier: http://www.idera.com/Products/Free-Tools/SQL-job-manager/

jDiskReport
Java-Anwendung. Durchsucht ein definiertes Verzeichnis und zeigt die Größenverteilung der einzelnen Verzeichnisse und Dateien an. Klasse um große Dateien zu finden oder einfach nur um aufzuräumen.

Download hier: http://www.jgoodies.com/freeware/jdiskreport/

Notepad++
Erweitertes Notepad mit Tabs, Syntax-Highlighting etc.. Braucht jeder :-)

Download hier: http://notepad-plus.sourceforge.net/de/site.htm

SQL Server Performance Dashboard
In SSMS integrierte Reports, die – wie der Name schon sagt – Aufschluss über die momentanen Aktivitäten auf dem SQL Server geben. Einen super Artikel über die Reports gibt es hier: http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

Download hier: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc

ROW_NUMBER()

Mit Hilfe von ROW_NUMBER() lassen sich tolle Sachen machen. Die einfachste Anwendung ist die Nummerierung von Datensätzen nach einer festgelegten Sortierreihenfolge.

Ein kleines Beispiel:

Wir haben die Tabelle tBirthdates mit den Geburtsdaten einiger Leute.

CREATE TABLE tBirthdates
(  
Name VARCHAR(50),
Gender bit, -- 0 = male, 1 = female
Birthdate DATETIME
)
GO
INSERT INTO tBirthdates VALUES ('Peter', 0, '19800502')
INSERT INTO tBirthdates VALUES ('Mary', 1, '19810603')
INSERT INTO tBirthdates VALUES ('Hans', 0, '19840830')
INSERT INTO tBirthdates VALUES ('Pedro', 0, '19720502')
INSERT INTO tBirthdates VALUES ('Mario', 0, '19651002')
INSERT INTO tBirthdates VALUES ('Maria', 1, '19850221')
INSERT INTO tBirthdates VALUES ('Lena', 1, '19601231')
GO

Um nun die Geburtstage aller sortiert nach dem Datum zu nummerieren folgendes SQL-Statement:

SELECT Reihenfolge = ROW_NUMBER() OVER (ORDER BY Birthdate)
,*
FROM tBirthdates       

Gibt als Ergebnis die durchnummerierte Reihenfolge des Geburtsdatums.

Row_number_1

Zur Erläuterung: ROW_NUMBER() benötigt immer eine Sortierreihenfolge in der OVER Klausel um entscheiden zu können, wo die Zählung anfängt und wo sie aufhört. Doch ROW_NUMBER() kann noch mehr. Möchte man die Geburtsdaten durchnummeriert nach Geschlecht hilft folgendes SQL-Statement:

SELECT Reihenfolge = ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Birthdate)
,*
FROM tBirthdates       

Hier wird in der OVER Klausel nach Gender, also dem Geschlecht partitioniert. Das kann man sich vorstellen wie eine Gruppierung. ROW_NUMBER() zählt die Einträge pro Gruppe durch (wieder sortiert nach dem Geburtsdatum). Ergebnis siehe Screenshot.

Row_number_2

Das ist es im Grunde auch schon. Mehr Informationen gibt es u.a. hier: http://msdn.microsoft.com/de-de/library/ms186734.aspx

SSF – Redgate SQL Prompt

Eigentlich will ich ja hier keine Werbung machen. Tue ich aber hiermit doch einmal :-) Es gibt ja einige Tools, die die Arbeit mit dem SQL Server einfacher machen sollen. Eins davon – und in meiner persönlichen Hitliste ganz weit oben – ist SQL Prompt von RedGate.

Microsoft hat zwar im SQL Server 2008 bzw. im Management Studio dafür Intellisense integriert um bspw. schneller auf Tabellen, Variablen etc. zuzugreifen, allerdings hat dieses integrierte Intellisense nicht halb so viel Charme wie die Funktionen von SQLPrompt.

Eine Art Intellisense bietet das Tool auch. D.h. wird angefangen ein Tabellenname einzugeben bietet SQLPrompt Vorschläge an…an sich nix besonders tolles. Viel interessanter wird das Ganze bei JOIN Bedingungen. Es genügt “INNER JOIN” oder auch “ij” + [TAB] einzugeben und die Tabelle mit der man joinen möchte, schon schlägt SQL-Prompt anhand der FKs (oder auch Spaltennamen [konfigurierbar]) die richtige Join-Bedingung vor.

Was gibt es noch? Snippets! Jeder schreibt häufiger mal ähnliche oder gleiche Abfragen, weil es sich teilweise gar nicht lohnt diese zu speichern und zu öffnen wenn sie benötigt werden. Mit SQL Prompt sind bereits einige Snippets vorgefertig bspw. “ssf” für “SELECT * FROM”, aber man kann auch eigene definieren. So habe ich für die wichtigsten Datenbanken immer ein paar Snippets mit Kürzel um “Standard-Abfragen” schnell eingeben zu können.

Auf jeden Fall eine Empfehlung und wert es sich mal näher anzuschauen, auch wenn es nicht ganz kostenlos ist :-). => http://www.red-gate.com/products/SQL_Prompt/index.htm

T-SQL Zeilen in Spalten ausgeben (PIVOT)

Um Zeilen in T-SQL in Zeilen ausgeben zu können gibt es den PIVOT Operator. Ein kleines Beispiel:

Im Forum von http://www.sqlservercentral.com wurde gefragt, wie eine Tabelle, die folgendermaßen aussieht umgeformt werden kann:

DatabaseName Date Qty
Db1 20090101 1000
Db2 20090101 500
Db1 20090201 2000
Db2 20090201 600

Diese Tabelle zeigt den von mehreren Datenbanken verbrauchten Speicherplatz im Zeitverlauf an. Um jetzt das Ergebnis so umzuformen, das folgendes erscheint…

DatabaseName 20090101 20090201
Db1 1000 2000
Db2 500 600

…kann folgendes SQL Statement verwendet werden:

declare @dates varchar(1000) = ''
select @dates = @dates + ',' + '[' + convert(varchar,date,112) +']' 
from 
(
select distinct date from dbs
) a
DECLARE @sql nvarchar(1000)
set @sql = 
'select * from dbs 
PIVOT
(
MAX(qty)
FOR date in (' + substring(@dates,2,LEN(@dates)) + ')
) p
'
exec sp_executesql @sql

Der Hauptteil ist die in dynamischen SQL geschriebene Abfrage mit PIVOT. Hier wird für jedes Datum, für jede DB die MAX(qty) bestimmt. Für jedes Datum gibt es nur eine Qty also wird diese zurückgegeben. Da nicht bekannt ist, wie viele Daten (Datums) in der Tabelle enthalten sind und mit PIVOT keine Parameter erlaubt sind (zumindest habe ich darüber nichts gefunden) muss der obere Teil alle Daten selektieren und als String für PIVOT zurückgeben zurückgeben.

Weitere Informationen zu PIVOT gibt es bspw. hieroder kurz gehalten hier

Zum ausprobieren gibt es hier noch die CREATE Skripte etc. Mehr...

SSMS Toolpack

Ich möchte kurz das SSMS Toolpack vorstellen, welches unter http://www.ssmstoolspack.com/Main.aspx zum Download verfügbar ist. Das Toolpack funktioniert mit dem Management Studio für SQL 2005, SQL 2008 und auch SQL Express und bietet einige nützliche Features für die Arbeit mit dem Management Studio.

Was kann das Toolpack?

Die Funktionen werden auf der Seite http://www.ssmstoolspack.com/Features.aspx ausführlich beschrieben. Am nützlichsten finde ich das “Window Connection Coloring”, die “Query Execution History” und die Suche für die Ergebnismenge. Mit dem Connection Coloring wird jede Verbindung zu einem bestimmten Server in einer definierten Farbe angezeigt. So passiert es nicht so leicht, dass man ein SQL Statement ausversehen auf dem falschen Server absetzt.

concoloring

Die Query Execution History speichert alle an den Server gesendeten SQL-Statements. So kann man immer wieder zurück, falls man mal ein Fenster ungewollt geschlossen hat oder wenn man mal nachschauen möchte was man eigentlich den ganzen Tag gemacht hat ;-)

exechistory

Mit der Suche für die Ergebnismenge kann man leicht im Grid nach Text suchen (was sonst nicht geht). Spart man sich teilweise das zusätzliche ORDER BY oder Scroll-Orgien, wenn man mal nur etwas nachschauen möchte.

suche

Diese drei Features waren nur Beispiele…im Toolpack gibt es noch weit mehr interessante Funktionen. Es lohnt sich auf jeden Fall das Toolpack mal anzuschauen.

SQL Server Database Copy Tool – Beta

Habe soeben eine neue Version des SQL Server Database Copy Tool auf Codeplex veröffentlicht: http://dbcopytool.codeplex.com

Habe es jetzt schon öfter eingesetzt um Datenbanken erfolgreich (:-) ) von einem auf den anderen Server zu kopieren und umgehe damit die Methode per Copy Database Task aus dem SSMS. Wer trotzdem wissen möchte, wie das geht findet hier eine Beschreibung: [Copy Database Beschreibung]

Achja, das Tool hat nun auch ein “hübsches” Logo :-)

logo

SQL Server Messages empfangen (ExecuteNonQuery)

Im SQL Server kann per Skript eine Nachricht ausgegeben werden. Ganz einfach per:

PRINT 'HALLO'

Um jetzt diese Nachricht in einer SQLConnection abzufangen gibt es nun eine einfache Möglichkeit. Zunächst wird eine Methode benötigt, die die Nachricht empfängt und weiterverarbeitet. Hier am Beispiel _con_InfoMessage(…).

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);                
}

Nun muss noch der EventHandler für das Ereignis festgelegt werden:

_con.InfoMessage += new SqlInfoMessageEventHandler(_con_InfoMessage);

Mit dieser Einstellung wird nach einem ExecuteNonQuery() die Nachricht, die vom SQL Server empfangen wird in der Methode _con_InfoMessage verarbeitet (und hier in diesem Fall einfach in die Konsole geschrieben).

Für das SQL Server Database Copy Tool wollte ich anzeigen, wie weit der Server bereits mit dem BACKUP bzw. dem RESTORE ist. Mit der obigen Einstellung wird wie gesagt das Event nur einmal am Ende von ExecuteNonQuery() ausgeführt. Für eine Fortschrittsanzeige soll es allerdings jedes Mal auftreten, wenn eine Nachricht empfangen wird. Dafür gibt es die Eigenschaft (für SQLConnection):

_con.FireInfoMessageEventOnUserErrors = true;

Diese Eigenschaft legt fest, dass das Ereignis jedes Mal ausgeführt wird. Wie am Namen “OnUserErrors” zu erkennen gilt dies nicht nur für Nachrichten, die mit PRINT erzeugt werden. Vielmehr werden Fehler in der Ausführung bis zu einem Schweregrad von 16 an die Methode weiter gegeben (normalerweise würde eine Exception bei ExecuteNonQuery() entstehen. Um nun an die richtigen Exceptions (also Fehler) ran zu kommen habe ich mir noch Folgendes gebastelt:

private void _con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    if (e.Errors.Count > 0)
{
if (e.Errors[0].Class > 0)
{
//save error for use
_internalError += e.Message + " ";
}
}
Console.WriteLine(e.Message);             
}

Tritt also ein richtiger Fehler auf (Schweregrad > 0 [PRINT = Schweregrad 0]) wird dieser in _internalError gespeichert und ich kann diesen später nach ExecuteNonQuery() behandeln.