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
- CANCAT_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 I hope see more useful changes in future versions to come.
