Maximum size of the contains_search_condition clause in a CONTAINS statement

Hi,
We're using the SQL Server Full Text Engine to implement a search engine. Users can enter queries which are then executed using the CONTAINS statement. I can't find any documentation that indicates the maximum allowable size of the contains_search_conditi
on clause. Our users' search terms can be quite complex with many Boolean predicates. As a result they can become quite long: several hundred or even a few thousand characters long. What is the upper size limit?
Many thanks,
Dan
Dan,
There was an early bug in SQL Server 2000 RTM that limited the max character
length to 1023 (1K -1), but it was fixed in SP1.
I believe that the max character limit was extended to the max length of
char/varchar datatype length of 8000.
Regards,
John
"Dan Crow" <anonymous@.discussions.microsoft.com> wrote in message
news:A46B5192-072F-4DF0-8D9E-F44D955EDB58@.microsoft.com...
> Hi,
> We're using the SQL Server Full Text Engine to implement a search engine.
Users can enter queries which are then executed using the CONTAINS
statement. I can't find any documentation that indicates the maximum
allowable size of the contains_search_condition clause. Our users' search
terms can be quite complex with many Boolean predicates. As a result they
can become quite long: several hundred or even a few thousand characters
long. What is the upper size limit?
> Many thanks,
> Dan

Maximum Size of OPENXML IN SP

How much data we can pass through as an XML Text into SP by the concept OPENXML

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xmloptions.asp

"Limitations
...The size of an XML document that can be stored in varchar (max) data type is limited to 2GB. ... "

HTH

Maximum length of Datatype text...

Hello,
I see that the max length of datatype text is some where abouts 2
million characters.
My question is, when I conenct the MSDE database to SQL Enterprise
administrator, and look at the table design, the length of the data
reads "16"
It's quite obvious this doesn't mean 16 characters... what does it
mean? 16 bytes of information? if so, that's an insanely large
amount; but that's okay. Right now, just to be safe, I've limited the
input to 200 characters. After you finish snickering to yourself, how
many characters does this value of 16 equate out to? 2 ba-gillion?
Thank!
Matt
On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:

>Hello,
>I see that the max length of datatype text is some where abouts 2
>million characters.
Hi Matt,
More, actuallly. 2Gb of characters, meaning well over 2 billion
characters.

>My question is, when I conenct the MSDE database to SQL Enterprise
>administrator, and look at the table design, the length of the data
>reads "16"
That's the length of the pointer.
For a text column, the only thing stored in the regular storage area for
the table is a 16-byte pointer. This pointer points to the start of the
structures that store the real information, up to 2Gb. The sice of this
information is not reported in Enterprise Manager.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 11, 6:46 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:
>
> Hi Matt,
> More, actuallly. 2Gb of characters, meaning well over 2 billion
> characters.
>
> That's the length of the pointer.
> For a text column, the only thing stored in the regular storage area for
> the table is a 16-byte pointer. This pointer points to the start of the
> structures that store the real information, up to 2Gb. The sice of this
> information is not reported in Enterprise Manager.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
So... Hugo... you're saying I've got relatively no limit aka a 2GB
limit per-data type entry? like 200 characters is a comical limit? I
can more like make it 2000 without a problem?
|||On Thu, 12 Jul 2007 03:34:21 -0000, Matt Brown - identify wrote:

>So... Hugo... you're saying I've got relatively no limit aka a 2GB
>limit per-data type entry? like 200 characters is a comical limit? I
>can more like make it 2000 without a problem?
Hi Matt,
(Sorry for the delayed reply.)
That is indeed what I am saying. I'd actually go a step further, and say
that as long as you are under the limit of 8000 bytes (that is 4000
characters for unicode text or 8000 character for ASCII text), you
should not even use the text / ntext datatypes at all. These datatypes
have many limitations, making them awkward to work with. Use varchar or
nvarchar for strings with a length of up to 8000 bytes. Onlu ise text or
text if you really need to store more characters.
Also note that in SQL Server 2005, text and ntext have been replaced by
varchar(max) and nvarchar(max), with the same length limit but without
most of the limitations. For compatibility reasons, text and ntext are
still supported - but they will be removed in a future version. For SQL
Server 2005, the advice to use varchar and nvarchar for strings up to
8000 bytes still stands, but you shoud use varchar(max) or nvarchar(max)
rather than text or ntext for longer strings.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis