This course teaches the new developer features in SQL Server 2016,
including Drop If Exists (DIE), SESSION_CONTEXT, dynamic data masking
(DDM), row-level security (RLS), Always Encrypted, stretch database,
temporal data, and built-in JSON support.
Leonard Lobel (Microsoft MVP, SQL Server) is CTO and co-founder of Sleek Technologies, Inc., a New York-based development shop. He is also a principal consultant at Tallan, Inc., a Microsoft National Systems Integrator and Gold Competency Partner. Lenni is also a consultant, trainer, and frequent speaker at major industry conferences.
Course Overview Welcome to Pluralsight. My name is Lenni Lobel, and I'm very excited to deliver my latest course, SQL Server 2016: New Features for Developers. Whether you've had extensive experience with earlier versions of SQL Server or not, this course will get you up to speed quickly on the many exciting new features in SQL Server 2016. We start with Drop If Exists, a small, but convenient language enhancement, which helps you write new T-SQL code because you no longer need to test if an object exists before you delete it. You'll learn about SESSION_CONTEXT, which is a new server side in-memory dictionary object that maintains its state across the lifetime of the database connection, so it's a new easy way to share data across stored procedures running on the server, and even to share state between the client and the server. With Dynamic Data Masking, or DDM, you can shield sensitive information in your tables from unauthorized users not by encrypting, but by masking. You can fully or partially mask data on the fly as it gets queried by users that don't have permission to see the real data. Row-Level Security, or RLS, lets you hide different rows for different users based on your own custom criteria. Then you'll learn about Always Encrypted, which lets you encrypt data that is never decrypted in any location other than the client. And by using client-side certificates to perform client-side encryption and decryption, the data is always encrypted, not just on disk, but in flight as it traverses the network. With Stretch Database, you can keep using your own data centers and SQL severs to host and manage your data, but still allow certain tables that you designate for remote data archive to be migrated to the cloud on Azure SQL Database. Temporal Data is an exciting new feature that automatically tracks changes made to a table and records those changes to a history table. Then the query engine integrates with the history table and gives you this almost magical experience of time travel, where you can run a query against a table as it appeared at any point in time of the past. And finally there's also new built-in support for JSON, very similar to the native XML support that's been available in SQL Server now for more than a decade, and today JSON is the new XML, and SQL Server 2016 provides JSON support very similar to what's possible with XML. You can shred, store, and manipulate JSON documents in just about any way that you need to. By the end of this course, you'll have the knowledge you need to leverage the latest and greatest new features for developers in SQL Server 2016.
Row-level Security Welcome back. In this module, you'll learn about another powerful new security feature in SQL Server 2016, Row-Level Security, or RLS. RLS gives you the power to filter and block access to individual rows within a table based on custom logic that you write inside a predicate function. A predicate function is just an ordinary inline table-valued function that you can write in T-SQL, which SQL Server executes against every DML operation on the table, that is every select, insert, update, or delete statement. Your predicate function will typically compare the current user context with some column value in each row, and indicate yes or no if that row is accessible by the DML operation. You just bind your predicate function to the table by creating a security policy, and SQL Server immediately filters in the case of select, or blocks in the case of insert, update, and delete, each row in the table. And you can of course disable the security policy without dropping it, so it's easy to turn RLS on and off at will for any given table.