Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
SQL Server 2016 Developer's Guide
SQL Server 2016 Developer's Guide

SQL Server 2016 Developer's Guide: Build efficient database applications for your organization with SQL Server 2016

Arrow left icon
Profile Icon Dejan Sarka Profile Icon Radivojević Profile Icon William Durkin
Arrow right icon
$19.99 per month
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (8 Ratings)
Paperback Mar 2017 616 pages 1st Edition
eBook
$35.99 $51.99
Paperback
$65.99
Subscription
Free Trial
Renews at $19.99p/m
Arrow left icon
Profile Icon Dejan Sarka Profile Icon Radivojević Profile Icon William Durkin
Arrow right icon
$19.99 per month
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (8 Ratings)
Paperback Mar 2017 616 pages 1st Edition
eBook
$35.99 $51.99
Paperback
$65.99
Subscription
Free Trial
Renews at $19.99p/m
eBook
$35.99 $51.99
Paperback
$65.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

SQL Server 2016 Developer's Guide

Chapter 2. Review of SQL Server Features for Developers

Before delving into the new features in SQL Server 2016, let's have a quick recapitulation of the features of SQL Server for developers already available in the previous versions of SQL Server. Recapitulating the most important features will help you remember what you already have in your development toolbox and also understand the need and the benefits of the new or improved features in SQL Server 2016.

Tip

This chapter has a lot of code. As this is not a book for beginners, the intention of this chapter is not to teach you the basics of database development. It is rather a reminder of the many powerful and efficient Transact-SQL (T-SQL) and other elements included in SQL Server version 2014 and even earlier.

The recapitulation starts with the mighty T-SQL SELECT statement. Besides the basic clauses, advanced techniques such as window functions, common table expressions, and the APPLY operator are explained. Then, you...

The mighty Transact-SQL SELECT

You probably already know that the most important SQL statement is the mighty SELECT statement you use to retrieve data from your databases. Every database developer knows the basic clauses and their usage:

  • SELECT to define the columns returned, or a projection of all table columns
  • FROM to list the tables used in the query and how they are associated, or joined
  • WHERE to filter the data to return only the rows that satisfy the condition in the predicate
  • GROUP BY to define the groups over which the data is aggregated
  • HAVING to filter the data after the grouping with conditions that refer to aggregations
  • ORDER BY to sort the rows returned to the client application

Besides these basic clauses, SELECT offers a variety of advanced possibilities as well. These advanced techniques are unfortunately less exploited by developers, although they are really powerful and efficient. Therefore, I advise you to review them and potentially use them in your applications. The advanced...

DDL, DML, and programmable objects

As a developer, you are often also responsible to create database objects. Of course, in an application, you also need to insert, update, and delete the data. In order to maintain data integrity, enforcing data complies with business rules, you need to implement constraints. In a quick review of the data definition language (DDL) and data modification language (DML) elements, the following statements are presented:

  • CREATE for creating tables and programmatic objects
  • ALTER to add constraints to a table
  • DROP to drop an object
  • INSERT to insert new data
  • UPDATE to change existing data
  • DELETE to delete the data

In a SQL Server database, you can also use programmatic objects. You can use triggers for advanced constraints or to maintain redundant data like aggregated data. You can use other programmatic objects for data abstraction, for an intermediate layer between the actual data and an application. The following programmatic objects are introduced here:

  • Triggers...

Transactions and error handling

In a real-world application, errors always appear. Syntax or even logical errors may be in the code, the database design might be incorrect, there might even be a bug in the database management system you are using. Even if everything works correctly, you might get an error because the users insert wrong data. With Transact-SQL error handling you can catch such user errors and decide what to do upon them. Typically, you want to log the errors, inform the users about the errors, and sometimes even correct them in the error handling code. Error handling for user errors works on the statement level. If you send SQL Server a batch of two or more statements and the error is in the last statement, the previous statements execute successfully. This might not be what you desire. Frequently, you need to execute a batch of statements as a unit, and fail all of the statements if one of the statements fails. You can achieve this by using transactions. In this section...

Beyond relational

Beyond relational is actually only a marketing term. The relational model, used in the relational database management system, is nowhere limited to specific data types or specific languages only. However, with the term "beyond relational," we typically mean specialized and complex data types that might include spatial and temporal data, XML or JSON data, and extending the capabilities of the Transact-SQL language with CLR languages such as Visual C#, or statistical languages such as R. SQL Server in versions before 2016 already supports some of the features mentioned. Here is a quick review of this support that includes:

  • Spatial data
  • CLR support
  • XML data

Defining locations and shapes with Spatial Data

In modern applications, often you want to show your data on a map, using the physical location. You might also want to show the shape of the objects that your data describes. You can use spatial data for tasks like these. You can represent the objects with points...

The mighty Transact-SQL SELECT


You probably already know that the most important SQL statement is the mighty SELECT statement you use to retrieve data from your databases. Every database developer knows the basic clauses and their usage:

  • SELECT to define the columns returned, or a projection of all table columns

  • FROM to list the tables used in the query and how they are associated, or joined

  • WHERE to filter the data to return only the rows that satisfy the condition in the predicate

  • GROUP BY to define the groups over which the data is aggregated

  • HAVING to filter the data after the grouping with conditions that refer to aggregations

  • ORDER BY to sort the rows returned to the client application

Besides these basic clauses, SELECT offers a variety of advanced possibilities as well. These advanced techniques are unfortunately less exploited by developers, although they are really powerful and efficient. Therefore, I advise you to review them and potentially use them in your applications. The advanced...

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Utilize the new enhancements in Transact-SQL and security features in SQL Server 2016 to build efficient database applications
  • Work with temporal tables to get information about data stored in the table at any point in time
  • A detailed guide to SQL Server 2016, introducing you to multiple new features and enhancements to improve your overall development experience

Description

Microsoft SQL Server 2016 is considered the biggest leap in the data platform history of the Microsoft, in the ongoing era of Big Data and data science. This book introduces you to the new features of SQL Server 2016 that will open a completely new set of possibilities for you as a developer. It prepares you for the more advanced topics by starting with a quick introduction to SQL Server 2016's new features and a recapitulation of the possibilities you may have already explored with previous versions of SQL Server. The next part introduces you to small delights in the Transact-SQL language and then switches to a completely new technology inside SQL Server - JSON support. We also take a look at the Stretch database, security enhancements, and temporal tables. The last chapters concentrate on implementing advanced topics, including Query Store, column store indexes, and In-Memory OLTP. You will finally be introduced to R and learn how to use the R language with Transact-SQL for data exploration and analysis. By the end of this book, you will have the required information to design efficient, high-performance database applications without any hassle.

Who is this book for?

This book is for database developers and solution architects who plan to use the new SQL Server 2016 features for developing efficient database applications. It is also ideal for experienced SQL Server developers who want to switch to SQL Server 2016 for its rich development capabilities. Some understanding of the basic database concepts and Transact-SQL language is assumed.

What you will learn

  • Explore the new development features introduced in SQL Server 2016
  • Identify opportunities for In-Memory OLTP technology, significantly enhanced in SQL Server 2016
  • Use columnstore indexes to get significant storage and performance improvements
  • Extend database design solutions using temporal tables
  • Exchange JSON data between applications and SQL Server in a more efficient way
  • Migrate historical data transparently and securely to Microsoft Azure by using Stretch Database
  • Use the new security features to encrypt or to have more granular control over access to rows in a table
  • Simplify performance troubleshooting with Query Store
  • Discover the potential of R s integration with SQL Server

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Mar 22, 2017
Length: 616 pages
Edition : 1st
Language : English
ISBN-13 : 9781786465344
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Mar 22, 2017
Length: 616 pages
Edition : 1st
Language : English
ISBN-13 : 9781786465344
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 192.97
SQL Server 2017 Administrator's Guide
$60.99
SQL Server 2017 Integration Services Cookbook
$65.99
SQL Server 2016 Developer's Guide
$65.99
Total $ 192.97 Stars icon
Banner background image

Table of Contents

14 Chapters
1. Introduction to SQL Server 2016 Chevron down icon Chevron up icon
2. Review of SQL Server Features for Developers Chevron down icon Chevron up icon
3. SQL Server Tools Chevron down icon Chevron up icon
4. Transact-SQL Enhancements Chevron down icon Chevron up icon
5. JSON Support in SQL Server Chevron down icon Chevron up icon
6. Stretch Database Chevron down icon Chevron up icon
7. Temporal Tables Chevron down icon Chevron up icon
8. Tightening the Security Chevron down icon Chevron up icon
9. Query Store Chevron down icon Chevron up icon
10. Columnstore Indexes Chevron down icon Chevron up icon
11. Introducing SQL Server In-Memory OLTP Chevron down icon Chevron up icon
12. In-Memory OLTP Improvements in SQL Server 2016 Chevron down icon Chevron up icon
13. Supporting R in SQL Server Chevron down icon Chevron up icon
14. Data Exploration and Predictive Modeling with R in SQL Server Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5
(8 Ratings)
5 star 50%
4 star 50%
3 star 0%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by




Jeff Miller May 15, 2017
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Wanted to know how SQL Server implemented JSON, and they had just the right amount of documentation and examples. I put together a hybrid relational/document DB schema together with this books help.Beyond JSON, I found the book to be well put together and had a great mix of example code and verbiage to get their points across.Well done!
Amazon Verified review Amazon
Saveloy Sep 09, 2017
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Good book for the professional....
Amazon Verified review Amazon
OldSkuLL84 Oct 27, 2018
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Gives deep knowledge about the new features of SQL Server 2016. I would skip the R chapters.
Amazon Verified review Amazon
Ian Stirk Jul 05, 2017
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Hi,I have written a detailed chapter-by-chapter review of this book on www. DOT i-programmer DOT info, the first and last parts of this review are given here. For my review of all chapters, search i-programmer DOT info for STIRK together with the book's title.This book aims to provide best practices and example architectures for Hadoop technologists, how does it fare?This book aims to introduce you to the salient new and enhanced features in SQL Server 2016, how does it fare?This book is aimed at SQL Server developers and architects that want to understand the latest features in SQL Server 2016. The ideal reader would be an intermediate-level developer with experience of SQL Server 2014, that said, the book does cover some background material from previous versions of SQL Server where appropriate.In many ways, SQL Server 2016 removes many of the limitations found in SQL Server 2014 (especially In-Memory OLTP), as well as implementing other useful developer features.Below is a chapter-by-chapter exploration of the topics covered.Chapter 1 Introduction to SQL Server 2016The book’s opening chapter provides an overview of the new and enhanced SQL Server 2016 features that are subsequently detailed in the rest of the book. There’s a brief overview of the recent history of SQL Server, culminating in Microsoft’s “Mobile First, Cloud First” strategy. Next, security enhancements are highlighted (row level security, dynamic data masking, always encrypted).The chapter continues with an overview of the major SQL Server engine enhancements, namely: -Query Store (troubleshooting sudden performance changes) -Live Query Statistics (real-time analysis of performance) -Stretch Database (offload older data to slower/cheaper storage) -Database scoped configuration (some server-level settings now set at database-level) -Temporal Tables (easier table history/versioning) -Columnstore indexes (now updateable)Programming enhancements are outlined next, including new T-SQL functions, and JSON (a “better” XML). Business Intelligence has been enhanced with the incorporation of the language R into SQL Server. The chapter ends with a look at the increasing frequency of software releases.This chapter provides a useful, if very brief, overview of the major new and enhanced features in SQL Server 2016, it also highlights what to expect from the rest of the book, where these features are examined in greater detail. It is generally easy to read, with useful explanations, tips, example code, and diagrams. These traits apply to the whole of the book....ConclusionThis book aims to introduce you to the salient new and enhanced features in SQL Server 2016, and succeeds. It is generally easy to read, well written, with useful explanations, tips, example code, and diagrams.Whilst the book doesn’t cover all the new features (e.g. PolyBase), it does cover the major ones. I suspect the more you know about SQL Server already, the more useful this book will be. Since the book focuses on new and enhanced features, large subject areas are omitted (e.g. database design).Sometimes, before discussing an extended feature (e.g. In-Memory OLTP), a large amount of background information is given with reference to previous editions of SQL Server. Whilst this may be useful if you don’t know the feature, it could be argued it is unnecessary - if you already know SQL Server 2014.It might have been useful to include a section discussing SQL Server changes in terms of some of the industry’s wider trends (e.g. Big Data, Social Media, the Cloud).Overall, if you want to know more about the new and enhanced features in SQL Server 2016, I can recommend this well-written book.
Amazon Verified review Amazon
Amazon Customer Nov 30, 2017
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
I like this book as its easy to read have screenshots to understand and easy to finish.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.