Design Question

I am creating a table and would like to know the most efficent way to do
this.
One of the colums will be text datatype and can have several thousand
characters in it (less than10k). The 'ntext' datatype in SQL and hold over
a million characters so that isn't a problem.
The question is: Is it more efficent to have the data directly in the
database or should I write it to a file and store a filename?
I would prefer storing the data directly in the table but if this is a
terribly bad idea I want to know. The problem I have with the file aspect
is maintenace of all the files and over years of use there could be quite a
few files generated.

Thank you in advance,
EricEric, in my opinion the choice of keeping the data in the database and
keeping only a reference to external data really depends on the use of
the data.

Will the files in question be updated after initial creation?
How often?
Are the updates complete file replacement or changes to data within the
file?
What are the chances multiple uses will attempt to update a file at the
same time?
How many different applications need access to the data?

The more changes, changes to only peices of the files, the more times
the data is accessed, and the more concurrent update activity the more
I would favor storing the data in the database.
IMHO -- Mark D Powell --|||Thank you for your response. Answers to your questions.

> Will the files in question be updated after initial creation?
Yes
> How often?
A small subset would rarely be updated.
> Are the updates complete file replacement or changes to data within the
> file?
The updates would be edits only.
> What are the chances multiple uses will attempt to update a file at the
> same time?
Infrequent.
> How many different applications need access to the data?
1

I favor the database method too, despite the fact that the updates would not
be a frequent.
Partly for security, easy to back up the database compared to many files.
My concern was eventual database size, but there must be many large database
that store records at least as large as I mentioned.

Thanks again,
Eric|||Beringer (borden_eric@.invalid.com) writes:
> I am creating a table and would like to know the most efficent way to do
> this.
> One of the colums will be text datatype and can have several thousand
> characters in it (less than10k). The 'ntext' datatype in SQL and hold
> over a million characters so that isn't a problem.
> The question is: Is it more efficent to have the data directly in the
> database or should I write it to a file and store a filename?
> I would prefer storing the data directly in the table but if this is a
> terribly bad idea I want to know. The problem I have with the file
> aspect is maintenace of all the files and over years of use there could
> be quite a few files generated.

There are people who quite strongly recommend storing only the the filename
in the database, and the text as separate files.

I am not one of those. The point with storing on file is mainly simplicity
in programming. The text datatype is messy to work with, not the least in
ADO.

On the other hand, you perfectly right that backing one database is
easier than backing up one database + lots of files. Add to that you
cannot get transactional consistency with a file system, and that a
file may disappear without the knowledge of SQL Server.

Thus, in my opinion, storing only the filename is a low-budget solution
when your requirements are lax. Storing the data in the database may
require more development, and, yes, it can also have some negative
effect on performance. But it is a more robust solution.

And in your case, since your texts are no more than 10000 chars, it's
not really an issue. Go with ntext.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL server is capable of handling that sort of data in a column.. So I
would suggest holding it in the database rather than putting them in a
filesystem. What will happen if you need to migrate things to a
different server.. or one of the path to that folder changes...? Plus
if you need to remove the data, you need to delete the file plus the
path/file name reference in the database.. Things can go wrong if you
dont stay consistant with all the updates... Plus, if more than one
person need to update one record in a particular time(dont ask me how
often thats gonna happen... but as an architect you need count that
into account when making a decision) that will complicate things...!
Plus its easy to do searches of a text in sql than thru windows
search....