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
• Programmers – Any language programmer who deals with SQL Server
Agenda
Day 1: T-SQL with Basic 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
Logical Query Execution Plan
Every programming language has its own execution plan. T-SQL queries do have its own execution plan but hardly people care to understand the same. In this session we will discuss how SQL Server understands different elements of the query. We will understand in which order any T-SQL query is being executed and what is the sequence of the algorithm. This is extremely important session to learn before even beginning to write any T-SQL. Often oversight on this subject, leaves developers confused forever while writing T-SQL.
Introduction to New DataTypes – A Real World Talk
In SQL Server 2008 there are several kind of new datatypes introduced. Few datatypes extended earlier features and few have introduced totally new techniques. We will go over it from entire different angle and learn about new datatypes. A simple change in datatype can change how the architecture is built. This session will cover all the new datatypes and related enhancements. We will talk about hierarchy datatypes and spatial datatypes as well.
LAB: Date and Time Exercise
Table Expressions – View, CTE, Temp Tables and more…
In this session we will talk about various table expressions. It is very important to learn about that as this is the only expressions in database which holds our data and returns us result. 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. View has existed forever but is they really useful with introduction to the new features is something we will discuss in depth.
LAB: Implementing Recursive Common Table Expression
Day 2: T-SQL with Basic Solid Fundamentals
The Set Theory Joins – An Advanced Starter – An Internal Look
In this session we will take a look at joins how we understand it at the moment. Once mastered the simplicity of the joins we will take a deep look at the subject. We will talk about how internally they are build and what is the real usefulness of it. We will learn the algorithm which drives the join we know and the join SQL Server understands.
LAB: Creating various joins scenario using various methods
Views – The Limitations and Capabilities
SQL Server Views is very interesting concept. There are plenty of the limitations of the views. Views can often be the cause of reduction of the performance and view can come in a way of writing normal T-SQL. Learning the limitations of the views is very important. We will cover the capabilities and advantages of the views once we learn the limitations.
This session will address non-indexed views and indexed views.
LAB: Creating View which improves performance of the query
Triggers – DML, DDL and Advantages
There are many different sides of triggers. Some people call it enemy of DBA and some call it angle of DBA. In this session we will learn what different methods of creating the triggers are. The real learning will be where we will discuss why one should not create trigger. We will go over various concepts where we will talk about how one can use alternate techniques and do not use triggers. The implementation of the trigger is very different from views and stored procedures and we will discuss the same in detail.
The interesting side is the DDL trigger is that it can be used for policy management.
LAB: Creating Trigger to manage policy
Stored Procedure – Myths, Best Practices and Advance Subjects
Stored Procedure are advanced SQL Server 2008 concept. This concept is often confused by developers in terms of transactions and error handling. We will first clear few myths related to Stored Procedures and then we will learn the advanced concepts. We will learn all the best practices related to Stored Procedures in these single and highly interactive sessions.
LAB: Implementing best practices for sample Stored Procedure
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
Beginning with Indexes – The Primer
Index is extremely important element of SQL Server and it is often ignored. We will talk about the basics of indexes in this session. From B-Tree to SQL Server Internal – we will do the complete journey understand each stone of in simple words. This session will provide all the necessary insight to developers who are writing T-SQL queries every day. Learning this session will change how one person is writing T-SQL query. This session will talk about many more thing besides talking about clustered and non-clustered index.
LAB: Creating Indexes – Clustered, Non Clustered
Advancing with Indexes – The Advance Primer
After attending this session developer will be able to write index which can improve any specific queries. We will go over all the pitfall index presents to developers. Once learned about the pitfalls developer will be able to also understand the negative impact indexes can bring on table due to over use of this subject.
We will discuss various subjects like filtered index, effect of statistics, Included Index and covering Index. This session will enable a basic developer to learn about tricks and tips of the improving performance of SQL Server.
LAB: Writing query to improve T-SQL 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)
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.