Roundcrisis

home about Conferences rss feed  rss

FluentMigrator Composite Index

02 Apr 2010

Fluentmigrator is a really nice migration tool that allows you to tear your database up and down keeping version, please visit the project page for more info.

Anyway, the other day I had to create a composite index, and I didnt know how,  the way to do that is:

Create.Index(“Name_of_the_index_String”).OnTable(“Name_of_the_Table_String”) .OnColumn(“Column_Name_string”).Ascending() .OnColumn(“Other_Column_Name_string”).Ascending();

Looks kinda obvious no? but what threw me off at the time is that I was looking at the possible operations after OnColumn and there was nothign obvious there,

FluentMigrator Create Index Column Intellisense

then  I looked at the Sql documentation for CREATE INDEX

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]

The solution was (kind of) there, you could do more columns after you specify the sorting direction. Perhaps it’s  obvious but it took me a while to figure out. It was logical however not obvious.

Also on composite, before I created an index I thought about using composite keys, J.Miller has a post here on why that is not really a good idea ( have a look at the comments in particular). For me, it boils down to:

There are more reasons for and against surrogate keys, but this was what was suitable to me at the time

Cheers

If you want to discuss this post, the best place right now for me is twiter, please @silverspoon with your comment/question.