May 25

Written by: Steve Gray
5/25/2010 12:48 PM 

I spend a lot of time optimizing queries, and I need a reference base. I don’t want to plagiarize content so I’ll just link to it. Like anything else on this site, we’re interested in your comments

Assert

The Assert is used to verify a certain condition, it validates a Constraint on every row to ensure that the condition was met. If, for example, our DDL includes a check constraint which specifies only two valid values for a column, the Assert will, for every row, validate the value passed to the column to ensure that input is consistent with the check constraint.

From Simple-talk.com

 

BookMark/Key Lookup

Used to fetch values via a clustered index, when the required data isn’t in a non-clustered index

SQL 2000 icon

SQL 2005 & 2008 icon

From Simple-talk.com

 

Clustered Index Scan

<need content>

 

Compute Scalar

Performs a scalar computation and returns a computed value. This calculation can be as simple as a conversion of value, or a concatenation of values

Compute Scalar

From Simple-talk.com

 

Concatenation

Receives one or more input streams and returns all the rows from each input stream in turn. We can see its effect whenever we use the Transact–SQL UNION ALL command.

Concatenation

From Simple-talk.com

 

Hash Operator

 

Spools

There are five types of Spool operations, each with its own behavior, and idiosyncrasies, but they all share in common the way that they save their intermediate query results on the TempDB database, and use this temporary area to search a value.

 

Eager Spool

From Simple-Talk

 Lazy Spool

From Simple-Talk

 Row Count Spool

From Simple-Talk

The Row Count Spool operator is a simple component in an execution plan that can make an enormous difference to SQL query plans that would otherwise read a table on every row of the inner input to a nested loop.

The Row Count Spool operator just scans an input, counting how many rows are present, and returns the number of rows without any of the data they contained

 Table Spool

  Non-Clustered Index Spool

From Simple-Talk
The Index Spool is used to improve the read performance of a table which is not indexed and, as with other types of Spool operators, it can be used in a “Lazy” or an “Eager” manner. So, when SQL Server needs to read a table that is not indexed, it can choose to create a "temporary index" using the Spool, which can result in a huge performance improvement in your queries.

 

From Simple-talk.com

Stream Aggregate

<need content>