Good design practice using TEXT fields

[DukeLeto]

I work at a publishing consulting company where I'm responsible for building SQL Server databases for online viewing from CD-ROM Magazine archives.

I'm trying to decide if it would be wiser to insert the bodies of articles into the database as TEXT fields or store them externally as HTML files.

Long opinionated views are welcome and encouraged.

 

[SQLSister]

Well if there's anything we have plenty of, it is opinionated views. My personal preference is to store the files as files and only use the database to store the locations. Text data type requires special handling which can be a pain.

However, if you need to do full text search on the text in the files, I think you might need to use the text datatype. Never did this so I don't know for sure. If you do this you need to read up on the special ways to handle this.

From BooksONLine:
"Text pointers are passed to the READTEXT, UPDATETEXT, WRITETEXT, PATINDEX, DATALENGTH, and SET TEXTSIZE Transact-SQL statements used to manipulate text, ntext, and image data."

So those are statements you need to particularly look up. You might also read up on Fulltext search before you make your decision.