:::: MENU ::::

Posts Categorized / User Defined Views

  • Jun 03 / 2009
  • 0
dbDigger, Security and Permissions, T-SQL Tips and Tricks, User Defined Views

Implement required restrictions on view by using WITH CHECK OPTION

I have created views for employees in different departments. Each employee is able to view records for his own department in the view. Also employees can update or insert records for their department through that view. I noticed that employees are even update and insert data that is related to any other department.
Now it was required to restrict that no employee can insert/update data that conflicts with department restriction that i have implemented in each view. For example consider the following imaginary view where records are picked based on a department provided in where clause

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 GO  

Employees from finance department are able to perform DML operations on this view. It is required that these employees may not be able to insert a record with department name other than Finance. First i planned to use a trigger for implementation of this logic. But while discussing this issue with fellow DBA i came to know that there is an option provided with the view definition itself, that will prohibit any DML that conflicts with the conditions provided in where clause of view definition.
So creating the above view with additional WITH CHECK OPTION in following way will not allow any DML operation for which employee does not belong to Finance department.

 
Create VIEW UVW_DeptRecords  
 AS  
 SELECT name, designation, department  
 FROM employees   
 WHERE department = 'Finance'  
 WITH CHECK OPTION  
 GO  
  • Dec 29 / 2008
  • 0
dbDigger, User Defined Views

Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

The error message
Msg 4508, Level 16, State 1, Procedure v#t, Line 4
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

is a run time error message of severity level 16. It can occur on all versions of SQL Server. This error message appears when you try to create a view or a function on a temporary table. You cannot create views or functions on temporary tables.

  • Dec 05 / 2008
  • 0
dbDigger, Indexes, User Defined Views

Cannot create an index on a view or computed column because the compatibility level of this database is less than 80.

The error message
Msg 1959, Level 16, State 1, Line 2
Cannot create an index on a view or computed column because the compatibility level of this database is less than 80. Use sp_dbcmptlevel to raise the compatibility level of the database.

is run time error level of severity level 16. It can occur on all versions of SQL Server onwards to SQL Server 2000. This error message appears when you try to create an indexed view that is contained in a database with a compatibility level less than 80. Compatibility level 80 belongs to SQL Server 2000 database. As errors of severity level 16 are generated and can be corrected by user. To index a view the compatibility level of the database must be at least 80 (or higher), so you have to increase the compatibility level of that specific database.
It is relevant to mention here that on a higher version of SQL Server you may have a database with compatibility level to a previous version.

  • May 21 / 2008
  • 2
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.
Consult us to explore the Databases. Contact us