Should I add a column or 1-1 table if property is usualy null

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?

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.

StackExchange Database Administrators Q#47913, answer score: 8