Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

CONCAT 1 Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 7:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Carlo Romagnano (8/22/2013)

In this case CONCAT also is faster than ISNULL(FirstName + ' ', '') + ISNULL(MiddleName + ' ', '') + ISNULL(LastName + ' ', '') + ISNULL(CONVERT(VARCHAR(3), Age),'')
Because you call ONE function and 4 parameters vs 4 functions with 8 parameters and ONE CAST.


That's why i love it.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1487256
Posted Thursday, August 22, 2013 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 7,815, Visits: 9,564
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
Post #1487294
Posted Thursday, August 22, 2013 8:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 17,854, Visits: 15,803
L' Eomot Inversé (8/22/2013)
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".


+1




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1487328
Posted Thursday, August 22, 2013 9:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 1,360, Visits: 495
Hugo Kornelis (8/22/2013)
palotaiarpad (8/22/2013)
BOL: However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL for the current session.
The right answer is: it depends.

Maybe you should specify WHERE in Books Online you found that quote. I am pretty sure that it's on a page that does not describe the CONCAT() function, but other methods of string concatenation. CONCAT() is not affected by this setting.



You are right!
Wrong page found. It was the concat operator: +
Post #1487337
Posted Thursday, August 22, 2013 10:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
Koen Verbeeck (8/22/2013)
2 points for this easy question?

Thanks Ron, always nice to have SQL 2012 questions.


Wow missed that... it's 2 points?

Good question, thanks Ron!




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1487400
Posted Thursday, August 22, 2013 10:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 7:09 AM
Points: 157, Visits: 163
L' Eomot Inversé (8/22/2013)
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".


I agree with most of what you're saying, but the world isn't ideal.

1. Many databases are in existence with sloppy code and misuse of null
2. People still have to maintain these

This function makes it easier to work with such databases in many cases, such as ad-hoc querying / analysis where you wont have to take as much time concatenating fields that may be null. It takes away some of the penalty you face due to sloppy coding, even for those who aren't responsible for the sloppy coding to begin with. I don't think this function equates to encouraging sloppy coding, but mitigating it's effects.

I do think it's good that MS is going to the standard and deprecating CONCAT_NULL_YIELDS_NULL OFF.
Post #1487410
Posted Thursday, August 22, 2013 11:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 2,123, Visits: 2,128
I've been building databases since I started working with PFS File in the 1980's.

It seems like there is a literal meaning for NULL, which is "unknown", and a practical meaning, which is "data is not present in this field".

If I declare a CreateDate field as: [CreateDate] [datetime] NULL

When I select from that table and see NULL in some of the columns for the CreateDate field, I don't start thinking "Gee, I wonder what's in those fields because it's unknown". I know perfectly well that there is no data present because I allowed the record to be saved without requiring data in that field.
Post #1487432
Posted Thursday, August 22, 2013 12:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:18 PM
Points: 6,056, Visits: 8,342
Dave62 (8/22/2013)
I've been building databases since I started working with PFS File in the 1980's.

It seems like there is a literal meaning for NULL, which is "unknown", and a practical meaning, which is "data is not present in this field".

This is actually not correct.

The literal meaning of NULL is not "unknown", it is "data is not present in this field". Or, to quote the definitions section of the ANSI standard for SQL:
"3.1.1.12 null value
special value that is used to indicate the absence of any data value"
(Older versions of the standard used a slightly different wording: "r) null value (null): A special value, or mark, that is used to indicate the absence of any data value". I liked that wording better, because it presents "mark" as an alternative to "value". I don't like how any version of the ANSI standard insists on using the term "null value" instead of just "null" or "null mark", since by its very own definition, NULL denotes the absence of a data value).

This distinction is very relevant. If you have a table with product prices, including columns ValidFrom and ValidTo, a NULL mark in the ValidTo column does not mean that the end date of that price is unknown. It means it is not applicable, because the price is still valid. (Okay, you could argue that it is not yet known).
If you have a table with customers and they can be either legal bodies or natural persons, then every row will have a NULL in either the Birthdate column or the FoundingDate column. That does not mean those values are unknown. The birthdate of Microsoft Inc. is unknown because it is not applicable - companies have no birthdates. Likewise, the FoundingDate of Hugo Kornelis is NULL, not because it's unknown but because I was born, not founded. (Ohh, this is SO going to attract witty remarks...)
Finally, in the table that represents my friends, the birthdate for Jane is NULL - and in this case, it is because it IS indeed unknown. I am pretty sure that Jane has been born, and that must have happened on some date - sho just close not to tell me her age, so I am stuck with not knowing her birthdate,

For each of these examples, when you look at the data in the table, you know what the NULL represents - because you know the context. But taken out of the context, just by itself, a NULL can mean any of those things, and a whole bunch of other stuff. And unless you like unwarranted speculation, you best steer clear of all those possible interpretations and stick to the one thing you do know - that NULL represents the absence of any data value.


Note that the above argument, about different things that can be represented by NULL, has been used in a famous argument ("much ado about nothing") between Date and Codd. Date, well known for his aversion of NULLs, used this argument to convince Codd that there should be not one but at least two types of NULLs, and when Codd admitted to that Date went on to prove that this resulted in the need to upgrade three-valued logic to four-valued logic, with all the accompanying consequences. There have then also been publications that went on to expose even more types of NULLs - I believe one even went on to 17 types! All this was intended to, eventually, show that avoiding NULL is the only sensible way to do.

But what Codd apparently never got (and I'm not sure about Date) is that the whole argument is based on a fallacy. To show this, let's replace NULL with 42. When presented without context, you can make all kind of assumptiions about this number. It can represent all kinds of stuff. Maybe it's a counter (42 children in a school bus). Or a measurement of elapsed time (42 minutes spent on typing this reply - okay, I'll admit that it's actually less). It could be just a meaningless number chosen to represent something else (product code 14 - for a small package of grey staples). Or it could be the answer to a very important question. So do we need to implement different kinds of 42 in the database, just to be able to distinguish all these possible interpretations? No, of course not! When you look at 42 in its context, you will know the meaning from that same context. If you see 42 in the ProductNumber column of your OrderLine table, you know that it represents that box of staples, not a time measurement. And when you then see that same number 42 in the NumberOrdered column of the same table, you know that it's a counter of the number of those boxes some customer is ordering. In the context, the meaning of any "42", like the meaning of any "NULL" is clear. Without context, the only safe thing is to reduce it to the minimum guaranteed meaning - for 42, that is "the decimal number exactly between 41 and 43"; for NULL, that is "the absence of any data value".

But what if we have a table where a NULL can represent multiple things? For instance, the customers table can have NULL in the birthdate column for Microsoft Inc (because that customer was founded, not born) as well as for Ms Davies (because she didn't want to disclose her age) - surely, that would be a great example for why Date was right and we do need multiple types of NULL, right? Again, no. Wrong. And again, I'll use 42 to prove it. Let's imagine a table with data about clothing - women's blouses and sweaters to be precise. One of the columns is called "Size", and I see the value "42" in one of the rows. What does it tell me? Yup, size 42 - but there are three different systems for sizes of women's blouses and sweaters (EU, UK, and US); all of them include a size "42" - and all of them represent a different size. (See http://en.wikipedia.org/wiki/Clothing_sizes). Now there are two possibilities. Either the company using the data knows what they mean with this 42. Maybe because they are strictly UK based and hence use only UK clothing sizes. In that case, it is clear what this 42 means. Just as the NULL in the original example, where only companies could have a NULL birthdate. And then there is the other possibility - that the company does use different size systems interchangably. Now the value "42" in this column is suddenly a lot less informative. like the NULL in the birthdate column that was used for both Microsoft and Ms Davies. Problem? Well ... maybe. It depends.
If the company using this table is a transport company, and the only reason for storing this information is to have a double check that the information on the box that is delivered matches the information on the delivery report, they are still good. They don;t care about the actual size of the blouse that's in the box, they just want to make sure thaht Ms Davies gets the correct package. So for this case, where the distinction between size 42 (EU) or size 42 (US) is irrelevant for the owner of the data, just "42" will still do. The same might be the case for the NULL (n/a) or NULL (unknown) example - neither customer will get a birthday present, and that's the only thing I use the column for.
This leaves us with just one possible case. The company DOES care about the actual size of the blouse. They do NOT want to recommend a size 42 (EU) blouse to a customer who ordered a size 42 (US) blouse. So ... did we now finally find a case for needing several different types of 42 in the database? No ... we still didn't. We might want to feel a deep desire to store 42 (EU) or 42 (US) in the same column, and we might want to do that with two different types of 42 - but if we did, we would be violating First Normal Form. One of the requirements of this most basic normal form is that the domain of each attribute has to be atomic. A domain that includes both 42 (US) and 42 (EU) is not atomic. The attribute should be split. Or, in database terms, we should use two columns instead of one - a column to represent the size of a blouse (42), and a second column to represent the size system used for this particular blouse (EU).
Cycling back to NULL - if the company owning the data has an interest in knowing why the birthdate for a specific customer is NULL (because, apparently, they want the response of their system to an unknown birthdate to be different from the response to a not applicable birthdate), they should indeed ensure that this is stored in the database. But not by inventing two tpes of NULL - that would violate First Normal Form. Instead, they should create a second column - one that is only populated for rows with a NULL birthdate, and that contains an atomic value representing the reason why there is no data value in the birthdate column.

Oh boy, I guess I got sidetracked a little. Sorry for that. I'll wrap up now - not only because, by now, the time spent typing this IS indeed approaching the 42 minute mark, but also because my cats are hungry, and demanding to be fed. And when the cats demand, I obey,



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1487454
Posted Thursday, August 22, 2013 1:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 2,123, Visits: 2,128
Thanks Hugo. I'm glad to see that my practical use of NULL is the same as the ANSI standard.

Apparently, there are some bright minds out there who are in the Date camp and feel very strongly that NULL means unknown or any number of things. Even though, in reality, it is very well known that it simply means no data is present.

I can see why this creates confusion with concatenation. People in the "unknown" camp will have difficulty concatenating an unknown value with a known value. In contrast, it should be very easy to concatenate "no data present" with any known value. The result is simply the known value.
Post #1487475
Posted Thursday, August 22, 2013 1:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 7,815, Visits: 9,564
Dave62 (8/22/2013)
I've been building databases since I started working with PFS File in the 1980's.

It seems like there is a literal meaning for NULL, which is "unknown", and a practical meaning, which is "data is not present in this field".

If I declare a CreateDate field as: [CreateDate] [datetime] NULL

When I select from that table and see NULL in some of the columns for the CreateDate field, I don't start thinking "Gee, I wonder what's in those fields because it's unknown". I know perfectly well that there is no data present because I allowed the record to be saved without requiring data in that field.

I think anyone is putting a somewhat weird interpretation on "unknown" if they imagine it's possible to ask what value is in a field that is unknown; if a value is unknown, it can't be in the database; if a field doesn't have a value in it. then from the point of view of the database the value for that attribute of the entity represented by the row containing the empty field is not know, so it is unknown. So unknown and not present amount to the same thing. I can see from your comment that you understand that; I've also observed over the years than an awfully large number of people don't.

Of course you may want to know why it's unknown: that's easy, roughly as Hugo pointed out: put the reason in a separate column. The data is still absent; the value is still not known; but now you know why it is absent and not known.

Hugo's example with birth date and foundation date is fair enough in one sense, but not in another sense; if we look at "legal person" and "physical person" they have far more attribute differences than just "birth date" versus "foundation date", so rather than having hordes of NULLable columns you may save space and make life easier by having a person table and two extra tables, one for the legal person only attributes and another for the physical person only attributes. Of course even if you do that, you may not have birth date in one of those and foundation date in the other, but a single column (called something like start date or inception date or beginning date) in the person table. That doesn't mean that what he's saying is wrong, just that he's perhaps chosen something which isn't the best example.


Tom
Post #1487493
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse