Index Management with Biml

Why Indexes Need Special Handling

A common Biml pattern reads a source database with 'GetDatabaseSchema', then calls 'GetDropAndCreateDdl' on each table to refresh the staging environment. That round trip works well for columns and data types: a column rename or a type change in the source flows straight into the staging definition. Indexes are a different story. Any nonclustered index added directly on the staging table to speed up downstream queries vanishes the next time the table is dropped and recreated.

The fix is to teach the generation template about the indexes that already exist on the target. Before each table is rebuilt, the live indexes are read from the staging database and added back to the Biml table definition. The generated DROP and CREATE then includes those indexes, so they survive the round trip.

Steps the Template Performs

The pattern is straightforward:

1. Check whether the table exists in the target environment. A table that does not exist yet has no indexes to preserve.
2. If it exists, walk the live index list.
3. For each index, walk its column list and emit only the columns that still exist in the source definition. A column that has been dropped from the source is silently skipped so the index does not fail to recreate.
4. Add an 'Index' element with its surviving columns to the table.

Reading Indexes Back from the Target

The 'ImportDB' method on a connection returns a schema graph for one or more tables. The 'Indexes' collection on each returned table node holds the indexes as 'AstTableIndexNode' objects, each with its own 'Columns' collection of 'AstTableIndexColumnNode' entries. Both expose 'GetBiml' so the existing definition can be emitted directly into the new Biml output:

<#@ template language="C#" tier="2" #>
<#@ import namespace="System.Data" #>

<#
  var stagingConn = (AstDbConnectionNode)RootNode.Connections["StagingConn"];
  var sourceConn = (AstDbConnectionNode)RootNode.Connections["OperationsConn"];

  var includeTables = new List<string> { "Customer", "OrderHeader", "OrderLine" };

  var importOptions =
    ImportOptions.ExcludeIdentity |
    ImportOptions.ExcludePrimaryKey |
    ImportOptions.ExcludeUniqueKey |
    ImportOptions.ExcludeColumnDefault |
    ImportOptions.ExcludeIndex |
    ImportOptions.ExcludeCheckConstraint |
    ImportOptions.ExcludeForeignKey;

  var importResult = sourceConn.GetDatabaseSchema(null, includeTables, importOptions);
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <# foreach (var srcTable in importResult.TableNodes) {
         var stagingTableName = "Staging_" + srcTable.Schema.Name + "_" + srcTable.Name;
    #>
    <Table Name="<#=stagingTableName#>" SchemaName="StagingDb.dbo">
      <Columns>
        <#=srcTable.Columns.GetBiml()#>
      </Columns>
      <Indexes>
        <# var liveImport = stagingConn.ImportDB(null, stagingTableName);
           if (liveImport.TableNodes.Count > 0) {
             foreach (var liveIndex in liveImport.TableNodes[0].Indexes) {
        #>
        <Index Name="<#=liveIndex.Name#>"
               Clustered="<#=liveIndex.Clustered.ToString().ToLower()#>"
               Unique="<#=liveIndex.Unique.ToString().ToLower()#>">
          <Columns>
            <# foreach (var indexColumn in liveIndex.Columns) {
                 if (srcTable.Columns[indexColumn.Column.Name] != null) {
            #>
            <#=indexColumn.GetBiml()#>
            <# } } #>
          </Columns>
        </Index>
        <# } } #>
      </Indexes>
    </Table>
    <# } #>
  </Tables>
</Biml>

The new table definition now carries every existing index whose columns still make sense. The next time the generation pipeline issues a DROP and CREATE for that table, the indexes come back with it.

What the Pattern Buys

This approach keeps two concerns separate. The source database remains the authority for column shape. The target database remains the authority for indexes. A column rename in the source updates the staging columns automatically. A new index added by a DBA on the staging table survives the next regeneration without anyone needing to copy that DDL into the Biml project. When source and target diverge enough that an index column no longer exists, the column is quietly dropped from the index rather than producing a CREATE INDEX failure.

When Some Indexes Should Be Lost

Adding or removing source columns can shift an index from useful to useless. A nonclustered index on a column that no longer exists is silently skipped. An index on a remaining column whose data type has changed will still be recreated, even though the new shape may make it less effective. Treat the preservation as a starting point rather than a guarantee that downstream query plans stay identical.