What’s new in SQL Server 2016 for Developers?

Session: What’s new in SQL 2016 for Developers

Track: NET343

Speaker: Greg Low

Once again one of the most informative and useful sessions at Microsoft’s Ignite (formerly Tech Ed) is the session on SQL Server by Greg Low.  This seems to be the session that impacts my day-to-day operations in the most profound way (three Tech Ed’s ago when this season showed that you could use alt key on selection to vertically cut/copy/paste/delete still worth the entry fee for that year alone).

Greg is a great speaker that manages to provide a large amount of information in the time allocated for his session.  He does so by a clear, concise and controlled speaking style and does not rush his presentation.  His use of examples and the method of display of those examples helps to provide a good level of detail to his audience.  A word of warning, unfortunately this talk was one that was audio recorded rather than video, as such some of this is lost when only viewing the channel nine content.

Some of the highlights of this session:

Drop If Exists and Create Or Alter: these are just two great pieces of syntax that enable you to specify your meaning clearer and provide a more simplified and readable query for these types of processes.  In both cases these are items that have been regular operations of those crafting SQL statements (i.e. the multi-line “if exists (select * from sys.triggers where name = ‘triggerName’) DROP TRIGGER triggerName”).  The meaning is there (I want to remove this trigger), but it is lost in the syntax.  Now statements like DROP TRIGGER IF EXISTS triggerName make that statement much more readable.

Temporal Tables and At Time Zone: timezones, logtables with timestamps, adjustments for gmt or local time, and more eventually rear their heads when looking at data that changes over time or is input in different locations.  This has then built out huge chunks of infrastructure/plumbing code to handle these items.  How does one answer the question “What did the data look like at 4:38pm on 01 May 2015 when it looks like there was an error”?  In many cases there are log tables with timestamps and complex queries to get that data back or an abundance of backup files and logs and similar.  Temporal Tables now enable a native view to that question (and one that works with much less effort).  This is one of those features that will remove a ton of code and structure, that while at first feels like (oh no, that was some of our best stuff and provided great functionality) is actually one of those great (oh yes, we don’t have to worry about that stuff moving forward) items.  I’m looking forward to the improvements that will be made capable by these features.

JSON Data: this is one of those great examples of why I never miss a Tech Ed or Ignite.  This is a feature that doesn’t fully yet fit into what we are doing, but it might and there are a number of upcoming features that we are looking at that might really benefit from this item.  This is the improvement on storage of JSON data in a SQL Server (i.e. improved over “JSON data is stored in that nvarchar(max) column”).  In many ways this has the potential to be a merging of capabilities with document databases and relational databases, though with that I’m conscious of blurring of fit-for-purpose (i.e. the avoidance of some of the issues one sees with other blurs such that can occur in functional languages that had non-functional features added)

Memory Optimization Advisor: while I’ve not yet been able to use this tool, this looks to be a very interesting utility to enable improved performance out of one’s SQL database.  It looks to have a similar function to the SQL Server Profiler or the execution plan suggestions.  This looks to help utilise many of the in-memory table functions and features now available.  This might be a bit of a slower-uptake for us as we work to determine how memory optimized filegroups and table features can be utilised, but this tool definitely factors heavily into those investigations and as implemented the on-going reviews for performance.

Row Level Security with Security Predicate: this feature looks to be the most amazing so far, with predicate based row-level security that can be enforced at user and group levels.  This looks to be an amazing re-structuring of how prior row-level security has been implemented by individual applications and databases.  I have not yet see how this feature will integration with other items (in particular the entity framework), but as that is better developed and implemented I can see this being one of the best new features around.

These were just some of the high value learnings from this session.  We have already started to look at how to utilise some of these features and schedule the upgrades to our products to enable the usage that will provide true value to our end clients.

Related Links

Leave a Reply

Your email address will not be published. Required fields are marked *