T-SQL Language Changes in SQL Server 2022

  • Comments posted to this topic are about the item T-SQL Language Changes in SQL Server 2022

  • This is all great stuff...that SQL Server should have had a decade ago. PostgreSQL users have been enjoying these features for a LONG time. Great to finally see the catch up.

    And IS DISTINCT FROM is very useful. We use it all the time in our ETL.

    Now if only SQL Server would get DISTINCT ON, and allow column ordinals in the GROUP BY, then we'd be really cooking with gas!

  • I dont have access to SQL22, but looking at GREATEST/LEAST, I assume that using them in a WHERE clause will make the query NON-SARGEABLE.

    SELECT P.Name, P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate AS ModelModifiedDate
    , GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) AS LatestDate
    FROM SalesLT.Product AS P
    INNER JOIN SalesLT.ProductModel AS PM on P.ProductModelID = PM.ProductModelID
    WHERE GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) >='2007-01-01'
  • Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

  • paul.hermeneutic wrote:

    Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

    At the prices they charge for the specs, I'll never know.  I also have to admit, I don't really care because portability is a myth to begin with UNLESS you're content with the equivalent of only using the 4 basic math functions on a scientific calculator because someone somewhere might not have a scientific calculator.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    paul.hermeneutic wrote:

    Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

    At the prices they charge for the specs, I'll never know.  I also have to admit, I don't really care because portability is a myth to begin with UNLESS you're content with the equivalent of only using the 4 basic math functions on a scientific calculator because someone somewhere might not have a scientific calculator.

    Portability will remain a myth as long as we continue to let vendors take the money without standards conformance.

    I am reluctant to suggest government involvement in anything, but POSIX did some good things in the OS market. Perhaps we need a similar approach to SQL.

  • paul.hermeneutic wrote:

    Jeff Moden wrote:

    paul.hermeneutic wrote:

    Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

    At the prices they charge for the specs, I'll never know.  I also have to admit, I don't really care because portability is a myth to begin with UNLESS you're content with the equivalent of only using the 4 basic math functions on a scientific calculator because someone somewhere might not have a scientific calculator.

    Portability will remain a myth as long as we continue to let vendors take the money without standards conformance.

    I am reluctant to suggest government involvement in anything, but POSIX did some good things in the OS market. Perhaps we need a similar approach to SQL.

    Heh... IMHO, if the government were involved, there would be no innovation since the standards first hit the street.

    Do you happen to have a copy of the standard for DATEDIFF()?  I'd love to see that because what they have in Postgres, although named the same, is wildly different from what is in SQL Server (for example).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • DesNorton wrote:

    I dont have access to SQL22, but looking at GREATEST/LEAST, I assume that using them in a WHERE clause will make the query NON-SARGEABLE.

    SELECT P.Name, P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate AS ModelModifiedDate
    , GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) AS LatestDate
    FROM SalesLT.Product AS P
    INNER JOIN SalesLT.ProductModel AS PM on P.ProductModelID = PM.ProductModelID
    WHERE GREATEST(P.SellStartDate, P.DiscontinuedDate, PM.ModifiedDate) >='2007-01-01'

    Haven't tested this, but I assume that's the case. The index would need to look at all three values, so possibly an index with all of them would be SARG-able, but perhaps not.

  • I haven't examined the ANSI standard compared with T-SQL. It does seem like some of the stuff from there related to the OVER() and window functions has been implemented, but not sure how standard the T-SQL part is.

     

  • Thank you for sharing this. My name is Amanda Dawson, and I just finished college. Now I'm looking for a job, and I'm seeking for the best cv writing service uk. I found a wonderful website that can help you find the finest agency for your cv writing. You may also check the reviews to get the finest cv writing service.

  • Jeff Moden wrote:

    paul.hermeneutic wrote:

    Jeff Moden wrote:

    paul.hermeneutic wrote:

    Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

    At the prices they charge for the specs, I'll never know.  I also have to admit, I don't really care because portability is a myth to begin with UNLESS you're content with the equivalent of only using the 4 basic math functions on a scientific calculator because someone somewhere might not have a scientific calculator.

    Portability will remain a myth as long as we continue to let vendors take the money without standards conformance.

    I am reluctant to suggest government involvement in anything, but POSIX did some good things in the OS market. Perhaps we need a similar approach to SQL.

    Heh... IMHO, if the government were involved, there would be no innovation since the standards first hit the street.

    Do you happen to have a copy of the standard for DATEDIFF()?  I'd love to see that because what they have in Postgres, although named the same, is wildly different from what is in SQL Server (for example).

    I'll take the 2 weeks of silence as a "no".  Thanks anyway.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    Jeff Moden wrote:

    paul.hermeneutic wrote:

    Jeff Moden wrote:

    paul.hermeneutic wrote:

    Is anything here part of ISO/IEC 9075:2016 (ANSI X3.135)? https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/

    What parts of the SQL standard has Microsoft not yet implemented?

    At the prices they charge for the specs, I'll never know.  I also have to admit, I don't really care because portability is a myth to begin with UNLESS you're content with the equivalent of only using the 4 basic math functions on a scientific calculator because someone somewhere might not have a scientific calculator.

    Portability will remain a myth as long as we continue to let vendors take the money without standards conformance.

    I am reluctant to suggest government involvement in anything, but POSIX did some good things in the OS market. Perhaps we need a similar approach to SQL.

    Heh... IMHO, if the government were involved, there would be no innovation since the standards first hit the street.

    Do you happen to have a copy of the standard for DATEDIFF()?  I'd love to see that because what they have in Postgres, although named the same, is wildly different from what is in SQL Server (for example).

    I'll take the 2 weeks of silence as a "no".  Thanks anyway.

    This thread reminded me of a quote worth repeating, "The great thing about standards is that there are so many to pick from."  😆

    It's true and it's never going to change.  Standards are always trying to catch up with innovation, as Jeff eludes to, that's how it should be.

Viewing 12 posts - 1 through 11 (of 11 total)

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