3 MS- TSQL

engl: Data Control Language

GRANT ...
DENY ...
REVOKE ... 

engl.: Data Definition Language

CREATE type object_name
ALTER  type object_name
DROP   type object_name

engl.: Date Manipulation Language

SELECT ...
INSERT ...
UPDATE ...
DELETE ...

Eine Variabel ist ein benannter Hauptspeicherplatz und dient zur temporären Speicherung von Zwischenergebnissen.

declare @anz_laender int
set @anz_laender = 1

Kontrollanweisungen beeinflussen den Programmablauf

if @x > 1
  select ...
else 
  print ...

Bezeichner

Ein Bezeichner ist eine ein Name für eine Variabel, eine Prozedur oder ein Datenbankobjekt.

binary

Bitfolgen. Werden alse Folge von Hexzahlen dargestellt.

Bei binary ist die Anzahl der Speicherplätze in Bytes fest anzugeben.

varbinary passt die Länge dem Bedarf automatisch an.

In beiden Fällen darf die Länge nicht mehr als 8 KB betragen.

Image ist wie varbinary. Hier darf die Länge jedoch 8 KB überschreiten.

Ab SQL Server 2005 sollte anstatt image varbinary(max) eingesetzt werden

create table maschinecodes (
 pos int,
 code binary(3)
)
go
insert into maschinecodes values
(1, A1F2D3)
go

varbinary

image bzw. varbinary(max)

char

Zeichendaten mit fester Länge (max. 8KB)

create table buecher (
  isbn char(20),
  titel varchar,
  inhalt text
)
go
insert into buecher (
('3-930673-56-8', 'JavaScript', 'bla bla ....')
go
-- Wie kann man ein Apostroph in einem Text darstellen, 
–- wenn Apostrophe Textbegrenzer sind ?
-- Antwort: mit doppeltem Apostroph ''
Set @txt = N'Nu is''s vollbracht'

varchar

Zeichendaten variabler Länge (max. 8KB)

text bzw. varchar(max)

Zeichendaten variabler Länge (> 8KB)

datetime

Datum und Uhrzeit. SQL- Server kennt keine separaten Typen für nur Datum und nur Uhrzeit

Wertebereich: 1.1.1753 bis 31.12.9999

create table termine (
  zeit datetime,
  thema varchar
)
go
set dateformat dmy
go
insert into termine values
('3/9/2003 19:00', 'Zahnartzt')
go

smalldatetime

Datum und Uhrzeit.

Wertebereich: 1.1.1900 bis 6.6.2079

decimal

Festkommazahlen mit bis zu 38 Ziffern

create table lebensmittellager (
   artikel char(4),
   einwage dec(3,3), -- in kg
   preis   dec(7,2)  -- in Euro
)
go
insert into lebensmittellager values
('4711', 1.237, 2.99)
go   

numeric

Synonym für decimal

float

Gleitpunktzahlen von -1.79E+308 bis 1.79E308

declare @MasseJupiterInKg float = 1.8986E+27

real

Gleitpunktzahlen von -3.4E+38 bis 3.4E+38

bigint

Festkommazahlen von -2^63 bis 2^63 – 1

int

Festkommazahlen von -2^31 bis 2^31 -1

declare @A int = 3
declare @B int
declare @C int 

-- Integerdivision schneidet Nachkommastellen ab
Set @B = @A / 2

-- Modulo- Operation liefert den Rest
Set @C = @A % 2

smallint

Festkommazahlen von -2^15 bis 2^15 -1

tinyint

Festkommazahlen von 0 bis 255

money

Währungswerte (8 Byte pro Wert). Nach dem Komma werden 5 Stellen gespeichert. Damit liegt die Genauigkeit bei einem zehntausendstel einer Währungseinheit.

create table lebensmittellager (
   artikel char(4),
   einwage dec(3,3), -- in kg
   preis   money     -- in Euro
)
go
insert into lebensmittellager values
('4711', 1.237, €2.99)
go   

smallmoney

Währungswerte (4 Byte pro Wert)

bit

Daten mit Entscheidungsgehalt von einem Bit. Dient zur Darstellung boolscher Werte (TRUE/FALSE oder YES/NO) als 0/1.

declare @ich_sehe_fern bit

if @ich_sehe_fern = 0 or @ich_sehe_fern = 1
  print 'Deutsche Realitaet der Jahre 2014, 2015, ...'
else
  print 'Marktwirtschaft'

cursor

Kann nur für Stored Procedures mit OUTPUT- Parameter verwendet werden, die Verweis auf einen Cursor enthalten.

timestamp

Ist ein Zähler, der nach jeder Manipulation am Datensatz um 1 erhöht wird. Stellt damit eine Art Versionsnummer für den aktuellen Datensatz dar.

uniqueidentifier

16 Byte große Dezimalzahl, die einen GUID darstellt.

create table [dms-files] (
      file_id  uniqueidentifier,
      ...
)

insert into [dms-files] (file_id) values (NewId())

SQL_variant

Speichert werte beliebiger Datentypen, außer text, ntext, timestamp, image und sql_variant

table

Kann Menge von Tabellenzeilen aus einer Abfrage aufnehmen. Darf nur zur Deklaration lokaler Batch- Variabeln bzw als Rückgabewert selbstdefinierter Prozeduren eingesetzt werden.

nchar

Zeichenkettentypen für Unicodes analog char, varchar und text.

nchar und nvarchar können maximal 4000 Unicode- Zeichen aufnehmen.

N'€'

nvarchar

N'۩'

ntext

N'۩...'

xml

Neuer Datentyp in SQL- Server 2005. Hier können xml- Dokumente und Fragmente abgelegt, und mittels XML- Query Operationen analysiert werden. Dokumente besitzen gegenüber Fragmenten ein einziges Rootelement. Die XML- Dokumente können bis zu 2GB groß sein.

-- Tabelle mit XML- Typ
create table data.event_log (
        logtime         datetime,
        [log]           xml 
)
go

-- Variable mit XML Typ
DECLARE @logmsg xml 
Set @logmsg = '<def><AddNewSchema n="doktyp.xsd"/></def>'

<

Kleiner als

<=

kleiner gleich

>

größer als

>=

größer gleich

 =

gleich

!=

ungleich

Aggregieren innerhalb einer Gruppe von Datensätze alle Werte von Spalten

Können auf einzelne Spalten angewendet werden

Liefern Datensätze aus externen Quellen, z.B. Access- Datenbank

select sum(size) 
from files
group by name

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers);
GO

AVG, COUNT, MAX, MIN, SUM, VAR, STDDEV

OPENDATASOURCE, OPENROWSET

1 bis 10

Statusmeldungen / Informationen

11 bis 16

Benutzerdefinierte Fehlermeldungen

17 bis 25

Hard und Softwarefehler

Spalten mit sensitiven Daten können ausgeblendet werden.

create view telbuch
as
select name, vorname, tel
from   tab_personal

Ausblenden von Zeilen mit sensitiven Daten

create view off_mitarb
as
select * from personal
where beruf not like 'geheimagent'

Vereinfachen des Zugriffs in normalisierten Datenbanken

create view tab_personal
as
select * from
personal join tel
on 
personal.id = tel.id

Vorverarbeitung von Daten

create view hochrechnung
as
select partei, sum(stimmen)
from auszaehlungen
group by partei

1

Datensätze aus den Quelltabellen laden

2

Datensätze entfernen, die Where Filterausdruck nicht erfüllen

3

Gruppieren bezüglich Group by

5

Pro Gruppe Spatenaggregate aus Select berechnen

6

Gruppen entfernen, die Having Filterausdruck nicht erfüllen

Hat eine Objekt eine Lesesperre, dann kann diese nur von den Transaktionen ausgelesen werden, die die Lesesperre gesetzt haben. Parallele Lesevorgänge sind möglich, jedoch keine Aktualisierungen und Löschvorgänge.

Ein Objekt mit einer exklusiven Sperre kann nur von der Transaktion bearbeitet werden, die die Sperre gesetzt hat. Alle anderen Transaktionen wird der Zugriff auf das Objekt verwehrt.

Transaktionen, bei denen im ersten Schritt die Datensätze durchsucht (Lesesperre) und im zweiten Schritt die Datensätze geändert werden (Exklusive Sperre), setzten Aktualisierungssperren um Deadlocks zu vermeiden. Diese überspannen den Lese- als auch den Aktualisierungsvorgang.

Zeigen an, das demnächst ein Objekt mit einer Sperre belegt wird.

Die Ausdehnung von Sperren wird auf Zeilen eingeschränkt

Die Ausdehnung von Sperren wird auf Seiten eingeschränkt

Die Sperren betreffen immer die gesamte Tabelle.

Belegt eine Tabelle mit einer exklusiven Sperre

Es werden nie Sperren auf die Tabelle angewendet. Dadurch können Datensätze von einer Transaktion gelesen werden, während eine zweite sie noch bearbeitet (Dirty Reads).

Die gesperrten Zeilen von aktualisierenden Transaktionen werden durch die lesende Transaktion übersprungen. Im Resultset fehlen diese dann, was zu Fehlern bei der Auswertung führen kann.

Lesesperren auf einer Tabelle werden erst aufgehoben, wenn die lesende Transaktion beendet wird. Update und Delete ist auf der Tabelle nicht möglich, wohl aber Insert.

Lesesperren werden erst aufgehoben, wenn die anfordernde Transaktion beendet ist. Insert- Operationen auf den geperrten Objekten sind blockiert.

Transaktion

Eine Transaktion ist eine Folge von T-SQL Anweisungen, die mit dem Befehl Begin Transaction eingeleitet, und mit den Befehlen Commit Transaction oder Rollback Transaction beendet werden.

Werden alle Anweisungen innerhalb einer Transaktion erfolgreich ausgeführt, dann muß die Tranwsaktion mit Commit Transaction abschließen, und die durch Anweisungen bewirkten Datenänderungen werden dauerhaft in der Datenbank übernommen.

Andernfalls muß die Transaktion durch Rollback Transaction beendet werden. In diesem Falle werden alle Datenänderungen wieder zurückgenommen, und die Datenbank befindet sich in dem Zustand wie vor der Ausführung der Transaktion.

ACID

ACID ist ein Akronym und steht für die vier Anforderungen, die eine Transaktion erfüllen muß: Atomicity (Unteilbarkeit), Consistency (Konsistenz), Isolation (Isolation), Durability (Dauerhaftigkeit).

Nur Daten werden gelesen, die zuvor mit einer Lesesperre belegt werden konnten. Dirty Reads sind nicht möglich.

Es werden keine Sperren gesetzt. Dirty Reads sind möglich

Lesesperren werden erst nach dem Ende der Transaktion aufgehoben. Wiederholte Select- Abfragen innerhalb einer Transaktion führen zu identischen Resultsets, wenn zwischen diesen keine Änderungsoperationen innerhalb der Transaktion vorgenommen wurden. Änderungsoperationen andere Transaktionen sind während der gesamten Verarbeitung einer Transaktion blockiert.

Wie REPEATABLE READ, zusätzlich werden noch Einfügeoperationen anderer Transaktionen verhindert.

Einsatz

Trigger werden implementiert zur Durchsetzung der referentiellen- und Datenintegrität sowie der Kapselung von Geschäftsregeln.

temporäre Tabellen

Für temporäre Tabellen können keine Trigger erstellt werden

Resultsets

Trigger dürfen keine Resultsets zurückliefern. Select- Anwiesungen in Triggern sollten in if exists(...) gekapselt werden.

Verschlüsselung/Sicherheit

Mit der Option with encryption kann die definition der Trigger in der Tabelle syscomments verschlüsselt werden.

verbotene Anweisungen

Folgende Anweisungen dürfen in Triggerimplementierungen nicht auftreten: alter table, alter database, truncate table, grant, revoke, reconfigure, load database, load transaction, update statistics, select into, alle disk -Anweisungen

rollback

Rollbacks von Transaktionen in Triggern können zu unerwarteten Ergebnissen führen