Nick Doyle (8/22/2013)
I don't agree that it encourages sloppy coding. You need to know how a language works and be a decent coder to not be sloppy. I don't see how providing more user-friendly functions contributes to sloppy coding. It makes non-sloppy coders' lives easier.This is much easier/quicker than coalesce or:
ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + CONVERT(VARCHAR(3), Age)
I'm definitely happy I no longer have to explicitly convert int to string. 😀
Having a concat function which requires string arguments so that explicit conversion is not needed in the cases where implicit conversion is possible is indeed a nice feature.
However, if you want a to write code in accordance with the relational model, you will also want to ensure that NULL can not be interpreted as zero or as a zero length string or as anything else other than "this data is not available, the value is unknown" and it is, as Toreador says, sloppy coding to use NULL for anything else; if there is some data for which absence should be interpreted as a zero length string, then declare it as such: in such a case the table should be declared as
CREATE TABLE #temp (
emp_name nvarchar(20) NOT NULL DEFAULT(''),
emp_middlename nvarchar(20) NOT NULL DEFAULT(''),
emp_lastname nvarchar(20) NOT NULL DEFAULT(''),
age int NOT NULL);
This avoids the misuse of NULL to represent a known value. The introduction of this concat function with its stupid treatment of NULL is just a sop to those who were horrified that SQL Server is about to switch to standard treatment of NULL and either were incapable of thinking straight about how to upgrade their sloppily coded legacy software to cope or too set in their ways and wanted to continue misusing NULL in a sloppy and error prone manner for new development. I firmly believe that MS should not have thrown them that sop, we would have suffered less from buggy software in future if they hadn't.
edit: I forgot to say "good question, Ron".
Tom