Level: Intermediate
Andrew Brust
Senior Director, Market Strategy and Intelligence
Datameer
This full-day workshop will get you up to speed on powerful features for developers in SQL Server 2016. You'll pull no punches as you rip through the most important SQL Server features for developers in this intensive demo-packed tour. You'll begin with the many enhancements made to T-SQL, starting with SQL Server's powerful windowing features. Learn how to leverage windowing with the OVER clause to calculate running and sliding aggregations, row lookups, and percentiles based on rank. You'll also explore other T-SQL enhancements, including new functions (all 22 of them), improved error handling with THROW, server-side paging with OFFSET/FETCH NEXT, sequence generators, and metadata discovery techniques.
Then you'll cover the latest features added in SQL Server 2016, including new security features such as dynamic data masking, row-level security, and always encrypted. Other innovative capabilities include "stretch" database (allow select tables in an on-premises database to be transparently relocated in Azure SQL Database), temporal data (enable "time travel" to access data as it existed at any point in time), and integrated JSON support. You'll also learn about QueryStore (maintain query plan history and performance data) and R integration (an open source data science language).
Then you'll look at the Big Data and analytics capabilities of SQL Server and the Microsoft data stack. You'll see how columnstore indexers can make a huge difference in your data warehouse-style queries, and how to make sure columnstore indexes are being invoked when you intend. You'll learn about Apache Hadoop, HDInsight (Microsoft's cloud implementation of Hadoop) and the PolyBase technology in SQL Server 2016 that lets you query Hadoop using T-SQL. The workshop will also cover basic data warehouse concepts, the fundamentals of Power BI, and the capabilities of SQL Server components like Integration Services, Analysis Services, Master Data Services, Data Quality Services, and Analytics Platform System (APS), and you'll see what's new in SQL Server 2016 Reporting Services.
To wrap up, you'll examine the "beyond relational" features that will get you thinking outside the box with respect to the types of data that can be managed by a relational database system. Learn how to use the hierarchyid data type to cast a hierarchical structure over any relational table. Dig into FILESTREAM and FileTable and learn how you can finally enjoy the native ability to store large binary objects in the file system transparently, and with full transactional capabilities. The geospatial data types enable you to integrate location-intelligence into the database, and you'll finish up by building several location-aware applications on top of these new data types. You'll learn a ton of new SQL Server features in this information-packed day.
You will learn:
- About windowing, other T-SQL programming enhancements, and the latest new features in SQL Server 2016
- About the Big Data and analytics capabilities in SQL Server, Power BI and HDInsight, including enhancements to Reporting Services
- About unstructured data storage, including native file streaming, and the hierarchical and geospatial data types