SQL data varbinary (max) and varchar (max) in a separate table

Question

using the standard version of SQL Server 2005 Service Pack 2 (SP2)

I need to create a table in which I will store a text file (~ 200 KB), as well as the file name, description and date and time.

Should we create a table in which the data varchar (max) and varbinary (max) should be stored in a separate table or should it be a column of LOB data types as part of the main table?

In this topic, What is the advantage of having a varbinary field in a separate table 1-1?

there are no operational or operational advantages that I agree to some degree; I see two advantages

• save them in a shared table that can be saved in a separate file group
• you cannot rebuild the index in a table containing the lob data type ONLINE

Any suggestions would be appreciated.

Answer 1

I would advise you not to separate. This complicates the design significantly for little or no use. As you probably know, SQL Server already stores the LOB in separate allocation units, as described in the Table and Index Organization .

Your first problem (a separate distribution of filegroups for LOB data) can be explicitly addressed, as Michael already pointed out, appropriately specifying the desired filegroup in the CREATE TABLE statement.

Your second problem is no longer bothering SQL Server 2012, see Online Indexing Operations for Indexes Containing Columns of Large Objects . Even before SQL Server 2012, you can easily reorganize indexes with LOB (and REORGANIZE on the network). Given that a complete rebuild of the index is a very expensive operation (online rebuild should be performed at the table / index level, there are no options for reinstalling online), you are sure you want to complicate the design to accommodate something that is, on the one hand, rarely required, but on the other hand, will be available when upgrading to SQL 2012?

Answer 2

I can answer your question with one simple word: "Kiss."

Which, of course, means ... Keep it just stupid.

Adding a table for no-no at all, unless you really need to solve the problem.

As a rule, I do not agree with dividing tables. This adds complexity to the databases and code. Having useless columns in a table is bad, but it's not as bad as multiple tables when you only need one.

Cases in which you would like to add another table:

• Some of your columns are BloB data (larger than the page size), and they are rarely used, and other columns with small data sizes are often used.
• If you do not have enough brain.
• If you are angry.
• Or ... if you are trying to anger your colleagues.