Advanced MS SQL T-SQL Training for Oracle PL/SQL Users - Bespoke

Day 1: Understanding the Fundamentals of MS SQL T-SQL

Module 1: Introduction to MS SQL Server and T-SQL

• Overview of MS SQL Server
  • Key differences between Oracle and MS SQL Server
  • SQL Server Management Studio (SSMS) basics
• Introduction to T-SQL: Core concepts and language syntax
  • Basic SELECT queries in T-SQL
  • Data manipulation: INSERT, UPDATE, DELETE

Module 2: Syntax & Procedural Differences Between Oracle PL/SQL and MS SQL T-SQL

• Syntax Differences
  • Data types: NUMBER in Oracle vs. DECIMAL in SQL Server
  • String functions: NVL in Oracle vs. ISNULL in SQL Server
  • DECODE in Oracle vs. CASE in T-SQL
• Procedural Differences
  • Loops, conditional logic, and error handling in T-SQL vs. PL/SQL
  • Using BEGIN...END, TRY...CATCH in T-SQL for error handling and transactions
• Transaction Management
  • Handling transactions in MS SQL vs. Oracle PL/SQL
  • COMMIT, ROLLBACK, and SAVEPOINT in MS SQL

Module 3: System Functions and Data Types

• Mapping Oracle functions to SQL Server equivalents
  • Oracle's TO_CHAR vs. SQL Server's CONVERT/FORMAT
  • Handling NULL values: NVL vs. ISNULL
  • Date functions and handling time zones in MS SQL
• Data Types in SQL Server
  • Understanding VARCHAR, CHAR, DECIMAL, and others in SQL Server
  • Converting data types and handling precision

Day 2: Advanced Query Techniques & Performance Optimization

Module 4: Query Optimization in MS SQL

• Writing Efficient Queries
  • Identifying and avoiding common query pitfalls
  • Understanding the importance of joins and their performance impact
  • Using EXPLAIN and query execution plans for optimization
• Indexing Strategies for MS SQL
  • How indexes work in MS SQL vs. Oracle
  • Creating and managing clustered and non-clustered indexes
  • Understanding index fragmentation and maintenance
  • Best practices for indexing in MS SQL
• Query Tuning: Identifying and Optimizing Slow Queries
  • Analyzing execution plans to find bottlenecks
  • Optimizing queries using JOIN, WHERE, and GROUP BY
  • Understanding the impact of nested queries and subqueries

Module 5: Handling Large Data Sets and Performance Tuning

• Working with large data volumes in MS SQL
  • Partitioning tables and indexes for performance improvement
  • Using TEMPORARY tables for large datasets
  • Query performance considerations for OLAP vs. OLTP systems
• Advanced Tuning with SQL Server Profiler and Extended Events
  • Using SQL Server Profiler for performance analysis
  • Monitoring long-running queries and system performance

• Handling Compatibility Issues 
  • Compatibility level settings in MS SQL
  • Differences in the way Oracle and SQL Server handle certain features (e.g., sequences, triggers)
• Post-Migration Tuning
  • Adjusting indexes and queries for optimal performance post-migration
  • Real-world examples of post-migration challenges

Module 6: Best Practices for Ongoing Maintenance & Optimization –

Ongoing optimization strategies for SQL Server

• Managing schema changes, data integrity, and backup strategies
• Best practices for ensuring long-term performance and scalability