SQL Server Indexes

Kopie von AdventureWorks von Backup wiederherstellen

Die Übung (wir verwenden die gleiche Datenbank: https://www.mssqltips.com/tutorial/sql-server-index-maintenance/)

Tabelle Kopieren

-- Heap-Tabelle (ohne Index)
SELECT * INTO [Sales].[SalesOrderDetail_Heap] FROM [Sales].[SalesOrderDetail];

-- Clustered Index Tabelle
SELECT * INTO [Sales].[SalesOrderDetail_Clustered] FROM [Sales].[SalesOrderDetail];

-- Non-Clustered Index Tabelle
SELECT * INTO [Sales].[SalesOrderDetail_NonClustered] FROM [Sales].[SalesOrderDetail];

-- Clustered Columnstore Index Tabelle
SELECT * INTO [Sales].[SalesOrderDetail_ClusteredColumnstore] FROM [Sales].[SalesOrderDetail];

Um die Performance zu testen kann SET STATISTICS IO ON; verwendet werden.

Heap (ohne Index)

SELECT * FROM [Sales].[SalesOrderDetail_Heap] WHERE SalesOrderID = 43659;

Scananzahl 1, logische Lesevorgänge 1498, CPU-Zeit = 15 ms, verstrichene Zeit = 15 ms.

Clustered Index

Create Clustered Index

CREATE CLUSTERED INDEX CIX_SalesOrderDetail_SalesOrderID
ON [Sales].[SalesOrderDetail_Clustered](SalesOrderID);

SELECT * FROM [Sales].[SalesOrderDetail_Clustered] WHERE SalesOrderID = 43659;

Scananzahl 1, logische Lesevorgänge 3,CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

Non-Clustered Index

Create Non-Clustered Index

CREATE NONCLUSTERED INDEX NCIX_SalesOrderDetail_SalesOrderID
ON [Sales].[SalesOrderDetail_NonClustered](SalesOrderID);

SELECT * FROM [Sales].[SalesOrderDetail_NonClustered] WHERE SalesOrderID = 43659;

Scananzahl 1, logische Lesevorgänge 14, CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

Columnstore Index

Create Columnstore Index

CREATE CLUSTERED COLUMNSTORE INDEX CCIX_SalesOrderDetail
ON [Sales].[SalesOrderDetail_ClusteredColumnstore];

SELECT * FROM [Sales].[SalesOrderDetail_ClusteredColumnstore] WHERE SalesOrderID = 43659;

Scananzahl 1, logische Lesevorgänge 0,CPU-Zeit = 15 ms, verstrichene Zeit = 10 ms.

Column Store indexes are optimized for read-heavy operations and analytical queries, making them ideal for data warehousing scenarios. They store data in a compressed format, which can significantly reduce storage requirements and improve query performance for large datasets. However, they may not perform as well for transactional workloads that involve frequent updates or inserts.

FullText Indexes