known bugs/limitations for mssql server

General Discussion
1

Just completed exporting our existing database to a liquibase changeset! Great tool!

However, after generating the changelog I used SQL Delta to compare the new database generated by liquibase with our existing pristine database and discovered a number of limitations with generateChagneLog which made the conversion a bit time consuming.  I thought I would share what I found so that they can be addressed in future releases. Our current database is MS SQL 2005.

  1. length of nvarchar columns are lost
  2. nvarchar(max)/varchar(max) is converted to ntext/text
  3. missing the ability to set the identity seed value on for auto increment column
  4. missing option to make primary key non-clustered
  5. missing ability to create defaults, on those columns where defaults were used it would generate the create default statement in default value which doesn’t work
  6. missing ability to include non-key columns on create index (http://msdn.microsoft.com/en-us/library/ms190806.aspx)
  7. missing create statistics
  8. missing column documentation (sp_addextendedproperty)
  9. several indexes were not generated, but many were.  I think most of them were those with non-key columns but some of those that were generated had non-key columns and I had to replace the create index with raw sql so I’m not sure why some were generated and others not.
  10. several foreign keys constraints were not generated, but many were, not really sure why.  I found a few tables in pristine database defined same foreign key constraint with different names and might have something to do with it, but these should still import so we can use liquibase to drop it.
  11. missing check constraints
2

Thanks for the update.  I created a feature request listing your findings (http://liquibase.jira.com/browse/CORE-399) so they do not get lost.

Many on the list are ones I knew of before, but have not had time to implement yet.  I see the diff/generateChangeLog tool as a helper and sanity check, and so have not put as much time into it as the core changelog functionality, especially as you get into more database-specific functionality (clustered primary keys, create statistics, etc.)  I would definitely hope to continue improving them and your list helps a lot. 

Let us know if you have any other questions or run into any problems.

Nathan

3

I’m trying to follow an oracle tutorial on the mysql (with the needed changes).

What I’ve discovered is, that the diff-mode of liquibase finds changes in the views, where the only change is the full-name of tables/fields (are fully-quilified in the db, even if the sql for view creations are equal in the changeLog).