6

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.

Community
  • 1
  • 1
SQL Learner
  • 571
  • 1
  • 9
  • 16
  • What is your actual question here? Asking for suggestions is not a good fit for the StackOverflow format. What are your constraints? – Oded Jan 02 '12 at 18:15
  • Generally, in terms of Database Design what is recommnded? you are free to give your opinion. I will not debate it. table will tops coupld millions rows varchar(max) column will be not null. – SQL Learner Jan 02 '12 at 18:20
  • The problem is that you are asking for opinions - not something that is encouraged here on StackOverflow. – Oded Jan 02 '12 at 18:21
  • I apologize, I did not mean it... I am just confused. May look into SQL 2005 design book I have on shelf but not sure it would have answer? – SQL Learner Jan 02 '12 at 18:24
  • It probably won't have an answer - this is the kind of thing that completely depends on your specific circumstances, hence not answerable by anyone but yourself... – Oded Jan 02 '12 at 18:26
  • 2
    For the first part it is possible to store the varchar(max)/varbinary(max) data in a filegroup of it's own using `TEXTIMAGE_ON` without having them in a separate table. http://msdn.microsoft.com/en-us/library/ms174979.aspx – Mikael Eriksson Jan 02 '12 at 18:26
  • And rebuild index online is not possible only when you are rebuilding the clustered index or an index where the LOB data is part of the index. http://msdn.microsoft.com/en-us/library/ms190981%28v=SQL.90%29.aspx – Mikael Eriksson Jan 02 '12 at 18:32

2 Answers2

7

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?

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Remus, you are a true SQL server gem @Microsoft. I appreciate your comments. – SQL Learner Jan 02 '12 at 19:20
  • To Remus: What if a table has 2 varchar(max) columns that are rarely used and causing a table to grow rapidly? In this scenario would you split into a separate table? – user2966445 Jun 18 '15 at 13:52
  • @user2966445: ask questions as separate questions, not as comments. – Remus Rusanu Jun 18 '15 at 14:09
  • Thanks. Wanted your input specifically. New question posted here: http://stackoverflow.com/questions/30918333/sql-server-2012-separate-2-varcharmax-columns-to-separate-table – user2966445 Jun 18 '15 at 14:35
0

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.
Christopher Upton
  • 25
  • 2