George Poulose's Query Tools have long been a core part of my SQL tools and utilities collection. And George just let me know that he has released new Windows 64bit versions of his ODBC and ODO tools.

SQLite is a vital core component of newly released AIR, providing a local SQL data store for your desktop AIR applications. Dave McAllister has announced that Adobe has joined the SQLite Consortium, supporting the continued growth and improvements in SQLite.

Amazon.com has created a feature page highlighting the 3 new ColdFusion Web Application Construction Kit books (as well as SQL books that may be of interest to ColdFusion developers).

The Microsoft bookstore (on the Microsoft campus in Redmond, WA) has only sold Microsoft Press books to date. But, starting next week they will be selling select books published by other publishers. And apparently my Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes is one that they have selected to sell. So, any of you working for (or visiting) Microsoft, feel free to pick up a copy! :-)

MySQL has released a beta of a new version of their Java driver. As per this post, MySQL Connector/J 5.1.2 is a Type-IV pure-Java JDBC driver that is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon alpha release. And this new driver contains code that automatically detects ColdFusion and then adjusts settings to optimize performance for ColdFusion use. Thank to Tom Jordahl for bringing this one to my attention.

Pinalkumar Dave is a DBA with extensive SQL Server (and ColdFusion) experience. I just stumbled upon his blog SQL Authority (via a link in a comment on my own blog) and am more than impressed by some of his SQL Server related posts. If you use SQL Server, then this is one blog you should add to your regular reading list.

After lots of rather painful delays, my new book Sams Teach Yourself SQL Server T-SQL in 10 Minutes is ready to ship. This book (which is the SQL Server version of my MySQL Crash Course) is based on my best-selling Sams Teach Yourself SQL in 10 Minutes and goes in to far more detail than the generic SQL book, and even includes coverage of new SQL Server 2005 functionality. Details, and a chapter listing, can be found on the book page.

Like many DBMSs, Apache Derby (included with ColdFusion 8) supports identity fields - fields that auto-increment each time a row is add. These are commonly used for primary key values, as the DBMS itself ensures that these values are unique and never reused.

One problem with identity fields is that sometimes you may need to insert a row providing an explicit value to be used, essentially overriding identity functionality. Some DBMSs (like SQL Server) allow you to turn off identity processing with an explicit directive while a row is inserted and then turn it on again, and numbering automatically continues from the new highest value. Other DBMSs (like MySQL) allow you to simply insert values with specific values and, if present, these are used instead of auto generated values.

Derby also supports identity fields. These can be defined as ALWAYS in which case Derby always generates the value (which can never be manually specified), or BY DEFAULT in which case identity fields are generated only if an explicit value is not provided.

It's a rather nice implementation. But, it does not work as you'd expect. You can indeed specify an explicit value if BY DEFAULT is used, and your value will be used. But Derby does not seem to pay attention to explicitly provided values and does not update the internal counters accordingly, so when you next insert a row without an explicit value it may generate the exact same value as the one you specified. And if that column is a primary key, well, obviously the second INSERT is going to fail.

There is a workaround. When the table is created you may specify an optional START WITH value. So, if you need to load the table with 25 rows you can set START WITH to 26, and that will be the starting point for generated identity values. I guess you could also START WITH some really high number, and reserve the lower values for when you needed to explicitly provide a value.

Still, this is a hack, and it makes BY DEFAULT rather useless, which is a shame.

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 ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves.

Here is an example:

This example performs a FREETEXT type search. Instead of filtering using the WHERE clause, the FREETEXTTABLE() function is used and given a search pattern instructing the full-text engine to match any rows that contain words meaning rabbit and food. FREETEXTTABLE() returns a table which is given an alias of "f" (so as to be able to refer to it in column selection and the join), this table contains a column named "key" which will contain the primary key value of the table that was indexed (productnotes in this example), and "rank" which is the rank value assigned. And finally, results here are sorted by rank descending, as the higher the rank the greater the match.

It is also possible to assign weight values to search patterns and words. The rankings assigned in the example used here assumed that all words were equally important and relevant. If this is not the case, and some words are more important than others, then the ISABOUT() function can be used to assign relative weights, and the full-text search engine will then use these values when determining rankings.

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:

WHERE CONTAINS(note_text, 'handsaw') means find the word handsaw in column note_text.

CONTAINS also supports the use of wildcards:

'"anvil*"' means match any word that starts with anvil. Note that unlike LIKE, full-text searching uses * as the wildcard character (instead of %). Wildcards may be used at the beginning or end of a string. Also, when passing simple text to CONTAINS then that text is enclosed within single quotes. When passing wildcards each search phrase must be enclosed within double quotes inside those outer single quotes. Failing to do this will likely cause your searches to return no matches.

CONTAINS also supports Boolean operators AND, OR, and NOT. Here are a couple of examples:

When searching through extremely long text there is a greater likelihood of matches being found if search terms are near each other in the saved data. A simple AND search matches terms anywhere in the text, but NEAR can be used to instruct the full-text search engine to only match terms when they are close together. Here is an example:

'detonate NEAR quickly' means match only rows that contain the words detonate and quickly near each other.

Sometimes you may want to match a word that is part of the same family (based on the same stem). For example, if you were searching for "life" you'd also want to match "lives". Obviously, a wildcard of life* could not help here, and using li* would likely match too many false positives. This is where inflectional matching helps. Here is an example:

'FORMSOF(INFLECTIONAL, life)' instructs the full-text engine to look for any words that share the same stem as the specified word, in this case "life".

FORMSOF() also supports THESAURUS searches, where words can match synonyms. To use this functionality you must first populate an XML thesaurus file with words and their synonyms.

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:

FREETEXT(column1, 'rabbit food') means perform a FREETEXT lookup on column column1 looking for anything that could mean rabbit food (but not necessarily those two exact words, and not necessarily as a phrase).

You can also search across all columns indexed for full-text search by using FREETEXT(*, 'search text').

If double quotes surround a search term then that exact phrase is matched, not the meaning.

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 <cfquery> 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 following:

1) Once a database has been created, support for full-text must be enabled before any full-text operations can be performed. To enable full-text support the sp_fulltext_database stored procedure is used. This stored procedure updates the currently selected database, so be sure to USE the correct database before issuing this statement:

If you are using SQL Server Management Studio and use the interactive New Database dialog to create your database, you can check the "Use full-text indexing" checkbox, which causes the above mentioned stored procedure to be automatically executed.

2) SQL Server stores full-text data in a catalog (a file that needs to be created). A single catalog can be used for multiple tables and indexes. If a catalog does not already exist, create one using CREATE FULLTEXT CATALOG:

This creates a catalog named my_catalog in the default catalog location. To specify the actual file location the IN PATH attribute can be specified.

3) Once a catalog has been created you can define the actual full-text indexes for each table containing columns that you want searchable. Indexes are created using CREATE FULLTEXT INDEX like this:

When creating a full-text index you specify the table and column to be indexed as table(column). More than one column may be indexed if needed, to do this simply specify the column names (comma delimited). The key with which to uniquely identify rows is required, and so KEY INDEX is used to provide the name of the table's primary key. And finally, the ON clause specifies the catalog to be used to store full-text data, and here the just created catalog is used (unless a default catalog has been defined).

Once set up, you'll be ready to use the CONTAINS and FREETEXT functions in your WHERE clauses to perform full-text searching.

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 all triggers associated with table myTable, along with the events they are associated with, and whether they are AFTER or INSTEAD OF:

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 help:

It gets a little trickier if the WHERE clause needs to compare string values, then you need quotes around the value, and those quotes need to be escaped:

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. And I am trying to find a sporadic logic problem. Simple stored procedure debugging can use embedded PRINT statements, but this one is more complex, and thus the need for a debugger.

But SQL Server 2005 removed the built-in debugger. Now you need to use Visual Studio .NET to debug SQL Server stored procedures. No problem, I happen to have a copy (which I use for occasional VB.NET and C# code). And sure enough, you can connect to SQL Server, open (and even edit) a stored procedure, set breakpoints, step through code, watch variables and expressions, everything you'd expect ... unless what you want to inspect is anything but a simple variable. As already said, I am working with lots of table variables (actually, as I am working with lots of values at any given time just about everything I need to inspect is a table variable). And when you inspect those it displays "Table". Gee, how useful!

The truth is that the old debugger had the same limitation, no visibility into temporary tables or table variables. But I was hoping that now that the debugger is actually Visual Studio that this limitation would be lifted, but nope.

If anyone knows of a solution or an ingenious workaround, I'd love to hear it. In the meantime, it's back to embedded PRINT statements. Bummer.

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 lot of pressure and so the ramblings are somewhat understandable.

But, at the end of the day, as is so often the case, the bottleneck was in database access and query processing. No, there was nothing wrong with the DBMS. And no, there was nothing wrong with the SQL used. And no, the connection and data source settings were not the problem.

So what was the problem? Simply, ColdFusion was being made to do the DBMS' job! I found dozens of queries that were returning data that was being used solely to feed other queries. I saw queries being returned and then filtered client-side, within ColdFusion. I even saw queries (some that were quite long running) that were never being used once executed (leftover code from previous edits, I assume). And more.

I've been saying this for years, but, let's go through this again. ColdFusion is NOT a DBMS. Whatever database back-end you are using, chances are that it is a big powerful application that is designed to do one thing and do it well, manage and manipulate data. It makes absolutely no sense to give your DBMS less work to do while making ColdFusion work hard doing what the DBMS is supposed to do, what it is designed to do, what it is optimized to do.

So, a few rules:

• Don't ever retrieve data you don't need.
• Don't ever retrieve too much data, only to filter the results within ColdFusion (via querying a query, or excluding data while looping).
• Don't ever retrieve data if that data is only going to be used to drive additional queries. If you have a query, and then are using the results (perhaps in a <cfloop>, or via a ValueList()) in subsequent queries, then you need to find a way to consolidate those queries and do the work on the DBMS. And yes, that may mean writing JOINs or using stored procedures.
• Don't ever perform calculations and aggregations within ColdFusion, unless the data being used is already retrieved for some other purpose. Yes, the SQL aggregate functions (and having to use GROUP BY and figuring out how to make that work while using WHERE or JOINs) can be a little tricky. But do it anyway.
• Don't ever underestimate the important of caching query results. If you use the data frequently, and it is not changing as frequently as it is used, then cache it!
• There's more, but you get the idea.

Keep the following in mind:

• ColdFusion is never going to be able to query/sort/filter/count/sum/average/etc. as fast as your DBMS will. That is not what ColdFusion is optimized to do, and it is exactly what your DBMS is optimized to do.
• Sending unnecessary data from your DBMS to ColdFusion creates unnecessary network traffic, puts unnecessary load on the ColdFusion server hardware, unnecessarily increases server memory load, and unnecessarily slows down your and other requests.
• More often that not, your ColdFusion box is going to be under heavy load while your DBMS box sits with cycles to spare. It makes no sense to not leverage that power and potential.
• And as a side benefit, by moving data processing and manipulation back where it belongs, on the DBMS, you significantly increase the likelihood that you'll be able to reuse that work and effort.

And a couple of side notes:

• None of these comments are ColdFusion specific, and are just as applicable to applications developed in PHP, ASP, JSP, etc.
• You MUST learn how to use your DBMS. In the situation that triggered this post, one particularly problematic query was taking over 10 seconds to execute. Adding an index to the table used dropped that down to a couple of milliseconds!
• "My DBA does not let me write stored procedures" is not a valid argument, and is all the justification you need to get a new DBMS or a new DBA!

Considering how data centric ColdFusion applications tend to be, all of this is critical!

Bottom line, you have a DBMS for a reason, use it!

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 characters, and more). Collation sequences can be defined at the database server level, at the specific database level, at the table level, and also at the individual column level. Collation sequences can also be overridden, so if a collation sequence is defined for a database or table, a specific column can have an alternate collation sequence.

Why do I bring this up? An app I am working on needs to use LIKE to perform wildcard searches against a table column, but those searches must be case-sensitive (so that %foo% will not match FooBar).

The simple solution would have been to modify the table so that the column used in the search would use a case-insensitive collation sequence. But that would have messed up other searches that need to remain case-insensitive.

So what to do? Here are a couple of solutions.

Collation sequences may be defined inline, right in the WHERE clause, as seen here:

In this example, the default collation sequence was SQL_Latin1_General_CP1_CI_AS (the CI indicates case-insensitive), but in the above WHERE clause an inline COLLATE statement is specified so that collation sequence SQL_Latin1_General_CP1_CS_AS (the CS indicates case-sensitive) is used in this search.

Another solution, better suited for situations where both case-sensitive and case-insensitive searches are frequently needed, is to define the table with one sequence (whichever sequence will be used more frequently), and a VIEW based on that table with another. Here is an example, setting an explicit collating sequence for one column:

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 performance (as well as readability and management).

SQL Server has supported temporary table use for any years, I first used them in SQL Server 6.5 (which were released in the mid 90s). Temporary tables are created and used just like any other tables, and then can be either local or global. Here is a simple code snippet that creates two temporary tables, one local and one global:

The two code snippets look similar, the only difference is that one table is named #MyTempTable and the other is named ##MyTempTable. In SQL Server, a single # is used to refer to a local temporary table, and double # is used to refer to a global temporary table.

Once created, these tables can be used like any other tables. You can SELECT, INSERT, UPDATE and DELETE, you can use them in JOINs and subqueries, and more. You just have to be careful to use # (or ##) as part of the table name.

Oh, and you can also use SELECT INTO to create and populate a temporary table in one step.

You can also DROP temporary tables. Although SQL Server can automatically drop the temporary tables for you, too. Temporary tables are automatically dropped when the session that created them terminates (goes out of scope).

SQL Server 2000 added support for an alternative to temporary tables, the table variable. Table variables are similar to temporary tables in that they are intended for temporarily working with data. Here is how one is created:

DECLARE is used to declare variables, and the variable is prefixed by @ (instead of #). In SQL Server, variables are always prefixed by @, and table variables are no different.

Once created, a table variable can also be used like a regular table. You can SELECT, you can INSERT, UPDATE, and DELETE, you can use the table variable in JOINs and subqueries, and more. And the table variable has some important benefits over temporary tables:

• Table variable access can be considerably faster than temporary table access.
• There is no need to DROP tables or perform cleanup, when the variable goes out of scope (the statement or stored procedure ends) the table variable is cleaned up just like any other variable.
• Table variables can be safer in a multi-user environment than temporary tables.

So, there are definitely benefits to using table variables. But is there any reason not to use them? Well, maybe. Table variables have some important restrictions that may impact whether or not you can use them:

• Tables variables are not available pre SQL Server 2000.
• Table variables must always be accessed using an alias (except in the FROM clause of course). If you want to refer to the table variable in a JOIN, for example, you must refer to is by its alias.
• Table variables do not support SELECT *, you must always explicitly list the columns you want.
• Table variables cannot be explicitly DROPped.
• Once created, table variable definitions cannot be changed.
• Tables variable do not support SELECT INTO syntax.
• There are fewer index options available for table variables, and this can impact performance.

This Microsoft FAQ explains some of the differences between temporary tables and table variables.

So yes, table variables do have some limitations that temporary tables do not. But, having said that, I was working on a rather complex stored procedure today, one that made extensive use of temporary tables. And in this project, replacing the temporary tables with table variables resulted in a very significant performance gain.

The bottom line is that both temporary tables are table variables are invaluable tools in your SQL Server toolbox, and you really should become familiar with both.

Note: Other DBMSs (including both Oracle and MySQL) also support temporary tables and table variables. Unfortunately, the syntax used to work with these tables (as well as the restrictions and differences between them) tends to differ significantly between DBMSs implementations.

I just discovered a new SELECT clause that has been added to T-SQL in SQL Server 2005. TABLESAMPLE can be used to return a sampling of rows (either a fixed number or a specified percent of the table rows). Here is how it is used: It is worth noting that you may not get the exact number of rows that you'd expect. Sampling occurs by table page, and the number of rows in a page can vary.

I needed to do a fresh install of SQL Server 2005 Express yesterday, and no matter what I tried the installation kept failing with really strange errors about invalid or missing MSI files. The solution? Manually extract the installer files to a temporary folder and then run the extracted setup, that fixed it. To extract installer files execute the installer on the command line with arguments /x:path (for example, /x:c:\sqltemp).

Mike Hillyer is a member of the MySQL AB documentation team. He just posted a short review on my MySQL Crash Course, stating that it is "an excellent book for the beginning users just getting started with MySQL and perhaps SQL in general." Thanks, Mike!

TechBookReport has posted a review of my MySQL Crash Course, and concluded that "If you want to learn SQL and aim to implement your database using MySQL then this is definitely the book to use".

Amazon.com has been paying for Google AdWords for my name for quite some time now. And now they are also paying for the AdWords "MySQL Crash Course" (and linking to that search term which find my new MySQL book). Cool!

Ray Camden has posted comments on my newly released MSQL Crash Course. Thanks, Ray.

Pete Freitag has posted comments on my newly released MSQL Crash Course. Thanks, Pete.