Very Computer

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 ?

Thanks,
--
Nayan Raval

Nayan,

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
SP.

Tangent:
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
run time.

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.

Quote:> 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 ?

> Thanks,
> --
> Nayan Raval

Nayan,

BP discussed why deferred name resolution might be desirable in sp's.
Here's my guess what it is not available in views:

A view is a relation. We expect to be able to derive meta-data out of
relation, i.e.: We expect that by using system tables, information_schema
views and other methods be able to derive column structure of a relation (a
view in this case). Such interrogation wouldn't be possible if deferred name
resolution was available for views.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Quote:> 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 ?

> Thanks,
> --
> Nayan Raval

On Mon, 29 Nov 1999 21:09:02 +0000, Nayan Raval

My guess, it was just a lower implementation priority than doing it
for stored procedures, where it is crucial, and it just didn't get
done.

Quote:> Is this by design ?

See above.

Quote:> If so, can anyone explain the apparent inconsistency ?

I'd be happy if sp_recompile that triggers recompilation for sp's
would also affect views.

Joshua Stern

Thanks, Tibor. Your explanation sounds good to me.

--
Nayan Raval

Quote:>Nayan,

>BP discussed why deferred name resolution might be desirable in sp's.
>Here's my guess what it is not available in views:

>A view is a relation. We expect to be able to derive meta-data out of
>relation, i.e.: We expect that by using system tables, information_schema
>views and other methods be able to derive column structure of a relation (a
>view in this case). Such interrogation wouldn't be possible if deferred name
>resolution was available for views.

>--
>Tibor Karaszi
>MCDBA, MCSE, MCSD, MCT, SQL Server MVP
>Cornerstone Sweden AB
>Please reply to the newsgroup only, not by email.