Fluent SQL

CREATE INDEX

To create an index, call Index.Create(). The methods Unique(), Clustered(), and NonClustered() can be used to define the kind of index you want. If none of these are called, the index will be non-unique and non-clustered. Next, call On() with the table and columns to create the index on. The table can be a string or the table name or a Table object. The column names can be strings or they can made into OrderedIdentifiers by calling the Asc() or Desc() string extension methods.

var createStatement = Index.Create("CustomerIdx").Unique().Clustered()
    .On("Customer", "ID", "Name".Desc());

CREATE UNIQUE CLUSTERED INDEX [CustomerIdx]
ON [Customer] ( [ID], [Name] DESC );

If you don’t call On() with a table reference, SQL generation will generate an error.

-- SQL Error: Create index statement must have a table reference.

Note that there’s a possible name collision between FluentSQL.Index and System.Index, so if you’re referencing System (highly likely) you will need to qualify Index.

Column-store indexes

To create a column-store index rather than the default row-store, use the CreateColumnStore() method.

var createStatement = Index.CreateColumnStore("CustomerIdx").Clustered()
    .On("Customer", "ID", "Name");

CREATE CLUSTERED COLUMNSTORE INDEX [CustomerIdx]
ON [Customer] ( [ID], [Name] );

Index options

There are lots of options available when an index is created. Use the With() to pass in as many index options as you need. Options are created with static factory methods. For example, to create an index with SORT_IN_TEMP_DB = ON and DATA_COMPRESSION = PAGE, pass in the options shown below:

var createStatement = Index.Create("CustomerIdx").Unique().Clustered()
    .On("Customer", "ID".Desc())
    .With(SortInTempDB.On(), IndexDataCompression.Page());

CREATE UNIQUE CLUSTERED INDEX [CustomerIdx]
ON [Customer] ( [ID] DESC )
WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Most of the options, like PadIndex and StatisticsNoRecompute are binary and have On() and Off() methods to create the option.

Options that take numeric values use EqualTo() methods that take the option value. FillFactor can take an integer or a Percentage.

var createStatement = Index.Create("CustomerIdx").Unique().Clustered()
    .On("Customer", "ID".Desc())
    .With(FillFactor.EqualTo(90));

CREATE UNIQUE CLUSTERED INDEX [CustomerIdx]
ON [Customer] ( [ID] DESC )
WITH (FILLFACTOR = 90);

MaxDuration and CompressionDelay can take an integer representing a number of minutes or a TimeSpan.

var maxDuration = TimeSpan.FromMinutes(5);

var createStatement = Index.Create("CustomerIdx").Unique().Clustered()
    .On("Customer", "ID".Desc())
    .With(MaxDuration.EqualTo(maxDuration));

CREATE UNIQUE CLUSTERED INDEX [CustomerIdx]
ON [Customer] ( [ID] DESC )
WITH (MAX_DURATION = 5);