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 ;

Answer 1

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

Answer 2

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

---

David http://blogs.msdn.com/b/dbrowne/

Answer 3

Hi davidbaxterbrowne

Thanks for you quick reply!

Any supporting link which describes this case in details?

Answer 4

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.

Answer 5

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

---

David http://blogs.msdn.com/b/dbrowne/