• The benefits of upgrading from 2008 to 2016 are cumulative. SQL Server 2012 was a huge upgrade from 2008. On the T-SQL side you have:

    * LAG/LEAD - super huge

    * Window Aggregate Function Specification (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    These are both game changers with respect to developing faster T-SQL code. You also have columnstore indexes and batch mode processing, again - game changers with respect to performance.

    In 2014 MS introduced in-memory support; this was a game changer which makes 2014 a big upgrade from 2012. Other huge performance improvements in 2014 include faster SELECT INTO, a new/better/faster cardinality estimator, and clustered & updatable columnstore indexes

    In 2016 (as mentioned) there's some big improvements to how in-memory works, filtered columnstore indexes and the ability to get batchmode processing against data not stored in columnstore indexes.

    Other great features in 2016 are big data components like R support and Hadoop support. The query store is huge too. I personally would consider 2014 if you're not interested in the big data stuff. I like to wait for the first service pack to be released before upgrading but that's just me.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001