Microsoft SQL Server (MS-SQL) Mastery

Table of Contents

1. What is MS-SQL? (Relational Design & SQL Server)2. Data Retrieval: SELECT, WHERE & Wildcards 3. Query Ranges: IN & BETWEEN 4. Results Control: ORDER BY, TOP & Aliases 5. Data Mutations: INSERT, UPDATE & DELETE 6. Advanced Joins & Set Operations (UNION)7. Schema Creation & DDL Constraints 8. Schema Evolution: ALTER, DROP & TRUNCATE 9. SQL Views: Virtual Dynamic Tables 10. T-SQL Date Power & CONVERT formatting 11. Null Handlers: ISNULL() vs. COALESCE()12. Aggregate Power: GROUP BY & HAVING 13. Indexes internals: Clustered vs. Non-Clustered 14. Stored Procedures, Functions & Cursors 15. Modern T-SQL: CTEs & Window Functions 16. High Availability & Always On Groups 17. T-SQL Variables & Scope (DECLARE & SET)18. Conditional Logic: IF...ELSE & CASE WHEN 19. Loops & Iterations: WHILE & BREAK 20. Error Handling: TRY...CATCH & THROW 21. Temporary Tables vs. Table Variables 22. Global Temporary Tables (##Temp)23. The OUTPUT Clause: Capturing mutations 24. Dynamic SQL & Injection Prevention (sp_executesql)25. Self Joins & Hierarchical Tree Querying 26. Cross Joins (Cartesian products)27. Semi-Joins & Anti-Joins (EXISTS vs. IN)28. The APPLY Operator (CROSS APPLY vs. OUTER APPLY)29. Set Operations: EXCEPT & INTERSECT 30. Common Table Expressions: Recursive CTEs 31. Data Transformations: PIVOT & UNPIVOT 32. Subqueries: Correlated vs. Non-Correlated 33. Clustered Index Seek vs. Scan Internals 34. Non-Clustered Index Lookups (Key Lookups)35. Covering Indexes & Included Columns (INCLUDE)36. Filtered Indexes (Partial Index Architectures)37. Columnstore Indexes: Data Warehouse Architecture 38. Index Fragmentation & Page Splits (Fill Factor)39. Index Maintenance: Rebuild vs. Reorganize 40. Database Statistics & Query Plan Internals 41. ACID Transactions & Concurrency Architectures 42. Isolation Levels: Read Uncommitted & Read Committed 43. Advanced Isolation: Repeatable Read & Serializable 44. Optimistic Isolation: Snapshot Isolation & RCSI 45. Concurrency Locks: Shared (S) vs. Exclusive (X)46. Intent Locks & Schema Lock Architectures 47. Deadlocks: Detection, Resolution & Priority 48. In-Memory OLTP: Memory-Optimized Tables 49. DML Triggers: AFTER Triggers 50. DML Triggers: INSTEAD OF Triggers 51. DDL Triggers: Server & DB Security Audits 52. Scalar User-Defined Functions (Performance Pitfalls)53. Table-Valued Functions (iTVF vs. mTVF)54. CLR Integration: Running C# in SQL Server 55. SQL Server Agent: Automating Database Jobs 56. Database Mail: SMTP System Alert Piping 57. Storage Internals: Pages (8KB) & Extents (64KB)58. Data File Internals: MDF, NDF, and the LDF Log 59. Transaction Log Architecture: Virtual Log Files (VLFs)60. TempDB Internals: Allocation Bottlenecks (PFS, GAM)61. Buffer Pool Internals: Data Cache vs. Plan Cache 62. Database Filegroups & Physical Data Partitioning 63. Partitioned Tables & Partition Swapping Mechanics 64. Partition Functions & Partition Schemes 65. Database Mirroring: Redundancy & Safe Failover 66. Log Shipping: Automated Scheduled Log Backups 67. Transactional Replication: Publisher & Subscriber 68. Failover Cluster Instances (FCI): Shared Storage HA 69. Always On Availability Groups: Read-Scale Clusters 70. Backups Internals: Full, Diff, and Log Backups 71. Point-in-Time Restore Operations (STOPAT log recovers)72. Database Corruption & DBCC CHECKDB Diagnostics 73. Query Store: Historical Plan Performance Tracking 74. SQL Server Profiler & Extended Events (xEvents)75. Dynamic Management Views (DMVs): OS Wait Stats 76. Parameter Sniffing Diagnostics & Optimization 77. SQL Server Memory Management: Buffer Pool Limits 78. CPU Optimization: MAXDOP & Cost Thresholds 79. Mathematical Database Sizing & IOPS Planning 80. Hybrid Cloud Architecture: Azure SQL DB vs. VMs

Microsoft SQL Server (MS-SQL) Mastery Hub

Back to Home