Upgrading a Schema

Upgrading a Schema

Upgrading a schema is pretty simple:

1. Update your SQL.
2. Run SchemaInstaller.Install with your new SchemaObject collection.
3. Insight will take care of the rest!

How It Works - the Schema Registry

Insight maintains a table called Insight_SchemaRegistry in the database. The schema registry has a record for each object in all of the schemas managed by Insight. Each object has a signature, which is calculated as the MD5 hash of the SQL Text used to create the object.

Each time you run SchemaInstaller.Install, Insight will look at the registry for the current schema collection and determine what has changed - whether a new object, deleted object, or a changed object.

Insight is pretty smart. It knows what order to install objects or remove objects, and uses the SQL Server SMO objects to determine the dependencies of the objects when it is modifying objects.

When the upgrade process runs, Insight will:

1. Calculate changed objects.
2. Delete any objects no longer in use.
3. Script dependencies for objects that have changed.
4. Update objects that have changed.

• If a table has changed, Insight will add/drop/change any columns that are different, as well as any constraints.

1. Create new objects.

IMPORTANT NOTE: Adding Columns

When you add a column to your table, Insight uses the "ALTER TABLE ADD column" command to add the column to the table. SQL Server puts the column at the end of the table. So in production, your column order may not match the order defined in your schema.

So if you do a SELECT *, the columns may not be in the order you expect. This is generally ok if you are sending the data to an ORM, but don't assume that your columns are in the same order for INSERTs, etc.

For example, this is probably bad:

INSERT INTO Foo SELECT * FROM Goo

You should always use a column list for this:

INSERT INTO Foo (Col1, Col2) SELECT Col1, Col2 FROM Goo