Level: Intermediate
Andrew Brust
Senior Director, Market Strategy and Intelligence
Datameer
This full-day workshop will get you up to speed on powerful new features in SQL Server 2008, 2012, and 2014. Lenni and Andrew will pull no punches as they rip through the most important SQL Server features for developers in this intensive demo-packed tour.
Part 1 - T-SQL Features. We'll start with the many enhancements made to T-SQL. Learn how to use table-valued parameters to marshal entire sets of rows across the network from client to server, and to pass them between your stored procedures and UDFs. Find out about MERGE, a powerful DML statement that combines the capabilities of four (or more) separate operations, and INSERT OVER DML, which enhances your ability to capture change data from the OUTPUT clause of any DML statement. Then preview the T-SQL enhancements coming in SQL Server 2012, such as windowing (OVER clause) enhancements, 22 new functions, the THROW statement, server-side paging, the SEQUENCE object, and new metadata discovery techniques.
Part 2 – Data Warehousing and Analytics. From the world of T-SQL we'll move on to a discussion of using SQL Server for data warehousing and analytics. We'll see how to architect data schemas for optimal performance in reporting and data discovery scenarios, we'll look at column store technology and SQL Server columnstore indexes and we'll discuss Microsoft's Analytics Platform System (APS) a special version of SQL Server built for data warehousing and Big Data.
Part 3 – Big Data and Data Discovery. Next, we'll investigate Apache Hadoop, Big Data and Microsoft's stack for data discovery and visualization. In this section, we'll cover what Apache Hadoop is, discuss its integration with SQL Server in Microsoft's Analytics Platform System (APS) and look at several demos of Azure HDInsight, Microsoft's cloud-based Hadoop service. We'll finish with look at how to visualize data from your data warehouse and/or Hadoop, using Microsoft's Power Query, Power View, Power Pivot and Power BI.
Part 4 – Beyond Relational. As a follow-up to our coverage of analytics and Big Data, we'll next examine the "beyond relational" features in SQL Server 2008, and the latest additions added in SQL Server 2012. These features will get you thinking outside the box with respect to the types of data that can be managed by a relational database system. Dig into FILESTREAM (and SQL Server 2012 FileTable) and learn how you can finally enjoy the native ability to store large binary objects in the file system transparently, and with full transactional capabilities. The geospatial data types enable you to integrate location-intelligence into the database, and we'll build several location-aware applications on top of these data types.
Part 5 – In-Memory OLTP. We'll finish up with the new in-memory OLTP capabilities (code name "Hekaton") added in SQL Server 2014. Learn how to achieve impressive performance gains (up to 30x!) by identifying those tables that can improve performance simply by implementing them as memory-optimized tables in the database. These tables are fully durable and function as ordinary tables, but do not require the same latching and locking mechanisms as disk-based tables, which boosts performance significantly. You will also see how to create natively compiled stored procedures (which actually converts T-SQL to C and then compiles to a DLL) to achieve even greater performance when accessing memory-optimized tables.
You'll learn a ton of new SQL Server features in this information-packed day!
You will learn:
- T-SQL enhancements added to SQL Server 2008 and SQL Server 2012
- The latest data warehousing and analytics features in SQL Server, the world of Big data with Microsoft HDInsight and how to enable data discovery with Power BI and related technologies
- Unstructured data storage, including native file streaming, and the hierarchical and geospatial data types
- In-memory OLTP technologies added in SQL Server 2014 for up to 30x improvement in performance