Try fast search NHibernate

04 July 2009

The art to invent: MsSQL’s artists

I’m sorry if this post will sound something heavy but I’m a little bit tired.

A little review of pagination:

in Firebird: SELECT FIRST number_of_rows SKIP offset_record rest-of-sql-statement

in MySQL: SELECT rest-of-sql-statement LIMIT offset_record, number_of_rows

in PostGre: SELECT rest-of-sql-statement LIMIT number_of_rows OFFSET offset_record

in MsSQL (even MsSQL2008) the pagination was thought by an artist of abstract art of the 19th century (with all my respect for abstract artists).

Now we have two new inventions : the function CONTAINS and FREETEXT.

Another time our “artist” in action.

If you seen the syntax, which is the natural return value of both functions ? a boolean ? a bit ? no man, that it is too much easy, a boolean is something every developer have in mind and for our “artist” is too much intuitive and easy. The solution can’t be so intuitive because the concept is opposed to “abstract art”. What the developer need is something more complicated and obscure.

So, you can do something like this:

SELECT rest-of-sql-statement WHERE CONTAINS(…, …)

SELECT rest-of-sql-statement WHERE FREETEXT (…)

SELECT rest-of-sql-statement WHERE CONTAINS(…, …) AND UnitPrice > 3.00

but you can’t do something

SELECT rest-of-sql-statement WHERE CONTAINS(…, …) = :pShouldContain

Which is the type of return-value of the two functions CONTAINS and FREETEXT ?

The definition of this kind of things, given by my father, is : The art of transforming an easy thing in a difficult through an unnecessary procedure.


  1. It is really heavy. So heavy, that i actually did not grasp the point of what is wrong with pagination and what are the returns values (isn't it boolean?)

  2. Probably you don't know how you should write a paginate query in MsSQL2005. Try to write a normal SQL with an order-by and then paginate it.
    About the two function, as I said, they aren't returning boolean.

  3. It seems that the gay responsible for this function was in his own world..

    Contains( column, ' "thing" or "other thing" ')

    should be:

    Contains (column, 'thing') or contains(column, 'otherthing')

    For sure he was trying to invent "delegates" under tsql.

  4. (a typo in my comment without any intention: guy).

  5. an ugly workaround would be
    select name from mytable
    (case when contains(name,'something') then '1'
    else '0' end)='1'