Home Remote DBA Service Consulting and Performance Server Health Checks Training Resources Calendar Past Customers What our Students say About SQLskills.com		IED: Immersion Event for Developers   Overview This 5-day class is designed to cover all the information developers should know when coding in SQL Server. It could be used (as has been) as a class geared toward obtaining the SQL Server Developer Certification, although it goes beyond the knowledge required for that certification. Coding for best practices and best performance is stressed throughout. The class focuses on, but is not limited to, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. Coding with "Beyond Relational" feature will be covered as well as T-SQL related coding. Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events FAQ for class costs and other frequently asked questions. Part 1: Using Transact-SQL This section covers T-SQL, the language most often used for writing code in SQL Server. Special attention is paid, not only to covering new SQL Server 2005 - 2012 features, but also to illustrating best practices common throughout. Module 1: Data Types in SQL Server This module covers the data types that are available in SQL Server from a usage point of view. This module covers the T-SQL functions that apply to different data types as well. We'll pay special attention to: Character data - Unicode and Nonunicode, collations, international considerations Integral and floating point numeric series Date and Time types - including SQL Server 2008 expanded date and time types Special data types - table parameters, table types (2008), UNIQUEIDENTIFIER, SQLVARIANT Large data types - Using VARCHAR(MAX)/VARBINARY(MAX) as replacements for TEXT and IMAGE FILESTREAM storage and FileTables .NET data types - Geometry, Geography, and HierarchyID Uniqueness – Identity, sequences, and uniqueidentifiers Integrity constraints Module 2: Writing Effective Queries This module starts out with an overview of the SQL language, illustrating best practices. Evaluating a query for syntactic correctness comes next, because knowing the parts of a SQL query helps to avoid most SQL coding errors. We then proceed to cover common SQL constructs, using a query plan-oriented approach to ensure best performance. JOINs in detail Reading query plans Subqueries and derived tables CASE statement UNION, EXCEPT, and INTERSECT Relational division CROSS APPLY and OUTER APPLY Common Table Expressions Module 3: Summarization and Analysis This module covers T-SQL functions that are used to summarize and group rows, with special attention paid to SQL Server 2005 and 2008 enhancements. We include: Row numbering, ranking, and analytic functions Expanded windowing functions Paging sets of data GROUPING SETS, ROLLUP, and CUBE TOP clause PIVOT operator Writing your own aggregate functions in SQLCLR Module 4: Optimizing Action Statements (INSERT, UPDATE, DELETE, MERGE) Topics covered include: Row constructors INSERTing SETS - SELECT INTO, INSERT-SELECT, and INSERT-EXECUTE UPDATE - updating via JOINs and CTEs INSERTing and UPDATing views DELETE statement - DELETE vs TRUNCATE TABLE MERGE statement - INSERT/UPDATE/DELETE in one statement Optimizing action statements with TOP clauses OUTPUT from action statements Module 5: Solving Difficult Problems in Relational Databases The module covers, in detail, the two most difficult problems in relational development: Hierarchies and Sparse Attributes. We'll pay special attention to SQL Server 2005 and 2008 improvements in these areas. Hierarchical data - trees and graphs, single-parent and multi-parent hierarchies Implementation - adjacency column vs. path enumeration vs. nested sets Querying hierarchical data with recursive Common Table Expressions The HierarchyID data type - an implementation of the path enumeration model as built-in type Modeling sparse attributes - sparse table vs. sparse columns vs. entity-attribute-value vs. XML PIVOT, UNPIVOT and entity attribute value Sparse column support and designing for sparse columns Part 2: Optimizing Transactional/Procedural Code This section covers writing effective code to optimize database functionality. SQLCLR - that is writing procedural code in .NET languages, is also covered. Module 1: Optimizing Transactions This module covers the essential concepts of transactions, that is, composing multiple SQL statements into an atomic unit of work. The topics covered include: Batches and transactions Transaction modes Understanding BEGIN-COMMIT-ROLLBACK-SAVEPOINT transaction nesting Distributed transactions Error handling in T-SQL TRY-CATCH error handling T-SQL vs. SQLCLR error handling Module 2: Transaction Isolation A relational database isolates each user from other users' work using a locking and, in some cases, a combination of locking and versioning. The module covers SQL Server's isolation levels when using locking and versioning. Topics covered include: Common problems in transaction isolation Locking and transactions - how the four transaction isolation levels solve the common problems SQL Server and statement-level consistency Versioning in SQL Server 2005 and beyond Enabling versioning isolation Versioning and transactions - how the two versioning isolation levels solve the common problems Versioning and statement level consistency Versioning uses and drawbacks Enabling and monitoring versioning Module 3: SQLCLR Coding A. Internals of .NET - SQL Server Interaction - This module covers how the CLR runs in SQL Server's process and how things such as memory allocation, appdomains, assembly loading, and threading work. We'll also cover the three levels of user code security and which assemblies are safe to use. This module covers diagnostics and troubleshooting of .NET code. Topics covered include: Cataloging assemblies Assembly dependencies Assemblies allowed in SAFE mode Assembly security SQLCLR and .NET framework interaction Troubleshooting tools B. Programming .NET Procedures and Functions - This module covers the basics of writing .NET procedures and functions. Topics covered include: .NET method signatures for procedures and functions .NET table-valued functions Data type mappings and nullability Parameter types/directions Object-oriented concepts as they apply to SQL Server Module 4: Optimizing Procedural Code - Stored Procedures This module covers the things a programmer can be aware of in order to produce better performing stored procedures. We cover all of the programmer/administrator-controllable areas that can result in slow and suboptimal code. Topics covered include: When and how to optimize code and queries The ten main reasons why queries run slowly Query processing and execution - observing and troubleshooting Using dynamic management views to obtain query information Plan selections, statistics, and indexes Optimizing query plan reuse Parameterization and auto-parameterization Parameter sniffing and query plan reuse How to control plan reuse Choosing the correct table structures - temp table vs. table variables Procedural code and query optimization Query hints, plan forcing, and plan guides - implementing plan stability Module 5: Optimizing Procedural Code - User-Defined Functions Each of the three types of user-defined function has its special usage, but user-defined functions are not stored procedures. In this module, we investigate the best usage of these functions for performance. We'll look at query plans produced and investigate when SQLCLR might be a better choice for some use cases. Topics covered include: Instead-of and after triggers Inserted and deleted tables Determining the number of rows affected Designing views for best performance Indexed views Distributed partitioned views The three types of user-defined functions Limitations of user-defined functions UDFs vs. stored procedures Query plans produced by user-defined functions Drawbacks of database access and scalar user-defined functions SQLCLR vs. T-SQL for scalar user-defined functions Part 3: Beyond Relational Topics This last day covers writing using XML in SQL Server. In addition, more specialized topics such as Service Broker, Full-text Search and Tracking Changes are covered. If time permits, there will be a question-answer session on topics of the students’ choice that are listed as SQL Server Developer exam topics. Module 1: Designing for the Correct Data Model XML is often used for unstructured documents. XHTML pages, book text, and legal documents fit into this category. Maintaining document order is critical. Semi-structured data may contain a definite "outline"-type structure with large text unstructured passages. XML can be stored and used in its native form by using the XML data type, or you can take advantage of XML's hierarchical nature to store the data in multiple relational tables. This is analogous to decomposing an object graph, so making rowsets from XML is decomposition. Recreating the XML hierarchy is known as XML composition. SQL Server has support for composition and decomposition, using this can sometimes result in a more performant application. A. XML data type XML columns XML variables Parsing XML - documents and fragments Using different encodings B. Versioning data using XML schema collections XML data storage fidelity XML check constraints and defaults C. Decomposition functions XML.Nodes method OpenXml D. Composing relational data into XML The "SELECT FOR XML" dialects XML PATH mode Handling NULL database values Module 2: Querying and Modifying XML Being able to effectively query your data is critical in any application, especially with data that contains structure and free-form text. XQuery is the language for querying XML in a database environment. It can be SQL-like in structure. In addition, SQL Server implements a XML modification language using an XQuery dialect specific to SQL Server. A. XML query functions Choosing the right function for the job Using the functions in a XML - relational hybrid Querying a collection of documents B. XQuery usage XPath expressions FLWOR expressions Caveats in expressions Constructing XML with XQuery C. XML indexing Requirements for XML indexes Types of XML indexes Uses of the node table in queries Reading an XQuery query plan D. XML DML Modify as a mutator method Modification operations Locking during modification Logging and rewrites during modification Designing updates for performance Module 3: Asynchronous T-SQL using SQL Server Service Broker Service Broker is queued asynchronous functionality inside the database, defined through database objects with DDL and programmed using DML with some broker-specific extensions. This module looks at Service Broker to implement application decoupling leading to better scalability and reliability. Topics include: Decoupling a traditional stored procedure Service Broker database object basics Service Broker internals Message priority Activation-based programming Conversations, messages, and conversation groups Diagnosing broker-related applications Best practices for broker-based programming Module 4: Full-Text and Semantic Search Full-text search has been an optional component of SQL Server since SQL Server 7. It provides special components, indexes, and T-SQL functions that allow semantic queries and relevance ranking of unstructured data in addition to traditional T-SQL LIKE operations that implement string-based queries. In this module, I’ll discuss the individual components that comprise full text search, as well as how to define and manage full text catalog, indexes, and queries. Full-text indexes – definition and performance-related choices Programming full-text and semantic search Full-text index plans and best practices for performance Troubleshooting full-text search with Dynamic Management Views Questions? If you have any questions about the training classes that SQLskills.com provides, please don't hesitate to send us email!
Privacy Policy. All Rights Reserved. Copyright 2011 SQLskills.com