Tuesday, March 1, 2011

SQL Server: What is an Algebrizer ?

 After an interview for DBA post, a friend of mine called me and without saying “Hello”, asked “What is Algebrizer?”  Yeah it’s the guy who is always ready to work for you when ever you write a query and press F5 to execute ;)
Algebrizer is a process in query execution, which starts its work after Parser. Once the parser finds query syntax correct, it passes it to the algebrizer. Here start work of algebrizer:
·     Algebrizer is responsible for objects and columns names (which you have provided in query or which are being referenced by your query) verification. For example if wrong name for a table is typed in a query, then its algebrizer’s duty to verify and generate an error.
·     Algebrizer also identifies all data types which are being processed for a given query.
·     Algebrizer verify that GROUP BY and aggregate columns are placed in right place or not. For example if you write following query and just Ctrl+F5 (just to parse). No error will be generated. But when you will press F5 (execute it). After Parsing, algebrizer will work and return an error.

USE AdventureWorks
SELECT  MakeFlag,SUM(ListPrice)
FROM Production.Product
GROUP BY ProductNumber

1 comment:

  1. Asim, Its really a nice and Knowledge full article.

    Basically we need to think about, what happens to the query, What Happens When a Query is Submitted?
    There are number of processes, which are executed on the server, when the query is submitted. These processes are used to manage the system and then send data back to the user.
    These process are executed for every query
    There are two kinds of process that occur
    1. in relational engine
    2. in storage engine.
    When a Query is executed, the first place it goes to is the rel­ation­al engine, it is then passes through a process which checks the TSQL of the query. This process is called the Query Parsing, After Parsing the Query, the parse tree generated which is is passed to a process called the algebrizer. This process resolves the names of the various objects, tables and columns ,aliases or synonyms , referred to within the query string.
    The algebrizer generates the query processor tree ,which is then passed on to the query optimizer.
    An other process called aggregate binding is used to determines the location of aggregates like Group By, Max, Min etc.


All suggestions are welcome