If you are processing or comparing numbers, make sure that they really are just numbers - with no leading 0's or spaces. One simple way to do this is to pass the value to Val() which returns the numeric value at the start of a string. (Applies to: ColdFusion All)
There is often more than one way to perform SQL operations, particularly more complex operations. And there is rarely a definitive right or wrong way. Performance can be affected by the type of operation, the DBMS being used, the amount of data in the tables, whether or not indexes and keys are...
Want to delete all data in a table? You can use DELETE FROM TABLE, but that is slow because changes are logged. A faster solution is to issue a TRUNCATE TABLE command which is faster because no logging occurs (and if really intent to delete all the data in a table, logging is probably not that...
Good DBMS's support the use of triggers (blocks of SQL code that execute when an event occurs), and constraints (rules associated with datatypes or columns). Both can (and should) be used to enforce data integrity and consistency, but as a rule, constraints execute quicker than triggers. (Applies...
Need to export or import data from one database table to another? Two SQL statements can be used to simplify these operations. Use SELECT INTO to export data, and use INSER SELECT to import data. (Applies to: ColdFusion All)
Confused about the difference between the SQL WHERE and HAVING clauses? Here's one way to remember the difference. WHERE is used to filter rows, HAVING is used to filter groups (and is only used when GROUP BY is used). When grouping data, WHERE filters data before it is grouped, and HAVING filters...
The SQL DISTINCT keyword forces SELECT operations to be performed on unique rows (ignoring duplicate values). If you are counting rows (using the COUNT() aggregate function) then DISTINCT may only be used if a column name is specified. DISTINT may not be used with COUNT(*). (Applies to: ColdFusion...
The SQL COUNT() aggregate function should be used to count the number of rows that match a condition (something that should never be done directly in CFML itself). But when counting, does COUNT() include rows with NULL values or not? The answer is - it depends. If you specify COUNT(*) then all rows...
provides an excellent mechanism with which to set variables, perform conditional processing, and implement loops. But unlike straight CFML, you cannot simply display expressions by typing them out. Instead, you must use the WriteOutput() function which takes the expression as a parameter. (Applies...
Need secure one-way string encryption? Try the Hash() function which takes a string and returns a 32 byte hexadecimal string, converted using an algorithm called MD5. You'll not be able to decode the string, but you'll be able to compare it to other encoded copies (which makes it useful for safely...