SQL 4 beginners - View

In most online articles about databases you can easily find a lot of articles explaining cool, complex and new features. But what’s about some basics? I decided to release some articles about the basics. The articles will mostly cover topics usable in most relational databases, but the examples and the focus will be on MS SQL Server database engine.

A database View is nothing else than a stored query! When you create a database view (CREATE VIEW), the database engine stores the input query and give him a specified name. When you query the view, the database engine takes the stored definition of the query and execute it with added statements by you (e.g. ORDER BY, WHERE, etc.) and execute it.

The advantage of using views is that you must not write the same query again and again. Using views does not improve performance. For performance gains you should use stored procedures, but this is another story.

So, data from view are not stored physically like table data. What are our options? To solve this, we could create a "materialized view".

Materialized view is a similar to a view, but the result of the query is stored physically.

To be able to create a materialized view in MS SQL Server, you must first create a VIEW using the SCHEMABINDING option and then give that view a primary unique key (unique clustered index). After the view is materialized, you can add additional indexes on the same view.

In some other databases (e.g. Oracle) the materialized view must be refreshed when data is inserted in base tables, but MS SQL Server will automatically refresh the materialized view when data is changed in base tables (the tables included in the view).

Materialized view will not give you a performance boost if you change the data in the base tables often because the maintenance of the materialized view cost resources.

SCHEMABINDING is used on any object (like view) to prevent any accidentally modifications to the objects referenced by the module. For example, if you have a view that relies on the data type of a column in a table, you can use WITH SCHEMDABINDING to ensure that the table doesn't change. If you try to drop the column that is used in a SCHEMABINDED view, you will get an exception:

ALTER TABLE [dbo].[InvoiceDetail] DROP [Quantity]

"Msg 3728, Level 16, State 1, Line 59 - 'Quantity' is not a constraint.
 Msg 3727, Level 16, State 0, Line 59 - Could not drop constraint. See previous errors."

Let see one example of creating two tables and a materialized view.

Example:

-- Invoice detail table
CREATE TABLE [dbo].[InvoiceDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceNumber] [int] NOT NULL,
	[Quantity] [smallint] NOT NULL,
	[Price] [money] NOT NULL,
 CONSTRAINT [PK_InvoiceDetail] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
-- Invoice table
CREATE TABLE [dbo].[Invoice](
	[InvoiceNumber] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceNumber] ASC)
) 
GO
-- Creating the view
CREATE VIEW [dbo].[vInvoices] 
WITH SCHEMABINDING
AS
SELECT 
	I.[InvoiceNumber]
	, I.[Date]
	, D.ID
	, D.Price
	, D.Quantity
	, D.Price * D.Quantity AS Value
FROM
	[dbo].[Invoice] I
	INNER JOIN [dbo].[InvoiceDetail] D ON I.[InvoiceNumber] = D.[InvoiceNumber]
GO
-- Materializing the view
CREATE UNIQUE CLUSTERED INDEX [IDX_vIDInvoice] ON [dbo].[vInvoices]
(
	[InvoiceNumber] ASC,
	[ID] ASC
)
GO

If you try to materialize a view that is not SCHEMABINDED, you will get an exception:

"Cannot create index on view 'vInvoices' because the view is not schema bound."