Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save LitKnd/6b6d58b819ff38ff0d6277bb8389ca29 to your computer and use it in GitHub Desktop.
Save LitKnd/6b6d58b819ff38ff0d6277bb8389ca29 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment