One man's opinion, not an official Microsoft position:
It is not uncommon for a stored procedure to create a temporary table and
then make use of it later in the same SP. With SQL Server 6.x, this would
cause problems since the query optimizer didn't see the temporary table and
so couldn't factor it into the compiled plan, and therefore would reject the
One might wonder why the query optimizer couldn't be built smarter to see
the CREATE TABLE or the SELECT INTO and make use of it. Two answers, but of
a similar nature:
First: either the CREATE TABLE or the SELECT INTO could be inside an IF /
ELSE statement, so the table could have different schema depending on the
values of the input parameters at run time.
Second: same essential logic as above but with the EXEC () statement and
again different schemas depending on the values of the input parameters at
Thus, programmers had to create the temp table outside of the SP just to get
the SP to compile. Microsoft resolved this problem via deferred resolution.
A view does not accept run time parameters, so the situation is not
comparable to that of SP's.
> After upsizing one of my SQL Server 6.5 systems to 7.0 I noticed that
> the logs reported errors in upsizing my views. My views are of the form
> CREATE VIEW dbo.vwAView AS
> -- vwBView and vwCView are views
> SELECT ColA,ColB FROM dbo.vwBView
> UNION ALL
> SELECT ColA,ColB FROM dbo.vwCView
> The errors mentioned 'Invalid object name 'dbo.vwBView' and
> 'dbo.vwCView''. (Note, vwBView and vwCView come alphabetically after
> vwAview and were upsized successfully). All views exist in my 6.5
> database. My simple workaround was to script the views from 6.5 and
> submit them to my 7.0 system after the upsizing.
> This got me wondering. In 7.0 I can create stored procedures and
> triggers referencing objects which don't yet exist (deferred name
> resolution) but I can't use the same feature when creating views. Why
> would this be so ? Is this by design ? If so, can anyone explain the
> apparent inconsistency ?
> Nayan Raval