SQL 2014 Developer, Part 12 of 13: Coding Techniques
As business systems become more intricate, so does the T-SQL development needed to get at that data. In this course we’ll discuss some of the challenges facing developers including complex data types and structures the “art” of writing efficient queries and how to tackle and simplify multi-step processes. First we’ll discuss data types, implicit and explicit conversion, and precedence. Next, we’ll move on to discuss working with multinational data and understanding collation—which are SQL Server’s rules for sorting and comparing strings. We’ll discuss working with and storing hierarchical data using the hierarchyid data type. Next you’ll learn that even little things make a difference. We’ll explore how to boost data operations by using JOINs rather than subqueries, and how to check for inclusion using EXISTS. We’ll finish up by discussing temporary tables, table variables, user-defined table-valued functions, CTEs, worktables, and user-defined table type parameters. I’ll show you how using implicit transactions can help in the debugging process and why keeping things simple usually makes the most sense.
Lesson 1:
- Complex Data and Structures
- Issues with Data Types
- Demo: CAST and TRY_CAST
- Working with Multinational Data
- Demo: Collation
- Working with Hierarchical Data
- Demo: Hierarchical Structure.
Lesson 2:
- Writing More Efficient Queries
- The EXISTS Clause
- Joins vs. Subqueries
- One-Pass Queries
- Demo: One-Pass Queries
- Writing More Efficient Queries
- Using Temporary Tables
- Table Variables
- User-Defined Table-Valued
- Demo: UDFs
- Common Table Expressions
- Worktables
- User-Defined Table Type Parameters
- Demo: CTEs
- Demo: Worktables
- Demo: Worktables Select.
Lesson 3:
- Working with Complex Queries
- Using Implicit Transactions
- Demo: Implicit Transactions
- Keeping it Simple
- Demo: Simplifying Procedures
- Demo: Create a Stored Procedure.
