SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

Strategische Wissens-Roadmap 2026

SQL Server

Datenbankdesign

Klare Regeln für saubere SQL-Server-Architekturen im Mittelstand

1

Entscheidungsmatrix

10 Kriterien — wann View, wann Stored Proc, wann beides

2

10 Design-Fallen

Typische Fehler in gewachsenen Datenbanken — mit Sofort-Korrekturen

3

Performance-Muster

Indexed Views, Execution Plans, Plan Caching — richtig eingesetzt

4

Sicherheits-Framework

Berechtigungen, Row-Level Security und Ownership Chaining

5

30-Tage-Refactoring-Plan

Gewachsene Datenbanken strukturiert bereinigen — ohne Betriebsunterbrechung

DIE DREI HÄUFIGSTEN FEHLMUSTER IN KMU-DATENBANKEN

■  Reporting direkt auf Basistabellen — ohne Views als Abstraktionsschicht. Jede Schemaänderung bricht alle Berichte.

■  Stored Procedures als Queries — Prozeduren ohne Parameter, die einfach einen SELECT ausführen und ein Resultset zurückgeben. Dafür sind Views zuständig.

■  Views mit Seiteneffekten — Entwickler versuchen, Views mit INSTEAD OF Triggern schreibbar zu machen. Fast immer ein Wartungsalptraum.

-- Einfache View: Abstraktionsschicht für Reporting

CREATE OR ALTER VIEW rpt.V_Auftraege_Aktuell AS

SELECT

    a.AuftragsNr,

    k.Name           AS Kunde,

    k.Region,

    m.Vorname + ' ' + m.Nachname AS Vertreter,

    a.Auftragsdatum,

    a.Lieferdatum,

    SUM(p.Menge * p.Einzelpreis * (1 - p.Rabatt)) AS Nettowert,

    a.Status

FROM dbo.Auftraege a

JOIN dbo.Kunden k      ON a.KundenNr    = k.KundenNr

JOIN dbo.Mitarbeiter m ON a.VertreterNr = m.MitarbeiterNr

JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

WHERE a.Status NOT IN ('STORNO', 'ENTWURF')

GROUP BY a.AuftragsNr, k.Name, k.Region,

         m.Vorname, m.Nachname, a.Auftragsdatum,

         a.Lieferdatum, a.Status;

-- Stored Procedure: Auftrag anlegen mit vollständiger Geschäftslogik

CREATE OR ALTER PROCEDURE dbo.SP_Auftrag_Anlegen

    @KundenNr        INT,

    @VertreterNr     INT,

    @Lieferdatum     DATE,

    @AuftragsNr      INT OUTPUT,    -- Rückgabewert

    @Fehlermeldung   NVARCHAR(500) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    SET @Fehlermeldung = NULL;

    BEGIN TRY

        -- Geschäftsregeln prüfen

        IF NOT EXISTS (SELECT 1 FROM dbo.Kunden WHERE KundenNr = @KundenNr

                       AND Gesperrt = 0)

        BEGIN

            SET @Fehlermeldung = 'Kunde nicht vorhanden oder gesperrt.';

            RETURN -1;

        END

        IF @Lieferdatum < CAST(GETDATE() AS DATE)

        BEGIN

            SET @Fehlermeldung = 'Lieferdatum darf nicht in der Vergangenheit liegen.';

            RETURN -2;

        END

        BEGIN TRANSACTION;

            INSERT INTO dbo.Auftraege

                (KundenNr, VertreterNr, Auftragsdatum, Lieferdatum, Status)

            VALUES

                (@KundenNr, @VertreterNr, CAST(GETDATE() AS DATE),

                 @Lieferdatum, 'NEU');

            SET @AuftragsNr = SCOPE_IDENTITY();

            -- Audit-Log

            INSERT INTO dbo.AuditLog (Tabelle, AktionTyp, ObjektID, Zeitpunkt)

            VALUES ('Auftraege', 'INSERT', @AuftragsNr, SYSUTCDATETIME());

        COMMIT TRANSACTION;

        RETURN 0;  -- Erfolg

    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        SET @Fehlermeldung = ERROR_MESSAGE();

        RETURN -99;

    END CATCH

END

-- Inline TVF: parametrisierte View — verwendbar in JOINs und WHERE-Klauseln

CREATE OR ALTER FUNCTION dbo.FN_Auftraege_Je_Kunde

(

    @KundenNr    INT,

    @DatumVon    DATE,

    @DatumBis    DATE

)

RETURNS TABLE

AS

RETURN

(

    SELECT

        a.AuftragsNr,

        a.Auftragsdatum,

        a.Status,

        SUM(p.Menge * p.Einzelpreis) AS Bruttowerk

    FROM dbo.Auftraege a

    JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

    WHERE a.KundenNr    = @KundenNr

      AND a.Auftragsdatum BETWEEN @DatumVon AND @DatumBis

      AND a.Status NOT IN ('STORNO','ENTWURF')

    GROUP BY a.AuftragsNr, a.Auftragsdatum, a.Status

);

-- Verwendung: wie eine Tabelle — joinbar, filterbar, in CTEs verwendbar

SELECT k.Name, f.AuftragsNr, f.Bruttowerk

FROM dbo.Kunden k

CROSS APPLY dbo.FN_Auftraege_Je_Kunde(k.KundenNr, '2025-01-01', '2025-12-31') f

WHERE f.Bruttowerk > 10000

ORDER BY f.Bruttowerk DESC;

1

Nur Lesezugriff, keine Datenveränderung

✓

—

✓

2

Schreiboperation (INSERT / UPDATE / DELETE)

—

✓

—

3

Eingabeparameter werden benötigt

—

✓

✓

4

Ergebnis soll in anderem SELECT verwendbar sein (JOIN, CTE)

✓

—

✓

5

Transaktion über mehrere Tabellen erforderlich

—

✓

—

6

Fehlerbehandlung mit TRY/CATCH und Rollback

—

✓

—

7

Abstraktion für Power BI / Reporting

✓

—

✓

8

Komplexe Geschäftslogik mit Verzweigungen

—

✓

—

9

Zeilenweise Filterung nach Benutzerkontext

✓ (+ RLS)

✓

✓

10

Soll indiziert werden (Indexed View)

✓

—

—

DIE FAUSTREGEL FÜR DEN ALLTAG

■  Lesen ohne Parameter → View.

■  Lesen mit Parametern → Inline Table-Valued Function.

■  Schreiben, Transaktionen, Fehlerbehandlung → Stored Procedure.

■  Alles andere → Stored Procedure, aber prüfen ob eine View + TVF sauberer wäre.

-- Mit SCHEMABINDING: SQL Server verhindert Schemaänderungen an Basistabellen

CREATE OR ALTER VIEW rpt.V_Kunden_Aktiv

WITH SCHEMABINDING

AS

SELECT

    k.KundenNr,

    k.Name,

    k.PLZ,

    k.Ort,

    k.Email,

    k.Jahresumsatz

FROM dbo.Kunden k          -- Schema muss explizit angegeben werden (dbo.)

WHERE k.Gesperrt = 0;

-- Jetzt schlägt ALTER TABLE dbo.Kunden DROP COLUMN Email fehl:

-- "Cannot DROP COLUMN 'Email' because it is referenced by object 'V_Kunden_Aktiv'."

CREATE OR ALTER PROCEDURE dbo.SP_Statistik_Aktualisieren AS

BEGIN

    SET NOCOUNT ON;  -- Erste Zeile, immer

    DECLARE @Zeilen INT;

    UPDATE dbo.Kunden

    SET Jahresumsatz = (

        SELECT SUM(Nettobetrag)

        FROM dbo.Auftraege

        WHERE KundenNr = dbo.Kunden.KundenNr

          AND YEAR(Auftragsdatum) = YEAR(GETDATE())

    );

    SET @Zeilen = @@ROWCOUNT;  -- Sofort nach DML sichern

    INSERT INTO dbo.ETL_Protokoll (ProzessName, ZeilenVerarbeitet, Zeitpunkt)

    VALUES ('Statistik_Aktualisieren', @Zeilen, SYSUTCDATETIME());

END

-- Schlecht: SELECT * ist eine Zeitbombe

CREATE VIEW rpt.V_Artikel_Schlecht AS

SELECT * FROM dbo.Artikel;  -- NIEMALS so!

-- Gut: explizite Spaltenliste

CREATE OR ALTER VIEW rpt.V_Artikel

WITH SCHEMABINDING

AS

SELECT

    a.ArtikelNr,

    a.Bezeichnung,

    a.Warengruppe,

    a.Einheit,

    a.VKPreis,

    a.EKPreis,

    a.AktuellerBestand

FROM dbo.Artikel a

WHERE a.Gesperrt = 0;

-- Anti-Pattern: View auf View auf View

CREATE VIEW rpt.V_Umsatz_Top AS

SELECT * FROM rpt.V_Umsatz_Gesamt    -- View auf View

WHERE Rang <= 10;

-- Besser: direkt auf die Basistabellen mit CTE

CREATE OR ALTER VIEW rpt.V_Umsatz_Top

WITH SCHEMABINDING

AS

WITH Basis AS (

    SELECT

        k.KundenNr, k.Name,

        SUM(p.Menge * p.Einzelpreis) AS Umsatz,

        RANK() OVER (ORDER BY SUM(p.Menge * p.Einzelpreis) DESC) AS Rang

    FROM dbo.Kunden k

    JOIN dbo.Auftraege a     ON k.KundenNr = a.KundenNr

    JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

    WHERE a.Status NOT IN ('STORNO','ENTWURF')

    GROUP BY k.KundenNr, k.Name

)

SELECT KundenNr, Name, Umsatz, Rang

FROM Basis

WHERE Rang <= 10;

CREATE OR ALTER PROCEDURE dbo.SP_Bestellung_Abschliessen

    @BestellungsNr INT

AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

        BEGIN TRANSACTION;

            UPDATE dbo.Bestellungen

            SET Status = 'ABGESCHLOSSEN', AbschlussDatum = GETDATE()

            WHERE BestellungsNr = @BestellungsNr;

            UPDATE dbo.Lagerbestand

            SET AktuellerBestand = AktuellerBestand - p.Menge

            FROM dbo.Lagerbestand lb

            JOIN dbo.Bestellpositionen p ON lb.ArtikelNr = p.ArtikelNr

            WHERE p.BestellungsNr = @BestellungsNr;

            -- Konsistenzprüfung: kein negativer Bestand erlaubt

            IF EXISTS (SELECT 1 FROM dbo.Lagerbestand WHERE AktuellerBestand < 0)

            BEGIN

                RAISERROR('Bestand würde negativ werden.', 16, 1);

            END

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        -- XACT_STATE(): -1 = nicht committbar, 1 = committbar, 0 = keine Transaktion

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        INSERT INTO dbo.Fehlerprotokoll

            (Prozedur, Fehlernummer, Fehlermeldung, Zeitpunkt, Parameter)

        VALUES ('SP_Bestellung_Abschliessen', ERROR_NUMBER(),

                ERROR_MESSAGE(), SYSUTCDATETIME(),

                CAST(@BestellungsNr AS NVARCHAR));

        THROW;  -- Fehler an Aufrufer weitergeben

    END CATCH

END

-- Business-Logik als dokumentierte Scalar Function kapseln

CREATE OR ALTER FUNCTION dbo.FN_Deckungsbeitrag

(

    @VKPreis    DECIMAL(18,4),

    @EKPreis    DECIMAL(18,4),

    @Menge      DECIMAL(18,4),

    @Rabatt     DECIMAL(5,4)   -- 0.10 = 10 %

)

RETURNS DECIMAL(18,2)

AS

BEGIN

    -- Gem. Kalkulations-Richtlinie Einkauf, Version 2025-03

    -- DB I = Nettoumsatz - Wareneinsatz

    RETURN (@VKPreis * @Menge * (1 - @Rabatt)) - (@EKPreis * @Menge);

END

-- View verwendet die zentrale Funktion — Logik nur einmal

CREATE OR ALTER VIEW rpt.V_Auftragspositionen_DB

WITH SCHEMABINDING

AS

SELECT

    p.PositionsID,

    p.AuftragsNr,

    p.ArtikelNr,

    p.Menge,

    p.Einzelpreis,

    p.Rabatt,

    dbo.FN_Deckungsbeitrag(p.Einzelpreis, a.EKPreis, p.Menge, p.Rabatt) AS DeckungsbeitragI

FROM dbo.Auftragspositionen p

JOIN dbo.Artikel a ON p.ArtikelNr = a.ArtikelNr;

-- FALSCH: Power BI ruft Stored Procedure auf

-- EXEC dbo.SP_Umsatz_Je_Region @Jahr = 2025  -- kein DirectQuery, kein Query Folding

-- RICHTIG: Inline TVF — voll kompatibel mit Power BI DirectQuery

CREATE OR ALTER FUNCTION rpt.FN_Umsatz_Je_Region

(

    @Jahr INT

)

RETURNS TABLE

AS

RETURN

(

    SELECT

        k.Region,

        SUM(p.Menge * p.Einzelpreis * (1 - p.Rabatt)) AS Nettoumsatz,

        COUNT(DISTINCT a.AuftragsNr)                   AS Auftragsanzahl,

        COUNT(DISTINCT a.KundenNr)                     AS Kundenanzahl

    FROM dbo.Auftraege a

    JOIN dbo.Kunden k            ON a.KundenNr  = k.KundenNr

    JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

    WHERE YEAR(a.Auftragsdatum) = @Jahr

      AND a.Status NOT IN ('STORNO','ENTWURF')

    GROUP BY k.Region

);

-- Alle Objekte, die von einer bestimmten View abhängen

SELECT

    OBJECT_SCHEMA_NAME(d.referencing_id) AS AbhaengigesSchema,

    OBJECT_NAME(d.referencing_id)        AS AbhaengigesObjekt,

    o.type_desc                          AS ObjektTyp

FROM sys.sql_expression_dependencies d

JOIN sys.objects o ON d.referencing_id = o.object_id

WHERE d.referenced_entity_name = 'V_Auftraege_Aktuell'

  AND d.referenced_schema_name = 'rpt'

ORDER BY o.type_desc, AbhaengigesObjekt;

-- Alle Objekte, von denen ein bestimmtes Objekt abhängt (umgekehrte Richtung)

SELECT

    d.referenced_schema_name + '.' + d.referenced_entity_name AS Abhaengigkeit,

    d.referenced_minor_name AS Spalte

FROM sys.sql_expression_dependencies d

WHERE d.referencing_id = OBJECT_ID('rpt.V_Auftraege_Aktuell')

ORDER BY Abhaengigkeit;

-- GEFÄHRLICH: SQL Injection möglich, kein Plan Caching

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.' + @Tabellenname;

EXEC(@sql);  -- NIEMALS SO!

-- SICHER: sp_executesql mit parametrisierten Werten

CREATE OR ALTER PROCEDURE dbo.SP_Artikel_Suche

    @Suchbegriff  NVARCHAR(100),

    @Warengruppe  NVARCHAR(50) = NULL

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @sql    NVARCHAR(MAX);

    DECLARE @params NVARCHAR(500);

    SET @sql = N'SELECT ArtikelNr, Bezeichnung, VKPreis

                 FROM dbo.Artikel

                 WHERE Bezeichnung LIKE @p_Such

                   AND (@p_WG IS NULL OR Warengruppe = @p_WG)

                   AND Gesperrt = 0';

    SET @params = N'@p_Such NVARCHAR(102), @p_WG NVARCHAR(50)';

    EXEC sp_executesql @sql, @params,

        @p_Such = '%' + @Suchbegriff + '%',

        @p_WG   = @Warengruppe;

END

-- Indexed View: materialisiert die Aggregation permanent

CREATE OR ALTER VIEW rpt.V_Umsatz_Je_Warengruppe_Monat

WITH SCHEMABINDING   -- Pflicht für Indexed Views

AS

SELECT

    a.Warengruppe                                AS Warengruppe,

    YEAR(b.Auftragsdatum)                        AS Jahr,

    MONTH(b.Auftragsdatum)                       AS Monat,

    SUM(p.Menge * p.Einzelpreis)                 AS Umsatz,

    COUNT_BIG(*)                                 AS Anzahl   -- COUNT_BIG Pflicht bei GROUP BY

FROM dbo.Artikel a

JOIN dbo.Auftragspositionen p ON a.ArtikelNr = p.ArtikelNr

JOIN dbo.Auftraege b          ON p.AuftragsNr = b.AuftragsNr

WHERE b.Status NOT IN ('STORNO', 'ENTWURF')

GROUP BY a.Warengruppe, YEAR(b.Auftragsdatum), MONTH(b.Auftragsdatum);

-- Unique Clustered Index: materialisiert die View physisch

CREATE UNIQUE CLUSTERED INDEX IDX_V_Umsatz_WG_Monat

ON rpt.V_Umsatz_Je_Warengruppe_Monat (Warengruppe, Jahr, Monat);

-- Ab jetzt: SQL Server nutzt den Index automatisch — auch wenn die Basistabellen direkt abgefragt werden

-- Schicht 1: Basis-Views — 1:1 auf Tabellen, nur Filterung und Umbenennung

CREATE OR ALTER VIEW base.V_Kunden

WITH SCHEMABINDING AS

SELECT KundenNr, Name, PLZ, Ort, Email, Region, Jahresumsatz, Gesperrt

FROM dbo.Kunden;

-- Schicht 2: Join-Views — verknüpfen mehrere Basis-Views

CREATE OR ALTER VIEW stg.V_Auftraege_Komplett

WITH SCHEMABINDING AS

SELECT

    a.AuftragsNr, a.Auftragsdatum, a.Status,

    k.KundenNr, k.Name AS Kunde, k.Region,

    m.MitarbeiterNr AS VertreterNr,

    m.Vorname + ' ' + m.Nachname AS Vertreter

FROM dbo.Auftraege a

JOIN dbo.Kunden k      ON a.KundenNr    = k.KundenNr

JOIN dbo.Mitarbeiter m ON a.VertreterNr = m.MitarbeiterNr

WHERE a.Status NOT IN ('STORNO', 'ENTWURF');

-- Schicht 3: Aggregations-Views — Kennzahlen für Reporting

CREATE OR ALTER VIEW rpt.V_KPI_Umsatz_Monatlich AS

SELECT

    YEAR(a.Auftragsdatum)  AS Jahr,

    MONTH(a.Auftragsdatum) AS Monat,

    k.Region,

    SUM(p.Menge * p.Einzelpreis * (1 - p.Rabatt)) AS Nettoumsatz,

    COUNT(DISTINCT a.AuftragsNr)                   AS Auftragsanzahl

FROM dbo.Auftraege a

JOIN dbo.Kunden k            ON a.KundenNr  = k.KundenNr

JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

WHERE a.Status NOT IN ('STORNO', 'ENTWURF')

GROUP BY YEAR(a.Auftragsdatum), MONTH(a.Auftragsdatum), k.Region;

-- Schicht 4: Security-Views — blendet sensible Spalten aus

CREATE OR ALTER VIEW sec.V_Kunden_Oeffentlich

WITH SCHEMABINDING AS

SELECT KundenNr, Name, PLZ, Ort, Region   -- KEIN Email, KEINE Umsatzdaten

FROM dbo.Kunden

WHERE Gesperrt = 0;

-- Alle Views mit Metadaten und letztem Änderungsdatum

SELECT

    SCHEMA_NAME(v.schema_id)    AS Schema,

    v.name                      AS ViewName,

    v.create_date               AS Erstellt,

    v.modify_date               AS GeaendertAm,

    OBJECTPROPERTY(v.object_id,'IsSchemaBound')     AS HatSchemabinding,

    OBJECTPROPERTY(v.object_id,'IsIndexed')         AS HatIndex,

    -- Abhängigkeitsanzahl

    (SELECT COUNT(*) FROM sys.sql_expression_dependencies

     WHERE referenced_id = v.object_id)             AS WirdVerwendetVon,

    -- Spaltenanzahl

    (SELECT COUNT(*) FROM sys.columns

     WHERE object_id = v.object_id)                 AS Spaltenanzahl

FROM sys.views v

ORDER BY Schema, ViewName;

-- Views OHNE SCHEMABINDING — Kandidaten für Risikobewertung

SELECT SCHEMA_NAME(schema_id) + '.' + name AS View_Ohne_Schemabinding

FROM sys.views

WHERE OBJECTPROPERTY(object_id,'IsSchemaBound') = 0

ORDER BY modify_date DESC;

-- Vollständiges SP-Template für produktive Umgebungen

CREATE OR ALTER PROCEDURE dbo.SP_[Name]

    -- INPUT-Parameter

    @Param1      DATENTYP,

    @Param2      DATENTYP = NULL,       -- Optionaler Parameter mit Default

    -- OUTPUT-Parameter (immer am Ende)

    @NeuID       INT          = NULL OUTPUT,

    @Fehler      NVARCHAR(500)= NULL OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    SET @Fehler = NULL;

    -- ── 1. Eingabevalidierung ──────────────────────────────────────────

    IF @Param1 IS NULL

    BEGIN

        SET @Fehler = 'Parameter @Param1 darf nicht NULL sein.';

        RETURN -1;

    END

    -- ── 2. Geschäftsregel-Prüfungen ───────────────────────────────────

    -- (Berechtigungen, Statusprüfungen, Limits)

    -- ── 3. Hauptlogik mit Transaktion ─────────────────────────────────

    BEGIN TRY

        BEGIN TRANSACTION;

            -- DML-Anweisungen hier

            INSERT INTO dbo.[Tabelle] ([Spalten]) VALUES ([Werte]);

            SET @NeuID = SCOPE_IDENTITY();

            -- Audit-Eintrag

            INSERT INTO dbo.AuditLog (Tabelle, Aktion, ObjektID, Benutzer, Zeitpunkt)

            VALUES ('[Tabelle]', 'INSERT', @NeuID, SUSER_SNAME(), SYSUTCDATETIME());

        COMMIT TRANSACTION;

        RETURN 0;   -- Erfolg

    END TRY

    BEGIN CATCH

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        SET @Fehler = ERROR_MESSAGE();

        INSERT INTO dbo.Fehlerprotokoll

            (Prozedur, Fehlernummer, Fehlermeldung, Zeitpunkt)

        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(),

                ERROR_MESSAGE(), SYSUTCDATETIME());

        RETURN -99;

    END CATCH

END

-- Effizientes Upsert-Pattern mit MERGE

CREATE OR ALTER PROCEDURE dbo.SP_Artikel_Upsert

    @ArtikelNr   NVARCHAR(20),

    @Bezeichnung NVARCHAR(200),

    @VKPreis     DECIMAL(18,4),

    @Warengruppe NVARCHAR(50),

    @GeaendertID INT OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

        BEGIN TRANSACTION;

        MERGE dbo.Artikel AS ziel

        USING (SELECT @ArtikelNr AS ArtikelNr, @Bezeichnung AS Bezeichnung,

                      @VKPreis AS VKPreis, @Warengruppe AS Warengruppe) AS quelle

        ON (ziel.ArtikelNr = quelle.ArtikelNr)

        WHEN MATCHED AND (

            ziel.Bezeichnung <> quelle.Bezeichnung OR

            ziel.VKPreis     <> quelle.VKPreis     OR

            ziel.Warengruppe <> quelle.Warengruppe

        ) THEN

            UPDATE SET

                ziel.Bezeichnung = quelle.Bezeichnung,

                ziel.VKPreis     = quelle.VKPreis,

                ziel.Warengruppe = quelle.Warengruppe,

                ziel.GeaendertAm = SYSUTCDATETIME()

        WHEN NOT MATCHED THEN

            INSERT (ArtikelNr, Bezeichnung, VKPreis, Warengruppe, AnlageDatum)

            VALUES (quelle.ArtikelNr, quelle.Bezeichnung,

                    quelle.VKPreis, quelle.Warengruppe, SYSUTCDATETIME());

        SET @GeaendertID = (SELECT ArtikelID FROM dbo.Artikel

                            WHERE ArtikelNr = @ArtikelNr);

        COMMIT TRANSACTION;

        RETURN 0;

    END TRY

    BEGIN CATCH

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        THROW;

    END CATCH

END

-- Cached Plans für Stored Procedures analysieren

SELECT

    qs.execution_count,

    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,

    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,

    OBJECT_NAME(st.objectid)           AS ProzedurName,

    qp.query_plan                      AS ExecutionPlan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE st.objectid IS NOT NULL          -- nur Objekte, keine Ad-hoc-Queries

  AND OBJECT_NAME(st.objectid) LIKE 'SP_%'

ORDER BY qs.total_elapsed_time DESC;

-- Parameter Sniffing erkennen: hohe Varianz in Laufzeiten derselben Prozedur

SELECT

    OBJECT_NAME(ps.object_id) AS Prozedur,

    ps.execution_count,

    ps.total_elapsed_time / ps.execution_count AS avg_elapsed_us,

    ps.min_elapsed_time AS min_us,

    ps.max_elapsed_time AS max_us,

    -- Hohe Ratio max/avg deutet auf Parameter Sniffing hin

    ps.max_elapsed_time / NULLIF(ps.total_elapsed_time / ps.execution_count, 0) AS max_avg_ratio

FROM sys.dm_exec_procedure_stats ps

WHERE ps.max_elapsed_time /

    NULLIF(ps.total_elapsed_time / NULLIF(ps.execution_count,0), 0) > 10

ORDER BY max_avg_ratio DESC;

-- Lösung: OPTION (OPTIMIZE FOR UNKNOWN) für variable Parameter

CREATE OR ALTER PROCEDURE dbo.SP_Auftraege_Je_Kunde

    @KundenNr  INT,

    @DatumVon  DATE,

    @DatumBis  DATE

AS

BEGIN

    SET NOCOUNT ON;

    SELECT a.AuftragsNr, a.Auftragsdatum, a.Status,

           SUM(p.Menge * p.Einzelpreis) AS Wert

    FROM dbo.Auftraege a

    JOIN dbo.Auftragspositionen p ON a.AuftragsNr = p.AuftragsNr

    WHERE a.KundenNr = @KundenNr

      AND a.Auftragsdatum BETWEEN @DatumVon AND @DatumBis

    GROUP BY a.AuftragsNr, a.Auftragsdatum, a.Status

    OPTION (OPTIMIZE FOR UNKNOWN);   -- verhindert Sniffing-Fallen

END

-- Views mit hoher Laufzeit identifizieren (Ad-hoc-Queries auf Views)

SELECT TOP 20

    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,

    qs.execution_count,

    qs.total_logical_reads / qs.execution_count AS avg_reads,

    SUBSTRING(st.text, 1, 200) AS QueryText

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE st.text LIKE '%rpt.V_%'   -- Views im rpt-Schema

ORDER BY qs.total_elapsed_time DESC;

-- Rollen anlegen

CREATE ROLE Rolle_Reporting;    -- Nur Lesezugriff via Views

CREATE ROLE Rolle_Sachbearbeiter; -- Lesen + Schreiben via SPs

CREATE ROLE Rolle_Teamleiter;   -- Erweiterte Views + SPs

-- Reporting: nur SELECT auf rpt-Schema-Views

GRANT SELECT ON SCHEMA::rpt TO Rolle_Reporting;

DENY  SELECT ON SCHEMA::dbo TO Rolle_Reporting;  -- keine Basistabellen

-- Sachbearbeiter: EXECUTE auf Stored Procedures

GRANT EXECUTE ON dbo.SP_Auftrag_Anlegen     TO Rolle_Sachbearbeiter;

GRANT EXECUTE ON dbo.SP_Artikel_Upsert      TO Rolle_Sachbearbeiter;

GRANT SELECT  ON SCHEMA::rpt                TO Rolle_Sachbearbeiter;

-- Benutzer zuweisen

ALTER ROLE Rolle_Reporting     ADD MEMBER [domain\bi-service-account];

ALTER ROLE Rolle_Sachbearbeiter ADD MEMBER [domain\max.mustermann];

-- Ownership Chaining: SP und Tabelle im selben Schema (dbo) →

-- Benutzer braucht NUR EXECUTE auf die SP, KEINE SELECT-Rechte auf die Tabelle

-- Das ist die sicherste und empfohlene Architektur

-- Prüfen ob Ownership Chain intakt ist

SELECT

    o1.name AS AufrufendesObjekt,

    o2.name AS AufgerufeneTabelle,

    SCHEMA_NAME(o1.schema_id) AS Schema_Aufrufer,

    SCHEMA_NAME(o2.schema_id) AS Schema_Ziel,

    CASE WHEN o1.principal_id = o2.principal_id

         THEN 'CHAIN INTAKT — kein extra Recht nötig'

         ELSE 'CHAIN UNTERBROCHEN — extra Rechte prüfen!'

    END AS OwnershipChain_Status

FROM sys.sql_expression_dependencies d

JOIN sys.objects o1 ON d.referencing_id   = o1.object_id

JOIN sys.objects o2 ON d.referenced_id    = o2.object_id

WHERE o1.type = 'P'   -- nur Stored Procedures

  AND o2.type = 'U';  -- auf User Tables

-- Security Policy: Vertreter sieht nur eigene Aufträge

-- Schritt 1: Filterfunktion

CREATE OR ALTER FUNCTION sec.FN_Auftraege_Filter(@VertreterNr INT)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

    SELECT 1 AS FilterErgebnis

    WHERE @VertreterNr = (

        SELECT MitarbeiterNr FROM dbo.Mitarbeiter

        WHERE LoginName = USER_NAME()

    )

    OR IS_MEMBER('db_owner') = 1      -- Admins sehen alles

    OR IS_MEMBER('Rolle_Teamleiter') = 1;

-- Schritt 2: Security Policy auf Tabelle anwenden

CREATE SECURITY POLICY sec.Policy_Auftraege

ADD FILTER PREDICATE sec.FN_Auftraege_Filter(VertreterNr)

ON dbo.Auftraege

WITH (STATE = ON);

-- Jetzt sieht jeder Vertriebsmitarbeiter nur seine eigenen Aufträge —

-- automatisch, in allen Views und Stored Procedures, ohne Code-Änderungen

Tabellen

dbo. (kein Präfix)

dbo.Kunden

Basistabellen ohne Präfix — immer im dbo-Schema

Einfache Views

rpt.V_

rpt.V_Umsatz_Monatlich

Schema trennt Zweck, V_ markiert den Typ

Security Views

sec.V_

sec.V_Kunden_Oeffentlich

Eigenes Schema für sicherheitsrelevante Views

Basis-Views

base.V_

base.V_Artikel_Aktiv

Interne Schicht, nicht für direktes Reporting

Inline TVFs

dbo.FN_

dbo.FN_Auftraege_Je_Kunde

FN_ für alle Functions

Scalar Functions

dbo.FN_

dbo.FN_Deckungsbeitrag

Gleicher Präfix, Typ aus Kontext erkennbar

Stored Procedures

dbo.SP_

dbo.SP_Auftrag_Anlegen

SP_ immer, kein usp_ oder proc_

ETL Procedures

etl.SP_

etl.SP_Lade_Auftraege

Eigenes Schema für ETL-Prozesse

-- Jedes neue Datenbankobjekt bekommt diesen Kommentarblock

CREATE OR ALTER PROCEDURE dbo.SP_Auftrag_Anlegen

/*

=======================================================================

Objekt:      dbo.SP_Auftrag_Anlegen

Typ:         Stored Procedure

Erstellt:    2025-03-01  Sascha Hess

Geaendert:   2026-01-15  M. Müller — Audit-Log ergänzt

-----------------------------------------------------------------------

Zweck:

    Legt einen neuen Auftrag an. Prüft Kundenstatus und Lieferdatum.

    Schreibt Audit-Log. Gibt neue AuftragsNr zurück.

Parameter:

    @KundenNr       INT             — Muss in dbo.Kunden existieren

    @VertreterNr    INT             — Muss in dbo.Mitarbeiter existieren

    @Lieferdatum    DATE            — Muss >= heute sein

    @AuftragsNr     INT OUTPUT      — Neue AuftragsNr bei Erfolg

    @Fehlermeldung  NVARCHAR(500)   — Fehlerbeschreibung bei Fehler

Rückgabewerte:

     0 = Erfolg

    -1 = Kunde nicht vorhanden oder gesperrt

    -2 = Lieferdatum in der Vergangenheit

   -99 = Unerwarteter Fehler (siehe @Fehlermeldung)

Verwendung:

    EXEC dbo.SP_Auftrag_Anlegen

        @KundenNr=42, @VertreterNr=7, @Lieferdatum='2026-04-01',

        @AuftragsNr=@id OUTPUT, @Fehlermeldung=@err OUTPUT;

Abhängigkeiten:

    Liest:   dbo.Kunden, dbo.Mitarbeiter

    Schreibt: dbo.Auftraege, dbo.AuditLog

=======================================================================

*/

    @KundenNr    INT,

    -- [...]

-- Vollständiges Objekt-Inventar der Datenbank mit Metadaten

SELECT

    SCHEMA_NAME(o.schema_id)    AS Schema,

    o.name                      AS ObjektName,

    o.type_desc                 AS ObjektTyp,

    o.create_date               AS Erstellt,

    o.modify_date               AS GeaendertAm,

    -- Letzter Aufruf aus Statistik

    MAX(qs.last_execution_time) AS LetzterAufruf,

    SUM(qs.execution_count)     AS AufrufeSeit_Neustart,

    -- Kommentarblock vorhanden?

    CASE WHEN sm.definition LIKE '%======%' THEN 'JA' ELSE 'FEHLT' END AS Dokumentation

FROM sys.objects o

LEFT JOIN sys.sql_modules sm ON o.object_id = sm.object_id

LEFT JOIN sys.dm_exec_procedure_stats qs ON o.object_id = qs.object_id

WHERE o.type IN ('V','P','IF','FN','TF')   -- Views, Procs, Functions

  AND o.is_ms_shipped = 0

GROUP BY SCHEMA_NAME(o.schema_id), o.name, o.type_desc,

         o.create_date, o.modify_date, sm.definition

ORDER BY ObjektTyp, Schema, ObjektName;

VOR DEM START

Führen Sie ein vollständiges Backup durch. Erstellen Sie einen Snapshot aller bestehenden Objekte mit dem Inventar-Skript aus Kapitel 9.3. Kommunizieren Sie geplante Änderungen an alle Anwendungsteams — auch kleine Umbenennungen können Anwendungen brechen.

ERGEBNIS NACH 30 TAGEN

Ihr Ergebnis nach 30 Tagen: Dokumentierte Objekte mit klaren Zuständigkeiten, SCHEMABINDING auf allen produktiven Views, robuste Fehlerbehandlung in allen kritischen Stored Procedures, eine saubere Berechtigungsstruktur und ein Power BI Stack, der ausschließlich auf definierten Reporting-Views aufsetzt.

SQL Server DB Health Check

Professioneller Audit Ihres SQL Servers — Managementreport, Risikobewertung, Architektur-Review und Maßnahmenplan. Scope: 3-5 Tage.

Datenbankdesign & Refactoring

Strukturiertes Refactoring gewachsener Datenbanken — Views, Stored Procedures, Berechtigungen, Namenskonventionen. Scope: ab 1 Woche.

BI-Dashboard-Aufbau

Power BI / DeltaMaster Dashboards inkl. ETL-Strecken, Data-Warehouse-Aufbau und sauberem View-Layer — für KMU ab 1 Woche Projektumfang.

ERP-Einführungsberatung

Begleitung von ERP-Projekten (MACH, Dynamics NAV, APplus) — Datenmigration, Schnittstellenentwicklung, Go-Live-Support.

Interim IT-Management

Übernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT-Ausrichtung.