Create.Index(name)...WithOptions().Unique() should use INCLUDE in SQL 2005+

[Justin Collum]

I've got a unique index that I created with this FM code:

Create.Index(_indexName).OnTable(Tables.Contacts)
.InSchema(Schemas.Core)
.OnColumn("FirstName").Ascending()
.OnColumn("LastName").Ascending()
.OnColumn("AddressLine1").Ascending()
.OnColumn("City").Ascending()
.OnColumn("State").Ascending()
.OnColumn("Zip").Ascending()
.WithOptions().Unique().NonClustered();

Which created an index that looks like:

CREATE UNIQUE NONCLUSTERED INDEX [IX_ContactNameUnique] ON [core].
[Contacts]
(
[FirstName] ASC,
[LastName] ASC,
[AddressLine1] ASC,
[City] ASC,
[State] ASC,
[Zip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

But I'm getting an error message "The index entry of length 1016 bytes
for the index 'IX_ContactNameUnique' exceeds the maximum length of 900
bytes." After reading online the suggested method is to instead create
the index with the INCLUDE keyword: http://www.sqlteam.com/article/included-columns-sql-server-2005
like this:

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].
[SalesOrderDetail]
(
[ProductID]
) INCLUDE ( [TestIndex])

I suggest after the WithOptions call there should be an option
called .WithIncludes(). Or is there another method?

[Josh Coffman]

If there's not an option, it should probably be added or some way to specify it. Please check the code and offer a solution. I haven't had time to work on FM lately and probably won't for a little while longer.

If you do need to add code, try to be sensible with respects to FM supporting multiple db's.

Sorry I can't help more right now.

--
You received this message because you are subscribed to the Google Groups "FluentMigrator Google Group" group.
To post to this group, send email to fluentmigrato...@googlegroups.com.
To unsubscribe from this group, send email to fluentmigrator-goog...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluentmigrator-google-group?hl=en.