SQL Server 2016 Service Pack 1

I have started working with Microsoft SQL Server 7.0 in late 1999 and even today I become very excited with the new version of Microsoft SQL Server.  

SQL Server 2016 has been in market for quite some time but now finally Microsoft has released service pack 1 for SQL Server 2016 to download. This is one of the big release after SQL Server 2012 so I am happy to welcome SQL Server 2016 in market officially as now I can start using SQL Server 2016 in my production system and can upgrade my old SQL Server box to new and latest RDBMS.

I am happy that Microsoft has given many enterprise level feature into standard edition of SQL Server 2016 at the same time I am little disappointed as well because I am seeing no sign of horizontal scale out feature in this big release which is becoming mandatory in today’s data world.

Let us not talk negative today as I, really, want to adopt this new system into my production as soon as possible especially the powerful standard edition (I can save big money of my company by using standard edition).

Here is the official product page of Microsoft SQL Server 2016. I have already downloaded my free copy of SQL Server 2016 Developer Edition.


I could not resist to mention some of my favorite features, mostly enterprise level , which are available in SQL Server 2016 Standard Edition.

Dynamic Data Masking: Data security is becoming very critical nowadays especially if you are dealing with sensitive data/information like SSN number, Credit Card number. By having dynamic data masking, you can store full SSN/CC number in database but user can see only those digits, (may last few digits) which you want whereas if user has UNMASK permission, they can see complete SSN/CC number.

Row level Security: Row level security is very important especially (not limited to) for the SAAS product. I might have billing/payroll information of many different vendors in my billing/payroll table but Vendor1 shouldn’t be able to see the data of Vendor2.

Database Snapshot: As per MSDN, A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.

I am still trying to understand where I can use this feature in some of the production system I manage as it has a list of pro/cons and needs to decide whether the pro list is big or the cons.

Columnstore: First I have learnt about Column store index in SQL Server 2008 R2 during some BI project. Columnstore index is available in database engine (from SQL Server 2012) but still I think it is more usable for analytical queries, maybe I am very much used to with row-store traditional index, especially non-clustered covering index.

Compression: SQL Server 2016 supports GZip algorithm for COMPRESS/DECOMPRESS data during DML operations. Finally, I could save some more valuable SSD disk space in my server.

In-Memory OLTP: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for transaction processing. In-Memory OLTP could surely increase performance of application by improving throughput and by reducing latency for transaction processing.

Always Encrypted: As per MSDN, Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). By ensuring on-premises database administrators, cloud database operators, or other high-privileged, but unauthorized users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Polybase: And lasts but not the least in my favorite list, PolyBase is a query engine designed to fill the gap between the relational database of SQL Server with the big unstructured data mostly stored in Hadoop which can be used for analytics. We could even use Polybase with Azure Blob storage.


If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah



Note: Microsoft Books online is a default reference of all articles.