Design Meeting Notes - January 10, 2013

Clustered/non-clustered unique identifier primary keys and SQL Azure

In a
previous meeting we came to a conclusion on better DDL generation from Code First Migrations for SQL Server. However, the problem with this is that SQL Azure uses the same provider as SQL Server and SQL Azure does not support the following:

NewSequentialId is not supported

Every table must have at least one clustered index

Options:

Don’t make any changes

This would mean that DDL generation for both SQL Azure and SQL Server would continue to have the problems that we are attempting to fix, so this doesn’t seem like a good option.

Make the changes as planned and by default create DDL that does not work on SQL Azure

At first this seems like a non-starter, but given that the DDL we do generate that “works” only works in a sub-optimal way, maybe it is good to fail fast and force the user to make a change

The problems here are:

The exception message from Azure will not immediately provide an obvious way to fix the issue

We could detect and issue a better message from Migrations, but this is not trivial because it would have to come from the SQL generator, and would require that SQL Azure is special-cased in the generator

Special case SQL Azure in Migrations and attempt to add a dummy column with a clustered index

It may be reasonable for an app developer to to add a dummy column to workaround the Azure issue, but it seems problematic for us to do this without knowing if this is what is really wanted.

Special case SQL Azure in Migrations so that it will continue to generate the DDL that we have always generated

The DDL will be sub-optimal, but it will at least work

Ideally, we would also output a warning from Migrations

Decision: we will try the last option listed above—continue to generate existing DDL for Azure, possibly with a warning.

How would we special case SQL Azure?

We will have SQL Azure return a different provider manifest token, since this is the current mechanism used to distinguish between different flavors of a particular server type.

Notes:

Suggested manifest token is “SQLAzure.2012”

It’s not clear whether or not we will need different manifest tokens for different Azure versions, but we don’t need to decide this one way or the other until later

The manifest token parsing code needs to account for this new token, but for everything other than Migrations it can still be treated like regular SQL Server