Oct 13, 2014

Did you know that Microsoft has released a new version of SQL Server? (Of course you did!) Well, a new version means new stuff! It is a Microsoft product, so there are all sorts of new things. For this article though, we’re just going to look at some changes in Transact-SQL. First up is that we now have the ability for inline specification of non-clustered (as well as clustered) indexes (or indices, if you’re old school). This means that you can now define and create a non-clustered index at the same time that you are creating a table. This may not seem like that big of a deal – as you could already create the index immediately after creating the table – but it does mean that you can integrate the elements of your table design and there’s less chance of you forgetting to create the index. Note however, included columns and filtered conditions are not (yet!) supported for inline specification. So if you need to do those, you’re back to doing things the “old” (i.e. 2012) way. One more quick fact: you can now also have an index on a table variable! Next up is the improved SELECT…INTO statement, which can now operate in parallel. Before SQL Server 2014, if you wanted to make a table out of a large, parallel query, you would have to use a non-parallel operator. So SQL Server would have to work through all the parallel streams before inserting anything into the table. But no more! So get out there and get parallel-ising! Finally (for this installment), I want to mention that several T-SQL statements have been modified to support In-Memory OLTP (In-Memory Optimisation). In-Memory OLTP is a new database engine component that has been fully integrated into SQL Server. As you might imagine from the name, it is designed to optimise OLTP workloads that access data in memory. This can give some amazing improvements in performance and processing time. Tables can be defined as ‘memory optimised,’ allowing them to take advantage of the new system while still being fully transactional and accessible with Transact-SQL. The In-Memory OLTP engine is designed for high concurrency and blocking is almost never a problem. The following Transact-SQL statements have been changed to support In-Memory OLTP:

• ALTER DATABASE (MEMORY_OPTIMIZED_DATA has been added as an option)
• CREATE DATABASE (again, MEMORY_OPTIMIZED_DATA has been added as an option)
• CREATE PROCEDURE
• CREATE TABLE
• CREATE TYPE
• DECLARE @local_variable

As always, for more information, come take one of the SQL Server training courses at New Horizons, or peruse the pages of Microsoft’s TechNet library.