Design Meeting Notes - January 10, 2013
Clustered/non-clustered unique identifier primary keys and SQL Azure
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
- 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.
- 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