When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions - FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are...
SQL Server 2005 supports two forms of full-text search, FREETEXT and CONTAINS. CONTAINS is used to search for rows that contain words, phrases, partial phrases, words with the same stem, proximity searches, synonyms (using a thesaurus lookup), and more. Here is a simple example: SELECT note_id,...
FREETEXT provides a simple mechanism by which to perform SQL Server 2005 full-text searches, matching by meaning as opposed to exact text match. Here is a simple example: SELECT * FROM my_table WHERE FREETEXT(column1, 'rabbit food'); FREETEXT(column1, 'rabbit food') means perform a FREETEXT lookup...
SQL Server 2005 features an integrated full-text search engine, which several people have e-mailed me to ask about recently. (ColdFusion users can use to perform queries using SQL Server full-text searches). If you want to play with SQL Server 2005 full-text search capabilities, you need to do the...
I just spent way too much time debugging some SQL Server code, only to discover that my results did not match my SQL statements because of a trigger that I was unaware of. How did I find this trigger? With the help of a wonderful built in stored procedure named SP_HELPTRIGGER. The following lists...
We all rely on writing dynamic SQL in ColdFusion, but what if you need to write dynamic SQL on the server, perhaps in a stored procedure? While I'd not recommend overusing this technique (there are performance penalties to this one), if you need server-side dynamic SQL, the EXEC() function can...
It's not that often that I need a debugger for my SQL code, but every once in a while it's an absolute must, and today is one of those days. I have a several hundred line stored procedure which works with multiple view and table variables, dozens of variables, and three levels of nested cursors....
I just spent some time helping a very irate customer. You know, the "ColdFusion sucks" "we're going to dump CF" "how can you sell this ****" brand of irate. The cause of all of this anger and frustration was very serious ColdFusion performance issues. And I am sure the individual has been under a...
SQL searches are usually case-insensitive, because most databases are set up by default for case-insensitive searching. Case sensitivity is defined by collation sequences, rules which define how strings are compared taking into account the specifics of individual languages (case, special...
Temporary tables have long been an integral part of any complex SQL processing. If you have a long script or stored procedure, one which needs to extract or manipulate data using multiple queries and related statements, then being able to save results into temporary tables can dramatically improve...