The class teaches students tips and tricks for writing highly efficient, performant and scalable stored procedures, batches, transactions. Participants will be shown how to properly code functions, joins, sub-queries, CTEs, and modularize their data access layer code for effectiveness. They will learn how to change their code and use specific code constructs to get the best performance and scalability. Attendees will see some untraditional techniques to get the optimal code performance and avoid pitfalls. Additionally, students will learn the root causes of locking, the role of properly written search arguments, the importance of index optimizations, and the stored procedure parameterization benefits and issues. They will understand how to avoid specific pitfalls in logical database design, how to write effective code and avoid code problems. They will be aware of upcoming technologies in new versions.

• DB Developers who are willing to gain deeper knowledge and understanding of the technology
• .NET developers who are responsible for database design and server-side development

• Make proper decision which statements and features to use, which one to avoid and how to architect an effective data access layer
• Have a deep understanding of how the code executes in order to avoid sending bad code to the database
• Design proper scalable and performant database programming layer
• Gain a mastering level of experience and knowledge in T-SQL

Module 1. SQL Server Architecture Overview and Tools

Module 2. Data and Index structures

Module 3. Complex T-SQL statements

Module 4. Query Execution and Query Plans

Module 5. T-SQL Programming Objects

Module 6. Transactions and lock management

Module 7. InMemory OLTP

Module 8. Loading and deleting data in large tables