1 Microsoft 2016 12/6/ :06 AM BRK3152 Achieve developer productivity with SQL Server 2016 and Azure SQL Database Borko Novakovic, Senior Program Manager Drazen Sumic, Principal PM Manager © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

2 Simple code is better and faster12/6/2017 Simple code is better and faster Very complex, although fast code… … replaced with simple, intuitive and even faster code © 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

3 Complex tasks made simple12/6/2017 Complex tasks made simple Analyze data history yourself Let the temporal tables do it 1 SCHEMA MAINTENANCE Double effort to maintain current and history. 1 SCHEMA MAINTENANCE Automatic and online. 2 HISTORY TRACKING User code is required (triggers, SPs, app). Hard to maintain and achieve good performance. 2 HISTORY TRACKING Automatic and optimal. 3 DATA ANALYSIS Complex queries are required. 3 DATA ANALYSIS Simple and efficient with FOR SYSTEM_TIME clause. 4 DATA PROTECTION Immutability of history data cannot be guaranteed. 4 DATA PROTECTION Out-of-box immutability of history data. © 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

4 SQL Server 2016 Modernizing the platformMicrosoft 2016 12/6/ :06 AM SQL Server 2016 Modernizing the platform © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

5 Text & semi-structured data Rich analyticsMicrosoft 2016 12/6/ :06 AM Data variety Numeric types, date and time, strings, large objects XML, geometry/geography Operators & functions Rich ANSI standard language support Conversions, math, string functions New query constructs and string functions Text & semi-structured data XQuery, Full text search JSON processing Polybase Rich analytics Aggregate, ranking, percentile functions Windowing computations → Up to 100X faster on 48-core machines In-database R Temporal tables Geospatial Spatial types, operators & specialized index → Up to 5X faster Connectivity & tools New in SQL 2016 © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

6 Temporal Tables Microsoft 2016 12/6/2017 10:06 AM© 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

7 Why Temporal? Tracking history is important but challengingA common scenario in healthcare, pharmaceutical, financial services, social networks… SQL Server makes it super easy Track data changes with zero code in your app Zoom-in data analysis on any point in time Data Audit Time Travel Slowly Changing Dimensions Row-level error correction

8 How Temporal System-Versioning Works?Microsoft Ignite 2015 12/6/ :06 AM How Temporal System-Versioning Works? temporal table (current data) history table * Old versions Insert / Bulk Insert Update */ Delete * © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

9 How Temporal System-Versioning Works?Microsoft Ignite 2015 12/6/ :06 AM How Temporal System-Versioning Works? temporal table (current data) history table * Include historical versions Regular queries (current data) Temporal queries * (time travel, etc.) © 2015 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

10 Demo: Time travel through online product catalogMicrosoft 2016 12/6/ :06 AM Demo: Time travel through online product catalog © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

11 Auditing data changes in scientific projectsMicrosoft 2016 12/6/ :06 AM Auditing data changes in scientific projects A US-based company that builds solutions to manage laboratory data. Leverage SQL DB as a back end for their SaaS product. Widely use Temporal for comprehensive audit tracking that traditionally would have required a great deal of custom development. “Temporal has brought significant value to our product in terms of meeting the data history tracking and auditing requirements of our customers in the scientific community. “ Abigail Ames, Director of Technology at RockStep Solutions © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

12 Time travel through IT configuration changesMicrosoft Ignite 2016 12/6/ :06 AM Time travel through IT configuration changes One of the world's leading integrated oil and gas companies. Use SQL Database as a back end for the configuration management application in IT (Syntosa) Temporal Tables helped us deliver a comprehensive analytical solution, bringing new capabilities to our DBaaS service clients faster than ever before. “Temporal is fast, it scales, and is easy to implement” Michael Ritacco, Database Product Manager © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

13 Analyzing performance trends with TemporalMicrosoft Ignite 2016 12/6/ :06 AM Analyzing performance trends with Temporal A global provider for Microsoft’s BI and Data Management platforms. Flex incorporates assessment of all monitoring components, benchmarks, forecast and provides a monitoring system that makes sense for all.  With Temporal data support they completely removed a complex structure of multiple logical views, procedures and tables for handling system version data. “With Temporal Tables in SQL 2016 we reduced development time up to 60%” Raoul Illyés, Founder – Flex services © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

14 Managing Growth of Historical DataMicrosoft 2016 12/6/ :06 AM Managing Growth of Historical Data Stretch the History Table Partitioning Custom delete script Built-in retention policy Coming soon! ALTER TABLE dbo.DepartmentHistory SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND)); ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 6 MONTHS)); Manage Retention of Historical Data in System-Versioned Temporal Tables https://msdn.microsoft.com/en-us/library/mt aspx © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

15 Works great with other SQL features12/6/ :06 AM Works great with other SQL features In-Memory OLTP Cost-effective auditing for intensive transactional workloads Columnstore Excellent compression and fast analysis for historical data Stretch Database Keep data history forever Security features Row-level security, Always Encrypted, Dynamic Data Masking Tooling First-class citizen in SSMS & SSDT © 2014 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

16 JSON in SQL Server/DatabaseMicrosoft 2016 12/6/ :06 AM JSON in SQL Server/Database © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

17 Scenarios for JSON features12/6/ :06 AM Scenarios for JSON features Interchange data with apps and services Exploit agility of NoSQL to easily extend your app How to choose the service? Mostly relational data model + JSON use cases -> SQL Database. Only JSON + index on all fields + JavaScript -> Document DB. © 2014 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

18 Index via computed columns and B-trees12/6/2017 Check, extract & modify Index via computed columns and B-trees Built-in functions ISJSON JSON_VALUE JSON_QUERY JSON_MODIFY 2 [ { "Number":"SO43659", "Date":" T00:00:00" "AccountNumber":"AW29825", "Price":59.99, "Quantity": }, { "Number":"SO43661", "Date":" T00:00:00“ "AccountNumber":"AW73565“, "Price":24.99, "Quantity": } ] 1 OPENJSON Transforms JSON text to table … or transform into a relational table Number Date Customer Price Quantity SO43659 T00:00:00 MSFT 59.99 1 SO43661 T00:00:00 Nokia 24.99 3 3 FOR JSON Formats result set as JSON text. Export any result set as JSON Store it as an NVARCHAR column 1 © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

19 JSON to table @json: Query with OPENJSON table-valued function:12/6/2017 JSON to table @json: [ { "Order": { "Number":"SO43659", "Date":" T00:00:00" }, "Account": "Microsoft", "Item": { "Price":59.99, "Quantity": } }, { "Order":{ "Number":"SO43661", "Date":" T00:00:00“ }, "Account": “Nokia“, "Item":{ "Price":24.99, "Quantity": } } ] Query with OPENJSON table-valued function: SELECT * FROM OPENJSON WITH ( Number varchar(200) N'$.Order.Number', Date datetime N'$.Order.Date', Customer varchar(200) N'$.Account', Quantity int N'$.Item.Quantity' ) Input table data: Number Date Customer Quantity SO43659 T00:00:00 Microsoft 1 SO43661 T00:00:00 Nokia 3 © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

20 Table to JSON JSON output: Input table data:12/6/2017 Table to JSON Input table data: JSON output: [ { "Order": { "Number":"SO43659", "Date":" T00:00:00" }, "Account": "Microsoft", "Item": { "Price":59.99, "Qty": } }, { "Order":{ "Number":"SO43661", "Date":" T00:00:00“ }, "Account": “Nokia“, "Item":{ "Price":24.99, "Qty": } } ] Number Date Customer Price Quantity SO43659 T00:00:00 MSFT 59.99 1 SO43661 T00:00:00 Nokia 24.99 3 Query with FOR JSON clause: SELECT Number AS [Order.Number], Date AS [Order.Date], Customer AS Account, Price AS 'Item.Price', Quantity AS 'Item.Qty' FROM SalesOrder FOR JSON PATH © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

21 Indexing on JSON values12/6/2017 Indexing on JSON values CREATE TABLE SalesOrderRecord ( Id int PRIMARY KEY IDENTITY, OrderNumber NVARCHAR(25) NOT NULL, SalesOrderItems NVARCHAR(MAX) CONSTRAINT sales_od_IS_JSON CHECK ( ISJSON(SalesOrderItems)>0 ), Price AS JSON_VALUE(SalesOrderItems, '$.Order.Price') ) CREATE INDEX idx_JsonPrice ON SalesOrderRecord(Price) INCLUDE (Id, OrderNumber) 1 2 3 © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

22 Support for new scenarios (coming soon)JSON analytics with the columnstore index Excellent compression and batch mode processing for large amount of data High rate ingestion and low latency querying with In- memory OLTP Lock-free execution and natively compiled modules Analytics queries with JSON_VALUE

23 Combine Temporal and JSON for ultimate productivityMicrosoft Ignite 2016 12/6/ :06 AM Combine Temporal and JSON for ultimate productivity Bewons is the social network for artists, built on Azure SQL DB. Exposing auditing information in JSON format is a very common scenario. “Temporal Tables replaced cumbersome solution with triggers and speeded up DMLs by 25%. Now I enjoy it when CTO asks me "what was the value of this record 3 months ago?” “Thanks to support in the database I’m able to write simple and efficient code that works directly with JSON data.” “Most of our stored procedures combine Temporal and JSON to get best of the platform!” Pasquale Ceglie, SQL Server Database Administrator/Developer © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

24 App/DB interaction diagramMicrosoft 2016 12/6/ :06 AM App/DB interaction diagram Initial data load (JSON) Application Request for data Database Users 1 2 Processing 3 Request for data Partial data refresh (JSON © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

25 Demo: Speaking JSON with the web app

26 Other T-SQL enhancementsMicrosoft 2016 12/6/ :06 AM Other T-SQL enhancements © 2016 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

27 Already available Coming soon! STRING_SPLIT DROP IF EXISTS12/6/2017 Already available STRING_SPLIT Splits a string on a provided separator. DROP IF EXISTS Conditional drop for SQL objects. AT TIME ZONE Converts input date to target time zone. COMPRESS/DECOMPRESS Compresses the input expression using GZIP algorithm. Coming soon! STRING_AGG Aggregates strings and adds a separator TRANSLATE Replaces multiple characters in a go. TRIM Trims a string on both sides. BULK INSERT Azure blobs Easily imports data from blob storage. © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

28 12/6/ :06 AM Takeaways SQL Server is the modern platform to power your next app Move your data back in time with Temporal Tables Connect your database to modern apps and services using JSON © 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

29 Call to action Try out database programmability features now!Check out online materials to learn more details Getting Started With Temporal Tables JSON support in Azure SQL Database Programmability Features Overview: SQL Server 2012, SQL Server 2008 Reach out should you have any questions Drazen Sumic ( ) Borko Novakovic ( )

30 Please evaluate this session12/6/ :06 AM Please evaluate this session Your feedback is important to us! From your PC or Tablet visit MyIgnite at From your phone download and use the Ignite Mobile App by scanning the QR code above or visiting https://aka.ms/ignite.mobileapp © 2014 Microsoft Corporation. All rights reserved. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

31