Advanced Querying Microsoft SQL Server with Transact-SQL
Course Code: TSQLA
Duration: 2 days
Advanced Querying Microsoft SQL Server with Transact-SQL Course Overview
The Advanced Querying Microsoft SQL Server with Transact-SQL course is designed to give delegates practical experience in using more complex Transact-SQL techniques.
Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.
Course Objectives
To provide more advanced coding skills for those already familiar with using basic Transact-SQL or as a follow-on course for those who have previously attended the Querying Microsoft SQL Server with Transact-SQL course.
Who will the Course Benefit?
IT Professionals and non-technical 'power users' who are required to implement more complex Transact-SQL queries.
Skills Gained
The delegate will learn and acquire skills as follows:
- Creating and using Views
- Using table expressions
- Enhanced Grouping and Pivoting Data
- Using Windowing ranking, offset and ranking functions
- Creating and managing Tables
- Creating and using Sequences and Synonyms
- Creating and managing Indexes
- Obtaining schema metadata from data dictionary
- Improving Query Performance
Advanced Querying Microsoft SQL Server with Transact-SQL Training Course
Course Introduction
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
Session 1: USING TABLE EXPRESSIONS
- Creating and using Views
- Using Inline Table-Valued Functions
- Using Derived Tables
- Using Common Table Expressions
Session 2: PIVOTING AND ENHANCED GROUPING
- Using PIVOT and UNPIVOT in Queries
- Working with GROUPING SETS
- Using ROLLUP, CUBE,GROUPING and GROUPING_ID
Session 3: USING WINDOWING FUNCTIONS
- Using Window AGGREGATE functions in Queries
- Using Window RANK functions in Queries
- Using Window OFFSET functions in Queries
Advanced Querying Microsoft SQL Server with Transact-SQL Training Course
Session 4: CREATE AND MANAGE TABLES
- Creating tables
- Datatypes and Constraints
- Altering and Dropping tables
- Creating temporary tables
- Copying tables
- Creating and using Sequences and Synonyms
- Obtaining metadata
Session 5: INDEXES
- Index types
- Creating indexes
- Performance considerations
- Filtered indexes
- Indexes on View
- Indexes with INCLUDE
- Dropping indexes
- Obtaining metadata
Session 6: METADATA
- INFORMATION_SCHEMA views
- Sys views
- Comparison between INFORMATION_SCHEMA and sys views
- Obtaining metadata in SSMS
Session 7: IMPROVING QUERY PERFORMANCE
- Factors in Query Performance
- Displaying Query Performance Data
- Course technical content is subject to change without notice.
- Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.
Requirements
A working knowledge of SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite Querying Microsoft SQL Server with Transact-SQL course.
Pre-Requisite Courses
Further Learning
Course Reviews
Very impressed with the training content and how useful I found it. John, the trainer, was also great, very accommodating and eager to share useful tips and tricks and answered all my questions. I also thought it helped that the workshop examples were usually on the same table, so that could just focus on practicing the specific content. The workshop examples were very well thought out and really helped with letting the acquired knowledge sink in.
Diana - Net Zero and Sustainable Procurement Man - NHS - March 2024
Very pleased with the content of the course and professional approach of tutor who's knowledge was overwhelming - fantastic approach which gave me a great understanding.
Tomasz - Automation Support Technician - Postal/Distribution
John - thank you for another excellent training course.
Dan - Commercial Officer - Utilities
Brilliant course, lovely attitude from Kevin. Never felt silly for not knowing answers and felt able to keep trying without being afraid to ask questions when needed.
Chrissy - System Specialist - Charity
I really enjoyed the course and found it very helpful. My lecturer was very professional and helpful with great ability to explain complicated terms.
Anna - Qaulity Advisor
Fantastic course, clearly delivered by John - I just need to practice now!
Leonie - Assistant Contracts Manager
Public Scheduled Events
Classroom & Live Virtual Instructor-Led Training
Duration: 2 days
Price: £870.00 exc. VAT
|
| |||
|---|---|---|---|
| Start Date | Options | Spaces | |
| 19 Dec 2024 | 
|
Spaces | Book Now |
| 23 Jan 2025 |
|
Spaces | Book Now |
| 20 Feb 2025 |
|
Spaces | Book Now |
| 27 Mar 2025 |
|
Spaces | Book Now |
| 01 May 2025 |
|
Spaces | Book Now |
| 12 Jun 2025 |
|
Spaces | Book Now |
| 10 Jul 2025 |
|
Spaces | Book Now |
| 14 Aug 2025 |
|
Spaces | Book Now |
| 18 Sep 2025 |
|
Spaces | Book Now |
| 23 Oct 2025 |
|
Spaces | Book Now |
Live Virtual Classroom
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.
Our Customers Include
Our Course Curriculum