I, like many, have grown used to NHibernate’s schema generation capabilities. Those make working with databases such a pleasure that I cannot imagine trying without them.

However, at some point, even NHibernate’s smarts reach an end, and such an occasion requires the use of direct SQL to manipulate the database directly. A good example of that would be:

<!-- SQL Server need this index -->
<database-object>
	<create>
	CREATE INDEX PeopleByCityAndLastName ...
	</create>
	<drop>
	DROP INDEX PeopleByCityAndLastName 
	</drop>
	<dialect-scope name="NHibernate.Dialect.MsSql2000Dialect"/>
	<dialect-scope name="NHibernate.Dialect.MsSql2005Dialect"/>
	<dialect-scope name="NHibernate.Dialect.MsSql2008Dialect"/>
</database-object>

<!-- Oracle need this stats only -->
<database-object>
	<create>
	CREATE STATISTICS PeopleByCityAndLastName ...
	</create>
	<drop>
	DROP STATISTICS PeopleByCityAndLastName 
	</drop>
	<dialect-scope name="NHibernate.Dialect.OracleDialect"/>
	<dialect-scope name="NHibernate.Dialect.Oracle9Dialect"/>
</database-object>

As you can see, this allows us to execute database specific SQL, using the dialect scope. It is not a common feature, but it can be incredibly useful.