In this short article, we’ll take a look at the latest version of SQL Server and compare it to previous versions of Microsoft’s DBMS.
A few months ago, we carried out a little technical review of the SQL Server 2022 Release Candidate (RC) based on the information provided by Microsoft at the Ignite conference and some tests that we performed ourselves.
Then, just over a week ago, on 16 November 2022, Microsoft released the final version and we thought that it would be a good idea to analyse the real differences between this version and previous releases, not just the RC but versions released in previous years.
To do this, we’re going to review some of the main features and compare them with the 2019 and 2017 versions as these are the versions that still receive Microsoft support.
As usual, Microsoft appears to have focused on enhancing existing features, boosting working capacity, increasing computing power and improving security, as well as increasing its usage capabilities with Azure.
So, let’s get down to business and see what’s changed!
What Editions of SQL Server 2022 Are Available?
First off, it appears that absolutely no changes have been made to the different editions of SQL Server that are available. The same editions that were available previously are also available for SQL Server 2022.
- Enterprise: The premium version that includes complete features for the data centre. There are no restrictions, it offers professional performance and it can handle a higher workload than other editions.
- Standard: A less powerful version than the Enterprise edition, but it is still a first-rate solution. This is aimed at small organisations.
- Express: The free version. Ideal for beginners or students. It’s useful for trials or small applications if you don’t want to pay for a licence.
- Developer: A version that allows you to program and test applications of all types on SQL Server. It has the same features and functionalities as the Enterprise version, but you cannot put it into production.
The table below shows a summary of the differences between the different editions of SQL Server 2022.
| Enterprise | Standard | Express | Developer | |
| Max. No. of cores | OS Limit | 24 | 4 | OS Limit |
| Max. memory per instance | OS Limit | 128GB | 1410MB | OS Limit |
| Max. database size | 10GB | 524PB | 10GB | 10GB |
The Evolution of SQL Server
If we look at the number of new features added with each release, we can see that this latest version features almost double the number of new features compared with previous versions, and that’s what makes this new release so exciting. However, it’s important to note that around 20% of these new features rely entirely on Microsoft Azure.
SQL Server 2017 included the following improvements:
- Support for different GNU/Linux distros, like SLES (SUSE Enterprise Linux), RHEL (Red Hay Enterprise Linux) or Ubuntu Linux
- Support for containers on GNU/Linux and Windows containers
- Cross-platform availability groups
- Adaptive and intelligent query processing
- Automatic plan correction for performance tuning
- Support for graph capabilities to model relationships
- Python support
SQL Server 2019 included the following improvements:
- Support for deployment with Kubernetes
- Native UTF-8 support
- Free Java support
- Persistent memory support
- Accelerated database recovery
- Free Azure Always Encrypted Disaster Recovery (DR) with secure enclaves
- Vulnerability assessment
- Data classification and auditing
- Integration with Azure Machine Learning and Spark ML
And finally, SQL Server 2022 brings us the following new features:
- SQL Server ledger
- Support for more memory
- Concurrency scalability support
- Data virtualisation for any Data Lake
- Query store by default and replica support
- Time series compatibility
- NextGen Intelligent Query Processing
- Time series support
- Query store hints
- New Transact-SQL functionalities
- Integrated acceleration and offloading
- JSON functions
- Extensions for Azure
- Azure Synapse links for SQL
- Azure Purview policies
Comparison of Features
We think that the best way to illustrate the differences between the different versions is to put them into some tables so you can see the availability of each feature or functionality.
Then, to make things easier, we’ve separated the data into the following sections:
- Database performance
- Database availability
- Database security
- Database management and programming
- BI and Analytics support
- Intelligence and databases
- Azure functionalities
Database performance: Here, we show the features that aim to ensure that SQL Server uses resources more efficiently and improve processing capabilities.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| In-memory database: in-memory OLTP | Yes | Yes | Yes |
| In-memory database: persistent memory | Yes | Yes | Yes |
| In-memory database: memory-optimised tempdb | Yes | Yes | |
| Real-time operational analytics | Yes | Yes | Yes |
| Intelligent query processing | Yes | Yes | Yes |
| Buffer pool parallel scan | Yes | ||
| Query store activated by default with replica support | Yes | ||
| Query store hints | Yes | ||
| Integrated acceleration and offloading | Yes | ||
| Hybrid buffer pool with direct write | Yes | ||
| Advanced vector extension (AVX) 512 to improve batch-mode operations | Yes |
Database availability: In this section, we look at the features aimed at providing availability capabilities to the DBMS and its databases to remain “Always On”.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| Always on | Yes | Yes | Yes |
| Accelerated database recovery | Yes | Yes | |
| Read scale-out availability groups | Yes | Yes | Yes |
| Large memory and concurrency scalability | Yes | ||
| Multi-write replication | Yes | Yes | |
| Link to Azure SQL Managed Instance: replicas | Yes | Yes | Yes |
| Link to Azure SQL Managed Instance: one-way | Yes | Yes | |
| Link to Azure SQL Managed Instance: two-way | Yes | ||
| Contained availability group | Yes |
Database security: In this section, we look at all the security features, such as encryption, AAD support, logs, antivirus technology, etc.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| Always encrypted with secure enclaves | Yes | Yes | |
| Data discovery and classification | Yes | Yes | Yes |
| Transparent data encryption | Yes | Yes | Yes |
| Backup encryption support | Yes | Yes | Yes |
| Encryption at rest and in motion | Yes | Yes | Yes |
| Dynamic data masking and security at queue level | Yes | Yes | Yes |
| Azure Active Directory authentication | Yes | ||
| Microsoft Defender for SQL | Yes | Yes | Yes |
| Central management of Microsoft Purview integration | Yes | Yes | Yes |
| Microsoft Purview Integration data-owner policies | Yes | ||
| SQL ledger | Yes | ||
| Support for PFX certificates and other cryptographic improvements | Yes | ||
| Support MS-TDS 8.0 and TLS 1.3 protocol | Yes |
Database management and programming: In this section, we look at programming langauge compatibility, data handling, etc.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| GNU/Linux support | Yes | Yes | Yes |
| Container support | Yes | Yes | Yes |
| Kubernetes support | Yes | Yes | |
| Temporary tables | Yes | Yes | Yes |
| JSON support | Yes | Yes | Yes |
| Graphic data support | Yes | Yes | Yes |
| UTF-8 compatibility | Yes | Yes | |
| Java support | Yes | Yes | |
| Azure Data Studio to manage SQL Server, including T-SQL support | Yes | Yes | Yes |
| Database compatibility certfication | Yes | Yes | |
| Bit manipulation functions | Yes | ||
| Time series support |
BI and analytics support: In this section, we look at all aspects related to Business Intelligence and data analysis.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| Server integration services | Yes | Yes | Yes |
| BI semantic models | Yes | Yes | Yes |
| Master data services | Yes | Yes | Yes |
| Data quality services | Yes | Yes | Yes |
| Many-to-many relationships in tabular models | Yes | Yes | |
| End-to-end mobile BI on any device | Yes | Yes | Yes |
| Direct query of SQL Server Analysis Services (SSAS) | Yes | Yes | Yes |
| Calculation groups in tabular models | Yes | Yes |
Intelligence and databases: In this section, we look at query autodetection and optimisation capabilities.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| PloyBase data virtualisation | Yes | Yes | Yes |
| Data Lake virtualisation | Yes | ||
| Object storage backup and restore | Yes | ||
| Azure Synapse Link for SQL | Yes |
Azure Functionalities: Here, we look at everything related to Azure support, use of Microsoft public cloud resources, etc.
| Feature | SQL Server 2017 | SQL Server 2019 | SQL Server 2022 |
| Link to Azure SQL Managed Instance: replicas | Yes | Yes | Yes |
| Link to Azure SQL Managed Instance: one-way | Yes | Yes | Yes |
| Link to Azure SQL Managed Instance: two-way | Yes | ||
| Optimised Virtual Machine (VM) images in the Azure gallery | Yes | Yes | Yes |
| Free asynchronous replication on Azure Virtual Machines for disaster recovery | Yes | Yes | |
| Microsoft Defender for SQL | Yes | Yes | Yes |
| Central management of Azure Purview | Yes | Yes | Yes |
| Azure Purview Data-owner policies | Yes | ||
| AAD authentication | |||
| Azure Synapse Link for SQL |
Downloading SQL Server 2022
Now, that you’ve seen all the new features that SQL Server 2022 has to offer, you might be thinking about trying it out for yourself. If so, click on the link below to download it.
SQL Server Downloads | Microsoft
And once you’ve downloaded it, if you need some help installing it, you can refer to our tutorial for SQL Server 2019, How to Install SQL Server 2019, although soon we’ll write an updated tutorial for 2022.
Conclusion
After a long way, SQL Server 2022, is finally here, three years after the previous version was released.
This new version offers a wide range of improvements compared to previous versions, offering greater security and business continuity features, as well as improvements to performance, resource management and many other areas.
As always, we recommend having the latest version of any software or product in order to ensure that your platform is secure, stable and optimised to handle your company’s workload.
Once again, Microsoft has reminded us of it’s importance as a provider of DBMS solutions and many other products and services.
Thanks for reading!
