T-SQL Language Changes in SQL Server 2022

Question

T-SQL Language Changes in SQL Server 2022

Frank Dolan

Say Hey Kid

Points: 693
More actions
August 31, 2022 at 12:00 am

#4078808

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

March 24, 2023 at 2:43 am

This was removed by the editor as SPAM

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

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

quickdraw SSC Enthusiast Points: 151 More actions · Answer 1

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!

DesNorton SSC-Insane Points: 24343 More actions · Answer 2

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'

How to post data/code on a forum to get the best help.

paul.hermeneutic SSC Rookie Points: 48 More actions · Answer 3

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?

This reply was modified 2 years ago by paul.hermeneutic.

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 4

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)

paul.hermeneutic SSC Rookie Points: 48 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 6

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)

Frank Dolan Say Hey Kid Points: 693 More actions · Answer 7

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.

Frank Dolan Say Hey Kid Points: 693 More actions · Answer 8

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.

AmandaDawson Grasshopper Points: 11 More actions · Answer 9

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 SSC Guru Points: 1004151 More actions · Answer 10

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)

thisisfutile Hall of Fame Points: 3668 More actions · Answer 11

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.