Take the SQL Server Index Quiz!
summary
Six quick, concrete takeaways: a nonclustered index’s key columns are subject to the 900 byte limit but included columns are not; applying a function to a key forces a scan (example on SQL Server 2014 using the Stack Overflow sample shows 8,700 logical reads versus 3 reads after rewrite); the optimizer does not consider physical fragmentation; a unique index allows one NULL; filtered indexes forbid OR and functions but allow NOT =; indexed views require a unique clustered index.
AI-written summary · about
chapters (29)
- 0:00Intro
- 0:55Technical Assistance
- 2:04PASS SUMMIT 2015
- 4:10The best way to learn about indexes
- 5:35Get your pen and paper ready!
- 6:46When creating a Non-Clustered Index, the size of any included columns counts towards the 900 byte size limit
- 7:34Non-Clustered Index B+ Tree
- 11:12Can SQL Server use this index for this query? CREATE INDEX ix_creationbute on dbo. Posts CreationDate
- 12:59Looking at the index...
- 13:16SQL Server can read backwards!
- 14:17Look at the query
- 14:48Functions aren't great
- 16:338,745 logical reads (each)
- 18:53How you write the query matters
- 19:41The Query Optimizer knows how fragmented each index is.
- 20:19The optimizer does NOT consider...
- 23:08But the optimizer DOES consider...
- 24:35How many rows with NULL values can be in an index defined as unique?
- 25:33Time to get controversial
- 26:48Filtered indexes are interesting for this!
- 28:20Which of the following filtered index definitions is valid?
- 29:49This one's tricky!
- 32:26Filtered indexes are very cool
- 32:42When creating an Indexed View, which of the following tasks is required, no matter what? A. Make sure all the underlying tables have Clustered
- 33:46If you answered, "rewrite your queries to force them to use the view"...
- 34:36No matter what, you must always...
- 36:23Indexed views are magical
- 37:07Join me for SQL Server Index Therapy
- 56:43Like What You've Heard?
related talks
57m5 Essential SQL Skills Every Developer Should Know
53mThe Most Important Performance Factor in SQL Server and Azure SQL
25mThe Secret Life of SQL: How to Optimize Database Performance
07mIndeksimi dhe përmirësimi i performancës në SQL
53mIndexing Internals for Developers & DBAs- 53mIndexing Internals for Developers & DBAs
- 53mIndexing Internals for Developers & DBAs
50mHow to choose the right index