I'm trying to get to the point where I can create a model through database import, modify it by fixing up readings and names, then save it back out and have no schema changes. Eventually, I'd be able to make my modifications to the conceptual model, and use the resulting schema.
To start with, I just created a model by using Add->New Item, and selecting "Object-Role Modeling (DB Import)", then selecting the database, and taking the defaults for the two dialog boxes. I then turned on generation for "SQL - SQL Server".
Unfortunately, this produced many differences from the original schema. In fact, what it produced was not usable.
-
Many columns which had been of types bigint, int, smallint or tinyint became decimal(38,38).
-
All the INT IDENTITY columns became DECIMAL(38,38) NOT NULL, and lost the IDENTITY setting.
-
A char(4) column became nvarchar(4).
-
All the varchar columns have become nvarchar.
-
Column order has changed
-
One datetime column has actually become nvarchar(255)
-
And the most surprising thing was a unique index on a column that caused a separate table to be generated for the column, along with a foreign key back to the original table. The "Table" entity played an optional role in the predicate. Changing that to mandatory put things back the way they were.
This is with the May 25, 2009 version of the tool.