Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

Browse by Tags

Tagged Content List
  • Blog Post: Subqueries in BETWEEN and CASE Statements

    Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT) CREATE TABLE T2 (A INT, B INT) SELECT * FROM T1 WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs to be evaluated once for each row of T1. Indeed...
  • Blog Post: CTEs (Common Table Expressions)

    CTEs or common table expressions, which are new in SQL Server 2005, provide an easy way to break a complex SQL statement down into smaller more manageable queries. CTEs are is some ways very much like views. Unlike a view which can be created once and used by many SQL statements, a CTE is associated...
  • Blog Post: Decorrelating Subqueries

    In my previous post , we saw some examples where the optimizer is able to take a query with a correlated subquery and rewrite it as a join. For instance, we saw how this simple “in” subquery: create table T1 ( a int , b int ) create table T2 ( a int , b int , c int ) select * from T1 ...
  • Blog Post: Scalar Subqueries

    A scalar subquery is a subquery that returns a single row. Some scalar subqueries are obvious. For instance: create table T1 ( a int , b int ) create table T2 ( a int , b int ) select * from T1 where T1 . a > ( select max ( T2 . a ) from T2 where T2 . b < T1 . b ) The subquery...
  • Blog Post: Subqueries: ANDs and ORs

    In my “Introduction to Joins” post , I gave an example of how we can use a semi-join to evaluate an EXISTS subquery. Just to recap, here is another example: create table T1 ( a int , b int ) create table T2 ( a int ) create table T3 ( a int ) select * from T1 where exists ( select ...
  • Blog Post: Subqueries in CASE Expressions

    In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just evaluate the CASE expression as we would any other...
Page 1 of 1 (6 items)