A CF datasource is basically a connection pool that will hand you a database connection preconfigured with the datasource’s parameters (username, password, default database, URL, etc.) whenever you ask for it.
There’s no guarantee that consecutive cfquery tags with the same datasource will get the same connection. This matters a lot if you’re using temporary tables, which are specific to a connection. For example, with the following SQL Server code you may get a “table not found” error on the second query:
select * from users into ##tempusers select * from ##tempusers
This may or may not work. It’s luck of the draw, and it’s dependant on load.
Temporary tables are dropped when the connection is closed. As you can configure ColdFusion to keep connections open, you may find yourself inheriting connections with some other request’s dirty laundry flapping around. Not a good look.
This picture changes if you are using <cftransaction>. Normally *, transactions are intrinsically tied to database connections. This is a characteristic of relational databases, not specific to ColdFusion or JDBC. For a <cftransaction> block to work at all, ColdFusion has to guarantee that you get the same connection for all queries within that block. You can safely use temporary tables within a transaction – just make sure you drop them when you’ve finished.
This isn’t really a post about temporary tables. They’re just a good way to demonstrate what’s going on with datasources. The relationship between datasources, connections and transactions has some important consequences for how and when you can use multiple datasources within a page.