Datentypen

SQL Server besitzt fest vordefinierte Basisdatentypen. Über die Definition von Einschränkungen können aus den Basisdatentypen benutzerdefinierte Typen gewonnen werden.

Die Datentypen des SQL- Servers lehnen sich an den ANSI- Standard an. Die Abbildung der SQL Server Typen auf ADO.NET wird hier beschrieben.

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

  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'

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

  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   

  declare @MasseJupiterInKg float = 1.8986E+27

  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

  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   

  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'

  create table [dms-files] (
      file_id  uniqueidentifier,
      ...
)
insert into [dms-files] (file_id) values (NewId())

  -- 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>'

Konvertierungen zwischen den Typen

  CAST(wert as Zieltype)
CONVERT(Zieltyp, wert, Option)

Prüfen auf Typ

  ISDATE(Ausdruck)    -– Gibt 1 zurück, wenn der Ausdruck ein Datumswert ergibt, sonst 0
ISNUMERIC(Ausdruck) –- Gibt 1 zurück, wenn der Ausdruck ein nummerischer Wert ergibt, sonst 0

uniqueidentifier - GUID

uniqueidentifiers sind 128 Bit große Zahlen, die nach einem bestimmten Verfahren gebildet werden, so dass weltweit eindeutige ID's entstehen, auch GUID's (Global Unique IDentifier) genannt.

Auf GUID's sind Vergleichoperatoren definiert.

Eine neue GUID kann mittels der Funktion NewId() gewonnen werden.

Zeichenkettenfuntionen

  len(Ausdruck)                   -- Gibt die Zeichenzahl in der durch Ausdruck gebildeten Zeichenkette
                                -- zurück
substring(Ausdruck, start, anz) –- Scheidet aus der durch Ausdruck gebildeten Zeichenkette ab
                                -- start anz Zeichen aus und gibt diese Teilzeichenkette zurück
upper(Ausdruck)                 -- Wandelt alle Klein- in Großbuchstaben um und gibt diese Zeichenkette
                                -- zurück
lower(Ausdruck)                 -- Wandelt alle Groß- in Kleinbuchstaben um und gibt diese Zeichenkette
                                -- zurück
ltrim(Ausdruck)                 -- entfernt alle führenden Leerzeichen
rtrim(Ausdruck)                 -- entfernt alle nachfolgenden Leerzeichen

Soundex

Soundex ist eine Textfunktion, die einen sog. Soundex- Code zurückliefert. Ähnlich klingende Wörter liefert den gleichen Soundex- Code.

Beispiel: Der Name Meier liefert mit e oder mit a geschrieben stets den gleichen Soundex- Code M600

select soundex('Maier')

Contains

MSSQL 2000 kann mit dem MSIndexServer zusammenarbeiten. Das Ergebnis sind sog. Volltextindizes, die z.B. das anspruchsvolle Contains- Prädikat ermöglichen.

Einrichten von Volltextindizes siehe S. 565 ff.

Contains gibt True zurück, wenn eine Spalte ein gesuchtes Wort enthält. Der vergleich kann dabei sehr flexibel gestaltet werden.

• Ist ein Wort in der Nähe eines zweiten enthalten ('programmieren NEAR c#')
• Ein Wort und seine Ableitungen wie programmieren -> Programmierung (' FORMSOF (INFLECTIONAL, programmieren) ')

select * from words where Contains ('programmieren NEAR c#')

xml (2005)

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.

xml Instanzen

Instanzen des Typs xml werden durch Konvertierung aus einem String erzeugt. Quelle kann dabei eine Wert eines beliebigen String- Types sein.

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

Einschränkungen

Operationen auf xml- Datentypen unterliegen folgenden Einschränkungen:

• sie können nicht in einer sql_variant Instanz gespeichert werden
• in ntext kann nicht konvertiert werden
• bei Spaltendefinitionen können sie nicht als primary key, foreign key, unique, collate oder rule ausgezeichnet werden
• sie können nicht sortiert, gruppiert und verglichen werden
• sie können nicht als Eingabe für eingebaute Scalarfunktionen benutzt werden (Ausnahmen: ISNULL(..), COALESCE(..), DATALENGTH(..))
• XML- Prozessorinstruktionen (<?xml ... ?>) werden vor dem Speichern entfernt.
• Die Reihenfolge der Attribute bleibt nach dem Speichern und wieder auslesen nicht erhalten
• In der Standardeinstellung werden nicht signifikante Whitespaces gelöscht
• Namespace- Prefixe werden nicht gesichert. Die Prefixe vor der Sicherung unterscheiden sich im allgemeinen von denen nach dem Lesen

Methoden des XML- Typs

Der xml- Datentyp liefert Methoden zum Analysieren und ändern des Inhaltes eines xml- Wertes. Zur Navigation und Abfrage von Werten sethen die Methoden generell XQuery ein. XQuery ist ein Stadard des w3.org und wird spezifiziert unter XQuery 1.0 Spzifikation des w3.org .

Namespaces in XQuery

Befinden sich die Elemente eines XML in Namespaces, dann müssen diese durch Deklarationen im XQuery- Ausdruck definiert werden.

Syntax:

declare namespace prefix="Namespace- Uri";

Das vereinbarte Prefix muß dann im XQuery- Ausdruck allen Knotennamen vorangestellt werden (auch wenn es sich um einen default- Namespace handelt).

  select record.value(
        'declare namespace n="http://www.tracs.de/xsdFotoFileinfo.xsd";
         (/n:foto-fileinfo/n:bildformat/@breite)[1]', 'int') as breite
from data.records

Die Methoden werden in einer SQL- Select Anweisung als Subqueries ausgeführt. Die mit Subqueries verknüpften Einschränkungen übertragen sich auch auf XML- Methoden.

Relationale- und Vergleichsoperatoren in XQuery

XQuery ist von der XPath- Syntax abgeleitet. Da XQuery aber nicht Bestandteil von XML Dokumenten wie XPath in XSLT ist, konnte die Sytax wesentlich komfortabler ausgelet werden. So werden die relationalen Operatoren nicht wie in XPath durch &lt; unschrieben, sondern direkt notiert:

  <

  <=

  >

  >=

   =

  !=

query(...)

Syntax:

xmlInstanz.query(XQuery)

Durch einen XQuery- Ausdruck wird eine Knotenmenge im XML- Wert definiert. Query liefert diese Knotenmenge als Fragment zurück:

  -- Abfrage auf data.event_log
Select log.query('/def/AddNewSchema')
from data.event_log
-- Ergebniss sind XMLFragmente der Form '<AddNewSchema n='...'/> für alle Zeilen,
-- auf deren log- Spalte der XQuery Ausdruck passt

value(...)

Syntax:

xmlInstanz.value(XQuery, SqlTyp)

Durch einen XQuery- Ausdruck wird ein skalarer Wert eines XML- Elementes oder Attributes im XML- Wert definiert. Value liefert diesen und konvertiert ihn dabei in den gewünschten Typ.

  -- Abfrage auf data.event_log
Select log.value('(/def/AddNewSchema/@n)[1]', 'nvarchar(400)')
from data.event_log
-- Ergebniss sind nvarchar(400) Strings mit den Namen der Schemadateien,
-- die hinzugefügt wurden

exist(...)

Syntax:

xmlInstanz.exist(XQuery)

Wenn ein XML- Wert eine durch den XQuery- Ausdruck definierte Knotenmenge enthält, dann liefert die Funktion 1 zurück. Wird kein Knoten aus der definierten Knotenmenge gefunden, dann liefert die Funktion 0 zurück. Ist der XML Wert ein Null- Wert, dann wird NULL zurückgeliefert.

  -- Abfrage auf data.event_log
Select *
from data.event_log
where log.exist('/def/AddNewSchema[@n="Doktyp.xsd"]')
-- Alle Zeilen, die das Fragment <def><AddNewSchema n="doktyp.xsd"/></def> enthalten
-- werden zurückgeliefert

modify(...)

Syntax:

xmlInstanz.modify(XML_DML)

Mittels der modify- Methode kann innerhalb einer SQL- Update- Anweisung in der Set- Klausel ein xml- Wert geändert werden. Dazu ist ein Spezieller XML_DML Ausdruck über die modify- Methode auf dem xml- Wert anzuwenden:

nodes(...)

Datumsfunktionen

Zeitintervalle berechnen und addieren

  dateadd(Intervall, anz_intervalle, datum) –- Zum Datum wird ein Zeit- Intervall anz_intervalle mal 
                                          -- hinzuaddiert
datediff(Intervall, startdatum, enddatum) – Der Zeitraum zwischen Start und enddatum wird als Menge
                                          -- in der Einheit Intervall zurückgegeben
-- Intervalle: year, month, week, day, hour, minute, second, millisecond

Tag, Monat oder Jahr aus einem Datum extrahieren

  day(Ausdruck)    -- gibt den Tag als Wert zw. 1 und 31 zurück 
month(Ausdruck)  -- gibt den Monat als Wert zw. 1 und 12 zurück
year(Ausdruck)   -- gibt das Jahr zurück

Aktuelles Datum

  declare @jetzt datetime
set @jetzt = current_timestamp 
-- alternativ
set @jetzt = getdate()

Konvertieren aus/ins ODBC- Format

Allgemein:

convert (Zieltyp, Quelltyp, Style)

Style muß bei der ODBC- Konvertierung auf 20 stehen.

Ausgabe des aktuellen Datums im yyyy-mm-dd hh:mm:ss Format

  print convert(char(30), current_timestamp,20)
go

Umwandeln eines ODBC DateTime- Literals in einen SQL- Server DateTime wert:

  insert into UpdateLog (begin) Values (convert(DateTime, "2004-05-23 19:21:31", 20)
go