Roadmap › SQL Server & T-SQL › Module: Indexes and Performance
Lessons in this course (19)
18. Partitioning, columnstore, temporal and security features
AdvancedMediumLesson 18 of 19
- Table partitioning — split a large table by a range (e.g. month) on a partition function/scheme; enables partition elimination and fast
SWITCH/TRUNCATE ... WITH (PARTITIONS (...))for data retention. - Columnstore — column-wise compressed storage; huge gains for analytics/aggregates (data warehouses).
- Temporal tables —
SYSTEM_VERSIONING = ONkeeps full row history; queryFOR SYSTEM_TIME AS OF. - JSON —
OPENJSON,JSON_VALUE,JSON_QUERY,FOR JSON PATH(2016+); nativejsontype in SQL Server 2025. - Security — logins (server) vs users (database), roles,
GRANT/DENY/REVOKE, schemas; Row-Level Security, Dynamic Data Masking, Always Encrypted, TDE (encryption at rest), auditing. - High availability / DR — Always On Availability Groups, Failover Cluster Instances, log shipping, backups: full, differential, transaction log (with FULL recovery model enables point-in-time restore).