Answered by:
What is role of Algebrizer process 'Object Binding'?

Question
-
I am trying to understand role of Algebrizer during 'Object Binding' process. As per definition:
"The algebrizer
resolves all the names of the different objects, meaning the tables, the columns, and so on, that are being
referenced in the T-SQL in a process called binding."Meaning it will check for existence of objects (tables/ columns) referred in the t-sql query. If if finds any non-existent object, then it will throw error. But, if this is the case then following query should not get passed by Algebrizer because table "dbo.no_test1" does not exist:
IF (SELECT OBJECT_ID('dbo.Test1') ) IS NOT NULL DROP TABLE dbo.Test1 ; GO CREATE TABLE dbo.Test1 (c1 INT) ; INSERT INTO dbo.Test1 VALUES (1) ; SELECT 'Before Error', c1 FROM dbo.Test1 AS t ; SELECT 'error', c1 FROM dbo.no_Test1 ; --Error: Table doesn't exist SELECT 'after error', c1 FROM dbo.Test1 AS t ;
Tuesday, June 2, 2015 1:49 PM
Answers
-
The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
The algebrizer outputs a binary called the query processor tree, which is then passed
on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
the query. The optimizer uses the hash to determine whether there is already a
plan generated and stored in the plan cache. If there is a plan there, the process stops here
and that plan is used. This reduces all the overhead required by the query optimizer to
generate a new plan. Hope this helps.This is a quote from "SQL Server Execution Plans Second Edition" by Grant Fritchey
http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf
David
- Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:17 AM
- Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:11 AM
Monday, June 8, 2015 7:18 PM
All replies
-
SELECT 'error', c1 FROM dbo.no_Test1 ;
wrong table name...check and confirm..
Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue
Tuesday, June 2, 2015 2:37 PM -
The first step is Parsing, and the whole batch is parsed and will fail if it contains a syntax error. After parsing the batch execution starts, and queries in the batch only go through the algebrizer as they are executed. This behavior is known as "Deferred Name Resolution", as object identifiers are not validated until the query plan is actually needed.
For instance this batch will not fail at parse or runtime.
if 1=2 begin SELECT 'error', c1 FROM dbo.no_Test1 ; end
David
Tuesday, June 2, 2015 2:50 PM -
Hi davidbaxterbrowne
Thanks for you quick reply!
Any supporting link which describes this case in details?
Tuesday, June 2, 2015 5:58 PM -
The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
The algebrizer outputs a binary called the query processor tree, which is then passed
on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
the query. The optimizer uses the hash to determine whether there is already a
plan generated and stored in the plan cache. If there is a plan there, the process stops here
and that plan is used. This reduces all the overhead required by the query optimizer to
generate a new plan. Hope this helps.Monday, June 8, 2015 7:05 PM -
The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the data types (varchar(50) versus datetime and so
on) for the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don't exist in the database, that need to be resolved, or the query may refer to objects not in the database. When objects don't exist in the database, SQL Server returns an error from
this step, defining the invalid object name. As an example, the algebrizer would quickly find the table Person.Person in the AdventureWorks2008R2 database. However, the Product.Person table, which doesn't exist, would cause an error and the whole optimization process would stop.
The algebrizer outputs a binary called the query processor tree, which is then passed
on to the query optimizer. The algebrizer's output includes a hash, a coded value representing
the query. The optimizer uses the hash to determine whether there is already a
plan generated and stored in the plan cache. If there is a plan there, the process stops here
and that plan is used. This reduces all the overhead required by the query optimizer to
generate a new plan. Hope this helps.This is a quote from "SQL Server Execution Plans Second Edition" by Grant Fritchey
http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf
David
- Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:17 AM
- Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:11 AM
Monday, June 8, 2015 7:18 PM