Creating and Editing Database Views

Go Up to Common Tasks

A database view, also known as a materialized view, is a dynamic virtual table that reflects changes made to the parent entity or table data. In SQL, a view is the set of columns or attributes resulting from a SELECT statement. It is not part of the physical schema of a relational database and does not incur any extra storage overhead.

Some of the uses for views include:

• Presenting a subset of the data included in a table.
• Joining and simplifying tables into a single virtual table.
• Hiding the complexity of data.
• Depending on the database platform, providing an additional layer of security by restricting user access to specific columns and/or rows.
• Acting as aggregated tables, where aggregated data such as sum and average, are calculated and presented as part of the view.
• Limiting the amount of exposure of sensitive data in the tables.
• Creating a layer of abstraction to simplify complex data, transparently partitioning the actual underlying table.
• Facilitating database normalization beyond second normal form.
• Making it easier to decompose the underlying table and then join the parts without losing any information.
• Some database platforms offer more properties than others that can be applied to physical view definitions.

In the View Editor you can select specific attributes from entities on the Entities tab or specific columns from tables on the Tables tab to include in the view. In the View Editor, you can reorganize the column order in the view and add notes and descriptions to your view. You can also add Pre and/or Post SQL that generate immediately prior to or after the CREATE OBJECT statement.

1. In the Data Model Explorer, expand the node of the model to which you want to create a view.
2. Right-click the Views node and then click New View.
3. Complete the tabs of the View Editor and then click OK to implement the changes to the view.

Notes

• You can reference the same table twice in a view, doing so automatically creates an alias for the parent table that is replicated more than once in the view.
• You can also create a view by selecting the tables in the Data Model Window that you want to include in the view and then choosing Insert > View. Clicking anywhere in the Data Model Window will create a new view that is comprised of the tables you selected. Double-click the view to edit its properties.
• After you have created the view, you can edit it by right-clicking the view and then clicking Edit View from the shortcut menu.
• The status bar at the bottom of the screen shows the total number of views in the selected model or submodel.
• In the Diagram Explorer tree you can see the views created both under the Views node and also by expanding the relevant entity or table node.
• Views that use the attributes/columns of the current entity/table are displayed on the Dependencies tab of the entity or table editor.

The following describes options in the View Editor that require additional explanation.

Entity/Table tab

• Logical Only: In a logical model, if selected, the view will not be implemented when generating a physical model and will be ignored when comparing the logical model with a physical model.
• Physical Only: In a physical model, if selected, the view will be ignored when comparing the physical model with a logical model.

Attribute/Column tab

• View Columns/Attributes > Column Expression: This is a standard SQL expression that creates a computed column, combining existing data in order to produce data that you do not need to store but want to have available. For example, in the Northwind model, the Product Sales for 1997 view uses the following column expression to calculate the value of sales using the unity price, quantity sold and the applicable discount.
  Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100
  A column expression can only be added when no column is specified and requires an alias.
• Select distinct: If selected, adds a SELECT DISTINCT statement to the View DDL, which selects and shows only unique rows.
• Include view column list in DDL: If selected, includes the columns selected on this tab in the DDL created for the View. For example, when this option is not selected the following DDL is created.

CREATE VIEW View1 AS
SELECT En.p p, En.a a, En.b b, Ent.k k, Ent.z z, Ent.y y, Ent.p f
FROM Entity1 En, Entity2 Ent
WHERE Ent.p = En.p

When Include view column list in DDL is selected, the following DDL is created.

CREATE VIEW View1
(p, a, b, k, z, y, f) AS
SELECT En.p, En.a, En.b, Ent.k, Ent.z, Ent.y, Ent.p
FROM Entity1 En, Entity2 Ent
WHERE Ent.p = En.p

Column Alias dialog

This dialog appears if you try to add columns with duplicate names to a view. To use the columns in the view, you must assign an alias to each duplicate column name. If an alias is not assigned to the duplicate column name, an SQL syntax error will result when generating the view. Note that a column expression can only be added when no column is specified. So you may have a column with an alias, or an expression with an alias. The expression requires an alias.

Where tab

Define a WHERE clause of the view, such as WHERE [city] = "Monterey", which selects rows matching the stated criteria.

With (CTE) tab

Define the WITH common table expression (CTE) for SQL Server 2005 and later, Azure SQL DB, and Azure Synapse Analytics. You can use a CTE clause in a CREATE VIEW statement as part of its defining SELECT statement. Note that you can apply multiple CTEs to a view and that CTEs can be recursive.

Create, edit, and delete CTEs using this tab. When you click Add or Edit, the View CTE Window opens.

CTE objects are created during reverse engineering and import from SQL. It also generates the appropriate DDL code during forward engineering.

The following examples help explain this feature:

Example 1

CREATE VIEW vwTest1 AS
CREATE VIEW vwTest1 AS
WITH cteData As (
SELECT t1.ColumnOne C1, t1.ColumnTwo C2
FROM TestTable1 t1
INNER JOIN TableRels tr ON tr.TestTable1ID = t1.TestTable1ID
INNER JOIN TableTable2 t2 ON t2.TestTable2ID = tr.TestTable2ID
)
SELECT C1, C2
FROM cteData

Example 2

CREATE VIEW vwTest2 AS
WITH cteData As (
SELECT t1.ColumnOne C1
FROM TestTable1 t1
INNER JOIN TableRels tr ON tr.TestTable1ID = t1.TestTable1ID
INNER JOIN TestTable2 t2 ON t2.TestTable2ID = tr.TestTable2ID
)
SELECT C1, t2.ColumnTwo
FROM cteData

Group By tab

Select columns and tables to include in a GROUP BY clause for the view, such as
SELECT Product, SUM (Sales) GROUP BY Product
which allows you to easily find the sum for each individual group of column values.

Having tab

Enter a HAVING clause for the view, such as,
SELECT Product, SUM (Sales) GROUP BY Product HAVING SUM (Sales) > 10000
which allows you to test for result conditions.

DDL tab

Displays and allows you to edit the CREATE VIEW statement needed to build the view. In physical models, ER/Studio Data Architect uses the platform-specific parser of the database platform of the selected model to generate the view. If you are adding a view to a logical data model, the default parser AINSI SQL is used unless you change the View Parser in the Logical Model Options to another parser. For more information, see Defining Model Options for the Selected Model.

SQL Validation dialog

If any errors are detected, they are displayed in the Validation Results area. You can then exit the SQL Validating dialog, correct the errors on the DDL tab and then rerun the validation check.

Options tab

When selected, the With Check Option and With Read Only options add a WITH CHECK OPTION and WITH READY ONLY clause, respectively to the DDL statement.

Definition tab

Enter or edit a definition for the view. If the target database supports it, ER/Studio Data Architect adds this definition as a view comment when generating SQL code.

Notes tab

Add notes about the view that you can format using HTML tags that are processed when creating an HTML Report of the model.

Where Used tab

Shows the models and submodels where the view is used according to definitions you set.

Dependencies tab

Create, edit or delete any scripted procedures that are bound to the view. If any procedures or functions are bound the view, the folder is expandable. Double-click or right-click the view folder to access the view editor. For information on creating procedures see Create an SQL Procedure and for information on creating functions, see Creating and Editing Functions.

PreSQL & PostSQL tab

Enter SQL to be applied before or after the CREATE OBJECT statement. The PreSQL and PostSQL scripts entered here are included in the script when you generate the physical database.

Permissions tab

Sets access roles and user permissions for the view. Keep in mind that the more specific the permissions are, the more time you may have to spend maintaining and updating them. The Roles and Users must be previously assigned to appear in the View Editor. For more information, see Creating and Editing Database Roles and Creating and Editing Database Users.

Security Information tab

Displays the current security information as defined in the Data Dictionary. You can add or edit security reporting information in by double-clicking the field you want to change. To set the model-wide default value for a property, go to the Data Dictionary tab and expand the Data Security Information Node. For more information, see Enforcing Security Using Data Security Types and Properties.

Attachment Bindings tab

Binds an external piece of information or attachment to the database view. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect displays the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary and must be applied to the view before they will display on this tab. For more information, see Attaching External Documents to the Data Model.

See Also

• Common Tasks
• Creating and Editing Submodels