How to create a view and use options to protect the view.

Problem: How to create a view or a read only version of a table data.

Solution: Views, Views.

Views are virtual tables having metadata based on the query inside the view definition, views have rows and columns just like any tables that exists in SQL Server. Views don’t consume space on the database, if they are indexed they would have index space on the database.

Views have few options which can provide security and consistency based on the options selected in the object definition.

WITH METADATA – Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors.

ENCRYPTION -Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

WITH CHECK OPTION - Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

SCHEMA BINDING - Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Example:

As Always let’s explore few varieties and options in views. I am going to show first how to create a basic view and then let’s go further north and explore the available options inside a view.

Please see the below script, where I am going to select columns from a tableA and alias the columns to change accordingly as the user wants to see.

CREATE VIEW [dbo].[View_A]

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

Let’s see an example for how to encrypt a view definition and see the results from sys.syscomments to see whether the text is showing in the system views or not.

CREATE VIEW [dbo].[View_A]

WITH ENCRYPTION

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- tableA results

SELECT  * FROM sys.syscomments  where object_name(id) ='View_A'

To alter a view or modify the definition inside the view query. please see the below script where i am renaming the alias of the view columns.

ALTER VIEW dbo.VIEW_A

AS

SELECT col1 AS Version_Col1,

col2 AS Version_col2,

col3 AS Version_Col3

FROM dbo.TableA

GO

The below script shows us how to drop a view.

DROP VIEW dbo.VIEW_A

Let’s explore the schema binding option and then try to change the underlying table definition by altering a column from INT to VARCHAR(10). This will help to protect the views metadata not to break the query inside the view.

CREATE VIEW [dbo].[View_A]

WITH SCHEMABINDING

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- tableA results

ALTER TABLE dbo.TableA

ALTER COLUMN col2 VARCHAR(10)

I am going to show how WITH CHECK OPTION WORKS and insert a record through the view.

CREATE TABLE dbo.TestTable (col_a INTEGER);

GO

CREATE VIEW dbo.TestView (col_a)

AS

SELECT col_a FROM dbo.TestTable WHERE col_a > 0

WITH CHECK OPTION;

GO

-- Inserting into View

INSERT INTO dbo.TestView VALUES (1);

-- Selecting from Table

SELECT * FROM dbo.TestTable

SELECT * FROM dbo.TestView

If you see the above picture this view is used as a check constraint on the input values and it is only accepting positive integers for the col_a.

The last but not the least View to have a view_metadata to show the metadata of the view using client side API’s.

CREATE VIEW [dbo].[View_A]

WITH VIEW_METADATA

AS

SELECT col1 AS V_Col1,

                col2 AS V_col2,

                col3 AS V_Col3

FROM dbo.TableA

GO

-- view metadata