Home

Introduction to Views

 

Fundamentals of Views

 

Introduction

From studying data analysis, we know that a query is a technique of isolating a series of columns and/or records of a table. This is usually done for the purpose of data analysis. This can also be done to create a new list of items for any particular reason. Most of the time, a query is created temporarily, such as during data analysis. After using such a temporary list, it is then dismissed.

Many database applications, including Microsoft SQL Server, allow you to create a query and be able to save it for later use, or even to use it as if it were its own pseudo-table. This is the idea behind a view.

A view is a series of records retrieved from one or more existing tables, or as a combination of one or more views and one or more tables.

Practical LearningPractical Learning: Introducing Views

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. Open the LambdaSquare2.sql file (LambdaSquare2.txt)
  4. To execute it, right-click the middle of the LambdaSquare2.sql window and click Execute
  5. Close the LambdaSquare2.sql window
  6. In the Object Explorer, expand Databases and expand LambdaSquare2
  7. Right-click Database Diagrams and click New Database Diagram
  8. A message box will inform you that this database doesn't have a diagram.
    Read the message and click Yes
  9. In the Add Table dialog box, click Add and keep clicking it until all tables have been added
  10. Click Close.
    Notice that, based on how we created the database and its objects, the relationships have been established (except for the TimeSheets table)
     
    Lambda Square
  11. Close the diagram window
  12. When asked whether you want to save it, click Yes
  13. Set its name to dgmLambdaSquare2
  14. Click OK

Visually Creating a View

Before creating a view, you must first decide where its columns and records would come from. Obviously the easiest view is one whose columns and records come from one table. To create a view, you can use the Object Explorer, a Query window, the Command Prompt, or Windows PowerShell.

Before starting the view, you would have to specify the table(s) that would be involved. To create a view from the Object Explorer, you can expand the database, right-click Views and click New View... This would open the Add Table dialog box:

The basic functionality is exactly the same as we reviewed for the Query Designer:

  • To specify the table that would be used as the source, you can click it in the list box of the Tables property page
  • If you will use another existing view, from the Views property page, you can click the name of the desired view
  • If a function would be used to generate the records, you can locate it in the Functions property page

After selecting the source object, you can either double-click it or you can click it once and click Add. In in the previous lesson, we saw that you could add more than one existing table.

Practical LearningPractical Learning: Creating a View

  1. In the Object Explorer, under LambdaSquare2, right-click Views and click New View...
  2. In the Add Table dialog box, click Units (Presentation)
  3. Click Add
  4. Click Close

The View Designer

After selecting the objects, they would display in the window. The View Designer toolbar would be added:

Query Designer Toolbar

If the View Designer toolbar is not visible:

  • On the main menu, click View -> Toolbars -> View Designer
  • Right-click any toolbar or the main menu and click View Designer

The window from which you create a view, the View Designer, displays four sections. These are the same sections we reviewed for the Query Designer. Each section is represented on the View Designer toolbar by a button: the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, the Show SQL Pane button Show SQL Pane, and the Show Result Pane button Show Result Pane.

If you don't want a particular section or you want to hide some sections, you can right-click anywhere in the table, position the mouse on Pane and click the name of the section. To hide any section, you can right-click anywhere in the window, position the mouse on Pane and click one of the selections:

Using Panes

When a section is displaying, its menu option is surrounded. To select a column:

  • Click its check box in the Diagram pane. This would display it in a box under the Column section of the Criteria pane and would add its name to the SELECT statement of the SQL pane
  • Click an empty box in the Column column to reveal its combo box, then click the arrow of the combo box and select the desired column from the list. After selecting the column, its check box would be checked in the top section of the window, its name would be displayed in the Column column, and it would be added to the SELECT statement in the SQL pane
  •  Type the name of the column in the SELECT statement of the SQL pane

The structure of a view can be considered complete when the SELECT statement is as complete as possible. At any time, to test the results of a view, you can run it. To see the result of a query:

  • On the main menu, click Query Designer -> Execute SQL
  • On the View Designer toolbar, click the Execute SQL button Execute
  • Right-click one of the panes and click Execute SQL

Any of these actions would cause the bottom section of the view to display the results of the query.

Practical LearningPractical Learning: Adding the Columns of a View

  • In the Diagram pane, click the check boxes of UnitNumber, Bedrooms, Bathrooms, Price, and OccupancyStatus

    New View

Saving a View

As stated already, one of the reasons for creating a view is to be able to use it over and over again. To achieve this, the view must be saved. Like most objects in Microsoft SQL Server, a view must be saved and given a name.

To save a view from the view window, you can click the Save button on the toolbar Save. You can also attempt to close the window. You would then be prompted to save it.

The Name of a View

When saving a view, you must give it a name that follows the rules and suggestions of SQL. In our lessons, here are the rules we will use to name our views:

  • A name will start with a letter. Examples are n, act, or Second
  • After the first letter, the name will have combinations of underscores, letters, and digits. Examples are n24, act_52_t
  • A name will not include special characters such as !, @, #, $, %, ^, &, or *
  • A name will not have spaces
  • If the name is a combination of words, each word will start in uppercase. Examples are DateHired, RealSport, or DriversLicenseNumber

After saving a view, it becomes part of the Views node of its database: a node would be created for it and its name would appear in the Views node of its database.

Practical LearningPractical Learning: Saving a View

  1. To close the view, click its system Close button Close
  2. When asked whether you want to save the items, click Yes
  3. In the Choose Name dialog box, type AvailableUnits
  4. Click OK

Creating a View in Transact-SQL

To programmatically create a view, you use the following formula:

CREATE VIEW [SchemaName.]ViewName
AS
SELECT Statement

Microsoft SQL Server can generate skeleton code of a view for you. To use it, first create an empty Query window. Display the Template Explorer and expand the View node. From the View node, drag Create View and drop it in the query window.

The creation of a view starts with the CREATE VIEW expression followed by a name. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is primarily a query, it is defined using a SELECT statement, using the same rules we studied for data analysis. Here is an example of a view:

USE rosh;
GO

CREATE VIEW FemaleStudents
AS
SELECT FirstName, LastName, Gender, ParentsNames
FROM Registration.Students;
GO

After creating the SQL statement that defines the view, you must execute the statement.

Encrypting a View

When creating a view, if you want, you can encrypt its entry in the database engine. The formula to follow to do this is:

CREATE VIEW [SchemaName.]ViewName
WITH ENCRYPTION
AS
SELECT Statement

Hee is an example:

CREATE VIEW Personnel.EmployeesIdentifications
WITH ENCRYPTION
AS
    SELECT EmplNbr,
    	   FirstName,
    	   LastName 
    FROM Personnel.Employees;
GO

Opening or Executing a View

 

Opening a View

As stated already, a view is a technique of selecting records to view or use over an over again. After a view has been created, you can open it. You have two main options:

  • To see the structure of a view, such as the table(s) on which it is based and the relationships, if any that compose it, in the Object Explorer, right-click the view and click Design
  • To see the SQL code that makes up a view, in the Object Explorer, right-click the view and click Select Top 1000 Rows

Executing a View

Executing a view consists of seeing its results. You have various options. To view the results of a view:

  • Open an empty Query window associated with the database that contains the view. In the Query window, write a SELECT statement using the same formulas and rules we saw for tables
  • From the Object Explorer, expand the database and its Views node. Right-click the name of the view and click Select Top 1000 Rows

Practical LearningPractical Learning: Executing a View

  1. In the Object Explorer, under LambdaSquare2, expand Views if necessary. Right-click dbo.AvailableUnits and click Select Top 1000 Rows
     
    Executing a View
  2. Close the view window

The Properties of a View

In Transact-SQL, a view is considered an object. As such, it can be viewed, changed, or deleted. Like any regular object, a view has its own characteristics. To see them, you can right-click the view and click Properties. A View Properties dialog box would come up. It can give you information such as the name of the database the view belongs to, the date the view was created, etc.

Practical LearningPractical Learning: Accessing the Properties of a View

  1. In the Object Explorer, under LambdaSquare2, in the Views section, right-click dbo.AvailableUnits and click Properties
     
    Accessing the Properties of a View
  2. After examining the dialog box, press Esc to close the Properties dialog box

Introduction to Views and Permissions

 

Introduction

A view is a transitional object between a table and a query. In some cases, it can behave as a table that receives and displays data. In some other cases, it can behave like a query that is used to perform all types of analysis of records.

As you may know already, a view presents one or more records that are actually stored in a table. In most cases, users are only asked to open a view. In some other cases, a developer may be asked to create a view. This leads to a series of scenarios for permissions. You must plan the permissions on the table(s) that hold(s) the records and then plan and apply permissions for people who are supposed to use the view. Obviously, before creating or managing permissions on a view, you must create a user in the database that owns the view.

To visually create or manage permissions on a view, in the Object Explorer, right-click the view and click Properties. In the Select a Page section, click Permissions. In the Users Or Roles section, specify the user whose rights you want to manage. In the Permissions For ... section, manage the rights:

View Properties

It is important to know that permissions of a view are related to, or depend on, the permissions of the table(s) it is based on.

View Creation and Permissions

As mentioned in our introduction to rights and permissions, a view uses two categories of permissions: those of the database developers and those of regular users. A developer who has to create a view must have the CREATE VIEW right on the database that will own the view:

Database Properties

 Here is an example that grants the CREATE VIEW permission to a developer:

GRANT CREATE VIEW
ON OBJECT::BethesdaCarRental.AvailableCars
TO [Orlando Perez];
GO

Here is an example of denying a developer the ability to modify a view:

DENY CREATE VIEW
ON OBJECT::BethesdaCarRental.AvailableCars
TO [Raymond Kouma];
GO

Here is an example that revokes a CREATE VIEW right that was previously granted to a developer:

REVOKE CREATE VIEW
ON OBJECT::BethesdaCarRental.AvailableCars
TO [Orlando Perez];
GO
 
 
 

View Maintenance

 

Renaming a View

After creating and executing a view, you can change its name with little worries with regards to its functionality.

To visually rename a view, in the Object Explorer, right-click the name of the view and click Rename. the name of the view will be highlighted without its schema. You can then type the new name.

To programmatically rename a view, open a Query window and use the following formula:

sp_rename CurrentViewName, NewName;

If the view uses a schema other than dbo, you must include it in the CurrentViewName. Here is an example:

sp_rename N'Personnel.StaffMembers', N'EmployeesNames';
GO

When renaming a view, you must take care of the permissions associated with it because those permissions will be lost.

Altering a View

After a view has been created, either by you or someone else, you may find out that it has an unnecessary column, it needs a missing column, it includes unnecessary records, or some records are missing. Fortunately, you can change the structure or the code of a view. This is referred to as altering a view.

Most of the time, normal users are never asked to modify a view. On the other hand, a developer who has to modify a view must have at least the ALTER permission on the view. Here is an example that grants the ALTER permission to a login:

GRANT ALTER
ON OBJECT::Personnel.StaffMembers
TO [Orlando Perez];
GO

Here is an example of denying a developer the ability to modify a view:

DENY ALTER
ON OBJECT::Registration.Students
TO [Raymond Kouma];
GO

Here is an example that revokes a DENY right that was previously granted to a developer:

REVOKE ALTER
ON OBJECT::Personnel.StaffMembers
TO [Orlando Perez];
GO

There are many options to modify a view:

  • To visually change the appearance of a view, in the Object Explorer, right-click the view and click Design. From the View Designer, you can add or remove the columns. You can also change any options in one of the panes. After modifying the view, save and close it
  • From the Object Explorer, right-click the view, position the mouse on Script View As -> ALTER To -> New Query Editor Window
  • Open a Query window and type code as we will see next

The basic formula to programmatically modify a view is:

ALTER VIEW ViewName
AS
SELECT Statement

You start the alteration with the ALTER VIEW expression followed by the name of the view. After the name of the view, use the AS keyword to specify that you are ready to show the change. After the AS keyword, you can then define the view as you see fit. For example, you can create a SELECT statement that includes a modification of the existing code or a completely new statement.

Here is an example:

ALTER VIEW FemaleStudents
AS
SELECT FirstName,
       LastName,
       DateOfBirth,
       Gender,
       ParentsNames
FROM Registration.Students;
GO

Deleting a View

Instead of modifying a view, if you find it altogether useless, you can remove it from its database. You must first consider who will have that responsibility. Obviously, users are never or hardly asked to remove a view.

A developer who has to delete a view must have the DELETE permission.

To delete a view:

  • In the Object Explorer, in a database, right-click the name of the view and click Delete. You would be given the opportunity to confirm your intention or to change your mind
  • In the Object Explorer, right-click the view, position the mouse on Script View As -> DROP To New Query Editor Window
  • Open an empty Query window associated with the database that has the undesired view
    • From the Template Explorer, in the View node, drag Drop View and drop it in the Query window
    • Use code as we will see next

The formula to programmatically delete a view is:

DROP VIEW ViewName

On the right side of the DROP VIEW expression, enter the name of the undesired view. Here is an example:

DROP VIEW FemaleStudents;
GO

Then execute the statement. You will not be warned before the interpreter deletes the view.

Views and Schemas

 

Introduction

When creating a view, you have the option of specifying its schema. In reality, like every object in the database, a view must be owned by a schema. You have many options. By default, when you create a view, if you don't specify a schema, it would be owne by dbo.

Instead of using dbo, you can use another schema of your choice and assign it to the view. If you had already created a schema in your database, you can use it or create a new schema.

To visually assign a schem to a view:

  • If you are creating a new view, start the view as you would normally and select the necessary table(s)
  • If the view exists already, in the Object Explorer, right-click it and click Design

 In the Properties window, click Schema, then click the arrow of its combo box and select from the list. If you are creating a view with code, precede the name of the view with the name of the schema.

Once/when a view belongs to a schema, in which case the view is not using the default dbo schema, the developer must have the ALTER permission on that schema. As mentioned for tables, you should know how to plan the permissions among schemas and views, namely what rights to grant, which ones to deny, and when to revoke a permission.

Practical LearningPractical Learning: Assigning a View to a Schema

  1. In the Object Explorer, under Views of the LambdaSquare2 database, right-click dbo.AvailableUnits and click Design
  2. If the Properties window is not visible, on the main menu, click View -> Properties window.
    In the Properties window, click Schema. Then click the arrow of its combo box and select Presentation

    Assigning a View to a Schema

    Assigning a View to a Schema

  3. Read the message box that appears and click Yes

    Assigning a View to a Schema

  4. To see the result of the view, on the main menu, click Query Designer -> Execute SQL
  5. On the Standard toolbar, click the Save button Save

Binding a View to its Parent

When you have created a view, you know that the records it displays are tied to its parent table. Consider the following example:

CREATE DATABASE Exercise8;
GO
USE Exercise8;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(10),
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Salary money,
	FullTime bit
);
GO
INSERT INTO Personnel.Employees
VALUES(N'524-880', N'Barbara', N'Grisby', 14.85, 1),
	  (N'688-364', N'Terrence', N'North', 22.05, NULL),
	  (N'461-852', N'Michael', N'Goldsmith', 22.14, 0),
	  (N'264-853', N'David', N'Ecker', 20.04, 1),
	  (N'207-025', N'Julie', N'Flanell', 36.55, 1),
      (N'684-946', N'Kevin', N'Rhems', 15.86, NULL);
GO
CREATE VIEW Personnel.Identifications
AS
SELECT EmplNbr, FirstName, LastName
FROM Personnel.Employees;
GO

SELECT * FROM Personnel.Employees;
GO
SELECT * FROM Personnel.Identifications;
GO

This would produce:

Binding a View to its Parent

For one reason or another, you may have to make modifications on the parent table. For example, you may want to change the data type and/or name of a column but without touching the view(s) that depend on that table. What would happen? Consider the following example that renames a column on a table:

sp_rename N'Personnel.Employees.EmplNbr', N'EmployeeNumber', N'COLUMN';
GO

This code would execute successfully and the column would be renamed:

Binding a View to its Parent

After renaming the column, you can use it in a SELECT statement of the table:

Binding a View to its Parent

Since the view would still be using the previous definition of the table, in this case the previous name of the column, if the user tries executing the view, the database engine would produce an error. Here is an example:

Binding a View to its Parent

The solution is to prevent any changes on a parent table if that change would affect the view(s) that depend on that table. To set this, if you are visually creating the view, in the Properties window, set the Bind to Schema field to Yes. It the view exists already, open it in Design and, in the Properties window, set the Bind to Schema field to Yes.

It you are creating the view with code, before the AS keyword, add the WITH SCHEMABINDING flag. The formula to follow is:

CREATE VIEW [SchemaName.]ViewName
WITH SCHEMABINDING
AS
SELECT Statement

Hee is an example:

CREATE VIEW Personnel.EmploymentStatus
WITH SCHEMABINDING
AS
    SELECT FirstName, LastName, FullTime
    FROM Personnel.Employees;
GO

You can then execute the view when necessary. Here is an example:

SELECT * FROM Personnel.EmploymentStatus;
GO

This would produce:

Binding a View to its Parent

Now, imagine you want to change something on the parent table. For this example, let's try to change the data type of the FullTime column:

ALTER TABLE Personnel.Employees
ALTER COLUMN FullTime int;
GO

If you make the modification visually using the View Designer, or if you try executing the code, when you execute it, you would receive an error. Here is an example:

Binding a View to its Parent

This means that the database engine will not allow you to change something on the table if that change will make the dependent view(s) to stop working. But, you can change anything on the parent table as long as no dependent view would be affected.

Data Entry Using a View

 

Introduction

As seen so far, a view is a selected list of records from a table. As you may suspect, the easiest view is probably one created from one table. Imagine you have a table of employees and you want to create a view that lists only their names. You may create a view as follows:

CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE SCHEMA Personnel;
GO
CREATE TABLE Personnel.Employees
(
	EmplNbr nchar(10),
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Salary money,
	FullTime bit
);
GO

CREATE VIEW Personnel.Identifications
AS
SELECT EmplNbr, FirstName, LastName
FROM Personnel.Employees;
GO

On such a view that is based on one table, you can perform data entry, using the view, rather than the table.

View Data Entry and Permissions

Most of the time, it is regular users who perform data entry. To make data entry easy, you can create one or more views so that a certain view lists only the necessary columns. The user can then use that view and not worry about listing the desired columns.

As mentioned for a table, a user who has to create records using a view must be granted the INSERT permission on that view. Of course, the user must be allowed to open the view, which is possible if granted the SELECT permission. To resume:

  • If you want a user to be able to create records using a view, you must grant him or her both the SELECT and the INSERT permissions
  • If you want to protect the table(s) that hold(s) the records the view displays, you should deny some permissions (mostly the SELECT permission) on that (those) table(s) to the user

Performing Data Entry

If you perform data entry using a view, the data you provide would be entered on the base table. This means that the table would be updated automatically. Based on this feature, you can create a view purposely intended to update a table so that, in the view, you would include only the columns that need to be updated.

As long as a view allows it, you can perform data entry visually or using code.

To visually add a record, in the Object Explorer of the SQL Server Management Studio, after expanding the Databases and the Views nodes, right-click the view and click Edit Top 200 Rows. Once the view is opened, you can create the records.

To create a record using a Data Definition Language (DDL) command, use the INSERT keyword. If the view already contains the necessary columns, add the VALUES keyword as in the following formula:

INSERT [INTO] ViewName VALUES(Column1, Column2, Column_n);

Alternatively, after the name of the table, put the list of columns in parentheses. In both cases, in the parentheses of VALUES, type the necessary values.

Practical LearningPractical Learning: Performing Data Entry on a View

  1. While the Presentation.AvailableUnits view is still opened, in the Results pane, click the first NULL field under UnitNumber
  2. Ceate new records as follows:
     
    UnitNumber Bedrooms Bathrooms Price OccupancyStatus
    114 3 2 1280.00 Available
    115 0 1 785.00 Available
    213 2 1 1065.00 Occupied
    214 1 1 895.00  
    313 2 2 1165.00 Available
    315 2 1 1065.00 Occupied
  3. Close the AvailableUnits window

Outputting the Result

Based on our previous description, here is an example of creating a creating a record using a view with code:

INSERT INTO Personnel.StaffMembers
VALUES(N'29730', N'Philippe', N'Addy');
GO

Just as done with table, you can create many records with with one INSERT ... VALUES statement. Add each set of values in its own parentheses. Here are examples:

INSERT INTO Personnel.Identifications
VALUES(N'28084', N'Joan', N'Shepherd'),
      (N'79272', N'Joshua', N'Anderson'),
      (N'22803', N'Gregory', N'Swanson'),
      (N'83084', N'Josephine', N'Anderson');
GO

As done for a table, after creating one or more records, if you want to see the result, add an OUTPUT.INSERTED expression after the name of the view. If you want to show the names of columns, use the following formula:

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value_X)

If you want to show all columns, use the asterisk. Here is an example:

INSERT INTO Personnel.Identifications
OUTPUT INSERTED.*
VALUES(N'79272', N'James', N'Anders'),
      (N'27924', N'Gregory', N'Hope'),
      (N'39742', N'John', N'Anderson');
GO

This would produce:

OUTPUT

Practical LearningPractical Learning: Outputting the Result of Data Entry

  1. In the Object Explorer, right-click LambdaSquare2 and click New Query
  2. To create new records using a view, type the following code:
    INSERT INTO Presentation.AvailableUnits
    OUTPUT INSERTED.*
    VALUES(113, 1, 1, 895.00, NULL),
          (215, 0, 1, 785.00, N'Available'),
          (314, 3, 1, 1225.00, N'Occupied');
    GO
  3. To execute, on the main menu, click Query -> Execute
     
    Outputting the Result
  4. Click inside the Query window and press Ctrl + A
  5. To see all records, type the following:
    SELECT * FROM Presentation.AvailableUnits;
    GO
  6. To see the result, on the SQL Editor toolbar, click the Execute button Execute

A View With Alias Names

It is important to know that a view is more of a table type than any other object. This means that a view is not a function but it can use a function. The word argument here only means that some values can be passed to a view but these values can be specified only when creating the view. They are not real arguments.

When structuring a view, you can create placeholders for columns and pass them in the parentheses of the view. This would be done as follows:

CREATE VIEW CarIdentifier([Tag #], Manufacturer, [Type of Car], Available)
. . .

If you use this technique, the names passed in the parentheses of the view are the captions that would be displayed in place of the columns of the view. This technique allows you to specify the strings of your choice for the columns. If you want a column header to display the actual name of the column, write it the same. Otherwise, you can use any string you want for the column. If the name is in one word, you can just type it. If the name includes various words, include them between an opening square bracket "[" and a closing square bracket "]".

After listing the necessary strings as the captions of columns, in your SELECT statement of the view, you must use the exact same number of columns as the number of arguments of the view. In fact, each column of your SELECT statement should correspond to an argument of the same order.

Because, as we stated already, a view is not a function and the values passed to the view are not real arguments, when executing the view, don't specify the names of arguments. Simply create a SELECT statement and specify the name of the view as the source. Here is an example:

Practical LearningPractical Learning: Ending the Lesson

  1. Close the Query window
  2. When asked whether you want to save, click No
  3. Close Microsoft SQL Server

Exercises

   

Lesson Summary Questions

  1. Which of the following is a keyword?
    1. ERROR
    2. CUSTOM
    3. SKIP
    4. PORT
    5. VIEW
  2. What's the keyword used to execute a view?
    1. SELECT
    2. EXECUTE
    3. WITH
    4. SET
    1. SWITCH
  3. If you want the entry of a view in a database to be encrypted, what clause must you add when creating the view?
    1. DO ENCRYPTION
    2. WITH ENCRYPTION
    3. FOR ENCRYPTION
    4. SET ENCRYPTION
    1. ENCRYPTION WHEN DONE
  4. If you want the bind a view to the schema of its parent table, what clause must you add when creating the view?
    1. DO SCHEMA BINDING
    2. WITH SCHEMA BINDING
    3. FOR SCHEMABINDING
    4. WITH SCHEMABINDING
    1. SET SCHEMA BINDING ON
  5. What's the basic formula to delete a view?
    1. CREATE OBJECT::VIEW [SchemaName.]ViewName
      AS
      SELECT Statement
    2. SET VIEW [SchemaName.]ViewName
      AS
      SELECT Statement
    3. CREATE [SchemaName.]ViewName
      AS
      SELECT Statement
      AS VIEW
    4. WITH VIEW [SchemaName.]ViewName
      CREATE
      SELECT Statement
    5. CREATE VIEW [SchemaName.]ViewName
      AS
      SELECT Statement
  6. What's the basic formula to delete a view?
    1. DELETE VIEW ViewName
    2. REMOVE VIEW ViewName
    3. DROP VIEW ViewName
    4. EXECUTE sp_remove ViewName
    5. KILL VIEW ViewName
  7. What are the permissions of a view (Select 2)?
    1. VIEW DATABASE STATE
    2. ALTER
    3. CREATE VIEW
    4. EXECUTE
    5. SELECT
  8. What are the permissions of a view (Select 2)?
    1. ALTER ANY AUDIT
    2. BACKUP LOG
    3. CONTROL
    4. TAKE OWNERSHIP
    5. CONNECT REPLICATION
  9. What are the permissions of a view (Select 2)?
    1. REFERENCES
    2. VIEW CHANGE TRACKING
    3. ALTER ANY VIEW
    4. CREATE DEFAULT
    5. BACKUP DATABASE
  10. What is the basic formula to rename a view?
    1. RENAME OBJECT::VIEW CurrentViewName, NewName;
    2. EXECUTE sp_rename NewName FROM CurrentViewName,;
    3. sp_rename NewName FROM CurrentViewName;
    4. sp_rename CurrentViewName, NewName;
    5. RENAME VIEW CurrentViewName, NewName;
  11. What's the basic formula to change a view?
    1. EDIT OBJECT::VIEW ViewName
      AS
      SELECT Statement
    2. ALTER VIEW ViewName
      AS
      SELECT Statement
    3. CHANGE VIEW ViewName
      WITH
      SELECT Statement
    4. WITH VIEW ViewName
      AS
      SELECT Statement
    5. EXECUTE sp_update ViewName
      AS
      SELECT Statement

Answers

  1. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  2. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Wrong Answer: There is no DO operator used like that
    2. Right Answer: To scramble the entry of a view in a database, add the WITH ENCRYPTION clause
    3. Wrong Answer: FOR is the wrong keyword
    4. Wrong Answer: SET is not used to encrypt a view
    5. Wrong Answer: That expression will cause an error
  4. Answers
    1. Wrong Answer: There is no value DO SCHEMA BINDING expression in Transact-SQL
    2. Wrong Answer: The SCHEMA BINDING expression will create an error
    3. Wrong Answer: The FOR word will cause an error
    4. Right Answer: To bind a schema to its parent table, add a WITH SCHEMABINDING clause when creating the view
    5. Wrong Answer: There is no valid SET SCHEMA BINDING expression
  5. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  6. Answers
    1. Wrong Answer: The DELETE operator is not valid
    2. Wrong Answer: There is no REMOVE operator in Transact-SQL
    3. Right Answer: The basic formula to delete a view is DROP VIEW Name
    4. Wrong Answer: There is no stored procedure named sp_remove
    5. Wrong Answer: There is no KILL VIEW command
  7. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Right Answer
  8. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Right Answer
    4. Wrong Answer
    5. Right Answer
  9. Answers
    1. Right Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
  10. Answers
    1. Wrong Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer
    5. Wrong Answer
  11. Answers
    1. Wrong Answer
    2. Right Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer
 
 
   
 

Previous Copyright © 2008-2011 FunctionX.com Next