Skip to content
  • There are no suggestions because the search field is empty.

Add an Index

Add an Index

To create a new Index for a Model Object, access the Indexes overview page for a Model Object:

Then click on the Add Index button:

The New Index side panel window is opened:

Fill in the following information:

  1. Index Name: Index name
  2. Index Type: choose the Index type:

    1. Clustered Rowstore Index: Physically sorts all table rows by key (1/table); fast OLTP lookups/ranges.
    2. Non-clustered Rowstore Index: Separate pointer index (many/table); selective queries, extra row fetch.
    3. Clustered Columnstore Index: Table is columnar/compressed (1/table); analytics scans/aggs.
    4. Non-clustered Columnstore Index: Columnar overlay on rowstore (many/table); hybrid OLTP+analytics.
    Depending on the Index Type, more information to fill in:
  3. Create as Unique Index: Enforces no duplicate values in index key columns.
    1. For Clustered Rowstore, Non-clustered Rowstore, and Non-clustered Columnstore Index
  4. Included Columns: Non-key columns stored at leaf level; covers queries without key lookups.
    1. For Non-clustered Rowstore and Non-clustered Columnstore Index
  5. Index Columns: Key columns forming the index structure; sorted/searched for fast lookups. 
    1. For Clustered Rowstore and Non-clustered Rowstore

To have a better understanding, let's take an example with the following table:

CREATE TABLE Sales (  
     SaleID int,  
     CustomerID int,  
     SaleDate datetime,  
     Amount decimal(10,2) 
);

Clustered Rowstore (Unique, Index Columns):

CREATE UNIQUE CLUSTERED INDEX IX_Sales_SaleID 
ON Sales (SaleID);  -- Physically sorts table by SaleID

Non-clustered Rowstore (Unique, Index+Include Columns):

CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_CustomerDate 
ON Sales (CustomerID, SaleDate)  -- Index columns
INCLUDE (Amount);                -- Included column

Clustered Columnstore (Full table columnar, no INCLUDE/UNIQUE needed):

CREATE CLUSTERED COLUMNSTORE INDEX IX_Sales_Col 
ON Sales;  -- Replaces rowstore entirely

Non-clustered Columnstore (Unique, Index+Include Columns):

CREATE UNIQUE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_CustAmt 
ON Sales (CustomerID)            -- Index column  
INCLUDE (Amount, SaleDate);      -- Included columns

Then click on the SAVE button:

  • The new Index appears:
  • A notification confirms the correct creation:

Demonstration video