As is usually the case, the release of a technology preview of SQL Server provides lots of interesting new features for SQL admins and developers to ponder. CTP 2.0 for SQL Server vNext (generally just called SQL Server 2017) is no exception. Here we are not going to provide an exhaustive list of new features, but rather take a first look at some features that represent a major departure from how things were done in the past.
We have already looked at the built-in support for the execution of Python scripts. Microsoft now refers to R and Python as the Machine Learning Services when integrated with SQL Server; analogously with the standalone “R Server” introduced in 2016 the two scripting environments are can be installed as the standalone “Machine Learning Server”.
While Python support is likely to dominate many of the headlines, at least for the analytics crowd, a powerful new capability has been added to the SQL Server query optimizer. In the past, SQL Server relied on index and column statistics to determine key features of query execution, like the sequence of operations and the physical types of joins. There is always some guesswork here, even with perfect statistics, since SQL Server has no knowledge of correlations among column values in a particular query. The SQL Server query engine now supports adaptive processing, which is the ability to change up “on the fly” as it were. In other words, results from steps earlier in query plan execution can alter steps later in the plan, presumably for the better. Some plan characteristics in cached plans, notably the memory granted for the execution of a query, can be modified as a result of the knowledge SQL Server gained from a previous execution. The slightly modified cached plan then performs better than the plan created when the query was first executed.
A third novel feature for SQL Server 2017 is support for graph data. In this context, the term “graph” is being used in the mathematical sense, that is, data best described as a set of nodes and a set of vertices that connect the nodes. Graph data has garnered attention in recent years because of its natural support for data from social media. Individuals and the set of links that connect them are, of course, graph data. Graph data is hardly limited to social media, though, and now that SQL Server provides direct support for graph data, many new applications will develop quickly. In traditional relational database terms, graph data is a “many-to-many” relationship and has not been easy to model with traditional tools.
SQL Server implements this support by provided two new table types, called NODE and EDGE, which are exactly what their names imply. Marking a table AS NODE in the CREATE TABLE statement causes SQL Server to automatically create a NodeID for each row. An EDGE table then simply records a connection between two nodes. There can be as many different node tables as needed to describe the entities in your data model, and you can create a separate edge table for each of the relationship types that connect these entities in your data.
Our friends in Redmond have been very busy providing not just enhancements in SQL Server vNext but also creating completely new features of direct importance to the worlds of data collection and analysis.