Page 74 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Otherwise, you do not need to specify column names when creating the view. SQL Server gives the
columns of the view the same names and data types as the columns to which the query defining the
view refers. The select list can be a full or partial list of the column names in the base tables.

Notes To create a view you must be granted permission to do so by the database owner and
you must have appropriate permissions on any tables or views referenced in the view definition.
By default, as rows are added or updated through a view, they disappear from the scope of the
view when they no longer fall into the criteria of the query defining the view. For example, a
query can be created, defining a view that retrieves all rows from a table where the employee’s
salary is less than ` 30,000. If the employee’s salary is increased to ` 32,000, then querying the
view no longer displays that particular employee because his or her salary does not conform to
the criteria set by the view. However, the WITH CHECK OPTION clause forces all data
modification statements executed against the view to adhere to the criteria set within the SELECT
statement defining the view. If you use this clause, rows cannot be modified in a way that causes
them to disappear from the view. Any modification that would cause this to happen is canceled
and an error is displayed.
The definition of a sensitive view can be obfuscated by using the WITH ENCRYPTION option.
Note that obfuscated view definitions can be reverse engineered because SQL Server must de-
obfuscate them when they are executed. In SQL Server 2000, the obfuscated text is visible in the
syscomments system table and may be susceptible to de-obfuscation attempts.

Creating a View

To create a view, you can use any of the tools given below:
1. Transact-SQL
2. Enterprise Manager

3. SQL-DMO
You can also create a view using the SQL Server Enterprise Manager Create View Wizard.

Self Assessment

Name the following:
6. A virtual table whose contents are defined by a query.
7. A visual tool that allows you to design and visualize a database to which you are connected.

8. A type of constraint that is declared independently from a column definition and can
apply to more than one column in a table.
9. A key in one table that points to a candidate key in another table.

10. Keys that enforce uniqueness.

5.9 Synonyms

Microsoft SQL Server introduces the concept of a synonym. A synonym is an alternative name
for a schema-scoped object. Client applications can use a single-part name to reference a base
object by using a synonym instead of using a two-part, three-part, or four-part name to reference
the base object.

68 LOVELY PROFESSIONAL UNIVERSITY