7

There's a new unique property we need to add, which is relevant only to a specific subset (less than 1%). We thought it's better to add a new table which will be 1-1, than to add a new column which will have Nulls 99% of the time.

Is a new table the preffered way?

8

If one of the following conditions is true then yes, go ahead and create a separate table.

  • You are working in SQL 2005 or lower
  • Your new column is one of the following datatypes: Geography, Geometry, Image, NText, Text, Timestamp, or a user defined data type
  • No default value
  • No rules
  • Column isn't a computed column

If none of those are true then you could use a SPARSE column.

ALTER TABLE tablename ADD mysparsecolumn INT SPARSE NULL

Here is BOL on SPARSE columns. Also you may want to look at COLUMN SETS. A sparse column is a column that is optimized for NULL values and according to BOL takes up no space when the value is NULL. There are tables in BOL that will give you an idea of the actual savings generated by using SPARSE columns.

All that being said if you want to use a separate table you certainly can. The down sides are that you will lose some space over all depending on how large your primary key is (the minimum you will need to include in the new table along with the extra column) and it's libel to be a bit slower because of the extra join.

| improve this answer | |
3

If your main concern is efficiency of storing and retrieving data, I'd recommend adding a new sparse column in the existing table. However, if you are dealing with OLTP system, you may have good reasons to choose new table with 1-0..1 relationship.

For instance, there are many legacy applications using existing model which potentially may be broken because of new column. Also, if a new column is any blob, select * in legacy application (despite the fact it's not a very good practice, such queries are quite common) will affect performance.

Secondly, new column may be accessed with different pattern (say, you mostly read old columns, but perform many updates of new column).

Also, I saw many tables which used to be more or less normalized in the beginning, but ended up with hundreds of columns ; some of the columns were not used anymore, others don't fit the model (it's even better to say the model was destroyed). This note is probably out of the question scope, but in my opinion worth mentioning...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.