Creating indexed views in SQL Server 2005 – Quick Guide

SQL Server 2005

Here’s a quick guide on how to create indexed views in SQL Server 2005.

How do you create indexed views in SQL Server 2005?

1) Create a table in your database, e.g.

CREATE TABLE [dbo].[TestTableSC] (
[ID] [INT] NOT NULL,
[name] [varchar] (100) NULL,
[description] [varchar] (255) NULL
)

2) SET ANSI_NULLS, ANSI_WARNINGS ON….

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,
ARITHABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS ON;
GO

3) In you database create a view WITH SCHEMABINDING, e.g.

CREATE VIEW [dbo].[TestTableSCView] WITH SCHEMABINDING AS
SELECT ID, name, description FROM dbo.TestTableSC

GO

4) Create a CLUSTERED INDEX

CREATE UNIQUE CLUSTERED INDEX TestTableSC_PKey
ON TestTableSCView(ID)
GO

Here are the Gotchas on creating indexed views in SQL Server 2005

You can only create a SCHEMABINDING view when the base table is in the same database. If the base table is in a different database or a linked server, you will get an error.

SQL Server does not allow SCHEMABINDING a view to a base table in a different database.

Example:

CREATE VIEW [dbo].[TestTableSCView2] WITH SCHEMABINDING AS
SELECT ID, name, description FROM database1.dbo.TestTableSC

GO

You’ll get the error:

Cannot schema bind view ‘dbo.TestTableSCView2′ because name ‘database1.dbo.TestTableSC’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

That concludes the quick guide to creating indexed views in SQL Server 2005.