Index Management with Biml

Diesen Artikel finden Sie hier auch in deutscher Sprache.

One of the cool things about Biml is, that it can automatically read your source database and get columns including their types from there. All you have to do is fire a GetDropAndCreateDdl on the table and Biml will generate the necessary code to generate your staging environment. Your source table has changed from varchar to nvarchar-columns? Some tables or columns have been removed from the source? No problem – one click and biml will fix that for you.

So far so good – but what about the indexes that you created in your staging environment? Will those all be lost during that procedure?

Not necessarily! All you need to do, is tell Biml about your existing indexes. To do so, we need to to follow a few simple steps.

– Check, if the table already exists in the staging Environment – if not, it won’t have an index yet :)
– If it exists, get it’s details and loop through all the indexes
– For each index column, check if it still exists
– If it does, add an index-tag for it in Biml

How does that look?

<#@ template language="VB" tier="2" #>
<#@ import namespace="System.Data" #>
<# dim targetConnection as AstDbConnectionNode  = RootNode.Connections("Target") 
dim srcconn as AstDbConnectionNode  = RootNode.Connections("AdventureWorks") 
dim tblname as string
Dim ImportTables As new List(of String)
ImportTables.add("Person") #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Tables>
		<# dim importResult as Importresults = srcconn.GetDatabaseSchema(nothing,ImportTables,ImportOptions.ExcludeIdentity or ImportOptions.ExcludePrimaryKey  or ImportOptions.ExcludeUniqueKey or ImportOptions.ExcludeColumnDefault or ImportOptions.ExcludeIndex or ImportOptions.ExcludeCheckConstraint or ImportOptions.ExcludeForeignKey) 
		   for each table as asttablenode  in importResult.TableNodes
		   tblname = "AW_" & table.schema.name & "_" & table.name #>
		<Table Name="<#= tblname#>" SchemaName="MyBiml_Destination.dbo">
			<Columns>
				<#=table.columns.GetBiml()#>
			</Columns>
			<Indexes>
			<# if targetConnection.ImportDB(nothing,tblname).TableNodes.count > 0 then
		 	   for each idx as AstTableIndexNode in targetConnection.ImportDB(nothing,tblname).TableNodes(0).indexes #>
				<Index Name="<#=idx.name #>"  Clustered="<#= idx.clustered.tostring.tolower #>" Unique="<#= idx.unique.tostring.tolower #>">
					<Columns>
						<# for each idxcol as AstTableIndexColumnNode in idx.columns 
						if not table.columns(idxcol.Column.Name) is nothing then #>
								<#= idxcol.getbiml() #>
						<# end if 
						 next #>
                    </Columns>
				</Index>					
				<# next 
				end if#>
			</Indexes>
 		</Table>
		<# next #>
	</Tables>
</Biml>

This block will add the existing indexes to your Biml meta-data and therefore, also make sure, that they will be re-created after a drop and recreate. Of course, when adding or removing columns, your indexes may not have the same effect anymore but at least, they won’t be lost.

Any questions or comments? We’d love to hear from you at !

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!