Query Plan(03) – Operators

Query plan constists of set of steps as I mentioned previously. These steps also called operators. The data flows from right operators to left. Each operator can also be called node. The SELECT is not a node. The node number is from left to right, for instance, Filter is Node 0, Compute Scaler is Node 1, and Clustered Index Scan is Node 2. By looking at this plan, you might roughly tell what the query does behind – The data is acquired by scanning a clustered index on syscolpars table(Clustered Index Scan operator). Rows are sent to Compute Scalar operator for calculations. After the calculation rows are sent to filter operator, data cannot match the condition will be discarded and data satisfis the predicates will be output to client.

Continue reading “Query Plan(03) – Operators”

Query Plan (02) – Set of Steps

Query plans are merely set of steps that will be running in specific order to achieve user defined operation(s). Such ideas are widely presented in all known and unknown areas. In order to light up a fire, someone might find a piece of stick, a rock and hays, scratch the stick on the rock, make the temporature of the stick high enough to light itself or the hays. But you may just go to grocery store to buy matches or lighters and use them to burn the hays. Mapping those 2 approaches in SQL Server, those are 2 query plans.

Continue reading “Query Plan (02) – Set of Steps”

Query Plan (01) – T-SQL vs Programming Language

A computer is a programmable hardware unit with many components integrated. CPU is one of the most important components which provides set of functionalities for calculations and memory/IO access. CPU can load data from memory to registries(memory units in CPU), perfrom calculation based on loaded data, and then following instructions within loaded data saves the result back to memory. Let’s say you want to add 2 values together, assuming they are all in the stack. You need to move them to AX and BX registry, perform ADD, and then move result back to stack.

POP AX //read an integer from stack to AX register
POP BX //read another integer from stack to BX register
ADD AX,BX // add 2 32-bit itegers together and save the result into AX
PUSH AX //save result back to stack

After result is saved to the stack, other functions can POP and perform code to show it on the screen. (back old days when MS DOS was prevalence, INT 21 can show charactors on the screen).

Continue reading “Query Plan (01) – T-SQL vs Programming Language”