SQL Server 2008, join or no join?

Question

Just a small question regarding joins. I have a table with around 30 fields and I was thinking about making a second table to store 10 of those fields. Then I would just join them in with the main data. The 10 fields that I was planning to store in a second table does not get queried directly, it's just some settings for the data in the first table.

Something like:

Table 1
Id
Data1
Data2
Data3
etc ...

Table 2
Id (same id as table one)
Settings1
Settings2
Settings3

Is this a bad solution? Should I just use one table? How much performance impact does it have? All entries in table 1 would also then have an entry in table 2.

Small update is in order. Most of the Data fields are of the type varchar and 2 of them are of the type text. How is indexing treated? My plan is to index 2 data fields, email (varchar 50) and author (varchar 20). And yes, all records in Table 1 will have a record in Table 2. Most of the settings fields are of the bit type, around 80%. The rest is a mix between int and varchar. The varchars can be null.

Answer 1

This is known as vertical partitioning, and is a legitimate strategy. You might do this for the following reasons:

• If certain columns are accessed or changed far more frequently than others.
• If you want to store some of the columns on one set of storage media and the others on another.
• If you have extensive triggers that don't need to run in response to updates on certain of the columns.

There will likely be a small performance hit when accessing through the JOIN, but performance may increase in the cases where you only need to access one or the other of the component tables.