RSS

Tag Archives: execution plan

SQL Logical Order of Operations

Following is SQL statement’s Logical Order of Operations (not particular to SQL Server):

  • FROM
  • WHERE
  • GROUP BY
  • Aggregations (COUNT, MAX, etc)
  • HAVING
  • WINDOW
  • SELECT
  • DISTINCT
  • UNION, INTERSECT, EXCEPT
  • ORDER BY
  • OFFSET
  • LIMIT, FETCH, TOP

That is why, running following SQL statement in SQL Server:

SELECT FirstName, LastName, COUNT(*)
FROM dbo.User
GROUP BY FirstName

Will throw:

Column ‘dbo.User.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

GROUP BY is run first and grouped User’s columns by FirstName therefore LastName columns and all other columns are not visible by SELECT clause.

Another not-so-obvious example:

SELECT FirstName, COUNT(*)
FROM dbo.User
WHERE COUNT(*) > 0
GROUP BY FirstName

Refer to logical order above, when WHERE clause is executed, COUNT(*) > 0 is not yet evaluated and thus SQL Server throw exception.

Read more here.

Advertisements
 
Leave a comment

Posted by on May 2, 2018 in General

 

Tags: , , , , ,

Elapsed Time of Each Operation in SQL Server Execution Plan

In SQL Server Execution Plan, you can see actual time it takes to run each operation. To do this: right click on the operation > Properties. In the Properties window, there should be Actual Time Statistics that will show elapsed time in millisecond.

elapsed-time-of-each-operation-in-sql-server-execution-plan

Little Kendra explains in more details: https://littlekendra.com/2016/12/20/actual-time-statistics-in-execution-plans-elapsed-cpu-time-and-more/

 
Leave a comment

Posted by on April 13, 2018 in General

 

Tags: , , ,

SQL Server Execution Plan Definition 101

Heap Table:

A database table that doesn’t have clustered index. As opposed to clustered table.

Clustered Table:

A database table that has clustered index.

Non-clustered and Clustered:

Clustered index: index that stores the actual data.

Non-clustered index: a pointer to the data.

Refer to this post for more detail definition.

Table Scan:

Full scan, that is go and read through all of the data, in a table to find the rows that satisfy the query. Normally happen on table without any indices.

Clustered Index Scan:

Full scan in a clustered index table to find the rows that satisfy the query.

Non-clustered Index Scan:

Full scan in a non-clustered index table to find the rows that satisfy the query. Probably happens mostly in query that select all columns.

Clustered Index Seek:

Scan only on clustered index (as opposed to whole table).

Non-clustered Index Seek:

Scan only on non-clustered index (as opposed to whole table).

Key Lookup (Bookmark Lookup):

Happens when the query requests data from columns not present in the clustered index.

RID Lookup:

Happens when the query requests data from columns not present in the non-clustered index.

For understanding of some of these terms with example, this post by Greg Robidoux, “Understanding SQL Server Indexing” is very helpful.

 
Leave a comment

Posted by on August 6, 2011 in General

 

Tags: , , ,

 
%d bloggers like this: