SQL Server 2008, join or not join?

Question

Just a small question about joining. I have a table with 30 fields, and I was thinking of creating a second table to store 10 such fields. Then I would just join them with the basic data. The 10 fields that I planned to store in the second table are not directly requested, these are just some of the parameters 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 decision? Should I just use one table? What impact does performance have? All entries in table 1 will also have an entry in table 2.

A small update is in order. Most data fields are of type varchar, and 2 of them are of type text. How is indexing handled? My plan is to index 2 data fields, email (varchar 50) and author (varchar 20). And yes, all entries in table 1 will have an entry in table 2. Most settings fields have a type bit, about 80%. The rest is a mixture between int and varchar. Varchars may be null.

Answer 1

This is called vertical partitioning and is a legitimate strategy. You can do this for the following reasons:

• If some columns are available or changed much more often than others.
• If you want to save some columns on one medium and others on another.
• If you have extensive triggers that don't need to be triggered in response to updates to certain columns.

When accessing through JOIN, a slight increase in performance may occur, but performance may increase when you only need to access one or another of the component tables.

Answer 2

This will depend on whether each row in table 1 has a row in table 2 (whether each row has parameters) and how many of these parameters are regularly NULL.

If all settings (or most of them) are used and are used regularly, I would recommend placing them in one table.

Also, the number of columns you are referencing is not extreme, so a single table, avoiding joins if fields are used, is what I would recommend.

Answer 3

If the data belongs together, keep it together.

If your settings do not make sense without the main data of the table and correspond to each other (for example, one row in table1 has exactly one row - table2), you should add them to the main table.

Answer 4

The solution is not bad, but consider the following things:

• A table containing more columns, and many of them are null, is a sign to create another table.
• If you want to save the settings, create a project in which you can save any number of settings without saving the zero value, for example, create a table with fields, primarykeyid, foreignkeyid, settingsname, settingvalue. Note that in 2 you keep the header name, but you use an int or tiny int, which will work much better.

Hope this helps

Answer 5

Do tables 1 and 2 have the same number of rows? That is, each row in table 1 has a corresponding row in table 2 and vice versa? If so, joining is not necessary, you can just filter the columns you want with select.

A join would only be useful if you have rows in table 2 that match more than one row in table 2, for example. A series of settings that apply to multiple rows of data.

Edit: Editing in the original post has already answered my questions. I would say that a connection is not required here, and this will only adversely affect performance.

Answer 6

The size of the fields, the frequency and type of use (selection, updates, etc.) and the size of the tables (rows) are all in addition to the way you index it.

In general, this is a good design if a subset of the data in one table is very often viewed (say, in a list), and the second subset is viewed / edited much less often and / or contains very large fields (for example, varchar (Maximum)).

However, if the data is always viewed together, this is probably not the way to go. Therefore, if you need to read these settings in the second table each time you read the first table, do not follow this road.

Update. Given your index and the fact that almost all settings are of a fixed size (bit), I would just make it one table. If you need a subset query without an entire table, you can consider the coverage index instead of splitting the table.

Answer 7

If you are going to regularly query the settings table at the same time as the data table, you will want them as one table. If you request each data set separately, it may be useful to store them separately.

There is performance in every connection, but you also want to see how you use tables. If you need them at the same time, it is best to keep them together, otherwise you can break them up until you plan to use them at the same time often in the future.