Tabellen vereinen

Mit JOIN kann die ursprüngliche Tabelle von FROM mit einer zweiten Tabelle verbunden werden. Die Verbindung selbst wird mit ON definiert.

LEFT (OUTER) JOIN

Hier werden alle Zeilen aus der bestehenden Tabelle ausgegeben und, wenn vorhanden, mit der zweiten Tabelle ergänzt.

FROM Items i
   LEFT JOIN Categories c ON i.CategoryID = c.ID

"Curry" und "Essig" werden also trotzdem angezeigt, einfach ohne die Werte aus der Kategorie-Tabelle. Jedoch die Kategorie "giftig" wird nicht angezeigt.

FULL (OUTER) JOIN

Damit werden alle Zeilen aus beiden Tabellen ausgegeben.

FROM Items i
   FULL JOIN Categories c ON i.CategoryID = c.ID

Das Ergebniss beinhaltet alle Zeilen und wo vorhanden, auch mit den Infos der jeweils anderen Tabelle befüllt.

Blättern

Möchte man nur die ersten 10 Zeilen der Abfrage ausgegeben erhalten, kann dies einfach mit einem TOP 10 geschehen:

SELECT TOP 10 * FROM table ORDER BY name

Möchte man jedoch eine Funktion zum Blättern, ist das schon umständlicher. Dafür muss man dies nicht ganz so intuitiv am Ende der Abfrage hinzufügen:

SELECT *
FROM table
ORDER BY name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Schleifen

Man kann im SQL mittels WHILE Schleifen nutzen. Selbstverständlich kann man in der WHILE-Bedinung alle gültigen Vergleiche anstellen. Das folgende Beispiel zeigt, wie man auch eine FOR-Schleife bauen kann. Den nächsten Schleifendurchlauf lässt sich mittels CONTINUE einleiten.

DECLARE @i int = 0

WHILE @i < 10
BEGIN
   SET @i = @i + 1
   IF @i = 6 CONTINUE
   PRINT CONCAT(@i, '. Iteration')
END

Json

MSSQL kann auch mit Json umgehen. Ein Json-Objekt kann mit OPENJSON gelesen werden.

DECLARE @json nvarchar(max) = '[{"Gegenstand": "Gummibärchen", "Bestand": 31},{"Gegenstand": "Pommes Chips", "Bestand": 13}]'

SELECT * FROM OPENJSON(@json) WITH
(
   Gegenstand nvarchar(max) 'strict $.Gegenstand',
   Bestand int 'strict $.Bestand'
)

XML

Auch lässt sich mittels OPENXML dieses alte Format lesen. Man kann mehrere Zeilen auslesen, wie auch Felderinhalte, -Bezeichnungen und -Attribute.

DECLARE @xml nvarchar(max) = '<gegenstaende><gegenstand order="1" added="20240204"><name>Apfel</name><bestand>7</bestand></gegenstand><gegenstand order="2"><name>Banane</name><bestand>3</bestand></gegenstand></gegenstaende>'
DECLARE @i int
EXEC sp_xml_preparedocument @i out, @xml, '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />'

SELECT * FROM OPENXML(@i, '/gegenstaende/*', 1) WITH
(
   Gegenstand nvarchar(8) 'name',
   Bestand int 'bestand',
   Feld nvarchar(16) '@mp:localname',
   Inhalt nvarchar(16) '.',
   Reihenfolge int '@order'
)

EXEC sp_xml_removedocument @i

Index

Ein Index ist eine interne "Merkliste" der Datenbank, um schneller den entsprechenden Datensatz im Speicher zu finden. Werden Datensätze entfernt und hinzugefügt, wird der entsprechende Index-Eintrag ebenfalls gelöscht bzw. hinzugefügt. Je öfters dies stattfindet, desto schneller fragmentiert der Index. Mit diesem Skript, lässt sich die Fragmentierung auslesen und optimieren, in dem er neu erstellt wird.

BEGIN TRANSACTION

DECLARE @overview as table
(
   [Row] int,
   [Schema] nvarchar(128),
   [Table] nvarchar(128),
   [Index] nvarchar(128),
   [Fragmentation] float
)

INSERT INTO @overview
   SELECT
      ROW_NUMBER() OVER (ORDER BY d.avg_fragmentation_in_percent),
      s.name as [Schema],
      t.name as [Table],
      i.name as [Index],
      d.avg_fragmentation_in_percent

   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS d
      JOIN sys.tables t on T.object_id = d.object_id
      JOIN sys.schemas s on T.schema_id = s.schema_id
      JOIN sys.indexes i ON I.object_id = d.object_id AND d.index_id = i.index_id

   WHERE d.database_id = DB_ID()
      AND i.name IS NOT NULL
      AND d.avg_fragmentation_in_percent > 0

DECLARE @i int = 0
DECLARE @max int = (SELECT MAX([row]) FROM @overview)
DECLARE @sql nvarchar(max)

WHILE @i < @max
BEGIN
   SET @i = @i + 1
   SET @sql =
   (
      SELECT CONCAT('ALTER INDEX [', [Index], '] ON ', [Table], ' REBUILD')
      FROM @overview WHERE [row] = @i
   )
   EXEC(@sql)
END

--SELECT * FROM @overview ORDER BY [Row]

COMMIT TRANSACTION