CODEWITHSUNDEEP
sql-server
SQL Learner
SQL Learner

Reputation: 601

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

using SQL Server 2005 standard edition with SP2

I need to design a table where I will be storing a text file (~200KB) along with filename ,description and datetime.

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

Per this thread What is the benefit of having varbinary field in a separate 1-1 table?

there is no performance or operational benefits which I agree to some extent however I can see two benefits

  1. store those into a separatable table that can be stored on a separate file group
  2. you can not rebuild index on a table containing lob data type ONLINE

Any suggestions would be appreciated.

Upvotes: 8

Views: 3295

Answers (2)

Christopher Upton
Christopher Upton

Reputation: 25

I can answer your question in one simple word: Kiss.

Which of course stands for... Keep It Simple Stupid.

Adding a table for is generally a no-no unless you really need one to solve a problem.

Generally, I disagree with splitting tables. It adds complexity to databases and code. Having useless columns in a table is a bad thing, but it's not as bad as multiple tables when you only need one.

Cases where you would consider adding another table:

  1. Some of your columns are BloB's of data (greater than page size) and they are rarely used and other columns with small data sizes are accessed frequently.
  2. If you lack a brain.
  3. If you are evil.
  4. Or... if you are trying to piss-off your coworkers.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294487

I would advise against separation. It complicates the design significantly for little or no benefit. As you probably know, SQL Server already stores LOBs on separate allocation units, as described in Table and Index Organization.

Your first concern (separate filegroup allocation for the LOB data) can be addressed explicitly, as Mikael has already pointed out, by appropriately specifying the desired filegroup in the CREATE TABLE statement.

Your second concern is no longer a concern with SQL Server 2012, see Online Index Operations for Indexes containing LOB columns. Even prior to SQL Server 2012 you could reorganize indexes with LOBs without problems (and REORGANIZE is online). Given that a full index rebuild is a very expensive operation (an online rebuild must be done at the table/index level, there is no partition online rebuild options), are you sure you want to complicate the design to accommodate for something that is, on one hand, seldom required, and on the other hand, will be available when you upgrade to SQL 2012?

Upvotes: 7

Related Questions