FluentMigrator Composite Index

02Apr10

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:

  • Maybe something that has meaning now, wont necessarily have the same meaning in the future
  • It’s harder to manage composite keys that it is to manage surrogate keys

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

Cheers

Advertisements


No Responses Yet to “FluentMigrator Composite Index”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: