SQL Server 2008  : Store 100K char string with indexing option

crazywolf2010
crazywolf2010 used Ask the Experts™
on
Hi,
How can I store a char string upto 100K within a column? I could use text else ntext but will normal indexing work with it?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooter

Commented:
You might want to use varchar(max) or nvarchar(max) to store the character string rather than text or ntext.  (TEXT and NTEXT are deprecated, and will likely disappear from a future version of SQL.)

I don't believe you can use a standard index on [n]varchar(max), but you CAN use Full Text Indexes.

Author

Commented:
Hi Razmus,
Do you have any example on full text indexes on such column which may contain upto 100K char data?
What are the pros and cons of this method?
Is this the only way?

Thanks
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooter
Commented:
> Is this the only way?

No, there are also XML indexes, which would be appropriate if the text string is well formed XML.

> What are the pros and cons of this method?
Pros: It really breaks out words, and does a little more intelligent matches.
It can index on a column which is larger than a page.
If your 100K char data is really a word documents, pdf* files, or other common types of documents -- the full text index can read that information as well.  (* With an external component from Adobe, I believe, for pdfs.)  (The datatype would be a varbinary(max) in that case.)
The full text query is much more efficient, and quicker than matches with LIKE.

Cons: Uses space outside the database (and the mechanism is different between SQL 2005 and SQL 2008).
Requires turning on Full-Text query.
Requires learning to use full text query syntax/functions.

> Do you have any example on full text indexes on such column which may contain upto 100K char data?
Are you asking for online examples to get started; whether I have a sample database using 100K data fields; or whether full text indexes would work on 100k?  (Max size for the varchar(max) is 2 GB...)

Author

Commented:
Thanks for the details.

Are you asking for online examples to get started;
-- Yeap
Professional Troublemaker^h^h^h^h^hshooter
Commented:
You could start with the Technet article, on Getting Started with Full-Text Searches, which will use examples from the AdventureWorks sample databases.

I think this article on Simple-Talk does a better job of introducing the concept though, with examples... which conveniently enough, still uses the AdventureWorks databases.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial