Making Developers Lives Easier with SQL Server 2016

Do you want insights into how your data has changed over a period of time?
Are you using JSON as your data interchange format and want to integrate with SQL Server?
Do you wish to have more statistical/historical information while troubleshooting a T-SQL performance issue?
Do you face any problem while troubleshooting a long running query?
Have you encountered performance issues after server/application upgrades?

If your answer to any of the above questions is YES, then this session is for YOU.
During this content rich and demo intensive session, you will understand the rich capabilities of SQL Server 2016 and how the life of a SQL Developer is getting transformed with features like Query Store, Live Query Statistics, Comparing Execution Plans, Built-in JSON Support, Temporal Database and other nice T-SQL enhancements.

Transcript

1. Samir Behara, Senior Developer, EBSCO Making Developers lives easier with SQL Server 2016 @samirbehara dotnetvibes.com
2. Please silence cell phones Please silence cell phones 2
3. Free online webinar events Free 1-day local training events Local user groups around the world Online special interest user groups Business analytics training Free Online Resources PASS Blog White Papers Session Recordings Newsletter www.pass.org Explore everything PASS has to offer PASS Connector BA Insights Get involved
4. Session evaluations Download the GuideBook App and search: PASS Summit 2017 Follow the QR code link displayed on session signage throughout the conference venue and in the program guide Your feedback is important and valuable. Go to passSummit.com Submit by 5pm Friday, November 10th to win prizes. 3 Ways to Access:
5. SAMIR BEHARA Senior Developer, EBSCO Has a Bachelor Degree in Computer Science with 11 years of IT experience. Works as a Senior Developer and builds software solutions using cutting edge technologies, primarily C# and SQL Server. Co-Chapter Lead of the Steel City SQL Server Users Group – which is the Birmingham, AL chapter of PASS. Author of www.dotnetvibes.com /samirbehara @samirbehara samirbehara
6. Agenda Performance Productivity Security Live Query Statistics Query Store JSON Support Temporal Tables TSQL Enhancements Dynamic Data Masking
7. SQL Server Management Studio 17
8. • SSMS 17 is FREE. • SSMS is an independent web installer. • SSMS is decoupled from the underlying SQL Server Engine. • SSMS now uses Visual Studio 2015 isolated shell. • More frequent releases for new features, enhancement and bug fixes.
9. Vertical Scroll Bar Enhancements Import Flat File Wizard Built in Performance Dashboard Searching in Execution Plans Built in XEvent Profiler Compare Execution Plans Execution Plan Window Improvements SSMS 17 Enhancements New Installation Experience
10. Live Query Statistics
11. Have you come across this scenario? You execute a T-SQL Query and it just runs and runs, without displaying any results and you finally end up cancelling the executing query. How do you troubleshoot this problem?
12. Collect actual metrics of the query while it is running…
13. Benefits of Live Query Statistics Troubleshoot long running queries Real Time Insights into SQL Query Execution Process Visually track overall query progress
14. How to enable Live Query Statistics? Using SQL Server Management Studio Using Activity Monitor
15. Query Store
16. Have you come across this scenario? • There is a temporary performance issue with your application and you realize that your queries are suddenly running slow. • T-SQL queries slow down after a server/application upgrade. • You are trying to troubleshoot a performance issue but don’t have any historical information on how the query ran over a period of time.
17. What is Query Store? • A new feature in SQL Server 2016 which collects and presents detailed historical information of queries, execution plans and run-time statistics. • Gives the SQL developers/DBAs lot more control and insight into the query performance issues. • Directly integrated in SQL Server. • No need of any 3rd party performance monitoring tool. • Simple to use – Graphical user interface built into SSMS.
18. What does Query Store do? Store history of execution plans and statistics of each query Baseline performance metrics and track deviations Displays customizable UI reports Helps diagnose Performance Issues after upgrades
19. How does Query Store work? Compile Execute Plan Store Runtime Stats Query Store
20. What are the various built in Query Store reports? Types of Reports Description Regressed Queries Shows all the queries whose Execution Plan worsened over a period of time. Overall Resource Consumption Shows summary of the query runtime statistics executed during a time interval. Top Resource Consuming Queries Shows most expensive queries executed during a time interval, based on Duration, CPU Time, Logical/Physical Reads, Memory consumption. Tracked Queries Shows the historic runtime statistics of a specific query.
21. Query Store Demo
22. Temporal Tables
23. Temporal Tables A new type of system-versioned user table that holds the entire history of data changes. Temporal Table in SQL Server 2016 provides built-in support for tracking old versions of data over a period of time, without any need for additional programming.
24. Why is Temporal required? Time Travel Simplify Data Audit Trend Analysis Repair Corruption
25. How Temporal Data works? Temporal Table History Table Insert/ Bulk Insert Update/ Delete Old Versions
26. Temporal Tables Demo
27. JSON Support
28. Why JSON? Javascript Object Notation is a lightweight data interchange format which helps to communicate between client and server side technologies. JSON is considered as the best tool for sharing data, because the data is stored in an array format. This makes data transfer easier since the array structure is pretty much familiar to object oriented languages.
29. What are the advantages of JSON over XML? • JSON is more lightweight in comparison to XML and has a smaller message size. • In JSON, data is stored in arrays whereas in XML data is stored in trees, hence XML needs to be first transformed before it can be imported. • JSON parsing is generally faster than XML parsing. • Because of the similarity in syntax, JSON is easier to be handled with Javascript. • Formatted JSON is generally easier to read than formatted XML. • Due to its simplicity, JSON runs faster and consumes less memory.
30. How to format query results as JSON? SQL Server 2016 provides built-in support for storing, managing and parsing JSON data. There is no separate JSON data type created, like XML – rather JSON is represented by NVARCHAR datatype. FOR JSON clause allows us to format query results as JSON text. Appending this syntax to a standard TSQL query returns the result set in JSON format. FOR JSON clause types Description FOR JSON AUTO JSON is formatted by the database engine based on the order of the columns in the SELECT statement. FOR JSON PATH JSON is formatted based on the user’s discretion. It gives us full control over the format of the JSON output.
31. How to transform JSON text to relational table? OPENJSON function can be used to convert JSON text into table rows and columns or to import JSON into SQL tables. By default, when we use the OPENJSON function, it returns 3 values – key, value and type. Type Value JSON Data Type 0 NULL 1 STRING 2 INT 3 BOOL 4 ARRAY 5 OBJECT
32. JSON Functions There are a number of newly added JSON functions to provide support for handling JSON data – Function Name Description ISJSON() Verifies that the text has valid JSON data. JSON_QUERY() Extract JSON fragment from JSON text. JSON_VALUE() Extract value from JSON text. JSON_MODIFY() Add, Delete or Update properties in JSON text
33. JSON Functions Demo
34. T-SQL Enhancements
35. DROP IF EXISTS Statement Syntax – DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME Example - DROP TABLE IF EXISTS dbo.Employee DROP IF EXISTS conditionally drops the column or constraint only if it already exists. If the object does not exist, it will not throw any error and the T-SQL execution will continue on. Applies to – Database, Table, Function, Trigger, Stored Procedure, Column, User, View, Schema, Index , Role
36. STRING_SPLIT() Function Syntax - STRING_SPLIT ( string , separator ) STRING_SPLIT is a T-SQL function that splits an input string by a separator and outputs the results as a table.
37. COMPRESS and DECOMPRESS Function COMPRESS – Compress data using GZip algorithm and returns binary data. DECOMPRESS – Decompress binary data using GZip algorithm and returns binary data. You will need to cast binary data to text to get the original compressed text. • You can compress data in Client Side and send compressed data to SQL Server. • You can compress data in SELECT query and decompress it in the Client side.
38. DATEDIFF_BIG Function Syntax – DATEDIFF_BIG ( DatePart , StartDate , EndDate ) This new function gives the difference between the two dates (StartDate and EndDate) in the units specified by the DatePart parameter and the returned unit is of type bigint. It is very similar to the DATEDIFF function, difference being that DATEDIFF function return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.
39. Dynamic Data Masking
40. What is Dynamic Data Masking? • A new built-in security feature which will limit access to sensitive information by masking stored data. • We can configure users who can have access to the unmasked data and for other unauthorized users, just show the masked data. • Data is masked on the fly and the underlying data in the database does not change. • Very simple to enable this feature in existing databases. • No need of any application changes to take advantage of this functionality. • Not a replacement for encryption.
41. What are the different Masking functions available? Masking Function Description DEFAULT() This method can be described as Full masking. It replaces the content of the column with ‘XXXX’. EMAIL() This method exposes the first letter of the email address and the constant suffix ‘.com’. It converts the email-id to the format aXXX@XXX.com PARTIAL() This method exposes the first and the last letters and adds a custom padding string in the middle. RANDOM() This method is used to mask numeric types with a random value within a specified range.
42. How does Dynamic Data Masking work? Security Officer defines the Data Masking Policy for sensitive data. Admin Other Users
43. Dynamic Data Masking Demo
44. Thank You samirbehara@gmail.com @samirbehara dotnetvibes.com