Views in SQL: Creating Table-Like Structures from Groups of Tables

SQL Server has an object named “view” that lets you pull data together to create a table-like data set from existing tables. You can’t manipulate data like you can do with a real table. However, if you have the same data returned frequently, you can set this data set up as a view. In your front-end application (such as a website or report), this view object is used in the same way you use a table.  Views improve performance for frequently used queries, aggregate large data sets, unifies complex queries and restrict users from accessing the “real” tables.

Understand more about SQL queries and programming with a class at Udemy.com.

Creating a View

Views are stored in each database it’s related to. Let’s say you want to create a view for an ecommerce databases named “store.” You would create your view within this database. Open your SQL Server Management Studio and expand the database by clicking the plus sign next to the database name.

Right-click the “Views” icon and select “New View.” SQL Server has a graphical interface to help you create a view instead of using complex SQL coding to create them.  The following window displays:

The window defaults to a list of tables. Views are comprised of tables, other views, functions and synonyms. Normally, you would use tables and views to create a view. Since views are similar to tables, your view will query another view as if it’s a table data set. In the above view, you see a list of tables similar to an ecommerce store. Click each table you want to include in your view and click “Add.” Notice the designer adds the table to your workspace in SQL Server Management Studio.

Click “Close” to close the table window. You can always reopen it and add more tables by right-clicking the designer and selecting “Add Table.” Now you’re ready to create your view and work with the designer. The designer window workspace looks like the following image:

Notice how the designer has a link between the order and customer tables. This link indicates that there is a relationship between the two tables. SQL Server is a relational database, so you have a primary and foreign key relationship in most of your tables. In the above example, CustomerId is the primary key for the customer table. CustomerId is stored in the order table as a foreign key. With the primary and foreign key relationship, your designer automatically knows to link the two tables together.

Understand SQL, databases and structured data with a course at Udemy.com.

If you notice under the designer’s GUI, Management Studio also writes the basic SQL query for you. In this example, the “inner join” statement is written for the relationship. You notice there is no filter or “where” clause. If you run this view right now, all customers and linked orders display. You could actually use this view if you commonly use this statement in your SQL queries. However, something this basic is rarely used for views. Views give you the advantage of creating large data sets, so you can have 10, 20 or more columns in your data set. Since you use a view like a table, you then write your “where” clause in the SQL statement that queries this view.

Do specify columns, drag and drop each column from the table to the center panel. Don’t duplicate columns. For instance, don’t drag and drop the customerId column from both the order and the customer table. These columns have the same data, so you only need to use the column once.

Notice the middle panel in the above image. If you use Microsoft Access, you’ll notice the format is similar to an Access designer. You can set your table column’s data type. This is useful if you use functions or aggregate functions on your view. For instance, suppose you want to add up all customer’s order history, so you can view the total amount a customer has ordered from you. This uses the “sum” SQL aggregate function. SQL will try to “guess” the column’s data type, but you can also manually set it in this panel. For a total revenue or summed money column, you probably want to use the money or decimal data type. You should match the data type with the source column’s data type to avoid any conversion errors. For instance, if you set a data type as integer but decimals are used in the column, SQL returns an error when you run the view code.

You can also sort your view in this panel. Sorting a view is useful if you want to use the raw data to display information on a report. You can sort on multiple columns. You can also sort on ascending order (which is the default) or descending order. “ASC” is used for ascending and “DESC” is used for descending order.

Finally, how do you query your new view? In the above example, the view is basically a list of customers and orders. You could use this type of query in your front-end code when you want to show a customer a list of current and past orders. For instance, suppose customer Id 5 wants to see all his orders. The following code uses the above view to show the customer a list of orders:

select * from v_CustomersAndOrders where customerId = 5

In the above code, the view’s name is “v_CustomersAndOrders”. Typically, views are prefixed with some kind of indication such as “view” or “v_”. The view’s data set returns all customers and orders, but the query above filters the resulting data set to only customers with an Id of 5. If you ordered your data set in the view, the data would already be ordered. If not, you must order the results in your front-end query.

After you create your views, you can freely use them in code or even in the Management Studio workspace. Views improve performance on your database for large data sets, so incorporate views when you need to return several columns at once.

Learn how to work more efficiently in SQL with a class at Udemy.com.