03 - View (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          View

-          SP

-          Functions

-          Index

-          Triggers

 

 

Views

 

·          Is a virtual representation of a table

·          Used to provide de-normalized data.

·          Views are also useful For Managing Security And Protecting sensitive data. If you wish to obscure the database's schema from the end user, you can grant permissions exclusively to views, rather than the underlying table.

 

Regular Views

 

·          You define a regular view with a SELECT statement.

·          Views are also updateable if they meet the following conditions:

 

o         They have no aggregate functions or the TOP, GROUP BY, UNION, or DISTINCT clauses in their definition.

o         They don't contain computed values or values based on functions.

o         Any non-nullable columns on the base table must be included in the view definition.

 

·          UPDATE and INSERT operations against a view must affect only one underlying table referenced in the view definition.

·          DELETE operations are only allowed if the view references one table in the FROM clause that defines the view.

 

CREATE VIEW <view_name>

{WITH ENCRYPTION | WITH SCHEMABINDING | WITH VIEW_METADATA }

AS

<select_statement>

{WITH CHECK OPTION }

GO

 

   Note:

 

           

WITH CHECK OPTION	Used to make sure any INSERT activity in the view does not include a value that cannot be read from that view.
WITH ENCRYPTION	To not to allow your view definition to be viewable

 

      

Indexed Views – or I should say 'Indexed Regular View'

 

Note: Indexed Views is diferent than Clustered Index on the Table. Reason - The underlying (base) tables are not impacted physically by the creation of indexed views, as the view is treated like a separate database object.

 

 

·          A regular view is no more efficient than the underlying SELECT query used to define it, unless an index is added to it.

·          One way to improve the performance - You must first create a unique clustered index on the view. Once this view has been built, the data that makes up the view is stored in much the same way as a clustered index for a table is stored.

·          You can also create additional nonclustered indexes, as you would for a regular table. Prior to creating nonclustered indexes on the view, you must first define the clustered index.

 

·          Once a indeexed view is created, that indexed view are used implicitly even if the table name is used in the from clause, in place of View name.

 

Select * from MyTable

Select * From MyTableView

 

Both queries can use the view, even though one of them does not even mention the view in the FROM clause:

 

    Disadvantage -

 

·          Indexed views require SQL Server Enterprise or Developer Editions.

·          They are also more useful for static data (reporting or data warehousing data), than data that is frequently updated; this is because base tables with frequent updates will trigger frequent index updates against the view, potentially reduces the benefit of query performance against data update speed.

 

 

Create an Indexed View

 

·          If you intend to create indexes for your view, the view must be schemabound.

·          The WITH SCHEMABINDING clause binds the view definition to the schema of the underlying base tables.

·          SCHEMABINDING restricts what changes can be made to the base tables while the view is schemabound to it.

 

 

CREATE VIEW vBooks WITH SCHEMABINDING AS

SELECT vchBookName, iAuthorId

FROM dbo.Books

GO

 

CREATE UNIQUE CLUSTERED INDEX idx_vBooks_BookName

ON vBooks (vchBookName)

 

CREATE NONCLUSTERED INDEX idx_vBooks_iAuthorID

ON vBooks (iAuthorId)

 

 

Refresh Meta Data for a Specific View

 

·          If the underlying table schema for the SELECT query definition of the view undergoes changes, you can use the sp_refreshview stored procedure to refresh the view's meta data.

 

EXEC sp_refreshview '<view_name>'

 

 

INSTEAD OF Trigger View

 

·          We know that INSTEAD OF triggers facilitate updates against views and are more a technique than a type of view.

·          If your view schema does not permit direct updates, you can use INSTEAD OF triggers to handle INSERT, UPDATE, and DELETE activity against the view; this is particularly useful for views that reference multiple tables. For example, one update against a view can be used to update multiple base tables..

 

 

 

Hope this helps.

 

 

Thanks & Regards,

Arun Manglick || Tech Lead