:::: MENU ::::

SQL Srever views for preliminary learners

dbDigger, User Defined Views

SQL Srever views for preliminary learners

Views are virtual tables whose content is derived from base tables but their structure is stored on disk.
DML statements manipulate views same as base tables with a exception that all data effected in view is actually affected in underlying table (base table). Views are nothing but saved SQL statements, and are sometimes referred as “Virtual Tables”.
Keep in mind that Views cannot store data (except for Indexed Views); rather they only refer to data resent in tables. There are two important options that can be used when a view is created. They are SCHEMABINDING and ENCRYPTION. Before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them.
View is a list of content of a column or group of columns. Rows in a table are ordered according to their insertion sequence. Indexes keep sorted list of values of a column. Hence it speeds up the search process.
Along with several advantages it has some disadvantages e.g. indexes use additional disk space and when table values of indexed columns are changed, the DBMS needs to maintain the index as well. This makes the insert, delete and update commands to run slow.
SQL SERVER 2000 allows an index to be created on a View. But one important point to be noted here is that the first index on the View should be a UNIQUE CLUSTERED INDEX only. SQL SERVER 2000 will not allow you to create any other INDEX unless you have an UNIQUE CLUSTERED INDEX defined on the view. Views can be used to insert/update and delete data from a table. Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, whereas Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.
It’s not anymore than a named SELECT statement, or a virtual table. You can select data, alter data; remove data and all other things you can do with a table (with some limitations). But there is a difference between a view and a table. The data accessible through a view is not stored in the database as its own object. It’s stored in the underlying tables that make up the view.

Here are some scenarios when a view can be very useful.

  • Restrict data access and/or simplify data access. A view can be used to limit the user to only use a few columns in a table If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the user use is the view’s name. Simple, but powerful!

 

  • Simplify data manipulation. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved.

 

  • Import and export data. A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view.

 

  • Merge data. A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator.
  • Anonymous

    Is it possible to update a view. i mean that will the base table be updated or not?

  • Atif Shehzad

    Yes it is possible to update the view and base table will be affected.

Consult us to explore the Databases. Contact us