SQL Server 2016

Vorwort ... 35


TEIL I. Grundlagen ... 37


1. Installation und Aktualisierung von SQL Server 2016 ... 39


1.1 ... Überblick über die verfügbaren SQL-Server-2016-Versionen ... 39

1.2 ... Installationsvoraussetzungen ... 40

1.3 ... Installation von SQL Server 2016 ... 41

1.4 ... Installation des Management Studios ... 49

1.5 ... Installation der SQL-Server-Beispieldatenbank ... 51

1.6 ... Aktualisierung auf SQL Server 2016 ... 55



2. Datenbankgrundlagen ... 61


2.1 ... Server und Client ... 61

2.2 ... Relationale Datenbanken ... 62

2.3 ... Datenbankmanagementsystem ... 80

2.4 ... Integritätsarten ... 83



3. Logischer Datenbankentwurf ... 85


3.1 ... Grundlagen des Datenbankentwurfs ... 85

3.2 ... Normalisierungsregeln (Normalformen) ... 86

3.3 ... Normalisierung in der Praxis ... 93

3.4 ... Denormalisierung ... 96

3.5 ... Entity-Relationship-Diagramme ... 97



4. Die Oberfläche von SQL Server 2016 ... 101


4.1 ... Das SQL Server Management Studio ... 101

4.2 ... Das Bearbeiten von SQL-Anweisungen im Management Studio ... 118

4.3 ... SQL-Server-Dokumentation und Hilfen zur Programmierung ... 130

4.4 ... Überblick über die SQL-Server-Dienste ... 138

4.5 ... Das Dienstprogramm »sqlcmd« ... 139

4.6 ... SQL-Server-Integration in die Windows PowerShell ... 142



TEIL II. SQL-Programmierung ... 151


5. Grundlegende Abfragetechniken ... 153


5.1 ... Einführung in SQL ... 153

5.2 ... SQL-Grundlagen ... 154

5.3 ... Grundlegende Operatoren ... 163

5.4 ... Einfache Abfragen ... 167

5.5 ... Auswahloperatoren ... 181

5.6 ... Aggregatfunktionen ... 189

5.7 ... Unterabfragen ... 192

5.8 ... Zusammenfassung, Gruppierung und Beschränkung von Abfrageergebnissen ... 198

5.9 ... Die Mengen-Operatoren »EXCEPT« und »INTERSECT« ... 209

5.10 ... Ausgaben mit »PRINT« und »SELECT« ... 211



6. Grundlagen der SQLProgrammierung ... 213


6.1 ... Das Stapeltrennzeichen »GO« ... 213

6.2 ... (Lokale) Variablen ... 215

6.3 ... Ablaufsteuerung ... 223

6.4 ... Fallunterscheidungen ... 233

6.5 ... Funktionen ... 241

6.6 ... Dynamische SQL-Anweisungen ... 261

6.7 ... Fehler in SQL Server und ihre Behandlung ... 263

6.8 ... Fehlerbehandlung ... 266



7. Mehrtabellenabfragen ... 287


7.1 ... Tabellen verknüpfen (»JOIN«-Anweisung) ... 287

7.2 ... Abfragen unter Verwendung mehrerer Server ... 296



8. Erstellen und Ändern von Datenbanken ... 299


8.1 ... Erstellen einer einfachen Datenbank ... 299

8.2 ... Einfluss der »model«-Datenbank auf das Erstellen neuer Datenbanken ... 306

8.3 ... Löschen von Datenbanken ... 308

8.4 ... Erstellen einer Datenbank mit mehreren Dateien ... 308

8.5 ... Nachträgliche Änderungen an Datenbankdateien ... 311

8.6 ... Erstellen einer Datenbank mit mehreren Dateigruppen ... 313

8.7 ... Datenbanken verkleinern ... 316

8.8 ... Datenbankdateien verkleinern ... 318

8.9 ... Gespeicherte Systemprozeduren (Stored Procedures) zur Datenbankverwaltung ... 319

8.10 ... Datenbank-Momentaufnahmen ... 320

8.11 ... Transparente Datenverschlüsselung ... 329



9. Erstellen von Tabellen ... 333


9.1 ... Die grundlegende Syntax zur Tabellenerstellung ... 333

9.2 ... Tabellen ändern ... 350

9.3 ... Löschen von Tabellen ... 352

9.4 ... Implementierung der Datenintegrität ... 353

9.5 ... Anwendungsbeispiel zu Einschränkungen ... 360

9.6 ... Nachträgliche Definition von Einschränkungen ... 362

9.7 ... Verwaltung von Einschränkungen ... 363

9.8 ... Temporäre Tabellen ... 365

9.9 ... Partitionierung von Tabellen ... 367



10. Speicherung von Daten in FileTables ... 377


10.1 ... Voraussetzungen für die Verwendung von FileTables ... 377

10.2 ... Erstellung einer FileTable ... 380

10.3 ... Zugriff auf eine FileTable ... 382

10.4 ... Sichten zur Abfrage vorhandener FileTables in einer Datenbank ... 384



11. Verwendung der räumlichen und hierarchischen Datentypen ... 385


11.1 ... Räumliche Datentypen ... 385

11.2 ... Der hierarchische Datentyp ... 419



12. Daten verwalten ... 429


12.1 ... Grundlegende Befehle zur Datensatzmanipulation ... 429

12.2 ... Verwendung des Zeilenkonstruktors ... 438

12.3 ... Kombinierte Auswahl-/Einfügeanweisungen ... 439

12.4 ... Die »MERGE«-Anweisung ... 441

12.5 ... Massenkopierprogramme ... 448



13. Benutzerverwaltung und Schemas ... 451


13.1 ... Authentifizierung am Server ... 452

13.2 ... Datenbankzugriff erteilen ... 456

13.3 ... Zusammenfassung von Benutzern zu Rollen ... 458

13.4 ... Rechtevergabe an Benutzer und Rollen ... 465

13.5 ... Deaktivierung und Aktivierung von Logins und Datenbankbenutzern ... 471

13.6 ... Vordefinierte Konten auf Server- und Datenbankebene ... 473

13.7 ... Schema ... 476



14. Eigenständige Datenbanken ... 483


14.1 ... Das Konzept der eigenständigen Datenbanken ... 483

14.2 ... Konfiguration des Servers zur Verwendung eigenständiger Datenbanken ... 483

14.3 ... Erstellung einer eigenständigen Datenbank ... 484

14.4 ... Identifizierung eigenständiger Datenbanken ... 485

14.5 ... Besonderheiten der Benutzerverwaltung eigenständiger Datenbanken ... 486

14.6 ... Verbindungsaufbau zu einer eigenständigen Datenbank ... 487

14.7 ... Konvertierung einer Datenbank in eine eigenständige Datenbank ... 489

14.8 ... Migration einer eigenständigen Datenbank auf einen anderen Server ... 492



15. Sichten ... 495


15.1 ... Einsatz von Sichten ... 496

15.2 ... Verwalten von Sichten ... 498

15.3 ... Datenmanipulationsanweisungen auf eine Sicht ... 500

15.4 ... Systemsichten von SQL Server ... 506



16. Programmierung von gespeicherten Prozeduren ... 509


16.1 ... Überblick über die Verwendung von gespeicherten Prozeduren in SQL Server ... 509

16.2 ... Erstellung und Aufruf von gespeicherten Prozeduren ... 513

16.3 ... Tabellenübergabe an eine gespeicherte Prozedur ... 530

16.4 ... Optionen von gespeicherten Prozeduren ... 532



17. Programmierung von benutzerdefinierten Funktionen ... 539


17.1 ... Überblick über benutzerdefinierte Funktionen ... 539

17.2 ... Skalare Funktionen (Skalarwertfunktionen) ... 540

17.3 ... Inlinefunktionen (Tabellenwertfunktionen) ... 544

17.4 ... Tabellenwertfunktion mit mehreren Anweisungen ... 547

17.5 ... Ändern und Löschen von benutzerdefinierten Funktionen ... 549

17.6 ... Verwendung des Datentyps »table« als lokale Variable ... 550



18. Programmierung und Einsatz von Triggern ... 555


18.1 ... DML-Trigger ... 555

18.2 ... DDL-Trigger ... 576



19. Nachverfolgung von Datenänderungen ... 583


19.1 ... Die Möglichkeiten zur Nachverfolgung von Datenänderungen: Change Data Capture und die Änderungsnachverfolgung ... 583

19.2 ... Change Data Capture ... 584

19.3 ... Änderungsnachverfolgung ... 598



20. Temporal tables ... 603


20.1 ... Die Funktionsweise von temporal tables ... 603

20.2 ... Erstellung von temporal tables ... 604

20.3 ... Verwaltung der Einträge in der temporal und history table ... 608

20.4 ... Abfragen von temporal tables ... 611

20.5 ... Löschen von temporal tables ... 615



21. Dynamische Datenmaskierung ... 617


21.1 ... Funktionen zur Maskierung von Spalteninhalten ... 617

21.2 ... Beispiel zur Erstellung einer Tabelle mit dynamischer Datenmaskierung ... 618

21.3 ... Zugriff auf dynamisch maskierte Spalteninhalte ... 620



22. Verschlüsselung von Spalten mit Always Encrypted ... 621


22.1 ... Voraussetzungen einer Client-Applikation für die Verwendung von Always Encrypted ... 622

22.2 ... Beispiel für die Konfiguration von Always Encrypted ... 622

22.3 ... Schlüsselmanagement ... 632



23. Zeilenbasierte Sicherheit ... 633


23.1 ... Das Prinzip der zeilenbasierten Sicherheit ... 633

23.2 ... Beispiel für die Implementierung der zeilenbasierten Sicherheit ... 634



24. Ereignisbenachrichtigungen (Event Notifications) ... 641


24.1 ... Konfiguration einer Datenbank zur Nutzung des Service Brokers ... 642

24.2 ... Konfiguration des Zieldienstes ... 643

24.3 ... Erstellen einer Ereignisbenachrichtigung ... 645

24.4 ... Auslösen und Empfangen einer Ereignisbenachrichtigung ... 646

24.5 ... Service-Broker-Aktivierung ... 647

24.6 ... Löschen der erstellten Objekte ... 651



25. Erstellung und Einsatz eines Cursors ... 653


25.1 ... Funktionsweise eines Cursors ... 653

25.2 ... Erstellung eines Cursors ... 655

25.3 ... Öffnen eines Cursors ... 657

25.4 ... Das Abrufen von Datensätzen aus einem Cursor ... 658

25.5 ... Schließen und Löschen eines Cursors ... 666

25.6 ... Schleifenprogrammierung zum automatischen Durchlaufen eines Cursors ... 667

25.7 ... Daten in der Cursorauswahl aktualisieren und löschen ... 672



26. Sequenzen ... 675


26.1 ... Erstellung einer Sequenz mit Standardeinstellungen ... 675

26.2 ... Informationen zu den vorhandenen Sequenzen in einer Datenbank ... 676

26.3 ... Abrufen von Sequenzwerten ... 676

26.4 ... Die Verwendung der Parameter »AS«, »START«, »INCREMENT«, »MINVALUE«, »MAXVALUE«, »CYCLE« und »CACHE« ... 678



27. Indizes ... 685


27.1 ... Der nicht gruppierte Index auf einem Heap ... 686

27.2 ... Der gruppierte Index ... 689

27.3 ... Der nicht gruppierte Index auf einem gruppierten Index ... 693

27.4 ... Der Columnstore-Index ... 696

27.5 ... Erstellung von Indizes ... 696

27.6 ... Verwaltung von Indizes ... 700

27.7 ... Statistiken ... 704

27.8 ... Planung des Einsatzes von Indizes ... 709

27.9 ... Weitere Optimierungsmöglichkeiten mithilfe von Indizes ... 718



28. Columnstore-Indizes ... 727


28.1 ... Das Grundprinzip von Columnstore-Indizes ... 727

28.2 ... Erstellung eines nicht gruppierten Columnstore-Index ... 729

28.3 ... Erstellung eines gruppierten Columnstore-Index ... 734

28.4 ... Abfragen von Informationen über vorhandene Columnstore-Indizes ... 737



29. Transaktionen ... 739


29.1 ... Einführung in Transaktionen ... 739

29.2 ... ACID ... 741

29.3 ... Interne Transaktionsverarbeitung ... 744

29.4 ... Verhalten bei Systemfehlern ... 745

29.5 ... Programmierung expliziter Transaktionen ... 747

29.6 ... Implizite Transaktionen ... 750

29.7 ... Sperren ... 752

29.8 ... Isolationsstufen auf Verbindungsebene ... 755

29.9 ... Sperrhinweise auf Tabellenebene ... 764

29.10 ... Informationen zu Sperren ... 766

29.11 ... Deadlocks ... 766



30. Speicheroptimierte Tabellen ... 771


30.1 ... Grundlagen der speicheroptimierten Tabellen ... 771

30.2 ... Beständigkeit speicheroptimierter Tabellen ... 772

30.3 ... Konfiguration einer Datenbank zur Verwendung speicheroptimierter Tabellen ... 773

30.4 ... Erstellung speicheroptimierter Tabellen ... 777

30.5 ... Vergleich des Verhaltens beider Arten von speicheroptimierten Tabellen ... 779



31. Indizierung speicheroptimierter Tabellen ... 781


31.1 ... Unterschiede zwischen der Indizierung speicheroptimierter und dateibasierter Tabellen ... 781

31.2 ... Indexarten für die Verwendung mit speicheroptimierten Tabellen ... 782

31.3 ... Überlegungen zur Verwendung nicht gruppierter Indizes oder nicht gruppierter Hash-Indizes ... 787

31.4 ... Abfragen zu Indizes speicheroptimierter Tabellen ... 787



32. Systemintern kompilierte gespeicherte Prozeduren ... 789


32.1 ... Überblick über systemintern kompilierte gespeicherte Prozeduren und deren Verwendung ... 789

32.2 ... Erstellung systemintern kompilierter gespeicherter Prozeduren ... 790

32.3 ... Ändern einer systemintern kompilierten gespeicherten Prozedur ... 794

32.4 ... Informationen zum Speicherort und den geladenen DLL-Dateien ... 795



TEIL III. .NET-Programmierung ... 797


33. SQL Server als Laufzeitumgebung für .NET ... 799


33.1 ... Die Programmiermodelle im Vergleich ... 799

33.2 ... Die CLR-Integration im Detail ... 803

33.3 ... Der Hosting-Layer ... 807

33.4 ... Verwaltung von Assemblies ... 809

33.5 ... Sicherheitsstufen ... 811



34. .NET-Programmierung ... 813


34.1 ... CLR-Unterstützung aktivieren ... 813

34.2 ... CLR-Prozeduren und Funktionen ... 815

34.3 ... Gespeicherte Prozeduren ... 820

34.4 ... Benutzerdefinierte Funktionen ... 828

34.5 ... Serverseitiger Datenzugriff mit ADO.NET ... 836

34.6 ... Pipes ... 838

34.7 ... Impersonalisierung ... 845

34.8 ... Benutzerdefinierte Typen ... 850

34.9 ... Benutzerdefinierte Aggregate ... 866

34.10 ... Benutzerdefinierte Trigger ... 873

34.11 ... Administration und Monitoring von CLR-Objekten ... 880



35. T-SQL: erweiterte Themen ... 885


35.1 ... Kompatibilität festlegen ... 885

35.2 ... Änderungen der Kompatibilität ab SQL Server 2008 ... 886

35.3 ... Änderungen bei nicht ANSI-konformen Abfragen ... 887

35.4 ... »CROSS APPLY« und »OUTER APPLY« ... 888

35.5 ... Kreuztabellen mit »PIVOT« erstellen ... 890

35.6 ... Common Table Expressions ... 897

35.7 ... Fehlerbehandlung ... 905

35.8 ... Ranking- und Windowing-Funktionen ... 912

35.9 ... Die »OUTPUT«-Klausel ... 919

35.10 ... Die »TABLESAMPLE«-Klausel ... 921

35.11 ... »EXCEPT«- und »INTERSECT«-Statement ... 923

35.12 ... Tabellenwertparameter ... 925



36. Einsatz von XML und JSON in der Datenbank ... 931


36.1 ... Warum XML? ... 932

36.2 ... HTML und XML ... 932

36.3 ... XML-Fähigkeiten von SQL Server ... 936

36.4 ... Der XML-Datentyp ... 937

36.5 ... Auf gespeicherte XML-Daten zugreifen ... 943

36.6 ... Darstellung von Abfrageergebnissen im XML-Format ... 954

36.7 ... XML-Schema ermitteln ... 970

36.8 ... XML-Daten in einem relationalen Modell speichern ... 972

36.9 ... Bereitstellung von Daten im JSON-Format ... 975



37. Datenbankzugriff mit ADO.NET ... 989


37.1 ... Einleitung ... 989

37.2 ... Die Datenprovider ... 992

37.3 ... Die Verbindung zu einer Datenbank herstellen ... 993

37.4 ... Die Datenbankabfrage ... 1015

37.5 ... Das »SqlDataReader«-Objekt ... 1021

37.6 ... Parametrisierte Abfragen ... 1029

37.7 ... Der »SqlDataAdapter« ... 1043

37.8 ... Daten im lokalen Speicher -- das »DataSet« ... 1056

37.9 ... Mit mehreren Tabellen arbeiten ... 1081

37.10 ... Aktualisieren der Datenbank ... 1089



38. LINQ ... 1119


38.1 ... Was ist LINQ? ... 1119

38.2 ... LINQ to Objects ... 1125

38.3 ... Die Abfrageoperatoren ... 1129



39. Einführung in das ADO.NET Entity Framework ... 1155


39.1 ... Kritische Betrachtung von ADO.NET ... 1156

39.2 ... Ein erstes Entity Data Model (EDM) erstellen ... 1158

39.3 ... Das Entity Data Model im Designer ... 1163

39.4 ... Der Aufbau des Entity Data Models ... 1168

39.5 ... Die Klassen des Entity Data Models (EDM) ... 1172

39.6 ... Die Architektur des Entity Frameworks ... 1180



40. Abfragen mit .NET 4 Framework-ObjectContext ... 1183


40.1 ... Abfragen mit LINQ to Entities ... 1184

40.2 ... Abfragen mit Entity SQL ... 1207

40.3 ... Der EntityClient-Provider ... 1212

40.4 ... Abfrage-Generator-Methoden (QueryBuilder-Methoden) ... 1215

40.5 ... SQL-Direktabfragen ... 1216



41. Entitätsaktualisierung und Zustandsverwaltung ... 1219


41.1 ... Aktualisieren von Entitäten ... 1219

41.2 ... Der Lebenszyklus einer Entität im Objektkontext ... 1228

41.3 ... Das »ObjectStateEntry«-Objekt ... 1236

41.4 ... Die Klasse »EntityKey« ... 1241

41.5 ... Komplexere Szenarien ... 1243



42. Konflikte behandeln ... 1249


42.1 ... Allgemeine Betrachtungen ... 1249

42.2 ... Konkurrierende Zugriffe mit dem Entity Framework ... 1251



43. Die DbContext-API ... 1259


43.1 ... Datenabfragen mit »DbContext« ... 1259

43.2 ... Ändern von Entitäten ... 1269

43.3 ... Change Tracking (Änderungsnachverfolgung) ... 1274

43.4 ... Kontextlose Entitäten ändern ... 1280



Index ... 1287