altering views with two different syntaxes.sql

USE master;
GO

SET NOCOUNT ON;
GO

/*******************************************
Set up the database and table
********************************************/

IF DB_ID('alterviewtest') IS NOT NULL
BEGIN
    ALTER DATABASE alterviewtest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE alterviewtest;
END;
GO

CREATE DATABASE alterviewtest;
GO

USE alterviewtest;
GO

CREATE TABLE dbo.iminaview
(
    i INT IDENTITY NOT NULL,
    CONSTRAINT pk_iminaview_i
        PRIMARY KEY CLUSTERED (i)
);
GO

INSERT dbo.iminaview
DEFAULT VALUES;
GO 10

/*******************************************
Create two indexed views, each with a nonclustered index
********************************************/

CREATE VIEW dbo.iamaviewalteredoldstyle
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS ct
FROM dbo.iminaview;
GO

CREATE UNIQUE CLUSTERED INDEX cx_iamaviewalteredoldstyle
ON dbo.iamaviewalteredoldstyle (ct);
GO

CREATE NONCLUSTERED INDEX ix_iamaviewalteredoldstyle_i
ON dbo.iamaviewalteredoldstyle (ct);
GO

CREATE VIEW dbo.iamaviewcreateoralter
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS ct
FROM dbo.iminaview;
GO

CREATE UNIQUE CLUSTERED INDEX cx_iamaviewcreateoralter
ON dbo.iamaviewcreateoralter (ct);
GO

CREATE NONCLUSTERED INDEX ix_iamaviewcreateoralter_i
ON dbo.iamaviewcreateoralter (ct);
GO

/*******************************************
Review the metadata
********************************************/

SELECT so.name,
       si.name
FROM sys.objects AS so
    JOIN sys.indexes AS si
        ON so.object_id = si.object_id
    JOIN sys.schemas AS sc
        ON so.schema_id = sc.schema_id
WHERE sc.name = 'dbo'
      AND so.name IN ( 'iamaviewcreateoralter', 'iamaviewalteredoldstyle' )
ORDER BY 1,
         2;
GO


/*******************************************
Alter each view in a different way
********************************************/

ALTER VIEW dbo.iamaviewalteredoldstyle
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS ct, 1 AS newcol
FROM dbo.iminaview;
GO

CREATE OR ALTER VIEW dbo.iamaviewcreateoralter
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) AS ct, 1 AS newcol
FROM dbo.iminaview;
GO


/*******************************************
Review the metadata
********************************************/

SELECT so.name,
       si.name
FROM sys.objects AS so
    JOIN sys.indexes AS si
        ON so.object_id = si.object_id
    JOIN sys.schemas AS sc
        ON so.schema_id = sc.schema_id
WHERE sc.name = 'dbo'
      AND so.name IN ( 'iamaviewcreateoralter', 'iamaviewalteredoldstyle' )
ORDER BY 1,
         2;
GO