Codito Ergo Sum

I'm a firm believer that you should always let your database server return data as pure and ready for display as possible. While coldfusion *can* take your entire dataset and break it into pages, and Query of Queries has some decent functionality to mix things up, I find it is orders of magnitude faster to leave that work to your database server. With the following snippets your mileage may vary, as these are primarily done in MS SQL of some variety, but at least this may show you some tricks you didn't know!

Joining between 2 different databases on the same server - I cannot tell you how many times I have seen someone select * from an entire table from datasourceA then select * from datasourceB, then mix them up using a Query of Queries. UGH!! You can perform joins between different databases (on the same SQL server) simply by referring to the full name of the table. Using the [] around the names also has the added benefit of allowing you to use SQL reserved words if necessary (don't name tables this way, but sometimes you are stuck with old code)

SELECT
A.Columnname, B.Columnname
FROM
[databaseName].[dbo].[tableName] A INNER JOIN [databaseNameB].[dbo].[newTableName] B
ON A.columnName = B.ColumnName

Using a sub-select - sub-selects are a method of getting a single bit of data from any table on the system, and taking that result as a column in your result set. This is often great for getting a total or count of orders from another table. In this basic example, my result set would have all the customers from my Customers table, and each one would have a column showing their total order count, and their total invoiced amount.

SELECT
A.CustomerName,
(SELECT Count(*) FROM Orders WHERE CustomerName = A.CustomerName) as TotalOrders,
(SELECT SUM(BilledAmount) FROM Orders WHERE CustomerName = A.CustomerName) as TotalInvoiced
FROM
Customers

Pagination - While there are many methods of doing SQL pagination, this one is SQL 2005 specific and uses some tricks that I think are pretty darn cool. This will accept a few parameters such as the number of items per page, and the page requested, and will return all upcoming 'events' that have a date after today. Also note the CAST done on the date, this is because the value contains a DateTime (including a time value), and I DO want to include everything from today in the morning, regardless of what time it is currently. This basically zero's out the time value and will return everything today and after.

<cfquery name=<span class='cc_value'>"q"</span> datasource=<span class='cc_value'>"#variables.config.dsn#"</span>>
DECLARE
   @rowsPerPage int,
   @pageNum int,
   @startRow int,
   @endRow int

SET @rowsPerPage = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_numeric"</span> value=<span class='cc_value'>"#variables.config.itemsPerPage#"</span> />
SET @pageNum = <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_numeric"</span> value=<span class='cc_value'>"#arguments.page#"</span> />

SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
SET @endRow = @startRow + @rowsPerPage -1

SELECT * FROM (
   SELECT
      A.ID,
      A.Title,
      A.StartDate,
      row_number() OVER (ORDER BY a.startDate) as resultNum
   FROM
      EventTable A
   WHERE
      active = 1
      AND CAST(FLOOR(CAST( endDate AS float)) AS datetime) <= <cfqueryparam cfsqltype=<span class='cc_value'>"cf_sql_date"</span> value=<span class='cc_value'>"#dateFormat(today, 'YYYY-MM-DD')#"</span>>
      ) as results
WHERE
   resultNum BETWEEN @startRow AND @endRow
ORDER BY
   startdate
</cfquery>

I will post more tricks as I come across them!