Can I force SQL Server to use the CONTAINS operator first?

If I do the query below, SQL Server does a table scan (thousands of rows) for fn_TestCol(), then evaluates the CONTAINS clause:

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
AND fn_TestCol(col1) = 0

How can I force it to evaluate CONTAINS clause, which returns only a few rows, first? The best I've come up with is this:

SELECT sub.col1, sub.col2
FROM (
SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), 'foo and bar')
) sub
WHERE fn_TestCol(sub.col1) = 0

It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.

Actually CONTAINS is a predicate that can be used with the WHERE clause there are two versions of it defined by ANSI SQL and implemented by Microsoft CONTAINS and CONTAINSTABLE, the other two FULLTEXT predicates are FREETEXT and FREETEXTTBALE, try the link below for details. Hope this helps.

http://technet.microsoft.com/en-us/library/ms187787.aspx

|||

Hi Caddre,

Thanks for the reply -- but that didn't seem to have anything to do with my question!

|||

(It's much faster, but still not as fast as if I could just use the first query, but force SQL Server to evaluate CONTAINS first.)

It does because since CONTAINS is a predicate and not a clause what you can force it to do is determined by the restrictions and limitations listed in the link I provided.

|||no, you will have no control over the freetext functionality as in SQL Server 2005 the query in FT searches still is not connected to the query engine and therefore cannot be optimized and tweaked in your requested way.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

OK, I found a way to do it:

DECLARE @.ftQuery nvarchar(32)

SELECT col1, col2
FROM myTable
WHERE CONTAINS((col1, col2), @.ftQuery)
AND fn_TestCol(col1) = 0

OPTION (OPTIMIZE FOR(@.ftQuery = 'foo'))

This structure allows the optimizer to make assumptions about what might be in @.ftQuery -- so it correctly assumes that the CONTAINS predicate will be a faster starting point than the udf, and runs with the right plan.

|||

_jesse,
Interesting.. this as long been a "by design" problem with SQL FTS since SQL Server 7.0. You never said which version of SQL Server you are using, but I'm assuming it is SQL 2000 and not SQL Server 2005. Correct? Can you provide more details on your UDF fn_TestCol? How many rows do you have in your real table or your myTable example?

Thanks,
John

John T. Kane
Search Evangelist, Intellisearch
email: john.kane@.intellisearch.no

|||

Hi John,

It is SQL 2005.

The problem, as I understand it, is this: when you pass a parameter to CONTAINS, as in

CONTAINS(col1, @.searchText)

the optimizer isn't able to recognize that the CONTAINS operator is a good place to start. The execution plan changes if you replace @.searchText with a literal string, as in:

CONTAINS(col1, 'foo')

In my case, the table being scanned was relatively small (20,000 rows), but the UDF is slow -- it's doing a bunch of string manipulation & comparison. Since the CONTAINS clause is likely to be very selective, the query is very fast if you start with CONTAINS, then run the UDF on the few rows matching the fts, but very slow if you do it the other way around.

Declaring the @.searchText parameter as nvarchar, then adding the OPTIMIZE FOR hint gets it to consistently run in the right order.

Can a field be made mandatory for just new records?

Hi,
Is there any way in SQL server 2000, of making a field mandatory in an
existing table (which already contains thousands of records), without
having to update all of the existing records?
In otherwords, can a field be made mandatory for just new records?
If not, is the only solution to code it into my front end application?
Thanks
ColinThis doesn't really make much sense to me, so far. What is the point of
making the column mandatory if you're not going to update the existing rows?
Is there some application limitation that requires a value? If so, why
would the limitation only be relevant on new rows? Can the application not
look at old rows? Why is it okay for an old row to be NULL and not for a
new row? Just trying to understand the logistics.
If you want only new rows to contain a value, then it is fairly trivial to
have your insert stored procedure (you are using stored procedures, right?)
make that parameter NOT optional, and return an error if it is NULL. (You
will probably want to slightly change your form appearance and/or
validation.) But you're not going to be able to enforce this at the table
level, as far as I can tell (but maybe if you detail your reasoning it may
spawn additional thought).
A
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1189685999.863746.93220@.g4g2000hsf.googlegroups.com...
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
>|||On 13 Sep, 13:31, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This doesn't really make much sense to me, so far. What is the point of
> making the column mandatory if you're not going to update the existing rows?
> Is there some application limitation that requires a value? If so, why
> would the limitation only be relevant on new rows? Can the application not
> look at old rows? Why is it okay for an old row to be NULL and not for a
> new row? Just trying to understand the logistics.
>
My FE application is written in Access 2003. I have a form which I use
to create Purchase Orders. This form has a sub form for PO Items. Due
to a change in company procedures, I need to add four fields to the
sub form which all require user input. However, this only applies to
new POs. There is no sense in going back through five years worth of
(20,000) existing POs to make sure that all four columns conform and
have the correct data.
> If you want only new rows to contain a value, then it is fairly trivial to
> have your insert stored procedure (you are using stored procedures, right?)
> make that parameter NOT optional, and return an error if it is NULL. (You
> will probably want to slightly change your form appearance and/or
> validation.) But you're not going to be able to enforce this at the table
> level, as far as I can tell (but maybe if you detail your reasoning it may
> spawn additional thought).
>
I'm not using a stored procedure on this form, but perhaps that's the
answer.
Thanks for your help
Colin|||> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
No, but you could update them all in one shot with some token value (e.g.
N/A) and then you could apply your constraint and prevent further rows from
being un-populated.
A|||<snip>
> to a change in company procedures, I need to add four fields to the
> sub form which all require user input. However, this only applies to
> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
Before you go further, why don't you step through the process of what is
expected when someone modifies a PO created before your change (regardless
of how it is accomplished). Will your front-end somehow "know" that the PO
was created before the requirement and will correctly "adjust" its
appearance and logic to account for this not-present and not-required data?
If you have difficulty answering that question, then you are in a bit of a
cart-before-horse situation since you need to define the business logic
first.
There is an alternative that will support your stated goal. Create a
dependent table (in a 1-0/1) relationship that contains your new columns.
Your existing rows will have no associated row in this new table, while any
orders created (and, perhaps, modified) after this change will (or at least
can) have a row.|||On Sep 13, 7:19 am, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
I agree with Aaron - you most likely don't want do it. Yet it is
doable:
CREATE TABLE a(i INT)
INSERT a(i) VALUES(NULL)
GO
ALTER TABLE a WITH NOCHECK ADD CONSTRAINT a_i_notnull CHECK(i IS NOT
NULL)
-- creates OK
GO
INSERT a(i) VALUES(NULL)
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint
"a_i_notnull". The conflict occurred in database "FinancialDW", table
"dbo.a", column 'i'.
The statement has been terminated.
*/|||> I agree with Aaron - you most likely don't want do it. Yet it is
> doable:
Yes, of course. Why do I always forget NOCHECK? Probably because it's not
a very good practice for this kind of situation. :-)
A