-- 1. Memory-optimierte Dateigruppe hinzufügen 
ALTER DATABASE InMemoryDemo 
ADD FILEGROUP InMemoryFG CONTAINS MEMORY_OPTIMIZED_DATA; 
 
-- 2. Einen Container (Ordner) hinzufügen 
ALTER DATABASE InMemoryDemo 
ADD FILE (NAME = N'InMemory_Container', FILENAME = N'D:\Data\InMemory_Container') 
TO FILEGROUP InMemoryFG; 
 
-- 3. Memory-Optimized Table erstellen 
CREATE TABLE dbo.SessionState ( 
SessionId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), 
UserName NVARCHAR(128) NOT NULL, 
LastAccess DATETIME2 NOT NULL, 
Data VARBINARY(8000) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
 
-- Alternative: Nur Schemadauerhaftigkeit (Verlust bei Neustart) 
CREATE TABLE dbo.CacheTemp ( 
Id INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000), 
Value NVARCHAR(255) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

-- HASH Index für Primärschlüssel (Punktzugriff) 
CREATE TABLE dbo.ShoppingCart ( 
CartId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), 
UserId INT NOT NULL, 
CreatedDate DATETIME2 NOT NULL 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
 
-- NONCLUSTERED Index für Bereichszugriffe 
CREATE NONCLUSTERED INDEX ix_Cart_UserId ON dbo.ShoppingCart (UserId); 
CREATE NONCLUSTERED INDEX ix_Cart_CreatedDate ON dbo.ShoppingCart (CreatedDate); 
 
-- Empfehlung: Bucket-Count = 2x oder 3x geschätzte eindeutige Schlüssel 
ALTER TABLE dbo.ShoppingCart 
ADD INDEX [IX_HASH_CartId] NONCLUSTERED HASH (CartId) WITH (BUCKET_COUNT = 2000000);

CREATE PROCEDURE dbo.InsertOrder 
@OrderId INT, 
@CustomerId INT, 
@OrderDate DATETIME2 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC WITH ( 
TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
LANGUAGE = N'Deutsch' 
) 
INSERT INTO dbo.MemOrders (OrderId, CustomerId, OrderDate) 
VALUES (@OrderId, @CustomerId, @OrderDate); 
 
UPDATE dbo.MemCustomers 
SET LastOrderDate = @OrderDate 
WHERE CustomerId = @CustomerId; 
END; 
 
-- Ausführung wie gewohnt 
EXEC dbo.InsertOrder 1001, 42, GETUTCDATE();

-- Speicherverbrauch pro Memory Optimized Table 
SELECT  
OBJECT_NAME(object_id) AS TableName, 
SUM(memory_allocated_for_table_kb) AS AllocatedKB, 
SUM(memory_used_by_table_kb) AS UsedKB 
FROM sys.dm_db_xtp_table_memory_stats 
GROUP BY object_id 
ORDER BY AllocatedKB DESC; 
 
-- Prüfung auf Hash-Konflikte (durchschnittliche Kettenlänge > 1 = zu wenig Buckets) 
SELECT  
OBJECT_NAME(h.object_id) AS TableName, 
i.name AS IndexName, 
h.avg_chain_length, 
h.total_bucket_count, 
h.empty_bucket_count 
FROM sys.dm_db_xtp_hash_index_stats h 
JOIN sys.indexes i ON h.object_id = i.object_id AND h.index_id = i.index_id 
WHERE h.avg_chain_length > 1 
ORDER BY h.avg_chain_length DESC;

-- Schritt 1: Neue Memory-Optimized Tabelle erstellen 
CREATE TABLE dbo.NewOrders_Mem ( 
OrderId INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000), 
CustomerId INT NOT NULL, 
OrderDate DATETIME2 NOT NULL, 
INDEX ix_Customer NONCLUSTERED (CustomerId) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); 
 
-- Schritt 2: Daten kopieren (ggf. in Batches, um Blockaden zu vermeiden) 
INSERT INTO dbo.NewOrders_Mem (OrderId, CustomerId, OrderDate) 
SELECT OrderId, CustomerId, OrderDate 
FROM dbo.Orders_Old 
WHERE OrderDate >= '2024-01-01'; 
 
-- Schritt 3: Umbenennung (nach Test) 
EXEC sp_rename 'dbo.Orders_Old', 'Orders_Old_Bak'; 
EXEC sp_rename 'dbo.NewOrders_Mem', 'Orders';