1 / 42

Samir Behara, Senior Developer, EBSCO

Learn about the latest enhancements in SQL Server 2016 that enhance productivity, security, and performance. Discover how Live Query Statistics, Query Store, Temporal Tables, and JSON Support can make your development tasks easier.

jthomas
Download Presentation

Samir Behara, Senior Developer, EBSCO

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. dotnetvibes.com @samirbehara Samir Behara, Senior Developer, EBSCO Making Developers lives easier with SQL Server 2016

  2. 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

  3. Agenda Productivity Security Performance JSON Support Dynamic Data Masking Live Query Statistics Query Store Temporal Tables TSQL Enhancements

  4. SQL Server Management Studio 2016

  5. SSMS 2016 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.

  6. SSMS 2016 Enhancements Automatic Check for Updates Navigation Buttons Color Theme Quick Launch Vertical Scroll Bar Enhancements Execution Plan Window Improvements New Installation Experience Compare Execution Plans

  7. Live Query Statistics

  8. Have you come across this scenario? You execute a TSQL 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?

  9. Collect actual metrics of the query while it is running…

  10. Benefits of Live Query Statistics Real Time Insights into SQL Query Execution Process Visually track overall query progress Troubleshoot long running queries

  11. How to enable Live Query Statistics? Using SQL Server Management Studio Using Activity Monitor

  12. Query Store

  13. Have you come across this scenario? • There is a temporary performance issue with your application and you realize that your queries are running slow all of a sudden. • TSQL 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.

  14. 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.

  15. What does Query Store do? Store history of execution plans and statistics of each query Baseline performance metrics and track deviations Diagnosing Performance Issues after upgrades Displays customizable UI reports

  16. How does Query Store work? Query Store Compile Plan Store Execute Runtime Stats

  17. What are the various built in Query Store reports?

  18. Query Store Demo

  19. Temporal Tables

  20. 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 inbuilt support for tracking old versions of data over a period of time, without any need for additional programming.

  21. Why is Temporal required? Time Travel Simplify Data Audit Repair Corruptions Trend Analysis

  22. How Temporal Data works? Temporal Table History Table Old Versions Update/ Delete Insert/ Bulk Insert

  23. Temporal Tables Demo

  24. JSON Support

  25. 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.

  26. What are the advantages of JSON over XML? • JSON is 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 lesser memory.

  27. 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.

  28. 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.

  29. JSON Functions There are a number of newly added JSON functions to provide support for handling JSON data –

  30. JSON Functions Demo

  31. T-SQL Enhancements

  32. 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 TSQL execution will continue on. Applies to – Database, Table, Function, Trigger, Stored Procedure, Column, User, View, Schema, Index , Role

  33. 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.

  34. 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.

  35. 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 much similar to the DATEDIFF function, difference being that DATEDIFF function return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.

  36. Dynamic Data Masking

  37. 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.

  38. What are the different Masking functions available?

  39. How does Dynamic Data Masking work? Security Officer defines the Data Masking Policy for sensitive data. Other Users Admin

  40. Dynamic Data Masking Demo

  41. samirbehara@gmail.com @samirbehara dotnetvibes.com

More Related