SQL server 2016 developer guide PDF
Preview SQL server 2016 developer guide
SQL Server 2016 Developer's Guide Get the most out of the rich development capabilities of SQL Server 2016 to build efficient database applications for your organization Dejan Sarka Miloš Radivojević William Durkin BIRMINGHAM - MUMBAI SQL Server 2016 Developer's Guide Copyright © 2017 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, and its dealers and distributors will be held liable for any damages caused or alleged to be 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. First published: March 2017 Production reference: 1150317 Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK. ISBN 978-1-78646-534-4 www.packtpub.com Credits Authors Copy Editor Dejan Sarka Vikrant Phadke Miloš Radivojević William Durkin Reviewer Project Coordinator Tomaž Kaštrun Nidhi Joshi Commissioning Editor Proofreader Amey Varangaonkar Safis Editing Acquisition Editor Indexer Vinay Argekar Tejal Daruwale Soni Content Development Editor Production Coordinator Aishwarya Pandere Shraddha Falebhai Technical Editor Vivek Arora About the Authors Dejan Sarka, MCT and SQL Server MVP, is an independent trainer and consultant who focuses on the development of database and business intelligence applications, located in Ljubljana, Slovenia. Besides his projects, he spends around half of his time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan is the main author and co-author of many books and courses about databases and SQL Server. He is a frequent speaker at many worldwide events. I would like to thank everybody involved in this book, especially to my co-authors, Miloš and William, to the content development editor, Aishwarya, and to the technical editor, Vivek. 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 and query tuning. Currently, he works as a principal database consultant in bwin (GVC Holdings)—the largest regulated online gaming company in the world. Miloš is a co- founder of PASS Austria. He is also a speaker at international conferences and speaks regularly at SQL Saturday events and PASS Austria meetings. I would like to thank my co-authors, Dejan Sarka and William Durkin. It has been a pleasure and privilege working with you guys! It was also a pleasure to work with editors, Aishwarya Pandere and Vivek Arora, in the production of this book. I'd also like to thank, Tomaž Kaštrun, for his prompt and helpful review. Finally, I would like to thank my wife, Nataša, my daughter, Mila, and my son, Vasilije, for all their sacrifice, patience, and understanding while I worked on this book. William Durkin is a DBA and data platform architect for CloudDBA. He uses his decade of experience with SQL Server to help multinational corporations achieve their data management goals. Born in the UK and now based in Germany, he has worked as a database developer and DBA on projects ranging from single-server installations, up to environments spanning five continents using a range of high-availability solutions. William is a regular speaker at conferences around the globe, a Data Platform MVP and is the chapter leader of a German PASS chapter. I would like to thank, Dejan and Miloš, for involving me in this book, it has been a challenge but a lot of fun! I would also like to thank Aishwarya and Vivek, for their editorial support. Last but certainly not least, thanks to my wife, Birgit, and son Liam, for your support and patience. About the Reviewer Tomaž Kaštrun is a SQL Server developer and data analyst. He has more than 15 years of experience in business warehousing, development, ETL, database administration and query tuning. He also has more than 15 years of experience in the fields of data analysis, data mining, statistical research, and machine learning. He is a Microsoft SQL Server MVP for data platforms and has been working with Microsoft SQL Server since version 2000. Tomaž is a blogger, author of many articles, co-author of statistical analysis books, speaker at community and Microsoft events, and avid coffee drinker. Thanks to the people who inspire me, the community, and the SQL family. Thank you, dear reader, for reading this. For endless inspiration, thank you Rubi. www.PacktPub.com For support files and downloads related to your book, please visit www.PacktPub.com. 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 [email protected] 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. https://www.packtpub.com/mapt Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career. Why subscribe? Fully searchable across every book published by Packt Copy and paste, print, and bookmark content On demand and accessible via a web browser Customer Feedback Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial process. To help us improve, please leave us an honest review on this book's Amazon page at https://www.amazon.com/dp/1786465345. If you'd like to join our team of regular reviewers, you can e-mail us at [email protected]. We award our regular reviewers with free eBooks and videos in exchange for their valuable feedback. Help us be relentless in improving our products! Table of Contents Preface 1 Chapter 1: Introduction to SQL Server 2016 8 Security 9 Row Level Security 9 Dynamic Data Masking 10 Always Encrypted 11 Engine features 12 Query Store 12 Live Query Statistics 13 Stretch Database 13 Database scoped configuration 14 Temporal Tables 15 Columnstore indexes 15 Programming 16 Transact SQL enhancements 16 JSON 17 In-Memory OLTP 17 SQL Server tools 18 Business intelligence 19 R in SQL Server 19 Release cycles 20 Summary 20 Chapter 2: Review of SQL Server Features for Developers 21 The mighty Transact-SQL SELECT 22 Core Transact-SQL SELECT statement elements 23 Advanced SELECT techniques 30 DDL, DML, and programmable objects 37 Data definition language statements 37 Data modification language statements 39 Using triggers 40 Data abstraction - views, functions, and stored procedures 42 Transactions and error handling 46 Error handling 47 Using transactions 50
The list of books you might like
