SQL Server 2005 TechTalk

SQL Server 2005 TechTalk
September 2005
Sebastian Weber
Developer Evangelist
Microsoft Deutschland GmbH
[email protected]
http://blogs.msdn.com/sebweber
Eine persönliche Fallstudie …
„Großhandel“ für digitale Medien
> 15 Mio. Medien
Verlage haben verschiedene Formate
> 30 Formate
Integration der Dienste in Kunden-Intranets
XML Web Services
Grobarchitektur des Systems
Kunden
Verlage
XML Daten
Importer
SQL Server
Bestellungen
Web Service
Zugang
Berichte &
Auswertungen
Eigenes
Management
Warum erfinden
wir das Rad
ständig neu?
SQL Server 2005 Plattform
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
Integration Services
ETL-Platform
“Best in Class”Usability
Workflow Designer
Erweiterbar
Analysis Services
Business Intelligence
Unified Dimensional
Model
Real-time
Web Services
Reporting Services
Berichte erstellen,
verwalten und verteilen
Office Integration
Verschiedene
Datenquellen
Verschiedene
Ausgabeformate
Reporting Services
• Report Builder
• Visual Studio Integration
Demo: Report Builder
Demo: Visual Studio Integration
Notification Services
Plattform für
Benachrichtigungsanw.
Deklaratives
Programmiermodell
Effizienter und
skalierende
Ausführungsdienst
Entwickler schreibt
Benachrichtigungsanw.
Replication Services
Standort-übergreifende
Datenverteilung
B
A
Peer-to-Peer Verteilung
Verteilung von Volltextindizes
Web-Synchronisation
Monitoring
C
Service Broker
Plattform für asynchrone
Kommunikation
B
A
Datenbankanwendungen
T-SQL Integration
Transaktionen
Zuverlässige Übermittlung
Transport
C
Transport
D
Transport
SQL Server 2005 Plattform
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
Viele neue Helfer …
Neue Datentypen
DDL Trigger
DML mit Ausgabe
TOP
DRI Erweiterungen
Fehlerbehandlung
CTE
Except und Intersect
Ranking Funktionalität
Index Verbesserungen
Snapshot Isolation
Level
Recompile Optimierung
XML Showplan
Mehr Speichern mit MAX
varchar(max) und nvarchar(max)
Nachfolger von „text“ bzw. „ntext“
varbinary(max)
Nachfolger von „image“
 bis 2 GB (2^31 - 1 Bytes)
 .WRITE & SUBSTRING
Versuchen & Fangen
Fehlerbehandlung á la C# & Co.
Verschachtelungen möglich
Kein „Rausfliegen“ aus Batches mehr!
BEGIN TRY
sql statements …
END TRY
BEGIN CATCH
sql statements …
END CATCH
Common Table Expressions
Common Table Expression (CTE)
Sind so was wie „ad-hoc views“
Sind in SQL-99 definiert
Sind weniger kompliziert als man denkt
CTEs sind Eintrittskarte zur Rekursion
Iterativer Ansatz meist deutlich aufwändiger
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 Rekursion
WITH <CTE-Name> (<Spaltennamen>) AS
(
<SELECT … FROM …>
UNION ALL
<SELECT … FROM CTE-Name>
)
SELECT * FROM <CTE-Name>
 Rekursion endet, wenn 2. Select kein
Ergebnis mehr liefert!
T-SQL
Rekursive CTE
EXCEPT / INTERSECT
TOP
Row_Number() OVER (…)
OUTPUT
Demo: Rekursive CTE
Abfrage
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 &
INTERSECT
-- “Zeige Daten von A, die nicht in B stehen”
SELECT * FROM A;
EXCEPT
SELECT * FROM B;
-- “Zeige Daten, die in A und B stehen”
SELECT * FROM A;
INTERSECT
SELECT * FROM B;
Demo: TOP
-- TOP unterstützt Ausdrücke:
DECLARE @n INT;
SET @n = 3;
SELECT TOP (@n) FROM A;
-- TOP
UPDATE
DELETE
INSERT
auch in
TOP (5)
TOP (5)
TOP (5)
DML:
A SET x=1, y=2;
...
... SELECT * ...
Demo: Paging dank CTE
WITH Namen AS
(
SELECT Row_Number() OVER (ORDER BY Nachname) AS
Nummer, Nachname, Vorname FROM NamenNeu
)
SELECT * FROM Namen
WHERE Nummer BETWEEN 5 AND 9
Demo: 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 Datensatz
DELETE FROM Tabelle OUTPUT DELETED.*
Zusammengefasst: T-SQL
varchar(max), nvarchar(max),
varbinary(max)
TRY / CATCH
CTE
EXCEPT/INTERSECT
TOP
SELECT Row_Number() OVER …
OUTPUT
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
Was bietet die Integration?
Serverseitige Routinen in C#, VB.NET, …
Stored Procedures, Functions, Triggers, Data
Types & Aggregates
Erweitertes Sicherheitsmodell
Einheitliche Entwicklungsumgebung
Performancevorteile
Integration != Integration
Hier: SQL 2000 Extended Stored Procedures
SqlServr.exe
1. Connection
Ext. SP
Select *
Client
2. Connection
Daten
2. Thread
Abbildung aus A First Look at SQL Server 2005 for Developers, AW
Select *
SQL Server 2005
Beste Integration der Welt!
SqlServr.exe
1. Connection
Client
SqlCommand cmd = ...
cmd.ExecuteNonQuery()
PInvoke
Managed Code
Daten
Unmanaged Code
Abbildung aus A First Look at SQL Server 2005 for Developers, AW
Implementierung
Servercode
Implementierung
• Stored Procedures,
Functions & Triggers
 Statische Methoden
mit „[Attribut]“
• Aggregates
 Implementierung als
Struct mit Attribut
[SqlUserDefinedAggregate]
• Types
 Implementierung als
Klasse oder Struct mit
Attribut
[SqlUserDefinedType]
So geht‘s!
Assembly
VB,C#,C++
SQL Abfragen:
SELECT
GetOrderCount(region)
FROM order
VS .NET
Projekt
SQL
Server
Build
SQL Definitionen:
create assembly …
create function …
create procedure …
create trigger …
create type …
So geht’s im Detail!
• Stored Procedure in C#
• User Defined Type in C#
Demo: 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 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?
Verwenden Sie .NET Routinen …
… wenn es mit T-SQL nicht möglich ist
… für aufwendige Rechenoperationen
… um Logik zu implementieren
… wenn auf wenige Daten zugegriffen wird
Verwenden Sie T-SQL …
… bei daten-intensiven Abfragen
 Fallweise Unterscheidung notwendig!!
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
<Damals/> & <Heute/>
XML mit dem Sql Server 2000 (SqlXml)
Relationale Daten  XML (FOR XML)
XML  Relationale Strukturen (OPEN XML)
XML-Dokumente als „Text“ speichern
SQL Server 2005 bietet aber mehr
XML-Datentyp mit XSD-Unterstützung
XQuery/XPath im Sql-Statement nutzbar
Indexierung der XML-Knoten
Verbesserung FOR XML und OPEN XML
XML-Datentyp verwenden
CREATE TABLE XmlTabelle {
ID int,
MeinXml XML ('MeinSchema.xsd')
}
CREATE INDEX idx_MeinXml ON XmlTabelle (MeinXml);
Indexierung nur möglich, wenn dem Xml ein Schema
zugewiesen wurde („typisiert“)
Volltext-Indexierung wird ebenfalls unterstützt!
Natürlich auch per Management Studio …
Die große Liebe: SQL + XML
SELECT ID, MeinXml.value('/Mein/XQuery‘, ‘string‘)
AS Wert
FROM XmlTabelle
SELECT ID, MeinXml.value('/Mein/XQuery‘, ‘string‘)
FROM XmlTabelle
AS Wert
XML Zugriffsmöglichkeiten
XML Methoden
query(), value(), exist(), modify(), nodes()
Komplexe XML-Operationen mit FLWOR
FLWOR spricht man „Flower“
Und bedeutet For-Let-Where-Order-Return
Eigentlich nur FOWR, da „L“et noch nicht
supported
XML im SQL Server
• FLOWR-Ausdruck
Demo: FLWOR-Ausdruck
declare @x xml
set @x='
<Bestellungen>
<Bestellung
<Bestellung
<Bestellung
<Bestellung
</Bestellungen>'
Name="Anne" Summe="110"/>
Name="Manfred" Summe="50"/>
Name="Peter" Summe="250"/>
Name="Marlene" Summe="75"/>
SELECT @x.query('
for $Bestellung in
//Bestellung[xs:double(@Summe) > 100]
return string($Bestellung/@Name)
')
Zugang zum SQL Server 2000
SQLXML
IIS
ODBC,
OLE DB,
Ado.Net
TDS/
TCP
Named
Pipes
SQL
SERVER
Zahlreiche Zugriffsoptionen vorhanden
Aber 1: trotzdem nicht für jeden erreichbar
Aber 2: weit weg vom Server
Zugang zum SQL Server 2005
SQLXML
IIS
ODBC,
OLE DB,
Ado.Net
TDS/
TCP
Named
Pipes
SOAP/
HTTP
(Unix) Client
SQL
SERVER
SQL Server Web Services
Interessant hinsichtlich Interoperabilität
Getestet mit: Jbuilder 9, Axis 1.1, Perl, …
SOAP/HTTP
Läuft ohne IIS
~%40 schneller als SQLXML
SQLXML bleibt bestehen
Updategrams, Bulkload, Query strings in URL
Zusammengefasst
XML
Eigener Datentyp mit XQuery Support
Indexierung der Knoten möglich
FOR XML / OPEN XML verbessert
Web Services
Flinker SOAP-Layer, ~40% schneller als
SQLXML
Kompatibilität zu anderen System getestet
Gut für SOA-Träume?
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
Was ist Volltextrecherche?
Quelle: http://wikipedia.de
Schneller & Besser als LIKE
Suchen wie eine Suchmaschine
Ranking, Stemmers, Stop Words …
Suche innerhalb der Dokumente
Word, Excel, PowerPoint, …
Erweiterbar durch IFilter
beispielsweise für PDF
Abfrage per T-SQL Befehle
Volltextindex ist etwas anders
Nicht ein „normaler“ Index
Aktivierung pro Datenbank notwendig
Braucht Pflege
Nicht zwingend aktuell
Sprachabhängig
Replikation mit SQL Server 2005 möglich
Suchen wie die Profis …
• CONTAINS
• FREETEXT
Demo: CONTAINS
-- Sucht gezielt nach dem Ausdruck
SELECT Volltext FROM VolltextTabelle WHERE
CONTAINS(Volltext, ‘Auto AND CD AND NOT Monitor‘)
-- Zeigt zusätzlich noch den Ranking-Wert an
SELECT Volltext, CT.Rank FROM VolltextTabelle JOIN
CONTAINSTABLE(VolltextTabelle, Volltext, ‘Auto AND CD
AND NOT Monitor‘);
Demo: FREETEXT
-- Sucht nach den besten Treffern für den Ausdruck
SELECT Volltext FROM VolltextTabelle WHERE
FREETEXT(Volltext, ‘Ein Auto mit CD-Player‘)
-- Zeigt zusätzlich noch den Ranking-Wert an
SELECT Volltext, CT.Rank FROM VolltextTabelle JOIN
FREETEXTTABLE(VolltextTabelle, Volltext, ‘Ein Auto mit
CD-Player‘);
Anwendungsbeispiele
Suche in Artikelstamm nach Beschreibung
Dokumentenmanagement auf eigene
Faust
 Suchmaschinen, Buch-Shops,
Auktionsplattformen, Musik-Downloads
Sie alle nutzen Volltextrecherche!
Agenda
Die Services im Überblick
Erweiterungen der Datenbank
T-SQL Erweiterungen
.NET Framework Integration
XML & Web Service Unterstützung
Volltextrecherche
Warum das Rad neu erfinden?
CLR !
SQL Web
Services !
Integration
Services !
XML Daten
Importer
Bestellungen
SQL Server
XML !
Volltextrecherche !
Web Service
Zugang
Berichte &
Auswertungen
SQL
Reporting
Services !
Fragen und Antworten
Vielen Dank!
Sebastian Weber
[email protected]
http://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.