Level: Intermediate
This full-day workshop will get you up to speed on powerful features for developers in SQL Server 2014. Lenni will pull no punches as he rips through the most important SQL Server programmability points in this intensive demo-packed tour.
Part 1 - SQL Server Data Tools (SSDT). We'll start with an introduction to SQL Server Data Tools (SSDT), a powerful integrated development environment for designing, testing, and deploying SQL Server databases - whether you're working locally or remotely, connected or offline, or in the cloud on Windows Azure - all from right inside of Visual Studio. Then we'll dive right into extensive demos that showcase the most important SSDT features. Lenni will demonstrate how SSDT can be used to develop for (and deploy to) on-premise and Azure SQL databases. You'll learn how to use features such as code navigation, IntelliSense, and refactoring with your database model-indispensable tools previously available only for application development in Visual Studio. We'll also cover the declarative model that allows you to design databases offline and under source control right from within Visual Studio.
Part 2 - T-SQL Features. Next, we'll explore 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. Work with the latest date and time data types, which include support for time zone awareness. Then learn about the newest T-SQL enhancements in SQL Server 2012, such as windowing (OVER clause) enhancements, running and sliding aggregates, 22 new functions, the THROW statement, server-side paging, the SEQUENCE object, and new metadata discovery techniques.
Part 3 - Beyond Relational. Then we'll examine the powerful "beyond relational" features, including the latest additions 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. Learn how to use the hierarchyid data type to cast a hierarchical structure over any relational table. Dig into FILESTREAM and FileTable to discover how you can finally enjoy the native ability to store large binary objects in the file system transparently. We'll also cover the geospatial data types that enable you to integrate location-intelligence into the database, and build several location-aware applications on top of these new data types. We'll also cover the rich XML support provided by the relational engine, including XSD, XPath, and XQuery.
Part 4 - In-Memory OLTP. Next we'll move on to 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.
Part 5 - Enterprise Features. We'll wrap up with powerful enterprise-level data warehousing and security features including Change Data Capture, SQL Server Audit and Transparent Data Encryption. With Change Data Capture (CDC), you can monitor and record changes made to transactional tables without requiring any application changes or resorting to triggers. Enabling Transparent Data Encryption (TDE) automatically encrypts the entire database in the background, without increasing database size, and without requiring any code. And with SQL Server Audit, virtually any action taken by any user can be recorded for auditing in either the file system or the Windows event log.
You'll learn a ton of new SQL Server features in this information-packed day!