Friday Flyway Tips–Comparison Options

Recently a customer asked how they could get index changes to be captured in Flyway Desktop. In their case, they wanted a different fill factor, but I decided to investigate a bit more how things work.

This post looks at how to control the comparison options in Flyway Desktop (FWD).

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

The Setup

I’ve got a table and an index, which I created with this script.

CREATE TABLE [dbo].[Customer]
(
[CustomerID] [int] NULL,
[CustomerName] [varchar] (75) NULL,
[PrimaryContact] [int] NULL,
[PrimaryAddress] [int] NULL,
[PurchaseLimit] [numeric] (10, 2) NULL,
[Status] [tinyint] NULL
)
GO
CREATE NONCLUSTERED INDEX [nci_customer_custname] ON [dbo].[Customer] ([CustomerName], [Status])
GO

I saved this in Flyway Desktop, which we can see here in the filesystem:

and here in VS Code.

If I refresh the schema model tab in FWD, there are no changes.

Making Index Changes

I’m going to alter this index. Specifically, I’m changing the pad index, fill factor, and statistics options. Here’s the script I’ll run.

ALTER INDEX [nci_customer_custname] ON [dbo].[Customer] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)

Once this runs, I’ll refresh FWD and I see this. Note that I see an index change, but only one option is captured: STATISTICS_NORECOMPUTE.

What’s happening is that FWD is using the SQL Compare engine and the default options set in the engine. Among these are to ignore fill factor and pad index. However, I can change this.

Changing Configuration

In the past, I would need to edit a config file to make this change, but the team has enhanced FWD to add new options. In this case, notice the button near the top of the Schema model tab: Static data & comparisons. Not a great name, but it’s there:

Once I click that, I get a new dialog. This starts with static data, but I’ll click the second tab, which is Configure comparisons. This shows all the options available in the Compare engine

Rather than scroll, I’ll type in the search box, and I see fill gets me the “Ignore fill factor and index padding” option.

I’ll uncheck this and click OK.

Once I do that, I’ll refresh the comparison, and now I see my options.

Try it out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added: