Sarka - SQL Server 2017 Developers Guide

Copyright 2018 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Commissioning Editor: Amey Varangaonkar
Acquisition Editor: Vinay Argekar
Content Development Editor: Snehal Kolte
Technical Editor: Sayli Nikalje
Copy Editor: Safis Editing
Project Coordinator: Manthan Patel
Proofreader: Safis Editing
Indexer: Rekha Nair
Graphics: Tania Dutta
Production Coordinator: Nilesh Mohite

First published: March 2018

Production reference: 1140318

Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.

ISBN 978-1-78847-619-5

www.packtpub.com

Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.

• Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals

• Improve your learning with Skill Plans built especially for you

• Get a free eBook or video every month

• Mapt is fully searchable

• Copy and paste, print, and bookmark content

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at service@packtpub.com for more details.

At www.PacktPub.com , you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.

Dejan Sarka, MCT and Data Platform MVP, is an independent trainer and consultant focusing on the development of database and business intelligence applications. Besides working on projects, he spends about half of his time training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. He is the main author or co-author of 16 books on databases and SQL Server. He has also developed many courses and seminars for Microsoft, SolidQ, and Pluralsight.

Milo Radivojevi is a database consultant in Vienna, Austria. He is a Data Platform MVP and specializes in SQL Server for application developers and performance/query tuning. He works as a principal database consultant in bwin (GVC Holdings). He is a cofounder of PASS Austria and speaks regularly at international conferences.

William Durkin is the co-founder of Data Masterminds, a Microsoft Data Platform consultancy. William is a UK born Data Platform MVP, now living in Germany. William speaks at SQL Server focused events across the globe and is the founder and organizer of the small, but popular, SQLGrillen.

Toma Katrun is an SQL Server developer and data analyst with more than 15 years experience in business warehousing, development, ETL, database administration, and query tuning. He also has more than 15 years experience in data analysis, data mining, statistical research, and machine learning.

He is a Microsoft SQL Server MVP for Data Platform and has been working with Microsoft SQL Server since version 2000. He is a blogger, the author of many articles, the co-author of a statistical analysis book, a speaker at community and Microsoft events, and an avid coffee drinker.

If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.

Microsoft SQL Server is developing faster than ever before in its almost 30-year history. The latest versions, SQL Server 2016 and 2017, bring with them many important new features. Some of these new features just extend or improve features that were introduced in the previous versions of SQL Server, and some of them open a completely new set of possibilities for a database developer.

This book prepares its readers for more advanced topics by starting with a quick introduction to SQL Server 2016 and 2017's new features and a recapitulation of the possibilities database developers already had in previous versions of SQL Server. It then goes on to, the new tools are introduced. The next part introduces small delights in the Transact-SQL language. The book then switches to a completely new technology inside SQL ServerJSON support. This is where the basic chapters end and the more complex chapters begin. Stretch Database, security enhancements, and temporal tables are medium-level topics. The latter chapters of the book cover advanced topics, including Query Store, columnstore indexes, and In-Memory OLTP. The next two chapters introduce R and R support in SQL Server, and show how to use the R language for data exploration and analysis beyond what a developer can achieve with Transact-SQL. Python language support is then introduced. The next chapter deals with new possibilities for using data structures called