Indexed view gets scripted without index

Question

I use SQL Server 2008 R2. In my database exists multi indexed view and multi view that use of these indexed view with hint NOEXPAND. When I get script of my database by SSMS (Database\Tasks\Generate Script ... ), SSMS product script by below order :

1. script of indexed view
2. script of view that use of above indexed view
3. script of clustered indexed of indexed view.

When I run created script, SQL server create first indexed view with no clustered index, then want to create view of indexed view with NOEXPAND hint, that cause raise error. What I do?

SSMS error : hint 'noexpand' on object ..(indexed view).. is invalid.

Answer 1

The default for scripting indexes is False. You need to change it to true in order to include the CREATE INDEX with your view. In Management Studio:

• Tools > Options
• SQL Server Object Explorer
• Scripting
• Table and view options > Script indexes

Set the option to True, click OK, and try generating your script again.

EDIT

On a default installation, the above setting is the only thing I changed. I generated the following schema:

CREATE TABLE dbo.x(i INT);
GO
ALTER VIEW dbo.v_x
WITH SCHEMABINDING
AS
  SELECT i, c = COUNT_BIG(*)
    FROM dbo.x
    GROUP BY i;
GO
CREATE UNIQUE CLUSTERED INDEX i ON dbo.v_x(i);
GO
CREATE VIEW dbo.v_y
AS
  SELECT i,c FROM dbo.v_x WITH (NOEXPAND);
GO

I right-clicked the database, Tasks > Generate Scripts... picked the two views and the table, scripted to a new query window, and this is what it produced (I removed all the SET options for brevity, not because they're not there or unimportant):

USE [foo]
GO
/****** Object:  Table [dbo].[x]    Script Date: 06/20/2012 08:03:59 ******/
CREATE TABLE [dbo].[x](
    [i] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[v_x]    Script Date: 06/20/2012 08:04:00 ******/
CREATE VIEW [dbo].[v_x]
WITH SCHEMABINDING
AS
  SELECT i, c = COUNT_BIG(*)
    FROM dbo.x
    GROUP BY i;
GO
CREATE UNIQUE CLUSTERED INDEX [i] ON [dbo].[v_x] 
(
    [i] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  View [dbo].[v_y]    Script Date: 06/20/2012 08:04:00 ******/
CREATE VIEW [dbo].[v_y]
AS
  SELECT i,c FROM dbo.v_x WITH (NOEXPAND);
GO

When I changed the USE [foo] to a different database, the script executed just fine (and obviously things are in the correct order).

So either you're generating the script differently, or you used the "continue scripting on error" option and your view prevents the index from being created.