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).

Assembly showing above is close to a machine language but it still need to be converted to the binary code in order to let CPU understand. The conversion is one-to-one mapping. When 8086 CPU, when it’s powed on, the Instruction Pointer (IP) is always pointing to FFFF0:FFFFF (Segment :- Offset), which is in BIOS area in the addressable memory, to read the first instruction to CPU. From there, the program in the BIOS will guide CPU to initialize all components connected to the computer, jump to BIOS extention address C800:0000, and finally load OS from disk drive and give the control to OS.

Different types of CPU will have different set of instructions. Nowadays almost no one will use it directly to write an business application, use programming languages such as C,C++, C++++ (C#), etc. instead. But assembly is still a tool for debuging. An compiled C application can be de-compiled easily into assembly language. This is also an approache for reverse engineering.

Codes written in higher level programming language must get directly/indirectly converted to instructions series and saved in persistant (or none persistant) storage to let CPU read and excute. The process does the convertion called COMPILE. C and C++ compiler directly translate codes to instruction series. Basic language compiler interprets codes to a series of function invocations that provided by library of the Basic language. C# is similar to Basic, but it translates code to Intermediate Language (IL), and the IL is running on a virual machine like framework.

T-SQL is a Basic like high level language. Before SQL statement is executed, it get transalated into series of operations first, like Basic, then SQL Server excutes the operations in order. That series of operations are called Query Plan or Execution Plan. If the operations in the query plan can be executed by CPU directly after a simple charactor-binary conversion, query plan would be equivalent to an assembly language.

When you write an application in C++, no matter how big the changes the object that application operating with happened, the execution will be always the same for each compilation and run. T-SQL is unlike regular programming language, SQL Server Engine will based on the costs of adoptable operation combinations to get a rather optimal execution plan for each compilation and run. But same code compile with C++ will always generate same “Execution Plan” (executable).

Programming languages do not need to expose execution plan since it always follows the source code logic regardless execution environmental changes. You know what you do is good enough for programming. But T-SQL is very different. Let’s say you have a table which has 100 records. One of the field is called Gender. You are performing a query to get all Males from it. You know that there is only one record tagged with Male and you have an index on Gender field. Obviously performing a index seek will be the best solution. However, what if I want you give me all the Females? Table scan will be more effecient. So the same T-SQL with different parameters desires different executables for quicker data access.

Same code generates the same executable in programming lanuages but the same SQL might not generate the same executable in SQL Server. Compiled T-SQL executes series of operations where dompiled programming language executes CPU instructions. T-SQL exposes execution plan where programming language doesn’t.