Database Data Types Question

Quick question, I will probably sound like a newb...but I don't have any good reference for these kind of questions.

I want to store a rather long piece of text in one of my tables in Microsoft SQL Server 2000. I looked over all the data types, char, varchar, text, ntext, and none of them seem to be able to hold this piece of text. The piece of text, according to Word, is 7,185 characters long, counting spaces etc. Is there a good way to store this in a database field? Or am I doing something stupid? If so, what's the alternative? Thanks.

i am not that familiar with SQL server, however i think that Oracle could handle this (however don't quote me). one solution might be to store the data in text files and the column that was supposed to have the data in it can now just have the name of the text file where the data resides. since the data is so large, this might be useful since you were probably not going to index the column anyway.

I think that ntext, text and image should be the answer.
Note ntext A Unicode text field that can hold up to 1,073,741,823 characters. These fields cannot be indexed or used in a WHERE clause.

That's interesting because I tried to copy and paste something that has substantially less characters into the field when it was of type ntext and it would not let me.

The Text File sounds like a good thing although I would have wanted to have users be able to search in the field where all the text would reside...

But according to you, pabloj, it won't work? I couldn't search for a specific word using a where clause even if I was able to put all the text in there?

well, the hard part comes at indexing something that large. a search on each row for this field would take a long time and your queries would be very expensive. if you want to search for key words in this field, then you might want to consider redesigning your table structure and/or relationships. you could have 1 record id join to another table that has that record id many times for different key words. then you could have another table that has an id and the complete sentence (or your large amount of data). this table could join to the bridge as well, seeing as how 1 sentence would have many key words as well.

Why not just store a pointer to your file?

This was already posted...also he explained that he wanted to be able to have some search functionality using the where clause.

The problem in trying to seach something so large is that it's not efficient. It would be better to add some keywords to search on.

hrm...sounds a lot like...



please read the posts first

So, basically, you got it covered. I guess my work is done here.