Object creation order causes views to fail when using NOEXPAND by SQL_DBA_OTENT

Closed

as Fixed

Type:	Bug
ID:	776322
Opened:	1/11/2013 4:06:13 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When using the ..Tasks..Generate Scripts method the order that objects are scripted are critical when using indexed views and objects that reference them using the NOEXPAND hint.

If you have view2 that references view1 (which is an indexed view) and use the NOEXPAND hint in view2 the script will fail because all the view definitions are created first before the clustered indexes. This means that when view2 gets created it complains about the NOEXPAND because at that point view1 is not considered to be an indexed view. The index for view1 is added further down the script.

To solve this issue the clustered index for view1 should be created directly after the view1 definition, this would then make the NOEXPAND hint inside view2 valid.

Details (expand)

English

SQL Server 2012 - Enterprise Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Professional

English

CREATE TABLE [dbo].[Goods](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](50) NULL,
    [Value] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE view [dbo].[vw_Goods1] with schemabinding
AS
select Description, sum(Value) as Sum_Value, COUNT_BIG(*) AS CountBig,value
from dbo.Goods
group by Description,value
GO

create unique clustered index clx_description_value on vw_Goods1 ([description], value)
GO

create view [dbo].[vw_goods2]
as
select description,value
from vw_Goods1 with(noexpand)
GO

--Now produce a "Generated Scripts" scriptfile. Right click database..Tasks..Generate Scripts. The script it produces will fail to recreate vw_goods2

Msg 8171, Level 16, State 2, Procedure vw_goods2, Line 5
Hint 'noexpand' on object 'vw_Goods1' is invalid.

Expect the clustered index definition to be generated directly after the view script.

X64

0 attachments

Posted by Microsoft on 4/26/2013 at 9:14 AM

We are happy to inform you that the issue is fixed in the next SQL release.
Thanks,
Olga Pechuk [MSFT SQL SERVER]

Posted by Microsoft on 2/19/2013 at 2:39 PM

Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]

Microsoft