SQL Server Alter Script Generation

Toad Data Modeler
1

When using the alter script capability, the script that is generated, has some fundamental issues. Say we add a single new column to a fact table (dimensional modeling).

The script basically follows this pattern:

  1. Generates a whole new copy, with a different name
  2. Does a select insert, into the new table.
  3. Drops the old table.
  4. Renames the new table to the original name.

There are a number of issues with this:
Default object with the same name cannot exist in the same DB.
Copying all that data takes forever.
There are a lot of places for errors to occur, when creating a whole new table. What if one of the objects does not generated. Weve now lost it forever.

What it should do, is alter the table, and add the new column, with a default value.

Is there a way to modify this behavior or does this have to be addressed as an enhancement request?

Alter Script Example.txt (1.42 KB)

2

Hi,

thank you for your input, good feedback! Though, from the example we aren’t able to find out the reason of the behavior. There are more possible causes and it would help us if you could send some part of your model with which we could reproduce it to modeling@quest.com.

We are trying to improve TDM all the time so any ideas are highly appreciated.

Regards,
Lukas

3

Hi,

you can try to change settings for SQL Server alter script generation. Click Settings | Options and in section Model - Physical Model - Microsoft SQL Server uncheck checkbox Use Temporary Table to Preserve Data.

Give it a try.

Regards,

Vaclav