Download PDF version
Objective
This workshop covers all the basic concepts of T-SQL. T-SQL has two sides – the features exist from early versions but not explored properly and newly introduced features. This workshop is created taking best of the both of the worlds and mixing it with real world scenarios. Attendees will learn lots of insights of T-SQL and will be able to go back and right away apply the newly learned knowledge in production environment.
Description
This 3 day workshop covers best of the best concepts and practices from popular courses offered by Solid Quality Mentors. Instead of learning theory only, the workshop focuses on providing real world experience by using demos and scenarios derived from customer engagements. The workshop is uniquely structured. Sessions are discussion based and designed to be an interactive between instructor and participants for an optimal learning experience.
The workshop is intended to be immersion-based where participants will have plenty of opportunities to get deeply involved in the concepts presented by the instructor. The best participants will receive special educational material from the instructor as further follow up.
Each learning session is followed up with Hands-On Lab (HOL). Each Hands-On Lab is carefully crafted to give maximum exposure to the subject discussed in session. After doing Hands-On Lab the learning concept will be strengthen and understood. This way attendee can be productive and ready to excel the tasks at work based on the learning experience from the workshop.
The last hour of the workshop is kept reserve to solve attendees’ real world problems and pressing issues at work. We will sit together and try to resolve them. If the subject is interesting to other attendees then we will also discuss generically. We will get our hands dirty with code and will do our best to reach to the solution.
Target audience
This seminar is intended for following audiences:
• SQL Developers – Professionals who are responsible for writing optimal SQL Query
• SQL DBA – Professionals who are responsible for performance of the server
• Programmers – Any language programmer who deals with SQL Server
Agenda
Day 1: T-SQL with Advanced Solid Fundamentals
Do you know? – 10 Questions in 10 Minutes
This session is very unique and serves as an ice breaker between instructor and attendees. This unique session contains 10 different performance related questions. Attendees are will participate and discuss the questions and its answers. These 10 minutes also serves as the introduction to the class as well gives idea to attendees what they are learning in next 3 days.
Demo: Each Question will be explained using sample T-SQL Script
Date, Time and New Datatypes – A Real World Talk
In SQL Server 2008 there are several kind of new datatypes introduced. Date and Time datatypes are enhanced with many different variations. It is very important to understand the new datatypes and use them at proper place. A simple change in datatype can save lots of database space as well dramatically impact performance. This session will cover all the new datatypes and related enhancements.
LAB: Date and Time Exercise
Common Table Expressions (CTE) and Performance
Common Table Expressions can be thought of a temporary result set. Though, quite commonly it is confused with temporary tables. The implementation of the common table expressions is quite different than other table expressions. If you have changed your code to use CTE and have not got performance improvement, it is quite possible that the understanding of this is quite different. Recursive CTE can provide significant advantage over any other self-referencing structure.
LAB: Implementing Recursive Common Table Expression
The Unknown of Subqueries and Joins – A Deep Dive into Advanced Logic
The SQL Server Engine looks at the Join and Subqueries very differently from how we look at it. Have you ever seen your suqueries converted to joins and vice a versa? Have you ever noticed that your join suddenly start behaving very strange and performance is degraded suddenly? This session will try to address many similar issues and demonstrate solutions.
LAB: Optimizing Join Techniques using Subqueries
Day 2: T-SQL with Advanced Solid Fundamentals
Ranking Functions – A Solution Much Awaited
SQL Server often had to face criticism for its lack of the ranking functions. Ranking functions are handy functions and extremely useful when logic is required by calculating each sets of the rows over each other. Learn about ROW_NUMBER(), RANK(), DENSE_RANK() and NTILE(n) in simple yet affective example.
LAB: Writing paging logic with ranking functions
TOP – Learning the Enhancements of TOP clause from Top to Bottom
Top is very ancient clause. Everybody knows about it. However, the enhancements of TOP clauses are hardly known to everyone. In this session we will see how top can be used along with SELECT, UPDATE, DELETE and the most important with INSERT.
LAB: Using TOP delete specific data from Common Table Expressions (CTE)
The PIVOT and UNPIVOT – Simplified Business Intelligence Presentation
Pivot & Unpivot are used in place of cross tab queries and widely used in when columns are to be converted to rows and vice a versa. This session will explain the complicated and convoluted concept of the Pivot and Unpivot in very easy manner. This session will explain the key reason why Unpivot is not an inverse of Pivot (a common misconception). Learn beyond the common usage of Pivot and Unpivot.
LAB: Remove the common NULL problem in PIVOT operation
Question and Answers & Additional Lab Time
Here is your chance to ask your questions to the instructor which are so far not covered in any of the sessions. You can ask as many as questions as you like as well bring your real world problems. The instructor will guide you towards attaining resolutions to your problems.
Day 3: Performance Tuning T-SQL with Solid Advanced Technique
A 360 View of Index Internals and Externals – 2008 Improvements
Indexes are very crucial element of any schema. They can increase the performance as well reduces the performance as well. Too many or too little is the common dilemma of developer and DBA for ages. The solution of this dilemma is extremely simple, once the technique of Index creation is mastered. This session will go deep into the index creation concept and will give solid understanding of the creating proper indexes.
LAB: Creating Indexes which improves
Sparse Columns – Better Storage, Better Performance – Too Good to be True!
Sparse Column is the one feature which helps to optimize storage.
Sparse Column is the one feature which helps to improve performance.
This feature is too good to be true and hard to believe. In this session we will explore both of the features of the Sparse Column. We will also learn the other side of the Sparse Column as well.
LAB: Transforming regular table to use Sparse Column and improve storage and performance
Unknown but Extremely Useful Features of SQL Server 2008
We will go over many different SQL Server 2008 features in one rapid fire sessions. For example:
Advanced Usage of Dynamic Management Views (DMV)
Spatial Datatypes and Spatial Features
Merge Statement
Index Enhancements
Administration Tools
Lab: User will go over scripts presented by instructor and represent alternate solution
Your Problems and Our Solution
This is the final session. During this session instructor will address all real world problem of attendees and will propose solutions of the same. If you have any performance issue in your production server, this is must attend session to get resolutions.