The next step in our journey toward writing efficient T-SQL queries is understanding how the SQL Server database engine optimizes a query; we will do so by exploring T-SQL query optimization internals and architecture, starting with the infamous cardinality estimation process and its building blocks. From there, we will understand how the Query Optimizer uses that information to produce a just-in-time, good-enough execution plan. This chapter will be referenced throughout this book, as we apply architectural topics to real-world uses.
Before we get started, it's important to have a common frame of reference about the following terms:
- Cardinality: Cardinality in a database is defined as the number of records, also called tuples, in each table or view.
- Frequency: This term represents the average number of occurrences of a given value in a...