T-SQL: Useful Tips

SCOPE_IDENTITY()	To retrieve identity value from the last inserted record, use SCOPE_IDENTITY() instead of @@IDENTITY.
EXISTS	To gain better performance, use EXISTS instead of IN. BONUS: SQL Server: JOIN vs IN vs EXISTS - the logical difference
SET NOCOUNT ON	Use it in a stored proc. It reduces network traffic by eliminating sending "done" messages to client.
@@ROWCOUNT	It returns number of rows affected by the last statement.
RETURN	It is usually used for returning status/error code. RETURN can be used to exit from a stored proc. Any statements that follow RETURN are not executed.
@@ERROR	It returns the error number for the last statement. Zero (0) means no errors.
ERROR_NUMBER()	The result of ERROR_NUMBER() is the same as @@ERROR. BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH -- All of these must be used inside CATCH. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

T-SQL: Variable Assignment Using SET vs. SELECT

Use SET instead of SELECT to make a variable assignment because it is ANSI standard and recommended in SQL Server doc.

References:

• SET @local_variable (Transact-SQL)
• T-SQL: SET vs SELECT when assigning variables
• Differences between SET and SELECT in SQL Server

-- You could assign variable with SELECT.
DECLARE @total_rows INT = 0;
SELECT @total_rows = COUNT(*) FROM customer;
PRINT @total_rows;

-- However, it is better to use SET.
DECLARE @total_rows INT = 0;
SET @total_rows = (SELECT COUNT(*) FROM customer); -- Parentheses around SELECT statement is required.
PRINT @total_rows;

ColdFusion: Immediate If vs. Ternary Operator

ColdFusion 9 introduces a new operator called ternary operator. Unlike iif(), ternary operator looks cleaner and less confusing.
See the snippet below. Pay attention on the quotes.

<cfparam name="condition" default="true">

<cfset var_1 = "TRUE">
<cfset var_2 = "FALSE">

<cfoutput>
 <!--- Use de() and quote will not evaluate the variable --->
 immediate if: #iif( (condition), de('var_1'), de('var_2') )#
 <br />
 <!--- Use quote 'var_1' or de(var_1) will evaluate the variable --->
 immediate if: #iif( (condition), 'var_1', 'var_2' )#
 <br />

 <!--- Use quote 'var_1' will not evaluate the variable --->
 ternary operator: #( (condition) ? 'var_1' : 'var_2' )#
 <br />
 <--- No quote var_1 will evaluate the variable --->
 ternary operator: #( (condition) ? var_1 : var_2 )#
</cfoutput>

ColdFusion: argumentCollection vs. attributeCollection

Passing arguments/attributes via collection is very handy. However, you might get an unexpected error. This error could be caused by using argumentCollection or attributeCollection incorrectly.
See the code below and read the comments. I hope that helps! :)

<!--- Define a function to accept string and return it back to the caller --->
<cffunction name="echo" returntype="string" output="false">
 <cfargument name="aString" type="string" required="false" default="" />
 
 <cfreturn toString(arguments.aString) /> 
</cffunction>

<!--- Create a structure to hold an argument that will be passed to echo() --->
<cfset args_echo = structNew() />
<cfset args_echo.aString = "Hello World!" />

<!--- Call echo() and pass the argument via argumentCollection --->
<!--- NOTE: Notice that the argument name is "argumentCollection", instead of attributeCollection.
 In other words, if it is a function/method then use argumentCollection. --->
<cfoutput>#echo(argumentCollection=args_echo)#</cfoutput>


<!--- Create a structure implicitly (require CF8 and up); and declare attribute name and its value --->
<cfset args = {var="#args_echo#", label="args"} />

<!--- Call <cfdump> and pass the attributes via attributeCollection --->
<!--- NOTE: Notice that the attribute name is "attributeCollection", instead of argumentCollection.
 In other words, if it is a tag then use attributeCollection. --->
<cfdump attributeCollection="#args#" />

Blogger: Syntax Highlighting

I use SyntaxHighlighter by Alex Gorbatchev to display source code in my blog. Here is a snippet to display ColdFusion code. Place the code in <pre> tag and replace all < character with <. That's it!

<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shAutoloader.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushColdFusion.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCss.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js"></script>
<script type="text/javascript" src="http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js"></script>

<link rel="stylesheet" type="text/css" href="http://alexgorbatchev.com/pub/sh/current/styles/shCore.css"></link>
<link rel="stylesheet" type="text/css" href="http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css"></link>

<script type="text/javascript">
    SyntaxHighlighter.config.bloggerMode = true;
    SyntaxHighlighter.all();
</script>

<pre class="brush: coldfusion">
    <cfoutput>#DateFormat(Now(), "mm/dd/yyyy")#</cfoutput>
</pre>