SQL Server 2017
- Overview of Database Changes in SQL Server 2017 by Aaron Bertrand
- What You Need to Know about Adaptive Joins over Rowstore
- Puzzle Challenge: Graph Matching with T-SQL Part 1-Concepts
- Ordered Set Functions: What’s New–and Missing–in SQL Server 2017
SQL Server 2016
- Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1
- Columnstore Indexes Versioned Feature Summary
- SQL Server 2016: In-Memory OLTP Enhancements
- SQL Server 2016 In-memory OLTP technical white paper
- First Look at System-Versioned Temporal Tables-Part 1: Creating Tables and Modifying Data
- First Look at System-Versioned Temporal Tables-Part 2: Querying Data and Optimization Considerations
- Tips Concerning Temporal Tables
- What You Need to Know about DATETIMEOFFSET and SQL Server 2016’s AT TIME ZONE Function
- What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1
- What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 2
- What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 3
Intervals
- Interval Queries in SQL Server
- Academic Paper – Managing Intervals Efficiently in Object-Relational Databases
- Article – Part I – A Static Relational Interval Tree
- Article – Part II – Advanced interval queries with the Static Relational Interval Tree
- Article – Part III – Using the Static Relational Interval Tree with time intervals
Various Topics
- Enhancements to SQL Server Column Stores in SQL Server 2014
- SQL Server Columnstore Performance Tuning
- Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
- Temporary Tables in Stored Procedures
- Temporary Object Caching Explained
- Partitioned Table and Index Strategies Using SQL Server 2008
- Arrays and Lists in SQL Server
- The Data Loading Performance Guide
- Dynamic Search Conditions
- Hekaton: SQL Server’s Memory-Optimized OLTP Engine
- SQL Server In-Memory OLTP Internals for SQL Server 2016
- In-Memory OLTP – Common Workload Patterns and Migration Considerations
- Bw-trees
- SQL Server 2014 Upgrade Technical Reference Guide (Download)
- SQL Server 2012 Upgrade Technical Reference Guide (Download)
- SQL Server 2008 Upgrade Technical Reference Guide
T-SQL
- T-SQL Fundamentals Third Edition (MSPress, 2016)
- T-SQL Querying (MSPress, 2015)
- T-SQL Window Functions Second Edition (MSPress, 2019)
- Exam Ref 70-761 Querying Data with Transact-SQL (MSPress, 2017)
Set Theory
- Set Theory and Logic (Dover Publications) by Robert R Stoll
- Set Theory and Its Philosophy : A Critical Introduction (Oxford University Press) by Michael Potter
Relational Model
- Data Modeling Essentials (Coriolis Group Books) by Graeme Simsion
- Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition (Addison-Wesley Professional) by Michael J. Hernandez
- Practical Issues in Database Management: A Reference for the Thinking Practitioner (Addison-Wesley Professional) by Fabian Pascal
- Foundation for Object / Relational Databases: The Third Manifesto (Addison-Wesley Professional) by C. J. Date, Hugh Darwen
- Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design (The Morgan Kaufmann Series in Data Management Systems) by Terry Halpin
SQL
- SQL and Relational Theory (O’Reilly, 2009) by C. J. Date
- The Art of SQL (O’Reilly Media, March 2006) by Stephane Faroult, Peter Robson
- SQL-99 Complete, Really (CMP Books) by Peter Gulutzan, Trudy Pelzer
- Joe Celko’s SQL for Smarties: Advanced SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management Systems) by Joe Celko
- Joe Celko’s Trees and Hierarchies in SQL for Smarties (The Morgan Kaufmann Series in Data Management Systems) by Joe Celko
- Free SQL Server Developer edition
- SQL Server 2016 Wide World Importers sample database (see ER diagrams for schemas: Application, Sales, Purchasing, Warehouse)
- Adventure Works 2014 Sample Databases
- TSQLV5 Sample Database (see ER diagram)
- TSQLV4 Sample Database (see ER diagram)
- TSQLV3 Sample Database
- PerformanceV5 Sample Database
- PerformanceV3 Sample Database
- GetNums Function
- TSQL2012 Sample Database
- Performance Sample Database
Source Code for Recent Conferences:
- Performance-centric T-SQL
- Mastering T-SQL Querying Fundamentals
- Advanced T-SQL Querying and Query Tuning
- Workarounds for T-SQL Restrictions and Limitations
- T-SQL tips and tricks focusing on backdoors and workarounds
- Missing T-SQL querying features for handling NULLs and for data analysis
- Advanced T-SQL Querying and Query Tuning
- 3604, 3605: Direct the output of some DBCC commands to the client or the error log, respectively
- 9481, 2312: Legacy (7.0) and new (2014) cardinality estimators, respectively
- 4137: use most selective estimate (only works with legacy CE 7.0)
- 9471: use most selective estimate (with new CE 120)
- 9472: use independence assumption
- 2389: Ascending keys and auto quick corrected statistics
- 2371: Dynamic auto update statistics threshold
- 652: Disable read ahead
- 8744: Disable Nested Loops prefetch
- 8649: Parallel query plan, (in 2016 SP1 CU2+, USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’) )
- 3917: Get information about eager writes behavior (requires 3604 or 3605 for output)
- 2453: Triggers recompiles for table variables based on thresholds like for other tables
- 272: Disables caching for identity in SQL Server 2012 and later
- 9453: Disable batch mode processing
In SQL Server 2016 SP1 and later you can use the query hint OPTION(USE HINT (‘<hint>’)) instead of some of the trace flags. For details see Developers Choice: USE HINT query hints by MSSQL Tiger Team.
- Itzik’s Articles in SQLPerformance.Com
- Itzik’s Articles in IT Pro Today
- Paul White’s Page Free Space blog
- Paul White @ SQLPerformance
- Erland Sommarskog’s home page
- Dmitry Pilugin’s Notes
- Erik Darling Data
- SQLPerformance.Com
- Craig Freedman’s SQL Server Blog
- Benjamin Nevarez
- Microsoft SQL Server Blog
- SQL CAT blog
- MSSQL Tiger Team Blog
- This Batch Mode Window Aggregate Operator will Change Your Life!
- (coming soon) Boost Your T-SQL with the APPLY Operator (source code can be found here)
- Handling Graphs, Trees, and Hierarchies in Microsoft SQL Server
- Run, Total, Run!
- Seeks and Scans