In part 3 I continue building on the table structure I learned about in part 2 of my training on How to Use Microsoft SQL by learning about creating custom views.
Creating the Tables
To learn how to setup a custom view in Microsoft SQL, we setup a second table so that we could join the data together. If you know Excel, then a good comparison is “Lists”, which are a separate table that can be referenced for a single cell. The second table shares a column name with the first table and it’s used to create a ‘list’ of sorts for the data in the first table.
Once the second table has been created, we right-clicked on each table and selected “Edit Top 200 Rows” so we could populate it with some data. Microsoft SQL creates little red circles with white exclamation points when you’re entering data. This is meant to show you that the data isn’t saved. Clicking the red exclamation point in the top menu will save the data. ‘Grayed out’ data is read-only.
Creating the Views
Right-clicking on the database and choosing, “New View” will bring up a box that prompts you to choose a table (or tables) or click “Close”. Once you’ve selected your table(s), click close to view the View. A view is it’s own object in the database so it can be saved separately and referenced in other SQL code later on. Once a View is created, it can be edited.
Along the top menu are different views that display the same data in different ways. One box brings up a visual view of the tables where you can draw lines between them. These lines are called “joins” and are simply visual representations of code, which can be viewed in a separate window. Unlike other programs, multiple views can be viewed at once. If you’ve ever used Access, you may be familiar with the screen where you can draw lines between tables. Microsoft SQL works similarly. Like in Access, the bold rows are the primary keys.
Editing the Views
Each row can be selected to appear or not appear in the view. This creates a corresponding “SELECT” statement in the SQL code. The “Alias” column adds a ‘nice name’ to the data to help you distinguish it later on. The “Output” column further distinguishes whether or not to display the data. “Sort Type” and “Sort Order” are used to sort the views and “Filter” is used to filter what data is displayed.
Saving the View
The View is its own object so it can be saved just like a table. To keep the object straight in your mind when coding, it’s best practice to append the View name with “vw” like this: vw[ViewName].