I saw this tweet recently, where Richie Rump asked what has changed in T-SQL since the SQL Server 2012 version. A few people from Microsoft responded that there were changes in all versions, and while I think some versions have few changes, I decided to look.
SQL Server 2012 introduced the window functions with the OVER() clause to SQL Server. This was a huge change in that many aggregate queries were much easier to write without needing complex GROUP BY lists and subqueries or unions to join together different data. While I’m not an expert by any means, I find lots of queries for reporting easier to write with the window functions, and I’ve grown to enjoy using these in code.
Looking across other versions, I’ve seen these changes:
SQL Server 2014
- UTF-8 for Bulk insert
- SELECT..INTO works in parallel
- In-Memory OLTP language enhancements
SQL Server 2016
- temporal tables
- JSON support
- more In-Memory T-SQL changes
- Security – DDM, RLS, AE T-SQL changes
- R services
SQL Server 2017
- graph query
- CONCAT_WS, TRANSLATE, TRIM, WITHIN GROUP
- BULK INSERT options
- Memory-optimized enhancements (CASE, TOP, JSON, computed columns
- Python language services
SQL Server 2019
- Graph enhancements
- UTF-8
- Java and other language enhancements
Some of these were to support other features, so perhaps these aren’t really T-SQL changes per se. If I look at PostgreSQL release notes, I see enhancements and changes, but relatively few new language changes. Certainly, there are some additions, but lots of improvements, which I think reflect the nature of a mature product. Not a lot of new things, but regular improvements and refinements to existing items.
I’ve been working with SQL Server since 1991, and it feels like T-SQL has grown a lot in that time. Back then it felt like there were relatively few keywords and functions, requiring complex coding for tough problems. Now, with the way the language changed a lot in 2005, 2012, and 2016, it feels like we have a lot of tools at our disposal. We could always use more, and we got some neat ones in SQL Server 2022. I hope to see more useful changes in future versions to come.
Steve Jones
Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
Voice of the DBA 
I love the Windows functions and like you stated they have made many queries much much easier to construct as well as cleaner. I know cleanliness in coding is not always important to most devs (where SQL is more of a tertiary language) but it matters to me. I can’t stand sloppily constructed queries.
I’ve yet to get the chance to use temporal tables because the vendor who hosts our DB is running 2016 but has no plans to convert any existing tables over to temporal tables (even the ones that really should be like transactional/accounting data). We use accounting software in which AR (Accounts Receivable) is only truly reliable as of now and sometimes you need to know what it was at some previous point in time and it sounds like temporal tables could assist with that. While we could create our own copy of the existing table(s) but make the copy a temporal table the data is too volatile and too large to make it practical to keep the temporal table in sync with the non-temporal table. It would have been nice had MS found some way to make this a feature you could apply to an existing table as opposed to having to create a new one and migrate the data.
LikeLike
Be careful with temporal tables. They are partly done, IMHO, with some tight linkages with the history, which makes schema changes complex and hard.
LikeLiked by 1 person
Good to know. In our case the table’s we’d look at trying to use it with are unlikely to change. Generally speaking the Vendor has rarely changed an existing table; probably because of how may code changes their application(s) would need to accommodate it.
LikeLike