Programming

The programming landscape of SQL Server has continued to improve in order to adopt newer technologies over the years. SQL Server 2016 is no exception to this: there have been some long awaited general improvements and also some rather revolutionary additions to the product that change the way SQL Server may be used in future projects. This section will outline what programming improvements have been included in SQL Server 2016.

Transact SQL enhancements

The last major improvements in the T-SQL language allowed for better processing of running totals and other similar window functions. This was already a boon and allowed developers to replace arcane cursors with high performance T-SQL. These improvements are never enough for the most performance-conscious developers among us, and as such there were still voices requesting further incorporation of the ANSI SQL standards into the T-SQL implementation.

Notable additions to the T-SQL syntax include the ability to finally split comma separated strings via a single function call STRING_SPLIT() instead of the previous "hacky" implementations using loops, functions, XML conversions or even the CLR.

The sensible opposing syntax for splitting strings is a function to aggregate values together: STRING_AGG() returns a set of values in a comma separated string. This replaces similarly "hacky" solutions using the XML data type or one of a multitude of looping solutions. Each improvement in the T-SQL language further extends the toolbox that we as developers possess in order to manipulate data inside SQL Server.

Note

Further details on T-SQL Enhancements can be found in Chapter 4, Transact-SQL Enhancements.

JSON

It is quite common to meet developers outside the Microsoft stack who look down on products released from Redmond. Web developers in particular have been critical of the access to the latest data exchange structures, or rather lack of it. JSON has become the de facto data exchange method for the application development world. It is similar in structure to the previous "cool-kid" XML, but for reasons beyond the scope of this book, JSON has overtaken XML in general programming projects and is the expected payload for application and database communications. Microsoft has included JSON as a possible data exchange data type in SQL Server 2016 and provided a set of functions to accompany the data type.

Note

Further details on JSON can be found in Chapter 5, JSON Support in SQL Server.

In-Memory OLTP

In-Memory OLTP (codename Hekaton) was introduced in SQL Server 2014. The promise of ultra-high performance data processing inside SQL Server was a major feature when SQL Server 2014 was released. As expected with a newly implemented feature, there were a wide range of limitations in the initial release and this prevented many customers from being able to adopt the technology. With SQL Server 2016 a great number of these limitations have been either raised to a higher threshold or completely removed. In-Memory OLTP has received the required maturity and extension in its feature set to make it viable for prime production deployment. Chapter 11, Introducing SQL Server In-Memory OLTP, of this book will show an introduction to In-Memory OLTP, explaining how the technology works under the hood and how the initial release of the feature works in SQL Server 2014. Chapter 12, In-Memory OLTP Improvements in SQL Server 2016, will build on the introduction and explain how the feature has matured and improved with the release of SQL Server 2016.

Note

Further details on In-Memory OLTP can be found in Chapter 11, Introducing SQL Server In-Memory OLTP and Chapter 12, In-Memory OLTP Improvements in SQL Server 2016.

SQL Server tools

Accessing or managing data inside SQL Server and developing data solutions are two separate disciplines, each with their own specific focus on SQL Server. As such, Microsoft has created two different tools, each tailored towards the processes and facets of these disciplines.

SQL Server Management Studio (SSMS), as the name suggests, is the main management interface between DBAs/Developers and SQL Server. The studio was originally released with SQL Server 2005 as a replacement and consolidation of the old Query Analyzer and Enterprise Manager tools. As with any non-revenue generating software, SSMS received less attention over the years than the database engine, with limitations and missing tooling for many of the newer features in SQL Server. With SQL Server 2016 the focus inside Microsoft has been shifted and SSMS has been de-coupled from the release cycle of SQL Server itself. This decoupling allows both SSMS and SQL Server to be developed without having to wait for each other or for release windows. New releases of SSMS are created on top of more recent versions of Visual Studio and have seen almost monthly update releases since SQL Server 2016 was released to the market.

SQL Server Data Tools (SSDT) is also an application based on the Visual Studio framework. SSDT is focused on the application/data development discipline. SSDT is much more closely aligned with Visual Studio in its structure and the features offered. This focus includes the ability to create entire database projects and solution files, an easier integration into source control systems, the ability to connect projects into automated build processes, and generally offering a developer-centric development environment with a familiarity with Visual Studio. It is possible to design and create solutions in SSDT for SQL Server using the Relational Engine, Analysis Services, Integration Services, Reporting Services, and of course, for Azure SQL Database.

Note

Further details for SQL Server Tools can be found in Chapter 3, SQL Server Tools.

This concludes the overview of programming enhancements inside SQL Server 2016. The improvements outlined are all solid evolutionary steps in their respective areas. New features are very welcome and allow us to achieve more, while requiring less effort on our side. The In-Memory OLTP enhancements are especially positive, as they now expand on the groundwork laid down in the release of SQL Server 2014. Read the respective chapters to gain a deeper insight into how these enhancements can help you.