What’s new in SQL Server 2014
SQL Server 2014 has been launched on April the 1st and we’re very excited about this new version as it promises to be a great one.
In this article, we will show you what are its new features and how they can help you become more efficient in your work through a more performant system.
We’re all excited about the new SQL Server 2014 and its new previewed features, but how do they actually help you in your daily tasks?
We will try to show you why it’s important to take a look at these new features and consider trying out the 2014 edition.
SQL Server 2014 new features
In-Memory OLTP
The most talked about and most awaited feature of SQL Server 2014 is most undoubtedly the new In-Memory OLTP engine, internally called Hekaton. It was announced at PASS 2012 and was warmly welcomed by the industry.
Hekaton comes from Greek and means 100 (hundred) which was the aim of performance improvement for this feature over previous methods of accessing and manipulating data. Actual performance improvements depend on many factors that may vary from client to client, but the most common improvements in terms of performance are between 5x and 20x FASTER!
In-Memory OLTP or online-transaction processing is a database engine that is memory-optimized specifically for OLTP operations and is part of SQL Server’s engine.
This engine has been designed to deliver increased concurrency and it has a brand new mechanism to control and eliminate delays due to locking with an optimistic concurrency control mechanism.
The OLTP tables are copied to memory and are made durable by writing transaction logs to disk. Also, the new engine has an improved lock-free mechanism that can process transactions for the in-memory tables.
The performance of stored procedures is also improved due to compiling these stored procedures into DLL native code. Currently, stored procedures are being interpreted, an operation that adds a few clocks to their execution, but by compiling them to native Win64 code, they are made to be directly executable, and thus their performance is maximized and their execution time is minimized.
If you want to evaluate how the new In-Memory OLTP engine improves your database’s performance, then Microsoft has a new Analysis, Migrate, and Report tool (AMR).
The in-memory OLTP engine will work just fine with commodity hardware, but this feature does have some limitations. First of all, not all data types are going to be supported for the memory-optimized tables. Some of these data types are:
- geography
- hierarchy
- text
- ntext
- image
- xml
- varchar(max)
Moreover, other standard SQL Server features cannot be combined with the In-Memory OLTP feature. These features include:
- database mirroring
- snapshots
- triggers
- clustered indexes
- computed columns
- identity columns
- constraints such as FOREIGN KEY, CHECK, and UNIQUE
- FILESTREAM storage
In terms of OS’s which support this feature, you can use either Windows Server 2012 or Windows Server 2012 R2 and also Windows Server 2008 R2 SP2. Also, you will have to use either one of the Enterprise, Developer, or Evaluation editions of SQL Server 2014 in order to have access to In-Memory OLTP.
