Following is SQL statement’s Logical Order of Operations (not particular to SQL Server):
- GROUP BY
- Aggregations (COUNT, MAX, etc)
- UNION, INTERSECT, EXCEPT
- ORDER BY
- LIMIT, FETCH, TOP
That is why, running following SQL statement in SQL Server:
SELECT FirstName, LastName, COUNT(*)
GROUP BY FirstName
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
Another not-so-obvious example:
SELECT FirstName, COUNT(*)
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.
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.
Little Kendra explains in more details: https://littlekendra.com/2016/12/20/actual-time-statistics-in-execution-plans-elapsed-cpu-time-and-more/
A database table that doesn’t have clustered index. As opposed to 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.
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.
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.