SQL-Server

Der SQL-Server ist eine relationale Datenbank. Datenbanksysteme wie objektorientierte Datenbanken haben sich hingegen nicht durchgesetzt. Seit einiger Zeit gibt es Aktivitäten die relationale Datenbanken unter dem Begriff NoSQL (not only SQL) zu verdrängen sind aber für sehr spezielle Fälle ausgerichtet und somit wird es schwer werden.

Der SQL-Server arbeitet nach dem Konsistenzmodell (ACID)

• Atomicity (Atomarität): Zusammenhängende Vorgänge werden vollständig oder überhautnicht ausgeführt. Hierzu wurden die Transaktionen geschaffen.
• Consistency (Konsistenz): Daten müssen immer vollständige Daten vorhanden sein. Verweise auf fehlende Daten darf es nicht geben. Als Hilsfmittel dient die Referenzielle Integrität.
• Isolation (Isolation): Daten dürfen nur von einer Stelle gleichzeitig verändert werden Auch hierfür helfen Transaktionen dieses Ziel zu erreichen.
• Durability (Dauerhaftigkeit/Haltbarkeit): Daten dauerhaft verfügbar zu machen. Auch nach Systemausfälle wie Stromausfall dürfen die Daten nicht verschwinden. Für diese Aufgabe ist Protokollierung integriert.

Dateien.

• MDF = Primäre Datendatei
• NDF = Weitere Datendateien (bei großen Datenbanken)
• LDF = Transaktionsprotokolldateien

Objekte im SQL-Server und deren Kürzel (z.B. tbl_). Kürzel sind kein muss, sollen aber den Objekttyp gleich am Namen erkennbar machen.

• db_ Database Diagrams (Datenbankdiagramme): grafische Darstellung von Beziehungen
• tbl_ Tables (Tabellen): Tabellen in denen die Daten gespeichert werden
• qry_ Views (Sichten): Virtuelle Sicht auf die Tabellen
• Synonyms (Synonyme) Links auf Datenbankobjete
• sp_ Stored Procedures (Gespeicherte Prozeduren): Vorkompilierte Transact-SQL-Anweisungen
• fn_ Functions (Funktionen): Unterroutinen mit Transact-SQL-Anweisungen
• Database Triggers (Datenbanktrigger): Änderungen an DB-Objekten protokolliert und gegebenenfalls unterbinden
• Assemblies: .NET-Code die über Common Language Runtime verwendet werden können
• Types (Typen): Benutzerdefinierte Datentypen aus einer oder mehreren Feldern
• Rules (Regeln): Eingaberegeln für ein Datenfeld
• Defaults (Standardwerte): Standardwert für ein Datenfeld
• Plan Guides (Planhinweislisten): Hiermit können Abfragen optimiert werden
• usr_ Users (Benutzer): Einzelbenutzer und Gruppen die Zugriff haben
• Roles (Rollen): Berechtigungsgruppen zur Rechtesteuerung
• Schemas In einem Schema können Datenbankobjekte gespeichert werden um sie z.B. separat zu berechtigen

Microsoft hat in der MSDN Library eine sehr gute Online-Hilfe geschaffen.

• SQL-Server: https://msdn.microsoft.com/de-de/library/bb545450.aspx
• Transact-SQL (T-SQL): https://msdn.microsoft.com/de-de/library/bb510741.aspx
• T-SQL integrierte Funktionen: https://msdn.microsoft.com/de-de/library/ms174318.aspx
• T-SQL Datum-/Zeitfunktionen: https://msdn.microsoft.com/de-de/library/ms186724.aspx
• T-SQL Zeichenfuntionen: https://msdn.microsoft.com/de-de/library/ms181984.aspx
• T-SQL Metadatenfunktionen: https://msdn.microsoft.com/de-de/library/ms187812.aspx
• T-SQL Konvertierungsfunktionen: https://msdn.microsoft.com/de-de/library/hh231076.aspx

Weitere gute Hilfe im großen weiten WEB.

• http://www.insidesql.org/blogs/frankkalis/tag/t-sql

Versionen

SQL Server Versionsübersichten

Ermitteln der Version, Edition und Updatestufe - SQL Server

Dieser Artikel enthält verschiedene Builds oder Updates, die für unterschiedliche Versionen von SQL Server verfügbar sind, und beschreibt die Verfahren zum…

support.microsoft.com

Letzte Updates (Stand 21.12.2019)

• SQL Server 2005 SP4 (9.00.5000.00 – Dezember 2010)
• SQL Server 2008 SP4 (10.0.6000.29 – September 2014) Hinweis: Neueste und endgültige Version für diese Version
• SQL Server 2008 R2 SP3 (10.50.6000.34 – September 2014) Hinweis: Neueste und endgültige Version für diese Version
• SQL Server 2012 SP4 (11.0.7001.0 – September 2017)
• SQL Server 2014 SP3 (12.0.6024.0 – Oktober 2018)
• SQL Server 2016 SP2 (13.0.5026.0 – April 2018)
• CU18 für 2017 ( 14.0.3257.3 – Dezember 2019)
• DDR1 für 2019 Wartungs-Update (15.0.2070.41 – 4. November. 2019)

SQL Server 2014 Service Pack 3 (SP3) und kumulative Update (CU) builds

KB2936603 – SQL Server 2014-Buildversionen

SQL Server 2016 Service Pack 2 (SP2) Kumulative Update-Builds (CU)

KB3177312 – SQL Server 2016-Buildversionen

SQL Server 2017 kumulative Update(CU)-Builds

=> Ab SQL-Server 20147 werden keine Service-Packs mehr bereitgestellt. Nur kumulativen Updates (Kunden) und wichtige Updates (GDRs).

KB4047329 – SQL Server 2017-Buildversionen

SQL Server 2019 DDR-Builds

KB4518398 – SQL Server 2019-Buildversionen

Datenbank (Database)

Als Datenbankname sollten keine Leer- und Sonderzeichen verwendet werden.

Sich mit der Datenbank verbinden (Connect)

Um sich mit der Datenbank zu verbinden gibt es verschiedene Möglichkeiten. Eine der besten Möglichkeiten ist eine Vewrbindung über OLE DB. Eine gute Seite für einen passenden Connect String ist: https://www.connectionstrings.com/sql-server/

Tabellen (Table)

Als Tabellenname sollten keine Leer- und Sonderzeichen verwendet werden. Als Trenner kann ein Unterstrich (_) verwendet werden. Damit man nicht mit reservierten Befehlen/Schlüsselwörtern in Konflikt kommt, verwende ich als Bezeichner nahezu ausschließlich Deutsche Namen.

Die Tabellennamen sollten kurz aber sprechend sein. Um Tabellennamen gleich als diese zu erkennen wäre ein vorangestelltes Kürzel tbl_ sinnvoll. Die maximale Länge sind 128 Zeichen.

Welches Schema man verwendet spielt bei einfachen Anwendungen ein untergeordnete Rolle. Es sollten aber in einer Arbeitsgruppe alle das selbe besitzen.

Tabellen über T-SQL anlegen.

CREATE TABLE [dbo].[tbl_Personen]
  ([PersNr] int IDENTITY(1000, 1)  NOT NULL,
   [Nachname] nvarchar(200) NOT NULL
  ) ON [PRIMARY];

CREATE TABLE = Befehl Tabelle anlegen
[dbo].[tbl_Personen] = dbo = Schema tbl_Personen = Tabellenname
([PersNr] ...) = Definition der Datenfelder (Spalten)
ON [PRIMARY] = Tabelle in der Dateigruppe [PRIMARY] anlegen

Datenfelder / Spalten (Fields)

Datenfelder werden innerhalb einer Tabelle definiert.

Als Feldname sollten keine Leer- und Sonderzeichen verwendet werden. Die Feldnamen sollten kurz aber sprechend sein.

Datentypen

• char(Länge), varchar(Länge) = Text bis max. 8000 Zeichen
  nchar(Länge), nvarchar(Länge) = Text bis max. 4000 Zeichen
  varchar(max), nvarchar(max) = Text bis max. 2GB Daten
  ==> Typ var... (variabel) Dateninhalt werden nicht mit Leerzeichen bis zur Länge aufgefüllt
  ==> Typ n... (national) verwendet Unicode und belegt den doppelten Speicherplatz
• datetime = Datum/Zeit 1753-01-01 00:00:00.000 bis 9999-12-31 23:59:59.997 (8 Byte)
  smalldatetime = Datum/Zeit 1753-01-01 00:00:00 bis 9999-12-31 23:59:59 (4 Byte)
  date = Datum 0001-01-01 bis 9999-12-31 (3 Byte)
  time(Länge) = Zeit Länge
  => 0 = 00:00:00 bis 23:59:59; ...; 2 = hh:mm:ss,nn (3 Byte)
  => 3 = 00:00:00,000 bis 23:59:59,999; ...; 4 = hh:mm:ss,nnnn; (4 Byte)
  => 5 = 00:00:00,00000 bis 23:59:59,99999; ...; 7 = hh:mm:ss,nnnnnnn; (5 Byte)
  datetime2(Länge) = date + time(Länge) in einem
  datetimeoffset(länge) = datetime2 für speichern von Zeitzonen
  => Format YYYYMM-DD hh:mm:ss.nnnnnnn {+|−}hh:mm
• decimal/numeric(Genauigkeit, Dezimalstellen) = identisch; Zahlen festgelegter Genauigkeit und Anz. Dezimalstellen
  => Genauigkeit Anzahl Stellen 1-9 minus Dezimalstellen (5 Byte)
  => Genauigkeit Anzahl Stellen 9-19 minus Dezimalstellen (9 Byte)
  => Genauigkeit Anzahl Stellen 20-28 minus Dezimalstellen (13 Byte)
  => Genauigkeit Anzahl Stellen 29-38 minus Dezimalstellen (17 Byte)
  float(Genauigkeit) = Gleitkommazahl; nicht als Primärschlüssel verwendbar
  => Genauigkeit Anzahl Stellen 1-24 (4 Byte)
  => Genauigkeit Anzahl Stellen 25-53 (15 Byte)
  real = Gleitkommazahlen (7 Stellen) (= float(24)); nicht als Primärschlüssel
  bigint = ganze Zahlen −2^63 bis 2^63 (8 Byte)
  int = ganze Zahlen –2.147.483.648 bis 2.147.483.647 (4 Byte)
  smallint = ganze Zahlen –32 768 bis 32 767 (2 Byte)
  tinyint = ganze Zahlen 0 bis 255 (1 Byte)
• money = Währung –922 bis 922 Billionen, 4 Nachkommastellen (8 Byte)
  smallmoney = Währung –214.748,3648 bis 214.748,3647 (4 Byte)
• bit = Wahr(1) / Falsch(0) / NULL
  => 1te - 8te Spalte je Tabelle (1 Byte)
  => 9te - 16te Spalte je Tabelle (2 Byte)...
• text / ntext = CLOB wird durch nvarchar(max) / varchar(max) ersetzt
  image = BLOB wird durch varbinary(max) ersetzt
  binary(Länge) = Binäre Daten fester Länge 1 - 8.000 Byte
  varbinary(Länge) = Binäre Daten variabler Länge 1 - 8.000 Byte
  varbinary(max) = BLOB, binäre Daten > 8.000 Byte; Max. 2^31-1 Byte
  ==> BLOB = Binary Large Object sind große binäre Objekte
  ==> CLOB = Character Large Object sind große Text Objekte
• xml = XML-Daten max. 2 GB
• sql_variant = Variabler Datentyp; Max. 8.016 Byte
• geography = Räumlicher Datentyp für geografische, ellipsenförmige (Erdkugel-) Daten (z.B.: GPS-Daten)
  geometry = Räumlicher Datentyp für geometrische Daten nach euklidischen (flachem) Koordinatensystem
• hierarchyid = Positionen in einer Hierarchie

Datenfelder über T-SQL anlegen.

CREATE TABLE [dbo].[tbl_Personen]
  ([PersNr] int IDENTITY(1000, 1)  NOT NULL,
   [Nachname] nvarchar(200) NOT NULL,
   [Vorname] nvarchar(200) NULL
  ) ON [PRIMARY];

[PersNr]... = Datenfeld PersNr; IDENTITY(Anfangswert, inkrementeller Wert für nächster Datensatz) = Autowert
[Nachname]... = Datenfeld Nachname; NOT NULL = Eingabe erforderlich
[Vorname]... = Datenfeld Nachname; NULL = Eingabe nicht erforderlich

Indizes

Indizes sind schnelle Sortierschlüssel für Datenfelder.

Einige Indizes dienen als Grundlage für verschiedene Constrains.

Der SQL-Server kennt 2 Arten von Indizes.

• Nonclustered Index (Nicht gruppierter Index): Das Datenfeld wird zusätzlich mit der Datensatz-ID sortiert gespeichert
• Clustered Index (Gruppierter Index): Nur 1x pro Tabelle; Datensätze werden schon sortiert gespeichert

Der Indexname muss in der gesamten Datenbank eindeutig sein. Um einen Index als solchen zu erkennen, sollte bei der Namensvergabe ein Präfix (z.B.: ix_) vorangestellt werden. Um eine Zuordnung zur Tabelle zu haben kann der Tabellenname in den Namen integriert werden (z.B.: ix_tbl_Adressen_Nachname).

Indizes über T-SQL anlegen.

CREATE TABLE [dbo].[tbl_Personen]
  ([PersNr] int IDENTITY(1000, 1)  NOT NULL,
   [Nachname] nvarchar(200) NOT NULL
   CONSTRAINT [pk_tbl_Personen_PersNr] PRIMARY KEY CLUSTERED 
     ([PersNr] ASC)
     WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
     ON [PRIMARY]
  )ON [PRIMARY];

CONSTRAINT = Schlüsselwort für Definition von PRIMARY KEY-, NOT NULL-, UNIQUE-, FOREIGN KEY- oder CHECK-Einschränkung
[pk_tbl_Personen_PersNr] = Name des Index
PRIMARY KEY = Index ist ein Primärschlüssel
CLUSTERED = Index gruppiert
[PersNr] ASC = Datenfeld PersNr einbeziehen; ASC (alphabetisch) | DESC Sortierreihenfolge
WITH (...) = Optionen des Index
ON [PRIMARY] = Index in der Dateigruppe [PRIMARY] anlegen

Optionen des Index (WITH)
PAD_INDEX = OFF (Standard) kein Fillfaktor anwenden
STATISTICS_NORECOMPUTE = OFF (Standard) Statistik über Verwendung nicht aktualisieren
IGNORE_DUP_KEY = OFF (Standard) Fehlermeldung wenn Index-Wert schon vorhanden ist
ALLOW_ROW_LOCKS = ON (Standard) Zeilensperren beim Zugriff auf den Index zulässig
ALLOW_PAGE_LOCKS = ON (Standard) Seitensperren beim Zugriff auf den Index zulässig
DATA_COMPRESSION = NONE Daten werden nicht komprimiert gespeichert

Constraints (Einschränkungen / Bedingungen)

Die Constraints sollten kurz aber sprechend sein. Um die Constraints gleich als solche zu erkennen, ist es sinnvoll ein Kürzel (z.B.: pk_) voran zu stellen. Um eine Zuordnung zur Tabelle zu haben kann der Tabellenname in den Namen integriert werden (z.B.: pk_tbl_Adressen_Personalnummer).

Bei einer Tabelle kann man verschiedene Einschränkungstypen definieren.

• pk_ Primary Key (Primärschlüssel): NULL unzulässig; nur eindeutige Inhalte; für Beziehung geeignet (Mastertabelle); autom. auch ein Index; 1x Tabelle
• fk_ Foreign Key (Fremdschlüssel): Für Beziehungen auf der Gegentabelle (Detailtabelle); nur Werte aus der Primärschlüsselfeld; NOT NULL zulässig; Primär- und Fremdschlüsselfeld müssen den selben Datentyp haben
• uk_ Unique Key (Eindeutiger Schlüssel): NULL zulässig; nur eindeutige Inhalte; mehrere pro Tabelle zulässig
• ck_ Check (Gültigkeitsregel): Regeln für die Eingabe in das Datenfeld
• Default (Standardwert): Wert der bei einem neuen Datensatz autom. in das Feld eingetragen wird

Wenn zwischen Tabellen über das Primär- und Fremdschlüsselfeld eine Beziehung angelegt wird, entsteht eine Referenzielle Integrität. Dabei kann ein Datensatz in der Mastertabelle nur geändert/gelöscht werden, wenn die Datensätze aus der Detailtabelle mit geändert/gelöscht werden.

Transact-SQL (T-SQL)

Transact-SQL ist die Programmiersprache des SQL-Servers.

T-SQL kann verwendet werden, wenn direkte Datenbankoperationen notwendig werden. Z.B. automatisches anlegen einer Tabelle oder Datenupdates vieler Datensätzen. Ansgewendet bzw. gespeichert werden T-SQL Befehle in:

• SQLQuery als Direkteingabe
• Stored Procedures wenn wiederkehrende Datenoperationen notwenig sind oder diese automatisiert werden sollen
• Userdefined Functions wenn komplexe Abfragen oder schnelle Auswertungen der Daten erfolgen sollen (können auch in Abfragen verwendet werden)
• Trigger um ereignisbezogen, z.B. beim Hinzufügen (INSERT), Ändern (UPDATE) oder Löschen (DELETE) von Datensätzen, automaisch Aktionen auszuführen. Z.B. ein Feld mit dem aktuellen Datum zu versehen wenn der Datensatz geändert wurde.

Variablen

Es können auch Variablen verwendet werden die wie Datenfelder mit einem Datentyp deklariert werden müssen. Um Variablen gleich erkennen zu können sollten sie mit einem @ beginnen.

DECLARE @intVar1 AS int
DECLARE @datVar2 AS smalldatetime
DECLARE @strVar3 AS varchar(50)

oder

DECLARE @intVar1 AS int, @datVar2 AS smalldatetime, @strVar3 AS varchar(50)

Das Zuweisen eines Wertes geschied mit SET (sollte bevorzugt werden) oder SELECT

SET @intVar1 = 4711
SET @strVar3 = (SELECT Nachname FROM dbo.tbl_Adresse WHERE ID = 25)
SELECT @strVar3 = Nachname, @datVar2 = GebDatum FROM dbo.tbl_Adresse WHERE ID = 25;

Ausgegeben werden können Variablen zum Beispiel über SELECT

SELECT @strVat3 AS Nachname, @datVar2 AS Geburtsdatum;

Im einen Wert NULL abzufangen kann folgende Möglichkeit verwendet werden

SET @intVar1 = ISNULL(@intVar1, 0)
SET @strVar3 = ISNULL(@strVar3, '')

Benutzerdefinierte Variablen

Benutzerdefinierte Variablen müssen ähnlich wie Tabellen vor Verwendung definiert werden. Hierzu dient die Anweisung CREATE TYPE.

CREATE TYPE dbo.type_Adressen AS TABLE
(ID int PRIMARY KEY,
 Nachname nvarchar(50),
 GebDat smalldatetime
);

Verwendet werden diese Variablen wie folgt:

DECLARE @varAdresse type_Adresse
INSERT INTO @varAdresse(ID, Nachname, GebDat)
FROM dbo.tbl_Adressen
WHERE Stadt = 'Stuttgart';

Diese Variable kann wie eine Tabelle angesprochen werden.

SELECT Nachname FROM @varAdresse;

Trigger

CREATE TRIGGER [trg_Adressen_update] ON [dbo].[tbl_Adressen] AFTER UPDATE
  AS
  BEGIN
    UPDATE [dbo].[tbl_Adressen] SET LastUpdate = getdate()
    WHERE id IN (SELECT id FROM inserted)
  END

Inhalte durchsuchen

Stored-Procedures durchsuchen

Wenn man z.B. eine Tabelle in den Stored-Procedures sucht kann man natürlich alle durchgehen aber einfacher geht es mit diesem Script

-- Stored-Procedures nach Textteilen durchsuchen
-- Quelle: http://www.fractalcenter.de/2010/09/

SELECT 
  ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
  AND ROUTINE_DEFINITION LIKE '%Suchtext%'         -- hier Suchtext ersetzen

In Tabellen nach Spaltennamen suchen

-- Tabellen nach Spaltennamen durchsuchen
-- Quelle: http://www.fractalcenter.de/2010/08/tabellenspaltennamen-in-mssql-suchen/

SELECT
    sysobjects.name AS 'table_name', syscolumns.name AS 'column_name'
  , systypes.name AS 'datatype', syscolumns.length as 'length'
FROM sysobjects
     INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
     INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE syscolumns.name like '%Suchtext%'           -- hier Suchtext ersetzen
ORDER BY sysobjects.name,syscolumns.colid

Numerische Inhalte am Anfang aus Text separieren

Wenn man eine Spalte hat die die am Anfang numerische Werte enthält und danach alphanumerische kann es Fälle geben die numerischen Inhalte zu separieren.

Beispiel

Die Spalte Sp1 enthält folgende Zeilen:

3456B

2345

3456A

1234

SELECT Sp1
  FROM (SELECT '3456B' AS Sp1
        UNION SELECT '2345' AS Sp1
        UNION SELECT '3456A' AS Sp1
        UNION SELECT '1234' AS Sp1
       ) AS Tbl1
  ORDER BY LEFT(Sp1, LEN(Sp1) - (PATINDEX('%[0-9]%', REVERSE(Sp1))-1)), Sp1

Nach Ausführung des SQL-Statements sind die Zeilen folgendermaßen sortiert:

1234

2345

3456A

3456B

SQL-Statements

Es gibt viele Plattformen die einem die Sprache SQL näher bringen. Hier soll aber eher auf SQL-Statemens wert gelegt werden die nicht ständig Verwendung finden oder schwer zu finden sind.

Tabellen kopieren

-- Tabellenstruktur kopieren
CREATE TABLE zieltabelle LIKE quelltabelle;
-- Tabellenstruktur mit Inhalte kopieren => Sicherungskopie
SELECT * db.dbo.zieltabelle INTO db.dbo.quelltabelle;

-- Inhalte in Tabelle mit gleicher Tabellenstruktur uebertragen
INSERT INTO db.dbo.zieltabelle SELECT * FROM db.dbo.quelltabelle;
-- ggf. kann es Geschwindigkeitsvorteile haben, wenn man die Indizes vorher deaktiviert
ALTER TABLE db.dbo.zieltabelle DISABLE KEYS;
INSERT INTO db.dbo.zieltabelle SELECT * FROM db.dbo.quelltabelle;
ALTER TABLE db.dbo.zieltabelle ENABLE KEYS;

-- Inhalte uebertragen; Spalten im SQL mussen bei Quell-Tabelle und Ziel-Tabelle gleich sein
INSERT INTO db.dbo.zieltabelle
           ([Spalte1], [Spalte2])
     SELECT db.dbo.quelltabelle
            [Spalte1], [Spalte2]
—- oder
INSERT INTO db.dbo.zieltabelle
           ([Spalte1], [Spalte2])
     SELECT [Spalte1], [Spalte2]
       FROM db.dbo.quelltabelle

Schema eines Objektes (z.B. Tabelle) ändern

—- Schema ändern
ALTER SCHEMA schema_neu TRANSFER schema_alt.tabellenname;