Performance of ALTER TABLE

Reading a post from Diego Dagum's blog I remembered about an interesting topic related to SaaS. In his article, Diego, talks about an experience he has with a customer that developed an application for multiple customers (multi-tenant). They had a performance issue because they were using shared-schema (i.e. extended fields stored in a separate table as rows pointing to the shared data table).

Figure 1 - Shared schema approach (Multi Tenant Data Architecture paper)

Diego and his colleague tried to convince the customer to move to the separate schema approach. In this approach each customer has its own table and every time he wants to customize an ALTER TABLE is used)

The customer immediately refused the ALTER TABLE because of the myth that altering the schema of a table is a *HEAVY *operation.

It seems the story ends with the customer afraid to go with that approach, but this is a very interesting issue for multi-tenant applications. Modern databases implement ALTER TABLE very fast depending on the structure of the table and the change that wants to be done.

Let's enumerate the common scenarios and how SQL Server manage this:

  • Adding a column: when you add a *NULLable* column to a table only metadata changes (syscolumns).
  • Dropping a column: the same applies when you drop a column
  • Changing a data type: this is tricky. Changing the data type to something larger should only be metadata change and the physical change in the rows should not happen until the rows are updated. When changing the data type to something smaller, SQL Server will have to validate to make sure current data fits in the smaller type

There is an interesting (paid) article that explore the insides of ALTER TABLE:

PS: Talking about Diego, if you read Spanish make sure to read his architecture bulletins. There are already 3 of them:

Abril 19, Boletin Oficial de Arquitectura #3

Abril 05, Boletin Oficial de Arquitectura #2

Marzo 18, Boletin Oficial de Arquitecture #1

Published: April 30 2007

blog comments powered by Disqus