Sql Server: Any Value In Vertical Partitioning When I'm Always Going To Re-join Them?

i'm faced with having to add 64 new columns to table that already had 32 columns. For examples sake: Customers ( CustomerID int Name varchar(50) Address varc

Solution 1:

For simplicity of data model, without further information, I would probably not partition, but you haven't indicated the nature of the data in these new columns (perhaps some columns are arrays which should be normalized instead).

However, some points:

If you do vertically partition, and have a FK constraint on the supplemental table, that may help eliminate the join in some scenarios, since it knows that one and only one row will exist. Obviously it will be indexed on the same unique keys, which will help to eliminate the need to determine if there is a cross-join, since there can only be 0 or 1 rows.

You can have a single updatable view which joins the two tables and have a trigger on the view which inserts into the two tables joined to make the view. You could also decide to do a left join and only create a supplemental row at all if any of the columns needing it are non-NULL.

You can also use a sparsely joined set of tables of supplemental data. Obviously this would also need joins, but you could also use similar techniques with multiple supplemental tables as you would with 1.

Solution 2:

If these values are a) unique to a record (a given customer should only have one value which would go in NewColumn1) and b) not used by any other record (at least, no other record that doesn't also require the base customer information) I'd say leave them as one table. Just don't forget to name your specific columns in any queries you write against the table.

I come from an EDI background, and sometimes you have to deal with flatfiles that contain 30+ columns of data per row. As you mention, NULL doesn't take up much room, and if you're NEVER going to be grapping the columns independently (and you'll never be able to grab the base customer data independently), I'd say you've got it right.

Solution 3:

The answer is in details that were omitted from the question. The number of columns is irrelevant, it is the nature of the data that matters.

• First, remember that a given row in any table can never exceed 8060 bytes. So if the new columns are sized such that that limit can theoretically be exceeded, you will have built a time-bomb into the database. Sometime when it is least convenient, a data insert or update will throw an error and/or data will be lost.

  To guard against this, you may need to use more than one table, it's just a limitation of most editions of SQL-Server. .

• The other important consideration is data-modeling. Do the new columns have a one-to-one relationship with CustomerID? For example, say eyeColor?

  Because of the number of columns and the fact that you omitted their names, I suspect that a non-normalized design is being contemplated. If the new columns are something like WebPage1, WebPage2, WebPage3, etc., then these need to be split into a separate, normalized table. .

But, if the columns really are unique items, unrelated to each other and with a 1-to-1 relationship to CustomerID (or whatever the primary-key of that table is), and the size limit cannot be busted, then having everything in one table is perfectly fine.