Best way to store text in SQL Server for ASP pages?

By Bill Graziano on 13 July 2000 | Tags: Data Types

Brad writes What is the best way to store text in SQL Server (7.0) so that it can easily and quickly be served up to an ASP page? For example, say I have 500 articles, ranging in size from 500 characters to 25,000 characters. Not only do I want to store the text of the article, but also the title of the article, its date, the author, the subject, and so on. I also want to be able to search the text of the articles. Obviously a VARCHAR field won't hold really long articles, and TEXT fields are slow and hard to work with. I also want to avoid using the SQL Server Search service. What is the ideal solution?

Intro
This sounds very familiar to what I went through to build this site. That said, let's start with the easy stuff first. The title and subject are probably char or maybe varchar fields. I actually created a field called Abstract which is what appears on the home page. My abstract is varchar(4000). It's going to get shrunk here real soon now since I really don't need 4,000 characters. Date is a datetime and author can either be a char field or a foreign key back to an authors table. I elected to go with a foreign key into the Users table and coded certain users as authors. I'm all about reuse :)

TEXT Fields
That leaves us with the text of the article. If you ever want more than 8,000 characters you can either use a text datatype or string a couple of varchar fields together. If you decide to use multiple varchar fields you are probably dealing with a subtable and have code to put them together and take them apart again. Suddenly text fields seem downright easy to use.

Real Big TEXT Fields
If you read books online they tell you that text fields of 8,000 characters or less are very easy to handle but if you go over the 8,000 character limit you have to use all these special functions which are different for OLE DB, ODBC, ADO and DB-Lib (if anyone here remembers DB-Lib). Balderdash! In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there. I tried the same thing on the hosted site using a 15,000 character field and it worked fine also (Hey, I pay the the KB!).

My Setup
A couple of quick notes on my setup. I'm running SQL Server 7.0 Service Pack 2. I'm also running Windows 2000 Server. My hosting company is also running Windows 2000 Server and I think that makes a big difference. My previous host (NT4 SP something) was truncating varchar fields at 255 characters and I never even tested really large text fields. Needless to say that's one of the reasons this site is moving. If you want to do something like this I heartily recommend ORCSWeb.com. They have been great to work with and they handle large text fields very well.

Quotes and Queries
You'll also spend time getting quotes handled properly. SQL Server needs single quotes converted to two single quotes. IIS 5.0 needs double quotes converted to something else to pass the string through POST. I convert them to a weird string, pass them and then convert them back. As far as queries, you can use LIKE to query a text field. It's not fast but it works. I'd prefer to use full-text indexing (Microsoft Search) myself and only update nightly but that's not an option with most hosting companies. My text pages are relative static and full-text indexing gives you some great tools to query the tables.

Performance
A last note on text fields. In a record, SQL Server stores a 16 byte pointer to the data pages that actually hold the text values. Therefore a select that doesn't return the text value (like the one that feeds my home page) runs very quickly. It also means you don't need to separate your text fields into a separate table for performance reasons. Just remember to only select them when you need them. In SQL 2000 this becomes an option you can define per table. You can specify that all text fields below a certain size are stored in the row with the data and all others are stored separately.

Whew . . .
Ok, there's all my secrets. I hope this helps. If anyone wants to this in action I can create a fake story greater than 8000 characters and post it here. Just post a comment if you'd like me to.