CONCAT 1

  • Ron McCullough

    SSC Guru

    Points: 63877

    Comments posted to this topic are about the item CONCAT 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    If you already use CONCAT function then it's easy to you. Otherwise first you need to check about CONCAT function.

    I love this function. It removes some overhead while adding multiple string.

    Previously, we need to place an ISNULL for each and every string while adding.

    Thanks Ron

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Good One. Thank you for posting.

    (well... this qtod very similar to example given under CONCAT function in BOL...)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • palotaiarpad

    SSCertifiable

    Points: 5524

    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. πŸ˜€

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    2 points for this easy question? :w00t:

    Thanks Ron, always nice to have SQL 2012 questions.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.

    That being said, it *IS* possible to get this query to error out. If the collation of the database that is active when running this code is different from the default server collation (and hence the collation of tempdb), you will get a collation conflict because the age will be converted to a string in the database collation, and the other arguments are all in the tempdb collation.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Toreador

    SSChampion

    Points: 11257

    Danny Ocean (8/21/2013)


    I love this function. It removes some overhead while adding multiple string.

    And I hate it for exactly the same reason!

    At last they've deprecated CONCAT_NULL_YIELDS_NULL but then spoil it by providing a new method that encourages the same sloppy coding and the same misunderstandings of how NULL works :crazy:

  • Nick Doyle

    SSC Eights!

    Points: 926

    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. πŸ˜€

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    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. πŸ˜€

    +1

    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.

  • Ed Wagner

    SSC Guru

    Points: 286982

    This was a nice one for me because we don't have 2012 (sigh) and it got me to research a 2012-only function. Thanks.

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    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 !!!

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • SQLRNNR

    SSC Guru

    Points: 281243

    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[/url]
    Learn Extended Events

  • palotaiarpad

    SSCertifiable

    Points: 5524

    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.

    :blush:

    You are right!

    Wrong page found. It was the concat operator: + πŸ˜€

  • mtassin

    SSC-Insane

    Points: 23099

    Koen Verbeeck (8/22/2013)


    2 points for this easy question? :w00t:

    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[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply