SQL Server: Object Validation Error by Estimated Plan While Actual Plan Working Fine
It happened dozen of times when I tried to get an Estimated Execution Plan, it return object verification error, while the same Stored Procedure was working perfectly. Then what’s wrong with Estimated Execution Plan, lets create a simple procedure.
CREATE PROCEDURE Proc_TestProcedure
AS
BEGIN
IF 1 =2
SELECT * FROM NoTable — NoTable doesn’t exists
ELSE
SELECT ‘ESLE’
END
GO
On compilation and execution, above stored procedure will not return any error, because condition is never true, so it never need to exec SELECT * FROM NoTable, and to check that table exists or not. Now just try to get execution plan of
EXECProc_TestProcedure
It will return error.
Msg 208, Level 16, State 1, Procedure Proc_TestProcedure, Line 8
Invalid object name ‘NoTable’.
Why So.
Because on estimation, query optimizer check each and every statement separately and once it try to estimate cost for “NoTable”, which never exists, it returns above mentioned error.
Posted on July 4, 2012, in Daily Dose. Bookmark the permalink. 1 Comment.
Thanks Aasim!