SQL Server 2005 SQL Server 2005 TechTalkTechTalkSeptember 2005September 2005

Sebastian WeberSebastian WeberDeveloper EvangelistDeveloper EvangelistMicrosoft Deutschland GmbHMicrosoft Deutschland GmbH

Sebastian.Weber@microsoft.comSebastian.Weber@microsoft.comhttp://blogs.msdn.com/sebweberhttp://blogs.msdn.com/sebweber

Eine persönliche Fallstudie Eine persönliche Fallstudie ……

„„Großhandel“ für digitale MedienGroßhandel“ für digitale Medien

> 15 Mio. Medien> 15 Mio. Medien

Verlage haben verschiedene FormateVerlage haben verschiedene Formate

> 30 Formate> 30 Formate

Integration der Dienste in Kunden-Integration der Dienste in Kunden-

IntranetsIntranets

XML Web ServicesXML Web Services

Grobarchitektur des Grobarchitektur des SystemsSystems

Kunden

Eigenes Management

SQL Server

XML Daten Importer

Web Service Zugang

Berichte & Auswertunge

n

BestellungenVerlag

e

Warum Warum erfindenerfinden

wir das Rad wir das Rad

ständig neu?ständig neu?

SQL Server 2005 PlattformSQL Server 2005 Plattform

AgendaAgenda

Die Services im ÜberblickDie Services im Überblick

Erweiterungen der DatenbankErweiterungen der DatenbankT-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

Integration ServicesIntegration Services

ETL-PlatformETL-Platform

““Best in Class”-Best in Class”-

UsabilityUsability

Workflow DesignerWorkflow Designer

ErweiterbarErweiterbar

Analysis ServicesAnalysis Services

Business IntelligenceBusiness Intelligence

Unified Dimensional Unified Dimensional ModelModel

Real-timeReal-time

Web ServicesWeb Services

Reporting ServicesReporting Services

Berichte erstellen, Berichte erstellen,

verwalten und verwalten und

verteilenverteilen

Office IntegrationOffice Integration

Verschiedene Verschiedene

DatenquellenDatenquellen

Verschiedene Verschiedene

AusgabeformateAusgabeformate

Reporting ServicesReporting Services

• Report BuilderReport Builder• Visual Studio IntegrationVisual Studio Integration

Demo: Report BuilderDemo: Report Builder

Demo: Visual Studio Demo: Visual Studio IntegrationIntegration

Notification ServicesNotification Services

Plattform für Plattform für BenachrichtigungsanwBenachrichtigungsanw..

Deklaratives Deklaratives ProgrammiermodellProgrammiermodell

Effizienter und Effizienter und skalierende skalierende AusführungsdienstAusführungsdienst

Entwickler schreibt Entwickler schreibt BenachrichtigungsanwBenachrichtigungsanw..

Replication ServicesReplication Services

Standort-übergreifende Standort-übergreifende DatenverteilungDatenverteilung

Peer-to-Peer VerteilungPeer-to-Peer Verteilung

Verteilung von Verteilung von VolltextindizesVolltextindizes

Web-SynchronisationWeb-Synchronisation

MonitoringMonitoring

AA BB

CC

Service BrokerService Broker

AA BBKommunikationKommunikation

CC

TransportTransport

DD

TransportTransport

TransportTransport

Plattform für asynchrone Plattform für asynchrone

DatenbankanwendungenDatenbankanwendungen

T-SQL IntegrationT-SQL Integration

TransaktionenTransaktionen

Zuverlässige ÜbermittlungZuverlässige Übermittlung

SQL Server 2005 PlattformSQL Server 2005 Plattform

AgendaAgenda

Die Services im ÜberblickDie Services im Überblick

Erweiterungen der DatenbankErweiterungen der DatenbankT-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

Viele neue Helfer …Viele neue Helfer …

Neue DatentypenNeue Datentypen

DDL TriggerDDL Trigger

DML mit AusgabeDML mit Ausgabe

TOPTOP

DRI ErweiterungenDRI Erweiterungen

FehlerbehandlungFehlerbehandlung

CTECTE

Except und IntersectExcept und Intersect

Ranking Ranking FunktionalitätFunktionalität

Index Index VerbesserungenVerbesserungen

Snapshot Isolation Snapshot Isolation LevelLevel

Recompile Recompile OptimierungOptimierung

XML ShowplanXML Showplan

Mehr Speichern mit MAXMehr Speichern mit MAX

varchar(max) und nvarchar(max)varchar(max) und nvarchar(max)Nachfolger von „text“ bzw. „ntext“Nachfolger von „text“ bzw. „ntext“

varbinary(max)varbinary(max)Nachfolger von „image“Nachfolger von „image“

bis 2 GB (2^31 - 1 Bytes)bis 2 GB (2^31 - 1 Bytes)

.WRITE & SUBSTRING.WRITE & SUBSTRING

Versuchen & FangenVersuchen & Fangen

BEGIN TRYsql statements …

END TRYBEGIN CATCH

sql statements …END CATCH

Fehlerbehandlung á la C# & Co.Fehlerbehandlung á la C# & Co.

Verschachtelungen möglichVerschachtelungen möglich

Kein „Rausfliegen“ aus Batches Kein „Rausfliegen“ aus Batches mehr!mehr!

Common Table ExpressionsCommon Table Expressions

Common Table Expression (CTE)Common Table Expression (CTE)Sind so was wie „ad-hoc views“Sind so was wie „ad-hoc views“

Sind in SQL-99 definiertSind in SQL-99 definiert

Sind weniger kompliziert als man denktSind weniger kompliziert als man denkt

CTEs sind Eintrittskarte zur RekursionCTEs sind Eintrittskarte zur RekursionIterativer Ansatz meist deutlich Iterativer Ansatz meist deutlich aufwändigeraufwändiger

So sieht eine CTE aus!So sieht eine CTE aus!

WITH <CTE-Name> (<Spaltennamen>) AS ( <Abfrage>)SELECT * FROM <CTE-Name>

WITH ProdukteCTE (Name, Kategorie) AS ( SELECT PName, KName FROM Produkte P INNER JOIN Kategorien K ON P.KategorieID = K.ID)

SELECT * FROM ProdukteCTE …

Der Traum von der Der Traum von der RekursionRekursion

WITH <CTE-Name> (<Spaltennamen>) AS ( <SELECT … FROM …>

UNION ALL <SELECT … FROM CTE-Name>

)SELECT * FROM <CTE-Name>

Rekursion endet, wenn 2. Select kein Rekursion endet, wenn 2. Select kein Ergebnis mehr liefert!Ergebnis mehr liefert!

T-SQLT-SQL

Rekursive CTERekursive CTE EXCEPT / INTERSECTEXCEPT / INTERSECT TOPTOP Row_Number() OVER (…)Row_Number() OVER (…) OUTPUTOUTPUT

Demo: Rekursive CTE Demo: Rekursive CTE AbfrageAbfrage

DECLARE @StartOrt char(50);SET @StartOrt = ‘Frankfurt';

WITH cte (ID, Ort, Macht) AS( SELECT ID, Ort, Macht FROM DieWelt

WHERE Ort = @StartOrt

UNION ALL

SELECT dw.ID, dw.Ort, dw.Macht FROM DieWelt dw JOIN cte ON dw.ID = cte.Macht)

SELECT * FROM cte ORDER BY ID

Demo: EXCEPT & Demo: EXCEPT & INTERSECTINTERSECT

-- “Zeige Daten von A, die nicht in B stehen”SELECT * FROM A;EXCEPTSELECT * FROM B;

-- “Zeige Daten, die in A und B stehen”SELECT * FROM A;INTERSECTSELECT * FROM B;

Demo: TOPDemo: TOP

-- TOP unterstützt Ausdrücke:DECLARE @n INT;SET @n = 3;SELECT TOP (@n) FROM A;

-- TOP auch in DML:UPDATE TOP (5) A SET x=1, y=2;DELETE TOP (5) ...INSERT TOP (5) ... SELECT * ...

Demo: Paging dank CTEDemo: Paging dank CTE

WITH Namen AS(SELECT Row_Number() OVER (ORDER BY Nachname) ASNummer, Nachname, Vorname FROM NamenNeu )

SELECT * FROM NamenWHERE Nummer BETWEEN 5 AND 9

Demo: OUTPUTDemo: OUTPUT

-- Zugriff auf alte und neue Werte: UPDATE Tabelle SET A=1 OUTPUT INSERTED.*, DELETED.*

-- Zugriff auf eingefügten Datensatz (berechnete -- Spalten, AutoIncs …): INSERT Tabelle (Wert) VALUES (42) OUTPUT INSERTED.*

-- Zugriff auf gelöschten DatensatzDELETE FROM Tabelle OUTPUT DELETED.*

Zusammengefasst: T-SQLZusammengefasst: T-SQL

varchar(max), nvarchar(max), varchar(max), nvarchar(max), varbinary(max)varbinary(max)

TRY / CATCHTRY / CATCH

CTECTE

EXCEPT/INTERSECTEXCEPT/INTERSECT

TOPTOP

SELECT Row_Number() OVER …SELECT Row_Number() OVER …

OUTPUTOUTPUT

AgendaAgenda

Die Services im ÜberblickDie Services im Überblick

Erweiterungen der DatenbankErweiterungen der DatenbankT-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

Was bietet die Was bietet die Integration?Integration?

Serverseitige Routinen in C#, Serverseitige Routinen in C#,

VB.NET, …VB.NET, …

Stored Procedures, Functions, Triggers, Stored Procedures, Functions, Triggers,

Data Types & AggregatesData Types & Aggregates

Erweitertes SicherheitsmodellErweitertes Sicherheitsmodell

Einheitliche EntwicklungsumgebungEinheitliche Entwicklungsumgebung

PerformancevorteilePerformancevorteile

Integration != IntegrationIntegration != IntegrationHier: SQL 2000 Extended Stored ProceduresHier: SQL 2000 Extended Stored Procedures

ClientClient

SqlServr.exeSqlServr.exe

Daten

Daten

Ext. SPSelect *Ext. SPSelect *

1. Connection

Select *Select *

2. Connection

2. Thread

Abbildung aus A First Look at SQL Server 2005 for Developers, AW

SQL Server 2005SQL Server 2005Beste Integration der Welt!Beste Integration der Welt!

SqlServr.exeSqlServr.exe

Daten

Daten

SqlCommand cmd = ...cmd.ExecuteNonQuery

()

SqlCommand cmd = ...cmd.ExecuteNonQuery

()

Managed Code

Unmanaged Code

PInvoke

Abbildung aus A First Look at SQL Server 2005 for Developers, AW

ClientClient1. Connection

ImplementierungImplementierung

ImplementierungImplementierungServercodeServercode

Statische Statische Methoden mit Methoden mit „[Attribut]“„[Attribut]“

• Stored Procedures, Stored Procedures, Functions & Functions & TriggersTriggers

• AggregatesAggregates Implementierung Implementierung als Struct mit als Struct mit Attribut Attribut [SqlUserDefinedAggregate[SqlUserDefinedAggregate]]• TypesTypes Implementierung Implementierung als Klasse oder als Klasse oder Struct mit AttributStruct mit Attribut[SqlUserDefinedType][SqlUserDefinedType]

So geht‘s!So geht‘s!

SQL Abfragen: SELECT GetOrderCount(region) FROM order

SQL Abfragen: SELECT GetOrderCount(region) FROM order

VS .NET

Projekt

VS .NET

Projekt

VB,C#,C++ Build

SQL Serve

r

SQL Definitionen: create assembly … create function … create procedure … create trigger …create type …

SQL Definitionen: create assembly … create function … create procedure … create trigger …create type …

Assembly

So geht’s im Detail!So geht’s im Detail!

• Stored Procedure in C#Stored Procedure in C#• User Defined Type in C#User Defined Type in C#

Demo: Stored ProcedureDemo: Stored Procedure

[SqlProcedure]public static void ZeigeDaten (string tabelle, string spalte){ // Context Connection öffnen SqlConnection conn = new SqlConnection(); conn.ConnectionString = “context connection = true”; conn.Open();

// Command “zusammen hacken” SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = “SELECT “ + splate + “ FROM “ + tabelle;

// Ergebnis des Readers an Client “senden” SqlContext.Pipe.Send(cmd.ExecuteReader());}

Demo: User Defined TypeDemo: User Defined Type

[SqlUserDefinedType(Format.Native)]public struct MeinRechteck : INullable{ public SqlDouble Laenge; public SqlDouble Breite; // “String-Handhabung” public override string ToString() { /*…*/} public static MeinRechteck Parse(SqlString s) {/*…*/}

// “Null-Handhabung” public bool IsNull { get {/*…*/} } public static MeinRechteck Null { get {/*…*/} }}

.NET oder T-SQL?.NET oder T-SQL?

Verwenden Sie .NET Routinen …Verwenden Sie .NET Routinen …… … wenn es mit T-SQL nicht möglich istwenn es mit T-SQL nicht möglich ist

… … für aufwendige Rechenoperationenfür aufwendige Rechenoperationen

… … um Logik zu implementierenum Logik zu implementieren

… … wenn auf wenige Daten zugegriffen wenn auf wenige Daten zugegriffen wirdwird

Verwenden Sie T-SQL …Verwenden Sie T-SQL …… … bei daten-intensiven Abfragenbei daten-intensiven Abfragen

Fallweise Unterscheidung notwendig!!Fallweise Unterscheidung notwendig!!

AgendaAgenda

Die Services im ÜberblickDie Services im Überblick

Erweiterungen der DatenbankErweiterungen der DatenbankT-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

<Damals/> &amp; <Damals/> &amp; <Heute/><Heute/>

XML mit dem Sql Server 2000 XML mit dem Sql Server 2000 (SqlXml)(SqlXml)

Relationale Daten Relationale Daten XML (FOR XML) XML (FOR XML)XML XML Relationale Strukturen (OPEN Relationale Strukturen (OPEN XML)XML)XML-Dokumente als „Text“ speichernXML-Dokumente als „Text“ speichern

SQL Server 2005 bietet aber mehrSQL Server 2005 bietet aber mehrXML-Datentyp mit XSD-UnterstützungXML-Datentyp mit XSD-UnterstützungXQuery/XPath im Sql-Statement nutzbarXQuery/XPath im Sql-Statement nutzbarIndexierung der XML-KnotenIndexierung der XML-KnotenVerbesserung FOR XML und OPEN XMLVerbesserung FOR XML und OPEN XML

XML-Datentyp verwendenXML-Datentyp verwenden

Indexierung nur möglich, wenn dem Xml ein Indexierung nur möglich, wenn dem Xml ein Schema zugewiesen wurde („typisiert“)Schema zugewiesen wurde („typisiert“)

Volltext-Indexierung wird ebenfalls unterstützt!Volltext-Indexierung wird ebenfalls unterstützt!

Natürlich auch per Management Studio …Natürlich auch per Management Studio …

CREATE TABLE XmlTabelle {ID int,MeinXml XML ('MeinSchema.xsd')

}

CREATE INDEX idx_MeinXml ON XmlTabelle (MeinXml);

Die große Liebe: SQL + Die große Liebe: SQL + XMLXML

SELECT ID, MeinXml.value('/Mein/XQuery‘, ‘string‘) AS Wert FROM XmlTabelle

SELECT ID, MeinXml.value('/Mein/XQuery‘, ‘string‘) AS Wert FROM XmlTabelle

XML ZugriffsmöglichkeitenXML Zugriffsmöglichkeiten

XML MethodenXML Methodenquery(), value(), exist(), modify(), query(), value(), exist(), modify(), nodes()nodes()

Komplexe XML-Operationen mit Komplexe XML-Operationen mit FLWORFLWOR

FLWOR spricht man „Flower“FLWOR spricht man „Flower“

Und bedeutet For-Let-Where-Order-Und bedeutet For-Let-Where-Order-ReturnReturn

Eigentlich nur FOWR, da „L“et noch nicht Eigentlich nur FOWR, da „L“et noch nicht supportedsupported

XML im SQL ServerXML im SQL Server

• FLOWR-AusdruckFLOWR-Ausdruck

Demo: FLWOR-AusdruckDemo: FLWOR-Ausdruck

declare @x xmlset @x='<Bestellungen>

<Bestellung Name="Anne" Summe="110"/><Bestellung Name="Manfred" Summe="50"/><Bestellung Name="Peter" Summe="250"/><Bestellung Name="Marlene" Summe="75"/>

</Bestellungen>'

SELECT @x.query(' for $Bestellung in

//Bestellung[xs:double(@Summe) > 100]return string($Bestellung/@Name)

')

Zahlreiche Zugriffsoptionen Zahlreiche Zugriffsoptionen vorhandenvorhanden

Aber 1: trotzdem nicht für jeden Aber 1: trotzdem nicht für jeden erreichbarerreichbar

Aber 2: weit weg vom ServerAber 2: weit weg vom Server

Zugang zum SQL Server Zugang zum SQL Server 20002000

TDS/TDS/TCPTCP

NamedNamedPipesPipes

TDS/TDS/TCPTCP

NamedNamedPipesPipes

ODBC, ODBC, OLE OLE DB, DB,

Ado.NeAdo.Nett

ODBC, ODBC, OLE OLE DB, DB,

Ado.NeAdo.Nett

SQL SQL SERVERSERVER

SQL SQL SERVERSERVERSQLXMSQLXM

LLIISIIS

SQLXMSQLXMLL

IISIIS

Zugang zum SQL Server Zugang zum SQL Server 20052005

TDS/TDS/TCPTCP

NamedNamedPipesPipes

TDS/TDS/TCPTCP

NamedNamedPipesPipes

ODBC, ODBC, OLE OLE DB, DB,

Ado.NeAdo.Nett

ODBC, ODBC, OLE OLE DB, DB,

Ado.NeAdo.Nett SQL SQL

SERVERSERVER

SQL SQL SERVERSERVER

SOAP/SOAP/HTTPHTTP

SOAP/SOAP/HTTPHTTP

SQLXMSQLXMLL

IISIIS

SQLXMSQLXMLL

IISIIS

(Unix) Client(Unix) Client(Unix) Client(Unix) Client

SQL Server Web ServicesSQL Server Web Services

Interessant hinsichtlich Interessant hinsichtlich Interoperabilität Interoperabilität

Getestet mit: Jbuilder 9, Axis 1.1, Perl, …Getestet mit: Jbuilder 9, Axis 1.1, Perl, …

SOAP/HTTPSOAP/HTTPLäuft ohne IISLäuft ohne IIS

~%40 schneller als SQLXML~%40 schneller als SQLXML

SQLXML bleibt bestehenSQLXML bleibt bestehenUpdategrams, Bulkload, Query strings in Updategrams, Bulkload, Query strings in URLURL

ZusammengefasstZusammengefasst

XMLXMLEigener Datentyp mit XQuery SupportEigener Datentyp mit XQuery Support

Indexierung der Knoten möglichIndexierung der Knoten möglich

FOR XML / OPEN XML verbessertFOR XML / OPEN XML verbessert

Web ServicesWeb ServicesFlinker SOAP-Layer, ~40% schneller als Flinker SOAP-Layer, ~40% schneller als SQLXMLSQLXML

Kompatibilität zu anderen System Kompatibilität zu anderen System getestetgetestet

Gut für SOA-Träume?Gut für SOA-Träume?

AgendaAgenda

Die Services im ÜberblickDie Services im Überblick

Erweiterungen der DatenbankErweiterungen der DatenbankT-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

Was ist Volltextrecherche?Was ist Volltextrecherche?

Quelle: http://wikipedia.deQuelle: http://wikipedia.de

Schneller & Besser als Schneller & Besser als LIKELIKE

Suchen wie eine SuchmaschineSuchen wie eine SuchmaschineRanking, Stemmers, Stop Words …Ranking, Stemmers, Stop Words …

Suche innerhalb der DokumenteSuche innerhalb der DokumenteWord, Excel, PowerPoint, …Word, Excel, PowerPoint, …

Erweiterbar durch IFilterErweiterbar durch IFilterbeispielsweise für PDFbeispielsweise für PDF

Abfrage per T-SQL BefehleAbfrage per T-SQL Befehle

Volltextindex ist etwas Volltextindex ist etwas andersanders

Nicht ein „normaler“ IndexNicht ein „normaler“ IndexAktivierung pro Datenbank notwendigAktivierung pro Datenbank notwendig

Braucht PflegeBraucht Pflege

Nicht zwingend aktuellNicht zwingend aktuell

SprachabhängigSprachabhängig

Replikation mit SQL Server 2005 Replikation mit SQL Server 2005 möglichmöglich

Suchen wie die Profis Suchen wie die Profis ……

• CONTAINSCONTAINS• FREETEXTFREETEXT

Demo: CONTAINSDemo: CONTAINS

-- Sucht gezielt nach dem AusdruckSELECT Volltext FROM VolltextTabelle WHERE CONTAINS(Volltext, ‘Auto AND CD AND NOT Monitor‘)

-- Zeigt zusätzlich noch den Ranking-Wert anSELECT Volltext, CT.Rank FROM VolltextTabelle JOIN CONTAINSTABLE(VolltextTabelle, Volltext, ‘Auto AND CD AND NOT Monitor‘);

Demo: FREETEXTDemo: FREETEXT

-- Sucht nach den besten Treffern für den AusdruckSELECT Volltext FROM VolltextTabelle WHERE FREETEXT(Volltext, ‘Ein Auto mit CD-Player‘)

-- Zeigt zusätzlich noch den Ranking-Wert anSELECT Volltext, CT.Rank FROM VolltextTabelle JOIN FREETEXTTABLE(VolltextTabelle, Volltext, ‘Ein Auto mit CD-Player‘);

AnwendungsbeispieleAnwendungsbeispiele

Suche in Artikelstamm nach Suche in Artikelstamm nach BeschreibungBeschreibung

Dokumentenmanagement auf eigene Dokumentenmanagement auf eigene FaustFaust

Suchmaschinen, Buch-Shops, Suchmaschinen, Buch-Shops, Auktionsplattformen, Musik-Auktionsplattformen, Musik-DownloadsDownloads

Sie alle nutzen Volltextrecherche!Sie alle nutzen Volltextrecherche!

AgendaAgenda

Die Services im ÜberblickDie Services im ÜberblickErweiterungen der DatenbankErweiterungen der Datenbank

T-SQL ErweiterungenT-SQL Erweiterungen

.NET Framework Integration.NET Framework Integration

XML & Web Service UnterstützungXML & Web Service Unterstützung

VolltextrechercheVolltextrecherche

SQL Server

XML Daten Importer

Web Service Zugang

Berichte & Auswertungen

Bestellungen

Warum das Rad neu Warum das Rad neu erfinden?erfinden?

SQL Web Services !

SQL Reporting Services !

XML !

IntegrationServices !

CLR !

Volltext-recherche !

Fragen und AntwortenFragen und Antworten

Vielen Dank!Vielen Dank!

Sebastian WeberSebastian Weber

Sebastian.Weber@microsoft.comSebastian.Weber@microsoft.com

http://blogs.msdn.com/sebweberhttp://blogs.msdn.com/sebweber

© 2005 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.