Monday, 9 January 2017

SQL Server 2016 SP 1

By David Postlethwaite

SQL Server 2016 SP1 was released in November 2016 only five months after the initial release.

There have been some significant changes and additions in this release. The most significant of which is that many features that once only existed in Enterprise edition, such as Always Encrypted, partitioning, in memory, Columnstore and compression are now available in lower editions as well.

The image below shows a list of previous enterprise edition features that are now available in other editions:

SQL Server 2016 SP1 previous Enterprise-Only Features
SQL Server 2016 SP1 previous Enterprise-Only Features

The one feature that remains Enterprise only is Transparent Data Encryption (TDE) which is a shame given how important security is becoming.

So the main difference between Enterprise and Standard edition is now over the amount of memory and CPU they can access. With so many new features now available in standard edition there is even less reason not to move to SQL 2016. 

One new T-SQL command that was introduced in SQL 2016 was “DROP IF EXISTS”. So rather than writing a complicated query to check if an object exists before dropping it you can now simply say

DROP TABLE IF EXISTS [Person].[CountryRegion];

As a result of this there was a campaign to add a function that Oracle has had for a long time namely “CREATE or ALTER”. And in SP1 we now have it. You can now write:

CREATE OR ALTER PROCEDURE dbo.sp_MyProc as
BEGIN
PRINT 'This is a new Procedure';
END

These two commands can be used against most objects in SQL Server  2016. For those of you that write installation scripts for your applications these two new commands should make your code much easier to manage. 

No comments:

Post a Comment