Microsoft SQL Server Cheatsheet¶

Microsoft SQL Server - Enterprise Database Platform

Microsoft SQL Server ist ein von Microsoft entwickeltes relationales Datenbankmanagementsystem. Es ist eine umfassende Datenbank-Plattform, die Enterprise-grade-Funktionen für Datenspeicherung, Verarbeitung und Analytik mit einer starken Integration mit Microsoft-Ökosystem bietet. < p>

generieren

Inhaltsverzeichnis¶

[Installation](#installation
(#basic-commands)
[Datenbankoperationen](LINK_2__
[Schema und User Management](#schema-and-user-management
[Table Operations](#table-operations_
Datentypen
(CRUD Operationen)(LINK_6__
[Query Operations](LINK_7__
[Indexes](LINK_8__
Ansichten
(#stored-procedures_)
Funktionen
Trigger
Transaktionen
T-SQL-Programmierung
(#backup-and-recovery)
(#performance-tuning_)
(Monitoring)(LINK_17_)
[Sicherheit](#security_
Beste Praktiken

Installation¶

SQL Server Installation (Windows)¶

# Download SQL Server from Microsoft website
# https://www.microsoft.com/en-us/sql-server/sql-server-downloads

# Silent installation example
Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQLENGINE,TOOLS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /TCPENABLED=1 /SECURITYMODE=SQL /SAPWD="YourStrongPassword"

# Install SQL Server Management Studio (SSMS)
# Download from: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

# PowerShell installation
choco install sql-server-management-studio
```_

### SQL Server auf Linux
```bash
# Add Microsoft repository (Ubuntu)
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

# Install SQL Server
sudo apt-get update
sudo apt-get install -y mssql-server

# Configure SQL Server
sudo /opt/mssql/bin/mssql-conf setup

# Install SQL Server command-line tools
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

# Add to PATH
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# Start SQL Server
sudo systemctl start mssql-server
sudo systemctl enable mssql-server
```_

### Docker Installation
```bash
# Pull SQL Server image
docker pull mcr.microsoft.com/mssql/server:2019-latest

# Run SQL Server container
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrongPassword123" \
   -p 1433:1433 --name sql-server \
   -d mcr.microsoft.com/mssql/server:2019-latest

# Connect to SQL Server
docker exec -it sql-server /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P "YourStrongPassword123"

# Docker Compose
cat > docker-compose.yml << EOF
version: '3.8'
services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sqlserver
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=YourStrongPassword123
      - MSSQL_PID=Express
    ports:
      - "1433:1433"
    volumes:
      - sqlserver_data:/var/opt/mssql

volumes:
  sqlserver_data:
EOF

docker-compose up -d
```_

## Grundlegende Befehle

### Verbindung mit SQL Server
```bash
# Using sqlcmd
sqlcmd -S server_name -U username -P password
sqlcmd -S localhost -U sa -P password
sqlcmd -S server_name -E  # Windows Authentication

# Using PowerShell
Import-Module SqlServer
Invoke-Sqlcmd -ServerInstance "localhost" -Database "master" -Query "SELECT @@VERSION"

# Connection string examples
# SQL Server Authentication
Server=localhost;Database=master;User Id=sa;Password=password;

# Windows Authentication
Server=localhost;Database=master;Integrated Security=true;

# Named instance
Server=localhost\SQLEXPRESS;Database=master;Integrated Security=true;
```_

### Grundlegende Informationsbefehle
```sql
-- SQL Server version
SELECT @@VERSION;

-- Current database
SELECT DB_NAME();

-- Current user
SELECT SYSTEM_USER, USER_NAME();

-- Server information
SELECT 
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('ProductVersion') AS Version,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition;

-- Show databases
SELECT name FROM sys.databases;

-- Show tables in current database
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- Show columns of a table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName';

-- Current date and time
SELECT GETDATE();

-- Exit sqlcmd
EXIT
QUIT
```_

## Datenbanken

### Datenbanken erstellen und verwalten
```sql
-- Create database
CREATE DATABASE MyDatabase;

-- Create database with options
CREATE DATABASE MyDatabase
ON 
( NAME = 'MyDatabase_Data',
  FILENAME = 'C:\Data\MyDatabase.mdf',
  SIZE = 100MB,
  MAXSIZE = 1GB,
  FILEGROWTH = 10MB )
LOG ON 
( NAME = 'MyDatabase_Log',
  FILENAME = 'C:\Data\MyDatabase.ldf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 10% );

-- Use database
USE MyDatabase;

-- Alter database
ALTER DATABASE MyDatabase 
MODIFY FILE (NAME = 'MyDatabase_Data', SIZE = 200MB);

-- Set database options
ALTER DATABASE MyDatabase SET RECOVERY FULL;
ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON;

-- Drop database
DROP DATABASE MyDatabase;

-- Show database information
SELECT 
    name,
    database_id,
    create_date,
    collation_name,
    state_desc,
    recovery_model_desc
FROM sys.databases;

-- Show database files
SELECT 
    name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    max_size,
    growth,
    type_desc
FROM sys.database_files;
```_

### Backup und Wiederherstellung
```sql
-- Full backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION;

-- Differential backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- Transaction log backup
BACKUP LOG MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Log.trn';

-- Restore database
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY;

-- Point-in-time restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH STOPAT = '2023-12-01 14:30:00', RECOVERY;
```_

## Schema und Benutzerverwaltung

### Logins und Benutzer erstellen
```sql
-- Create SQL Server login
CREATE LOGIN app_user WITH PASSWORD = 'StrongPassword123';

-- Create Windows login
CREATE LOGIN [DOMAIN\username] FROM WINDOWS;

-- Create user in database
USE MyDatabase;
CREATE USER app_user FOR LOGIN app_user;

-- Create user with default schema
CREATE USER app_user FOR LOGIN app_user WITH DEFAULT_SCHEMA = dbo;

-- Alter user
ALTER USER app_user WITH DEFAULT_SCHEMA = app_schema;

-- Drop user and login
DROP USER app_user;
DROP LOGIN app_user;

-- Show logins
SELECT name, type_desc, create_date, modify_date, is_disabled
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G');

-- Show users in current database
SELECT name, type_desc, create_date, default_schema_name
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G');
```_

### Berechtigungen und Roles
```sql
-- Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Employees TO app_user;
GRANT EXECUTE ON dbo.GetEmployeeInfo TO app_user;
GRANT SELECT ON SCHEMA::dbo TO app_user;

-- Deny permissions
DENY DELETE ON dbo.Employees TO app_user;

-- Revoke permissions
REVOKE INSERT ON dbo.Employees FROM app_user;

-- Create custom role
CREATE ROLE app_role;

-- Add permissions to role
GRANT SELECT, INSERT, UPDATE ON dbo.Employees TO app_role;

-- Add user to role
ALTER ROLE app_role ADD MEMBER app_user;

-- Built-in database roles
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
ALTER ROLE db_owner ADD MEMBER app_user;

-- Server roles
ALTER SERVER ROLE sysadmin ADD MEMBER app_user;
ALTER SERVER ROLE dbcreator ADD MEMBER app_user;

-- Show permissions
SELECT 
    p.permission_name,
    p.state_desc,
    pr.name AS principal_name,
    o.name AS object_name
FROM sys.database_permissions p
JOIN sys.objects o ON p.major_id = o.object_id
JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id;
```_

### Schemata
```sql
-- Create schema
CREATE SCHEMA sales AUTHORIZATION dbo;

-- Transfer object to schema
ALTER SCHEMA sales TRANSFER dbo.Orders;

-- Drop schema
DROP SCHEMA sales;

-- Show schemas
SELECT name, schema_id, principal_id FROM sys.schemas;
```_

## Tabelle Operationen

### Tabellen erstellen
```sql
-- Basic table creation
CREATE TABLE Employees (
    EmployeeID int IDENTITY(1,1) PRIMARY KEY,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    Email nvarchar(100) UNIQUE,
    HireDate date DEFAULT GETDATE(),
    Salary decimal(10,2),
    DepartmentID int,
    CreatedDate datetime2 DEFAULT GETDATE()
);

-- Table with constraints
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate datetime2 DEFAULT GETDATE(),
    TotalAmount decimal(10,2) CHECK (TotalAmount > 0),
    Status nvarchar(20) DEFAULT 'Pending' CHECK (Status IN ('Pending', 'Processing', 'Completed', 'Cancelled')),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Table with computed column
CREATE TABLE Products (
    ProductID int IDENTITY(1,1) PRIMARY KEY,
    ProductName nvarchar(100) NOT NULL,
    UnitPrice decimal(10,2) NOT NULL,
    Quantity int NOT NULL,
    TotalValue AS (UnitPrice * Quantity) PERSISTED
);

-- Temporary table
CREATE TABLE #TempEmployees (
    EmployeeID int,
    FullName nvarchar(100)
);

-- Table variable
DECLARE @EmployeeTable TABLE (
    EmployeeID int,
    FullName nvarchar(100)
);

-- Partitioned table
CREATE PARTITION FUNCTION SalesDateRange (datetime2)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

CREATE PARTITION SCHEME SalesDateScheme
AS PARTITION SalesDateRange
TO (Sales2021, Sales2022, Sales2023, Sales2024);

CREATE TABLE Sales (
    SaleID int IDENTITY(1,1),
    SaleDate datetime2,
    Amount decimal(10,2),
    CustomerID int
) ON SalesDateScheme(SaleDate);
```_

### Änderung der Tabellen
```sql
-- Add column
ALTER TABLE Employees ADD MiddleName nvarchar(50);

-- Modify column
ALTER TABLE Employees ALTER COLUMN Salary decimal(12,2);

-- Drop column
ALTER TABLE Employees DROP COLUMN MiddleName;

-- Add constraint
ALTER TABLE Employees ADD CONSTRAINT CK_Salary CHECK (Salary > 0);

-- Drop constraint
ALTER TABLE Employees DROP CONSTRAINT CK_Salary;

-- Add foreign key
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers 
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

-- Drop foreign key
ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customers;

-- Rename table
EXEC sp_rename 'OldTableName', 'NewTableName';

-- Rename column
EXEC sp_rename 'TableName.OldColumnName', 'NewColumnName', 'COLUMN';

-- Drop table
DROP TABLE Employees;
```_

### Angaben zur Tabelle
```sql
-- Describe table structure
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees';

-- Show constraints
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = 'Employees';

-- Show indexes
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyOrdinal
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Employees')
ORDER BY i.name, ic.key_ordinal;

-- Table size information
SELECT 
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = 'Employees'
GROUP BY t.name, p.rows;
```_

## Datentypen

### Numerische Datentypen
```sql
-- Integer types
bit                 -- 0, 1, or NULL
tinyint             -- 0 to 255
smallint            -- -32,768 to 32,767
int                 -- -2,147,483,648 to 2,147,483,647
bigint              -- -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

-- Decimal types
decimal(p,s)        -- Fixed precision and scale
numeric(p,s)        -- Synonym for decimal
money               -- -922,337,203,685,477.5808 to 922,337,203,685,477.5807
smallmoney          -- -214,748.3648 to 214,748.3647

-- Floating point
float(n)            -- Floating precision number
real                -- 4-byte floating point

-- Examples
CREATE TABLE NumericExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Price decimal(10,2),
    Quantity int,
    IsActive bit,
    LargeNumber bigint,
    FloatValue float,
    MoneyValue money
);
```_

### String-Datentypen
```sql
-- Character types
char(n)             -- Fixed-length non-Unicode string
varchar(n)          -- Variable-length non-Unicode string
varchar(max)        -- Variable-length non-Unicode string (up to 2GB)
nchar(n)            -- Fixed-length Unicode string
nvarchar(n)         -- Variable-length Unicode string
nvarchar(max)       -- Variable-length Unicode string (up to 2GB)
text                -- Variable-length non-Unicode string (deprecated)
ntext               -- Variable-length Unicode string (deprecated)

-- Binary types
binary(n)           -- Fixed-length binary data
varbinary(n)        -- Variable-length binary data
varbinary(max)      -- Variable-length binary data (up to 2GB)
image               -- Variable-length binary data (deprecated)

-- Examples
CREATE TABLE StringExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Code char(5),
    Name nvarchar(100),
    Description nvarchar(max),
    BinaryData varbinary(max)
);
```_

### Datum und Uhrzeit
```sql
-- Date and time types
date                -- Date only (0001-01-01 to 9999-12-31)
time                -- Time only (00:00:00.0000000 to 23:59:59.9999999)
datetime            -- Date and time (1753-01-01 to 9999-12-31)
datetime2           -- Date and time with higher precision
smalldatetime       -- Date and time with minute precision
datetimeoffset      -- Date and time with timezone offset

-- Examples
CREATE TABLE DateTimeExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    BirthDate date,
    StartTime time,
    CreatedDateTime datetime,
    PreciseDateTime datetime2(7),
    SmallDateTime smalldatetime,
    UTCDateTime datetimeoffset
);

-- Insert examples
INSERT INTO DateTimeExamples VALUES (
    '1990-05-15',
    '09:30:00',
    '2023-12-01 14:30:25',
    '2023-12-01 14:30:25.1234567',
    '2023-12-01 14:30',
    '2023-12-01 14:30:25 +05:30'
);
```_

### Sonstige Datentypen
```sql
-- Unique identifier
uniqueidentifier    -- GUID

-- XML data type
xml                 -- XML documents

-- Spatial data types
geometry            -- Planar spatial data
geography           -- Ellipsoidal spatial data

-- JSON (SQL Server 2016+)
-- No native JSON type, use nvarchar(max) with JSON functions

-- Examples
CREATE TABLE OtherExamples (
    ID int IDENTITY(1,1) PRIMARY KEY,
    UniqueID uniqueidentifier DEFAULT NEWID(),
    XMLData xml,
    JSONData nvarchar(max),
    Location geography
);
```_

## AUSRÜSTUNG Operationen

### Einsatzbereiche
```sql
-- Insert single row
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES ('John', 'Doe', 'john.doe@company.com', 50000, 1);

-- Insert multiple rows
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES 
    ('Jane', 'Smith', 'jane.smith@company.com', 55000, 2),
    ('Bob', 'Johnson', 'bob.johnson@company.com', 48000, 1),
    ('Alice', 'Brown', 'alice.brown@company.com', 52000, 3);

-- Insert from SELECT
INSERT INTO EmployeeBackup (FirstName, LastName, Email, Salary)
SELECT FirstName, LastName, Email, Salary
FROM Employees
WHERE HireDate < '2020-01-01';

-- Insert with OUTPUT clause
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName
VALUES ('Charlie', 'Wilson', 'charlie.wilson@company.com', 60000, 2);

-- MERGE statement (UPSERT)
MERGE Employees AS target
USING (VALUES (1, 'John', 'Doe', 'john.doe@company.com', 55000)) 
    AS source (EmployeeID, FirstName, LastName, Email, Salary)
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Salary = source.Salary
WHEN NOT MATCHED THEN
    INSERT (FirstName, LastName, Email, Salary)
    VALUES (source.FirstName, source.LastName, source.Email, source.Salary);
```_

### Wählen Sie Operationen
```sql
-- Basic select
SELECT * FROM Employees;

-- Select specific columns
SELECT EmployeeID, FirstName, LastName, Salary FROM Employees;

-- Select with alias
SELECT EmployeeID AS ID, FirstName + ' ' + LastName AS FullName
FROM Employees;

-- Select with WHERE clause
SELECT * FROM Employees WHERE DepartmentID = 1;
SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 60000;
SELECT * FROM Employees WHERE FirstName LIKE 'J%';
SELECT * FROM Employees WHERE HireDate > '2020-01-01';

-- Select with ORDER BY
SELECT * FROM Employees ORDER BY Salary DESC;
SELECT * FROM Employees ORDER BY DepartmentID, Salary DESC;

-- Select with GROUP BY
SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID;

-- Select with HAVING
SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

-- Select with TOP
SELECT TOP 10 * FROM Employees ORDER BY Salary DESC;
SELECT TOP 10 PERCENT * FROM Employees ORDER BY HireDate;

-- Select with OFFSET/FETCH (SQL Server 2012+)
SELECT * FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- Select with CTE
WITH HighEarners AS (
    SELECT * FROM Employees WHERE Salary > 50000
)
SELECT * FROM HighEarners WHERE DepartmentID = 1;
```_

### Aktualisierung der Operationen
```sql
-- Update single row
UPDATE Employees 
SET Salary = 55000 
WHERE EmployeeID = 1;

-- Update multiple columns
UPDATE Employees 
SET Salary = Salary * 1.1, 
    LastName = 'Smith-Johnson' 
WHERE EmployeeID = 2;

-- Update with JOIN
UPDATE e
SET e.Salary = e.Salary * 1.05
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'IT';

-- Update with OUTPUT clause
UPDATE Employees 
SET Salary = Salary * 1.1
OUTPUT DELETED.EmployeeID, DELETED.Salary AS OldSalary, 
       INSERTED.Salary AS NewSalary
WHERE DepartmentID = 1;

-- Update with subquery
UPDATE Employees 
SET Salary = (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = Employees.DepartmentID)
WHERE Salary IS NULL;
```_

### Löschen von Operationen
```sql
-- Delete specific rows
DELETE FROM Employees WHERE EmployeeID = 1;

-- Delete with conditions
DELETE FROM Employees WHERE HireDate < '2010-01-01';

-- Delete with JOIN
DELETE e
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Temp';

-- Delete with OUTPUT clause
DELETE FROM Employees 
OUTPUT DELETED.EmployeeID, DELETED.FirstName, DELETED.LastName
WHERE DepartmentID = 5;

-- Delete all rows
DELETE FROM Employees;

-- Truncate table (faster than DELETE)
TRUNCATE TABLE Employees;
```_

## Abfrage von Operationen

### Mitglieder
```sql
-- Inner join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Left outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Right outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Full outer join
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Cross join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

-- Self join
SELECT e1.FirstName + ' ' + e1.LastName AS Employee,
       e2.FirstName + ' ' + e2.LastName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
```_

### Subques
```sql
-- Subquery in WHERE clause
SELECT * FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Subquery with IN
SELECT * FROM Employees 
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

-- Subquery with EXISTS
SELECT * FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

-- Correlated subquery
SELECT * FROM Employees e1
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);

-- Subquery in SELECT clause
SELECT EmployeeID, FirstName, LastName,
       (SELECT DepartmentName FROM Departments d WHERE d.DepartmentID = e.DepartmentID) AS DeptName
FROM Employees e;

-- Subquery in FROM clause
SELECT DeptName, AvgSalary
FROM (SELECT d.DepartmentName AS DeptName, AVG(e.Salary) AS AvgSalary
      FROM Employees e 
      JOIN Departments d ON e.DepartmentID = d.DepartmentID
      GROUP BY d.DepartmentName) AS DeptAvg;
```_

### Fensterfunktionen
```sql
-- ROW_NUMBER
SELECT EmployeeID, FirstName, LastName, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- RANK and DENSE_RANK
SELECT EmployeeID, FirstName, LastName, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

-- PARTITION BY
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary,
       ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptRank
FROM Employees;

-- LAG and LEAD
SELECT EmployeeID, HireDate, Salary,
       LAG(Salary, 1) OVER (ORDER BY HireDate) AS PrevSalary,
       LEAD(Salary, 1) OVER (ORDER BY HireDate) AS NextSalary
FROM Employees;

-- FIRST_VALUE and LAST_VALUE
SELECT EmployeeID, DepartmentID, Salary,
       FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS MinSalary,
       LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MaxSalary
FROM Employees;

-- Running totals
SELECT EmployeeID, HireDate, Salary,
       SUM(Salary) OVER (ORDER BY HireDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Employees;

-- Moving averages
SELECT EmployeeID, HireDate, Salary,
       AVG(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;
```_

### Gemeinsame Tabellenausdrücke (CTE)
```sql
-- Basic CTE
WITH HighEarners AS (
    SELECT * FROM Employees WHERE Salary > 50000
)
SELECT * FROM HighEarners WHERE DepartmentID = 1;

-- Recursive CTE
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

-- Multiple CTEs
WITH 
DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
),
HighPerformers AS (
    SELECT e.*, da.AvgSalary
    FROM Employees e
    JOIN DeptAvg da ON e.DepartmentID = da.DepartmentID
    WHERE e.Salary > da.AvgSalary
)
SELECT * FROM HighPerformers;
```_

## Index

### Indexe erstellen
```sql
-- Simple index
CREATE INDEX IX_Employees_LastName ON Employees(LastName);

-- Composite index
CREATE INDEX IX_Employees_Dept_Salary ON Employees(DepartmentID, Salary);

-- Unique index
CREATE UNIQUE INDEX IX_Employees_Email ON Employees(Email);

-- Clustered index (only one per table)
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(EmployeeID);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_HireDate ON Employees(HireDate);

-- Filtered index
CREATE INDEX IX_Employees_Active ON Employees(EmployeeID) 
WHERE IsActive = 1;

-- Included columns
CREATE INDEX IX_Employees_Dept_Incl ON Employees(DepartmentID) 
INCLUDE (FirstName, LastName, Salary);

-- Columnstore index
CREATE COLUMNSTORE INDEX IX_Sales_Columnstore ON Sales;

-- XML index
CREATE PRIMARY XML INDEX IX_Products_XML ON Products(XMLData);
```_

### Index verwalten
```sql
-- Show indexes
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    c.name AS ColumnName
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Employees')
ORDER BY i.name, ic.key_ordinal;

-- Index usage statistics
SELECT 
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE i.object_id = OBJECT_ID('Employees');

-- Rebuild index
ALTER INDEX IX_Employees_LastName ON Employees REBUILD;

-- Reorganize index
ALTER INDEX IX_Employees_LastName ON Employees REORGANIZE;

-- Disable index
ALTER INDEX IX_Employees_LastName ON Employees DISABLE;

-- Drop index
DROP INDEX IX_Employees_LastName ON Employees;

-- Update statistics
UPDATE STATISTICS Employees;
UPDATE STATISTICS Employees IX_Employees_LastName;
```_

## Ansichten

### Ansichten erstellen
```sql
-- Simple view
CREATE VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID
FROM Employees
WHERE IsActive = 1;

-- Complex view with joins
CREATE VIEW vw_EmployeeDepartment AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    e.Salary,
    d.DepartmentName,
    d.Location
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- View with aggregation
CREATE VIEW vw_DepartmentSummary AS
SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) AS EmployeeCount,
    AVG(e.Salary) AS AvgSalary,
    MAX(e.Salary) AS MaxSalary,
    MIN(e.Salary) AS MinSalary
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;

-- Indexed view (materialized view)
CREATE VIEW vw_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductID,
    COUNT_BIG(*) AS SalesCount,
    SUM(Quantity) AS TotalQuantity,
    SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON vw_SalesSummary(ProductID);
```_

### Verwaltung von Ansichten
```sql
-- Show views
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS;

-- Show view definition
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_ActiveEmployees'));

-- Alter view
ALTER VIEW vw_ActiveEmployees AS
SELECT EmployeeID, FirstName, LastName, Email, DepartmentID, Salary
FROM Employees
WHERE IsActive = 1;

-- Drop view
DROP VIEW vw_ActiveEmployees;

-- Update through view (if updatable)
UPDATE vw_ActiveEmployees 
SET Salary = 60000 
WHERE EmployeeID = 1;
```_

## Gespeicherte Verfahren

### Erstellung gespeicherter Verfahren
```sql
-- Simple stored procedure
CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM Employees;
END;

-- Procedure with parameters
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID int
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END;

-- Procedure with input and output parameters
CREATE PROCEDURE GetEmployeeStats
    @DepartmentID int,
    @EmployeeCount int OUTPUT,
    @AvgSalary decimal(10,2) OUTPUT
AS
BEGIN
    SELECT 
        @EmployeeCount = COUNT(*),
        @AvgSalary = AVG(Salary)
    FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END;

-- Procedure with error handling
CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID int,
    @NewSalary decimal(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
        BEGIN
            RAISERROR('Employee not found', 16, 1);
            RETURN;
        END;

        UPDATE Employees 
        SET Salary = @NewSalary 
        WHERE EmployeeID = @EmployeeID;

        COMMIT TRANSACTION;

        SELECT 'Salary updated successfully' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity int = ERROR_SEVERITY();
        DECLARE @ErrorState int = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;
```_

### Calling Stored Procedures
```sql
-- Call simple procedure
EXEC GetEmployeeCount;

-- Call procedure with parameters
EXEC GetEmployeesByDepartment @DepartmentID = 1;

-- Call procedure with output parameters
DECLARE @Count int, @AvgSal decimal(10,2);
EXEC GetEmployeeStats 
    @DepartmentID = 1, 
    @EmployeeCount = @Count OUTPUT, 
    @AvgSalary = @AvgSal OUTPUT;
SELECT @Count AS EmployeeCount, @AvgSal AS AverageSalary;

-- Call procedure with error handling
BEGIN TRY
    EXEC UpdateEmployeeSalary @EmployeeID = 1, @NewSalary = 65000;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
```_

### Verwaltung gespeicherter Verfahren
```sql
-- Show stored procedures
SELECT name, create_date, modify_date
FROM sys.procedures;

-- Show procedure definition
SELECT OBJECT_DEFINITION(OBJECT_ID('GetEmployeeCount'));

-- Alter stored procedure
ALTER PROCEDURE GetEmployeeCount
AS
BEGIN
    SELECT COUNT(*) AS TotalEmployees, 
           COUNT(CASE WHEN IsActive = 1 THEN 1 END) AS ActiveEmployees
    FROM Employees;
END;

-- Drop stored procedure
DROP PROCEDURE GetEmployeeCount;
```_

## Funktionen

### Funktionen erstellen
```sql
-- Scalar function
CREATE FUNCTION GetFullName(@FirstName nvarchar(50), @LastName nvarchar(50))
RETURNS nvarchar(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

-- Inline table-valued function
CREATE FUNCTION GetEmployeesByDept(@DepartmentID int)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
);

-- Multi-statement table-valued function
CREATE FUNCTION GetEmployeeSummary(@DepartmentID int)
RETURNS @EmployeeTable TABLE
(
    EmployeeID int,
    FullName nvarchar(101),
    Salary decimal(10,2),
    SalaryGrade nvarchar(10)
)
AS
BEGIN
    INSERT INTO @EmployeeTable
    SELECT 
        EmployeeID,
        FirstName + ' ' + LastName,
        Salary,
        CASE 
            WHEN Salary >= 60000 THEN 'High'
            WHEN Salary >= 40000 THEN 'Medium'
            ELSE 'Low'
        END
    FROM Employees
    WHERE DepartmentID = @DepartmentID;

    RETURN;
END;

-- Function with complex logic
CREATE FUNCTION CalculateBonus(@EmployeeID int, @BonusPercent decimal(5,2))
RETURNS decimal(10,2)
AS
BEGIN
    DECLARE @Salary decimal(10,2);
    DECLARE @YearsOfService int;
    DECLARE @Bonus decimal(10,2);

    SELECT @Salary = Salary, @YearsOfService = DATEDIFF(YEAR, HireDate, GETDATE())
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    IF @YearsOfService >= 10
        SET @Bonus = @Salary * (@BonusPercent * 1.5) / 100;
    ELSE IF @YearsOfService >= 5
        SET @Bonus = @Salary * (@BonusPercent * 1.2) / 100;
    ELSE
        SET @Bonus = @Salary * @BonusPercent / 100;

    RETURN @Bonus;
END;
```_

### Funktionen nutzen
```sql
-- Use scalar function
SELECT EmployeeID, dbo.GetFullName(FirstName, LastName) AS FullName
FROM Employees;

-- Use inline table-valued function
SELECT * FROM dbo.GetEmployeesByDept(1);

-- Use multi-statement table-valued function
SELECT * FROM dbo.GetEmployeeSummary(2);

-- Use function in WHERE clause
SELECT * FROM Employees
WHERE dbo.CalculateBonus(EmployeeID, 10) > 5000;
```_

### Funktionen verwalten
```sql
-- Show functions
SELECT name, type_desc, create_date, modify_date
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF');

-- Show function definition
SELECT OBJECT_DEFINITION(OBJECT_ID('GetFullName'));

-- Alter function
ALTER FUNCTION GetFullName(@FirstName nvarchar(50), @LastName nvarchar(50))
RETURNS nvarchar(101)
AS
BEGIN
    RETURN ISNULL(@FirstName, '') + ' ' + ISNULL(@LastName, '');
END;

-- Drop function
DROP FUNCTION GetFullName;
```_

## Auslöser

### Trigger erstellen
```sql
-- AFTER INSERT trigger
CREATE TRIGGER trg_Employee_Insert
ON Employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeAudit (EmployeeID, Action, ActionDate, ActionBy)
    SELECT EmployeeID, 'INSERT', GETDATE(), SYSTEM_USER
    FROM inserted;
END;

-- AFTER UPDATE trigger
CREATE TRIGGER trg_Employee_Update
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, ChangedBy)
        SELECT 
            i.EmployeeID,
            d.Salary,
            i.Salary,
            GETDATE(),
            SYSTEM_USER
        FROM inserted i
        JOIN deleted d ON i.EmployeeID = d.EmployeeID
        WHERE i.Salary != d.Salary;
    END;
END;

-- INSTEAD OF trigger (for views)
CREATE TRIGGER trg_EmployeeView_Insert
ON vw_EmployeeDepartment
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
    SELECT FirstName, LastName, Email, 
           (SELECT DepartmentID FROM Departments WHERE DepartmentName = inserted.DepartmentName)
    FROM inserted;
END;

-- DDL trigger
CREATE TRIGGER trg_DatabaseChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData xml = EVENTDATA();

    INSERT INTO DDLAudit (EventType, ObjectName, LoginName, EventDate, EventData)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
        GETDATE(),
        @EventData
    );
END;
```_

### Verwaltung von Triggern
```sql
-- Show triggers
SELECT 
    t.name AS TriggerName,
    o.name AS TableName,
    t.type_desc,
    t.is_disabled
FROM sys.triggers t
JOIN sys.objects o ON t.parent_id = o.object_id;

-- Show trigger definition
SELECT OBJECT_DEFINITION(OBJECT_ID('trg_Employee_Insert'));

-- Enable/disable trigger
DISABLE TRIGGER trg_Employee_Insert ON Employees;
ENABLE TRIGGER trg_Employee_Insert ON Employees;

-- Drop trigger
DROP TRIGGER trg_Employee_Insert;
```_

## Transaktionen

### Transaction Control
```sql
-- Basic transaction
BEGIN TRANSACTION;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User', 'test@company.com', 1);

UPDATE Employees SET Salary = 50000 WHERE EmployeeID = SCOPE_IDENTITY();

COMMIT TRANSACTION;

-- Transaction with rollback
BEGIN TRANSACTION;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User2', 'test2@company.com', 1);

-- Simulate error condition
IF @@ERROR != 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END;

COMMIT TRANSACTION;

-- Named transaction with savepoint
BEGIN TRANSACTION MainTransaction;

INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
VALUES ('Test', 'User3', 'test3@company.com', 1);

SAVE TRANSACTION SavePoint1;

UPDATE Employees SET Salary = 60000 WHERE FirstName = 'Test';

-- Rollback to savepoint
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION MainTransaction;
```_

### Transaction Isolation Levels
```sql
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Table hints for specific queries
SELECT * FROM Employees WITH (NOLOCK);
SELECT * FROM Employees WITH (READPAST);
SELECT * FROM Employees WITH (UPDLOCK);
SELECT * FROM Employees WITH (XLOCK);
```_

### Fehlerbehandlung bei Transaktionen
```sql
-- TRY-CATCH with transactions
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Employees (FirstName, LastName, Email, DepartmentID)
    VALUES ('John', 'Doe', 'john.doe@company.com', 1);

    UPDATE Employees SET Salary = 50000 WHERE EmployeeID = SCOPE_IDENTITY();

    -- Simulate error
    DECLARE @Error int = 1/0;

    COMMIT TRANSACTION;
    SELECT 'Transaction completed successfully' AS Message;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine;
END CATCH;
```_

## T-SQL Programmierung

### Variablen und Datentypen
```sql
-- Declare variables
DECLARE @EmployeeID int;
DECLARE @FirstName nvarchar(50);
DECLARE @Salary decimal(10,2) = 50000;
DECLARE @HireDate datetime = GETDATE();

-- Set variable values
SET @EmployeeID = 1;
SET @FirstName = 'John';

-- Set from query
SELECT @FirstName = FirstName FROM Employees WHERE EmployeeID = @EmployeeID;

-- Table variables
DECLARE @EmployeeTable TABLE (
    EmployeeID int,
    FullName nvarchar(101),
    Salary decimal(10,2)
);

INSERT INTO @EmployeeTable
SELECT EmployeeID, FirstName + ' ' + LastName, Salary
FROM Employees
WHERE DepartmentID = 1;

SELECT * FROM @EmployeeTable;
```_

### Steuerstrom
```sql
-- IF-ELSE
DECLARE @Salary decimal(10,2) = 55000;

IF @Salary > 50000
    PRINT 'High salary';
ELSE IF @Salary > 30000
    PRINT 'Medium salary';
ELSE
    PRINT 'Low salary';

-- CASE statement
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Salary,
    CASE 
        WHEN Salary >= 60000 THEN 'High'
        WHEN Salary >= 40000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryGrade
FROM Employees;

-- WHILE loop
DECLARE @Counter int = 1;

WHILE @Counter <= 10
BEGIN
    PRINT 'Counter: ' + CAST(@Counter AS nvarchar(10));
    SET @Counter = @Counter + 1;

    IF @Counter = 5
        BREAK;
END;

-- FOR loop (using CTE)
WITH Numbers AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT Number FROM Numbers;
```_

### Kuratoren
```sql
-- Declare cursor
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE DepartmentID = 1;

-- Variables for cursor
DECLARE @EmpID int, @FName nvarchar(50), @LName nvarchar(50), @Sal decimal(10,2);

-- Open cursor
OPEN employee_cursor;

-- Fetch first row
FETCH NEXT FROM employee_cursor INTO @EmpID, @FName, @LName, @Sal;

-- Loop through cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + @FName + ' ' + @LName + ', Salary: ' + CAST(@Sal AS nvarchar(20));

    -- Fetch next row
    FETCH NEXT FROM employee_cursor INTO @EmpID, @FName, @LName, @Sal;
END;

-- Close and deallocate cursor
CLOSE employee_cursor;
DEALLOCATE employee_cursor;
```_

### Dynamischer SQL
```sql
-- Simple dynamic SQL
DECLARE @SQL nvarchar(max);
DECLARE @TableName nvarchar(50) = 'Employees';

SET @SQL = 'SELECT COUNT(*) FROM ' + @TableName;
EXEC sp_executesql @SQL;

-- Dynamic SQL with parameters
DECLARE @SQL nvarchar(max);
DECLARE @DeptID int = 1;

SET @SQL = 'SELECT * FROM Employees WHERE DepartmentID = @DepartmentID';
EXEC sp_executesql @SQL, N'@DepartmentID int', @DepartmentID = @DeptID;

-- Building complex dynamic SQL
DECLARE @SQL nvarchar(max) = 'SELECT EmployeeID, FirstName, LastName';
DECLARE @IncludeSalary bit = 1;
DECLARE @DepartmentFilter int = NULL;

IF @IncludeSalary = 1
    SET @SQL = @SQL + ', Salary';

SET @SQL = @SQL + ' FROM Employees';

IF @DepartmentFilter IS NOT NULL
    SET @SQL = @SQL + ' WHERE DepartmentID = ' + CAST(@DepartmentFilter AS nvarchar(10));

PRINT @SQL;
EXEC sp_executesql @SQL;
```_

## Backup und Recovery

### Vollständige Sicherung
```sql
-- Full database backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10;

-- Full backup with multiple files
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Full_1.bak',
   DISK = 'C:\Backup\MyDatabase_Full_2.bak'
WITH FORMAT, INIT, COMPRESSION;

-- Full backup to URL (Azure)
BACKUP DATABASE MyDatabase 
TO URL = 'https://mystorageaccount.blob.core.windows.net/backups/MyDatabase_Full.bak'
WITH CREDENTIAL = 'MyAzureCredential', COMPRESSION;
```_

### Differenzielle und Log-Backups
```sql
-- Differential backup
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

-- Transaction log backup
BACKUP LOG MyDatabase 
TO DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH COMPRESSION, STATS = 10;

-- Backup verification
RESTORE VERIFYONLY 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak';
```_

### Restore Operationen
```sql
-- Full restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY, STATS = 10;

-- Restore differential backup
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH NORECOVERY, STATS = 10;

-- Restore log backup
RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY, STATS = 10;

-- Point-in-time restore
RESTORE DATABASE MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

RESTORE LOG MyDatabase 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH STOPAT = '2023-12-01 14:30:00', RECOVERY;

-- Restore to different database
RESTORE DATABASE MyDatabase_Test 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase_Test.mdf',
     MOVE 'MyDatabase_Log' TO 'C:\Data\MyDatabase_Test.ldf',
     REPLACE;
```_

### Backup-Informationen
```sql
-- Show backup history
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,
    bs.backup_size / 1024 / 1024 AS backup_size_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MyDatabase'
ORDER BY bs.backup_start_date DESC;

-- Show restore history
SELECT 
    rh.destination_database_name,
    rh.restore_date,
    rh.restore_type,
    bs.backup_start_date,
    bmf.physical_device_name
FROM msdb.dbo.restorehistory rh
JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE rh.destination_database_name = 'MyDatabase'
ORDER BY rh.restore_date DESC;
```_

## Leistung Tuning

### Abrufleistung
```sql
-- Show execution plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET SHOWPLAN_ALL OFF;

-- Include actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Query hints
SELECT * FROM Employees WITH (INDEX(IX_Employees_DepartmentID))
WHERE DepartmentID = 1;

SELECT * FROM Employees 
WHERE DepartmentID = 1
OPTION (RECOMPILE);

-- Plan guides
EXEC sp_create_plan_guide 
    @name = N'Guide1',
    @stmt = N'SELECT * FROM Employees WHERE DepartmentID = @DeptID',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@DeptID int',
    @hints = N'OPTION (PARAMETERIZATION FORCED)';
```_

### Indexanalyse
```sql
-- Missing indexes
SELECT 
    mid.statement AS TableName,
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + '_' + 
    REPLACE(REPLACE(mid.equality_columns + ISNULL('_' + mid.inequality_columns, ''), '[', ''), ']', '') +
    ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.inequality_columns IS NOT NULL AND mid.equality_columns IS NOT NULL THEN ',' ELSE '' END +
    ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC;

-- Unused indexes
SELECT 
    i.name AS IndexName,
    o.name AS TableName,
    i.type_desc,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE o.type = 'U'
    AND i.index_id > 0
    AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
ORDER BY o.name, i.name;

-- Index fragmentation
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;
```_

### Web-Shop
```sql
-- Enable Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE = ON;

-- Configure Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 60
);

-- Top resource consuming queries
SELECT 
    qsq.query_id,
    qsqt.query_sql_text,
    qsrs.avg_duration / 1000 AS avg_duration_ms,
    qsrs.avg_cpu_time / 1000 AS avg_cpu_time_ms,
    qsrs.avg_logical_io_reads,
    qsrs.count_executions
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsrs.last_execution_time > DATEADD(day, -7, GETDATE())
ORDER BY qsrs.avg_duration DESC;

-- Clear Query Store
ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;
```_

## Überwachung

### Systeminformationen
```sql
-- Server information
SELECT 
    @@SERVERNAME AS ServerName,
    @@VERSION AS Version,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('MachineName') AS MachineName;

-- Database information
SELECT 
    name AS DatabaseName,
    database_id,
    create_date,
    state_desc AS State,
    recovery_model_desc AS RecoveryModel,
    collation_name AS Collation
FROM sys.databases;

-- Active connections
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.is_user_process = 1;

-- Blocking sessions
SELECT 
    blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    blocking_sql.text AS blocking_sql,
    blocked_sql.text AS blocked_sql,
    blocked.wait_type,
    blocked.wait_time
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql;
```_

### Leistungsüberwachung
```sql
-- CPU usage
SELECT 
    record_id,
    DATEADD(ms, -1 * ((SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info) - [timestamp]), GETDATE()) AS EventTime,
    100 - SystemIdle AS SQLServerCPU,
    100 - SystemIdle - SQLProcessUtilization AS OtherProcessCPU
FROM (
    SELECT 
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
        timestamp
    FROM (
        SELECT timestamp, CONVERT(xml, record) AS record 
        FROM sys.dm_os_ring_buffers 
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
            AND record LIKE '%<SystemHealth>%'
    ) AS x
) AS y
ORDER BY record_id DESC;

-- Memory usage
SELECT 
    (total_physical_memory_kb / 1024) AS total_physical_memory_mb,
    (available_physical_memory_kb / 1024) AS available_physical_memory_mb,
    (total_page_file_kb / 1024) AS total_page_file_mb,
    (available_page_file_kb / 1024) AS available_page_file_mb,
    (system_memory_state_desc) AS system_memory_state
FROM sys.dm_os_sys_memory;

-- Wait statistics
SELECT TOP 20
    wait_type,
    wait_time_ms,
    signal_wait_time_ms,
    waiting_tasks_count,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
    AND wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
                         'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
                         'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                         'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
                         'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT',
                         'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY wait_time_ms DESC;

-- I/O statistics
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    file_id,
    io_stall_read_ms,
    num_of_reads,
    CASE WHEN num_of_reads = 0 THEN 0 ELSE io_stall_read_ms / num_of_reads END AS avg_read_stall_ms,
    io_stall_write_ms,
    num_of_writes,
    CASE WHEN num_of_writes = 0 THEN 0 ELSE io_stall_write_ms / num_of_writes END AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms + io_stall_write_ms DESC;
```_

### Datenbankgröße und Wachstum
```sql
-- Database file sizes
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    physical_name AS PhysicalName,
    type_desc AS FileType,
    size * 8 / 1024 AS SizeMB,
    max_size * 8 / 1024 AS MaxSizeMB,
    CASE 
        WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(10)) + '%'
        ELSE CAST(growth * 8 / 1024 AS varchar(10)) + ' MB'
    END AS GrowthSetting
FROM sys.master_files
ORDER BY database_id, file_id;

-- Table sizes
SELECT 
    t.name AS TableName,
    p.rows AS RowCount,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.name, p.rows
ORDER BY SUM(a.total_pages) DESC;
```_

## Sicherheit

### Authentifizierung und Zulassung
```sql
-- Create login with password policy
CREATE LOGIN secure_user WITH PASSWORD = 'ComplexPassword123!',
    CHECK_POLICY = ON,
    CHECK_EXPIRATION = ON;

-- Create user with certificate
CREATE CERTIFICATE UserCert WITH SUBJECT = 'Certificate for secure_user';
CREATE LOGIN cert_user FROM CERTIFICATE UserCert;

-- Password policy
ALTER LOGIN secure_user WITH PASSWORD = 'NewComplexPassword123!',
    OLD_PASSWORD = 'ComplexPassword123!';

-- Lock/unlock login
ALTER LOGIN secure_user DISABLE;
ALTER LOGIN secure_user ENABLE;

-- Show login information
SELECT 
    name,
    type_desc,
    is_disabled,
    create_date,
    modify_date,
    default_database_name,
    is_policy_checked,
    is_expiration_checked
FROM sys.server_principals
WHERE type IN ('S', 'U');
```_

### Verschlüsselung
```sql
-- Transparent Data Encryption (TDE)
-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword123!';

-- Create certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

-- Create database encryption key
USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Enable TDE
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

-- Check encryption status
SELECT 
    db.name AS DatabaseName,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;

-- Column-level encryption
-- Create symmetric key
CREATE SYMMETRIC KEY ColumnKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TDECert;

-- Open symmetric key
OPEN SYMMETRIC KEY ColumnKey
DECRYPTION BY CERTIFICATE TDECert;

-- Encrypt data
INSERT INTO Employees (FirstName, LastName, SSN)
VALUES ('John', 'Doe', EncryptByKey(Key_GUID('ColumnKey'), '123-45-6789'));

-- Decrypt data
SELECT FirstName, LastName, 
       CONVERT(varchar, DecryptByKey(SSN)) AS SSN
FROM Employees;

-- Close symmetric key
CLOSE SYMMETRIC KEY ColumnKey;
```_

### Prüfung
```sql
-- Create server audit
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'C:\Audit\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 10)
WITH (ON_FAILURE = CONTINUE);

-- Enable server audit
ALTER SERVER AUDIT ServerAudit WITH (STATE = ON);

-- Create database audit specification
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
FOR SERVER AUDIT ServerAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Employees BY public),
ADD (EXECUTE ON dbo.GetEmployeeInfo BY public);

-- Enable database audit specification
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec WITH (STATE = ON);

-- View audit records
SELECT 
    event_time,
    action_id,
    succeeded,
    database_name,
    schema_name,
    object_name,
    statement,
    server_principal_name,
    database_principal_name
FROM sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;
```_

### Row Level Security
```sql
-- Create security policy function
CREATE FUNCTION dbo.fn_securitypredicate(@EmployeeID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @EmployeeID = USER_ID() OR IS_MEMBER('db_owner') = 1;

-- Create security policy
CREATE SECURITY POLICY EmployeeSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees,
ADD BLOCK PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_securitypredicate(EmployeeID) ON dbo.Employees AFTER UPDATE
WITH (STATE = ON);

-- Dynamic data masking
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');

-- Grant unmask permission
GRANT UNMASK TO hr_manager;
```_

## Best Practices

### Schema Design Best Practices
```sql
-- Use appropriate data types
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) PRIMARY KEY,        -- Use IDENTITY for auto-increment
    OrderDate datetime2 NOT NULL DEFAULT GETDATE(), -- Use datetime2 for better precision
    Amount decimal(10,2) NOT NULL,                 -- Use decimal for money
    Status nvarchar(20) NOT NULL DEFAULT 'Pending', -- Use nvarchar for Unicode
    CustomerID int NOT NULL,
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    CONSTRAINT CK_Orders_Amount CHECK (Amount > 0),
    CONSTRAINT CK_Orders_Status CHECK (Status IN ('Pending', 'Processing', 'Completed', 'Cancelled'))
);

-- Use proper indexing strategy
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);
CREATE INDEX IX_Orders_Status_OrderDate ON Orders(Status, OrderDate) INCLUDE (Amount);

-- Use schemas for organization
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE SCHEMA finance;

-- Move tables to appropriate schemas
ALTER SCHEMA sales TRANSFER dbo.Orders;
ALTER SCHEMA hr TRANSFER dbo.Employees;
```_

### Performance Best Practices
```sql
-- Use parameterized queries
-- Bad: Dynamic SQL without parameters
DECLARE @SQL nvarchar(max) = 'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DeptID AS nvarchar(10));

-- Good: Parameterized query
DECLARE @SQL nvarchar(max) = 'SELECT * FROM Employees WHERE DepartmentID = @DepartmentID';
EXEC sp_executesql @SQL, N'@DepartmentID int', @DepartmentID = @DeptID;

-- Use EXISTS instead of IN for subqueries
-- Good:
SELECT * FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.DepartmentID = e.DepartmentID AND d.Location = 'New York');

-- Avoid SELECT *
-- Bad: SELECT * FROM Employees;
-- Good: SELECT EmployeeID, FirstName, LastName FROM Employees;

-- Use appropriate JOIN types
-- Use INNER JOIN when possible
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Use SET NOCOUNT ON in stored procedures
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Employees;
END;

Sicherheit Best Practices¶

```sql -- Use least privilege principle -- Create role for specific access CREATE ROLE app_read_role; GRANT SELECT ON schema::dbo TO app_read_role; ALTER ROLE app_read_role ADD MEMBER app_user;

-- Use stored procedures for data access CREATE PROCEDURE GetEmployeeInfo @EmployeeID int AS BEGIN SET NOCOUNT ON;

SELECT EmployeeID, FirstName, LastName, Email
FROM Employees
WHERE EmployeeID = @EmployeeID;

END;

GRANT EXECUTE ON GetEmployeeInfo TO app_user;

-- Regular maintenance tasks -- Update statistics UPDATE STATISTICS Employees;

-- Rebuild indexes ALTER INDEX ALL ON Employees REBUILD;

-- Check database integrity DBCC CHECKDB('MyDatabase');

-- Backup database BACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase_Full.bak' WITH COMPRESSION, CHECKSUM; ```_

Überwachung und Wartung¶

```sql -- Create maintenance plan queries -- Check database integrity DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS;

-- Update statistics for all tables EXEC sp_updatestats;

-- Rebuild fragmented indexes DECLARE @SQL nvarchar(max) = ''; SELECT @SQL = @SQL + 'ALTER INDEX ' + i.name + ' ON ' + OBJECT_NAME(i.object_id) + ' REBUILD;' + CHAR(13) FROM sys.indexes i JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE ps.avg_fragmentation_in_percent > 30 AND ps.page_count > 1000;

EXEC sp_executesql @SQL;

-- Monitor long-running queries SELECT r.session_id, r.start_time, r.status, r.command, r.percent_complete, r.estimated_completion_time, t.text AS query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.estimated_completion_time > 0; ```_

--

Zusammenfassung¶

Microsoft SQL Server ist eine umfassende Unternehmensdatenbank-Plattform, die robuste Funktionen für Datenmanagement, Business Intelligence und Anwendungsentwicklung mit einer starken Integration mit dem Microsoft-Ökosystem bietet.

Key Strengths: - Enterprise Integration*: Nahtlose Integration mit Microsoft Technologien und Azure Cloud - **Business Intelligence: Integrierte Reporting-, Analyse- und Datenspeicherfähigkeiten - Performance: Advanced Query Optimierer und In-Memory Technologien - Sicherheit: Umfassende Sicherheitsfunktionen einschließlich TDE, RLS und Auditing - **Skalierbarkeit*: Unterstützung für große Datenbanken und hochverfügbare Lösungen

Best Use Cases: - Enterprise Anwendungen in Microsoft-Umgebungen - Unternehmensinformationen und Datenspeicherung - Anwendungen, die eine starke ACID-Compliance erfordern - Systeme, die fortschrittliche Sicherheitsfunktionen benötigen - Cloud-Hybrid-Einsätze mit Azure

Importierte Überlegungen: - Lizenzkosten können für Unternehmensmerkmale von Bedeutung sein - Beste Leistung unter Windows-Umgebungen - Erfordert regelmäßige Wartung und Überwachung - Speicher- und Speicheranforderungen können beträchtlich sein - Backup- und Recovery-Strategien müssen gut geplant sein

Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie SQL Server-Datenbanken effektiv entwerfen, implementieren und pflegen, die den Anforderungen des Unternehmens an Leistung, Sicherheit und Zuverlässigkeit entsprechen und dabei die volle Leistung der Microsoft-Datenplattform nutzen.

<= <= <= <================================================================================= Funktion copyToClipboard() {\cHFFFF} const commands = document.querySelectorAll('code'); alle Befehle = ''; Befehle. Für jede(cmd) => alle Befehle += cmd.textContent + '\n'); navigator.clipboard.writeText (allCommands); Alarm ('Alle Befehle, die in die Zwischenablage kopiert werden!'); }

Funktion generierenPDF() { Fenster.print(); }