An interesting discussion was started at the SQL Server Users Group last night.  Basically, the question was whether it was ok to add 26 (!) Text/Image fields to a table.  And if not, what a viable resolution would be.  Now, I've had the opportunity to address a similar issue before and will withhold my solution for the time being from this blog entry so as not to bias the result.

Well, first of all, it seemed like the mandate to include these 26 fields (an arbitrary amount?) came from management which is a glaring red flag to me.  Very rarely (if ever?) does management actually understand database architecture and design and even more rarely how to tune and optimize a database for the applications that use it.  Ok, so management has asked for the ability to store tons of additional (categorized?) information and be able to retrieve it...that's what we need to know, not how they think it should be implemented - that's the responsibility of a knowledgable and creative DBA (more on that another time).

Now the real question: How should the data be stored so that it can be retrieved when needed?  It should be painfully obvious that putting the 26 columns in the table is wrong.  Wrong for a lot of reasons, architecturally and logically.  Some suggestions were:

1.  Create a related table with a foreign key and a single Text field (and possibly other metadata).  When you need the information you join that child table in and return it.

2.  Move the data (if it's file-based) off to a file server with a link in the database back to the actual physical file.

The group (I was a mere observer and didn't venture an opinion) ultimately came to the conclusion that it depends.  It depends on

• the kind of data being stored (binary, text/comments, files, etc)
• the amount of information stored (can you use a (N)VARCHAR instead?)
• does the information need to be queried? (thus possibly requiring Full Text Search capabilities)
• how is the information to be retrieved? (SQL? FTP? etc)

Anyway, what is your opinion? How would you recommend approaching this problem?