Roadmap › 2. Database, ETL & BI › Step 28
SQL
SQL Server & T-SQL
Database design and normalisation, data types, constraints, joins, subqueries, CTEs, window functions, stored procedures, functions, views, triggers, indexes, execution plans, transactions, isolation levels, locking, error handling and performance tuning.
Interview priority:★ Must know High priority — asked in almost every interview, learn firstMedium asked regularlyLow good to know
Module 1: Database Design
- 1.Keys, constraints and data typesBasic ★ Must know
- 2.Normalisation (1NF → BCNF) and denormalisationBasic ★ Must know
- 3.DDL vs DML; DELETE vs TRUNCATE vs DROPBasic Medium
Module 2: Querying
- 4.SELECT, WHERE, ORDER BY, TOP, OFFSET-FETCHBasic ★ Must know
- 5.JoinsBasic ★ Must know
- 6.GROUP BY, HAVING and aggregatesBasic ★ Must know
- 7.Subqueries, EXISTS, CTEs and recursive CTEsIntermediate ★ Must know
- 8.Window functionsIntermediate ★ Must know
- 9.Set operators, CASE, PIVOT and MERGEIntermediate Medium
Module 3: Programmability
- 10.Stored proceduresIntermediate ★ Must know
- 11.Functions: scalar, inline TVF, multi-statement TVFIntermediate ★ Must know
- 12.Views, triggers and temporary objectsIntermediate Medium
- 13.Error handling and transactions in T-SQLIntermediate ★ Must know
Module 4: Indexes and Performance
- 14.Clustered, non-clustered, covering and filtered indexesIntermediate ★ Must know
- 15.Execution plans, seeks vs scans and SARGabilityIntermediate ★ Must know
- 16.Statistics, parameter sniffing and Query StoreAdvanced ★ Must know
- 17.Transactions, isolation levels and lockingAdvanced ★ Must know
- 18.Partitioning, columnstore, temporal and security featuresAdvanced Medium
Module 5: Connecting Applications to SQL Server
- 19.Connection strings, authentication and connecting from every technologyBasic ★ Must know
Next course in the roadmap: 29. SQL Server Internals — How the Database Engine Really Works →