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.