SQL Server 2016 contains lots of new goodies, as this blog explains
Microsoft have made huge changes to SQL Server Reporting Services and Analysis Services (Tabular Model), and smaller ones to SSIS, SSAS (cubes) and SQL. This blog gives the main highlights, with links to blogs giving the full Monty for each area of SQL Server 2016.

Posted by Andy Brown on 11 July 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

New features in SQL Server 2016 - the Wise Owl verdict

SQL Server 2016 was commercially released on 1st Jun 2016.  As this blog will show, it's a much bigger upgrade than 2014.

You can get a copy of SQL Server 2016 Developer for your own use free of charge by following these instructions.

I'll give a quick summary of what's new in each part of SQL Server 2016 below, but you should follow the links below to get the full story for each component program:

Product Link
SQL Server Reporting Services 2016 SSRS 2016
SQL Server Integration Services 2016 SSIS 2016
SQL in SQL Server 2016 T-SQL 2016
SQL Server Analysis Services 2016 (Tabular) SSAS Tabular 2016
SQL Server Analysis Services 2016 (Multi-dimensional) SSAS Cubes 2016

SQL Server Reporting Services 2016 (SSRS)

If you're creating reports, not that much has changed, but if you're publishing or consuming reports ... everything has.  Perhaps the biggest change is the new Web Portal (which replaces the antiquated Report Manager):

Web Portal

You can apply your corporate brand, and things like subscriptions are hugely improved.

Another huge innovation is the separate Mobile Report Publisher application:

Mobile Report Publisher

You can use this to create Desktop BI style dashboards which will look as good on your IPhone or Galaxy as they do on your tablet.

However, these are just two of the many new features in SSRS 2016.

SQL Server Integration Services 2016 (SSIS 2016)

Very little has changed in SSIS 2016.  There are a few new control flow tasks to do with Hadoop:

New control flow tasks

Regular SSIS users will recognise all of these tasks apart from the ones in the box.

 

You might also not recognise a few of the data flow tasks:

New data flow tasks

Tasks like the Balanced Data Distributor and Data Streaming Destination are new, but relatively minor (the HDFS tasks are to do with the Hadoop file system).

 

You can see more on the new SSIS 2016 features here.

The T-SQL Programming Language in SQL Server 2016

There are very few new SQL programming features in SQL Server 2016, but one which deserves mention is the DROP ... IF EXISTS feature:

-- delete temp table if it exists

DROP TABLE IF EXISTS #temp

No more messing about with Object_Id!  You can see a summary of what's new in T-SQL 2016 here.

SQL Server Analysis Services 2016 (Tabular Model)

Apart from Reporting Services, Tabular SSAS is the part of SQL Server which has changed the most for 2016.  First and foremost, the DAX editor is now usable!

DAX editor

With comments, code colouring, indentation and DAX variables, we welcome this new editor with open arms!

There are many more new features (bi-directional relationships being a highlight), all of which you can see in our list of SSAS Tabular 2016 new features.

SQL Server Analysis Services 2016 (Multi-dimensional model)

Last - and least.  There are virtually no changes to the older version of Analysis Services, although I've put together a list of new features in SSAS Multi-Dimensional Model (Cubes) 2016 to prove this.

The Wise Owl Verdict

As a training company, we think we're in a good position to know whether a software upgrade delivers gimmicks or genuine improvements.  Here's our initial view of the various parts of SQL Server 2016, in descending order of usefulness:

Software Verdict
SSAS Tabular If you work with Analysis Services Tabular Model, the improvements to the DAX Editor will change your working life.  Not only can you add comments and indentations, but it seems much less flaky.  There are other big improvements, but the improvements to the DAX editor justify the upgrade on their own.
Reporting Services Although the new web portal and mobile report publishing application revolutionise how people consume reports, the underlying software for creating standard reports hasn't changed much.  Even so, this is definitely worth the upgrade (SSRS has finally caught up with the SmartPhone age).
Integration Services If you work with Hadoop you'll need to upgrade, but otherwise the few cosmetic improvements for SSIS 2016 don't really justify the move to 2016 on their own.
T-SQL There are a couple of useful new language features, but they won't really make any difference to a programmer's life, and you'd be made to upgrade just to get them.
SSAS cubes Since Analysis Services (Multi-Dimensional model) 2016 hasn't really changed since 2014 (or even 2012), it's not worth upgrading just for this!

Taken in total, this seems an excellent new software release, putting SSRS back as the pre-eminent BI reporting tool and confirming Analysis Services Tabular as the optimal data warehousing and modelling tool on the market.  Enjoy!

This blog has 0 threads Add post