COALESCE() vs. ISNULL()

Having been reading the differences between COALESCE() and ISNULL() functions in T-SQL. I have found many other posts talking about this in details and thought put a summary table here for quick reference check.

	COALESCE()	ISNULL()
Parameters (Syntax)	Accept multiple parameters COALESCE ( expression [ ,...n ] ) Example: SELECT COALESCE(NULL, NULL, 'foo')	Accept only two parameters ISNULL( check_expression , replacement_value ) Example: SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))
Data Type Returned	Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.	Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.
Column Selected Into	Nullable column returned	Non-nullable column returned
SQL Function Type	Standard function (recommended to use)	Non-standard function
Performance Impact	Not recommended to use in the conditions of joins In some cases where subquery is used in the expression can generate less efficient plan. E.g., SELECT COALESCE (     (SELECT a2.au_id         FROM pubs..authors a2         WHERE a2.au_id = a1.au_id),     '' ) FROM authors a1 See details at http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html	Not recommended to use in the conditions of joins

References

COALESCE()
http://msdn.microsoft.com/en-us/library/ms190349.aspx

ISNULL()
http://msdn.microsoft.com/en-us/library/ms184325.aspx

Should I use COALESCE() or ISNULL()?
http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html

Can I have optional parameters to my stored procedures?
http://sqlserver2000.databases.aspfaq.com/can-i-have-optional-parameters-to-my-stored-procedures.html

Clearing IE9 cache

Have come across an issue lately with Internet Explorer 9. There is one temporary file seems not deletable regardless of how we tried with all available functions in IE.

1. Via F12 through development tool menu option
2. Via Internet Options menu option to delete all temporary files

At the end, we had to use PowerShell command to help clear IE temporary files, which finally deleted the file for us.

get-childitem "c:\Users\USERNAME\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\*" -recurse -force | remove-item -force -recurse

However, all applications that use these temporary Internet files need to be closed at mean time. Otherwise, the command will fail.

The myth to dynamically display page header in Crystal Report

This is one of the Crystal myths I have come across.

We have two different page headers to display based on a boolean parameter. It works perfectly until we tried to export the report to Excel (Data Only) format. One of the parameter choice will cause the corresponding header not to be exported to that format. However, it works with the opposite parameter selection.

After many different tests, I found it is not to do with the parameter choice but just the position of the header section. As we have to create two page header sections to achieve this, it will always be the one at bottom section gets exported.

With some Internet search, I have eliminate the following common causes of issues to export to Excel (Data Only) format:

1. different fields width and height
2. different alignment with the Detail fields
3. uncheck Simplify page header option in the Excel(Data Only) format export options

At the end, I tried from a mew blank report just had single same field in each header. Use the same boolean parameter to show/hide one and another. It still did not work.

Concluded it is a bug and now, I have to think of a work around.

So I have tried to place these two headers together in one page header and use Supress formula on fields to do show/hide. Found if placing them in parallel, it still will not work until to place them at one line i.e., overlap each other. However, the fields exported to Excel (Data Only) are having spaces in between as the difference between these two headers.

So we had to just use one header and show and hide the extra coulmn(s) which will leave space if it is not show and hopefully, someone else can find the better solution on this.

Be Cloud-ready

It has been several years that since Cloud Computing entered into ICT world. It is an exciting change that would affect the way we work. Personally, as a technical professional, it is an exciting experience to look forward to.

However, there are many to learn and to be Cloud-ready. Lately, I have come across CloudU and a very good site to start my Cloud Computing learning journey...

Six Steps That Simplify Moving to the Cloud

http://www.itbusinessedge.com/slideshows/six-steps-that-simplify-moving-to-the-cloud.html

CloudU to learn more

http://www.rackspace.com/knowledge_center/cloudu/curriculum