T-SQL - Techniken und Neuerungen

Jörg Neumann

RESCO GmbH

Hamburg

Jörg NeumannIT-Consultant bei der RESCO GmbH in Hamburg.Hält Schulungen und Coachings zum SQL Server 2005Schreibt für verschiedene FachzeitschriftenSchreibt an einem Buch über den SQL Server 2005Hält Vorträge auf EntwicklerkonferenzenSchwerpunkte:

SQL ServerSmart ClientsVisual Studio Extensibility

Links zu weiterführenden Artikeln und Büchern finden Sie am Ende der Slides.Kontakt: Joerg.Neumann@RESCO.de

Agenda

Kreuztabellen

Abfrage von hierarchischen Daten

Fehlerbehandlung

Agenda

KreuztabellenKreuztabellen

Abfrage von hierarchischen Daten

Fehlerbehandlung

Beispiel

Name 1 2 3 4

Buchanan, Steven 23431,90 8462,40 15365,00 28308,45

Callahan, Laura 52356,05 25487,81

27091,80 28365,37

Davolio, Nancy 60476,58 36195,55

48487,88 56983,70

Dodsworth, Anne 35498,55 14013,80

17323,70 16127,95

Umsatzliste aller Umsatzliste aller Mitarbeiter pro QuartalMitarbeiter pro Quartal

Name Amount Quarter

Buchanan, Steven 23431,90 1

Buchanan, Steven 8462,40 2

Buchanan, Steven 15365,00 3

Buchanan, Steven 28308,45 4

Callahan, Laura 52356,05 1

Callahan, Laura 25487,81 2

... ... ...

Lösungswege

UNIONTemporäre TabellenUser Defined FunctionsPIVOT-Klausel (SQL2005)

DemoDemo

KreuztabellenKreuztabellen

PIVOT / UNPIVOT

PIVOTPIVOT transformiert ein flaches Resultset in eine KreuztabelleUNPIVOTUNPIVOT transformiert eine Kreuztabelle in ein flaches ResultsetANSI SQL-99-Standard

PIVOT BeispielUmsatzliste aller Umsatzliste aller Mitarbeiter pro QuartalMitarbeiter pro Quartal

QueryQuery

SubquerySubquery

PIVOT BlockPIVOT Block

SELECT Employee, [1], [2], [3], [4]FROM( SELECT Employee,

Amount, Quarter

FROM Orders) AS OrdersPIVOT( SUM (Amount)

FOR QuarterIN ([1], [2], [3], [4])

) AS PVORDER BY Employee

Agenda

Kreuztabellen

Abfrage von hierarchischen DatenAbfrage von hierarchischen Daten

Fehlerbehandlung

BeispielEine Abteilung inkl. aller Eine Abteilung inkl. aller Unterabteilungen des Unterabteilungen des Unternehmens ermittelnUnternehmens ermittelnVorstand

Produktion

Einkauf

Verkauf

Verkauf Inland

Verkauf Ausland

Finanzen

Controlling

Finanzbuchhaltung

Lonhbuchhaltung

Department ParentDepartment

Vorstand null

Produktion Vorstand

Einkauf Vorstand

Verkauf Vorstand

Verkauf Innland Verkauf

Verkauf Ausland Verkauf

Finanzen Vorstand

Controlling Finanzen

Finanzbuchhaltung Controlling

Lohnbuchhaltung Controlling

Lösungswege

CursorSchleifeCommon Table Expressions (SQL2005)

DemoDemo

HierarchischeHierarchischeDatenDaten

Common Table ExpressionsEine CTECTE definiert ein Abfrage, die innerhalb eines Statements wieder verwendet werden kannVergleichbar mit einer View auf Statement-EbeneANSI SQL-92-StandardNützlich beim Umgang mit hierarchischen DatenAls Ersatz für

Redundante UnterabfragenViews, die nur von einem Statement verwendet werdenRekursive ProzeduraufrufeCursor-Läufe

Optimierter Ausführungsplan

WITHWITH CTE(Department, ParentDepartment)AS(

SELECT Department,ParentDepartment

FROM DepartmentsWHERE ParentDepartment = 'Finzanzen'

UNION ALL

SELECT Departments.Department,Departments.ParentDepartment

FROM Departments INNER JOIN CTE ONDepartments.ParentDepartment = CTE.Department

)SELECT DepartmentFROM CTE

QueryQuery

RekursionRekursion

AnchorAnchor

CTE Beispiel

ZugriffZugriff

Weitere OptionenEs können auch mehrere CTEs in einem WITH-Block definiert werden

Die maximale Rekursionsstufe ist einstellbar

Erzeugt Fehler beim Überschreiten

WITH DepartmentsCTE(Department, ParentDepartment)AS(…)SELECT DepartmentFROM DepartmentsCTEOPTION(MAXRECURSION 2)

WITH DepartmentsCTE(Department, ParentDepartment)AS(SELECT …),Employees(ID, Name)AS(SELECT …)

Agenda

Kreuztabellen

Abfrage von hierarchischen Daten

FehlerbehandlungFehlerbehandlung

FehlerbehandlungException-basierte FehlerbehandlungTry/Catch (kein Finally)Kann geschachtelt werdenVorteil: @@error muss nicht nach jedem Statement abgefragt werdenSyntax:BEGIN TRY { sql_statement | statement_block }END TRY

BEGIN CATCH { sql_statement | statement_block }END CATCH

TRY/CATCHSetzt die laufende Transaktion in einen uncommited-Status

Nur Lesezugriffe oder Rollback möglichAuslösen eines Fehlers mit RAISERROR Einschränkungen:

Es werden nur Fehler mit Schweregrad (Severity) 11-20 verarbeitetConnection-Abbruch oder KILL wird nicht berücksichtigtProzedur- oder Funktionsaufrufe werden nicht unterbrochen

Fehler verarbeitenInformationen ermitteln:

ERROR_NUMBER()ERROR_SEVERITY()ERROR_STATE()ERROR_PROCEDURE()ERROR_LINE()ERROR_MESSAGE()

Fehlerinformationen vor Rollback ermittelnInformationen nach Rollback speichern

DemoDemo

FehlerbehandlungFehlerbehandlung

????Fragen?

RessourcenBücher

A First Look at SQL Server 2005 for Developers Bob Beauchemin u.a., Addison-Wesley, ISBN: 0321180593

ArtikelSQL Server 2005 Transact-SQL Enhancementshttp://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

LinksSQL Server 2005 Developer Centerhttp://www.msdn.microsoft.com/SQL/2005/

T-SQL Enhancements Virtual Labhttp://msdn.microsoft.com/vstudio/tryit/hosted/sql/tsqlenhancements/

Resco GmbHMillerntorplatz 1D-20359 HamburgGermany

www.resco.de

... vielen Dank für Ihre Aufmerksamkeit

jobs@resco

----- gesucht -----

IT-Berater (m/w) mit Schwerpunkt Microsoft .NET, SQL Server, Sharepoint, BizTalk Server

Standorte: Hamburg, Frankfurt, Düsseldorf