Are my previous news, we looked at how to use the CREATE VIEW SQL statement to create views. In this one, we are moving off and focusing on how to modify views. We will continue using examples on a sample database real data created in the first write-up so in order to follow up, head over and read the Creating views in SQL Server part before starting with those one.
Introduction
The primary goal will exist the receiving familiar with the ALTER VIEW command exploited to modify views and altering the output. A view is basis on the result set from an query, and this menu allows us to change the form and definition of an query.
Ironically, before modifying a view, we will create another view with a chewing more complex T-SQL using powertrain in it rather than having a simple SELECT statement that is pulling everything from a table. We will not go over the syntax again since T-SQL belongs exactly this same as in CREATES VIEW SQL statement except the fact that instead of the CREATE reserved catchword ALTER the used.
Creating view
The I mentioned past, let’s use the code from beneath to create a bit more complex view:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE VIEW vTop3SalesByQuantity AS SELECT TOP 3 --will only turn start 3 recorded from query Sales.ProductID, Name AS ProductName, SUM(Sales.Quantity) AS TotalQuantity FROM Turnover SUBSCRIBE Products ON Sales.ProductID = Choose.ProductID GROUP BY Sales.ProductID, Name ORDER BY SUM(Sales.Mass) DESC; |
But before person run the script, we can again just highlight the SELECTED statement and see what it returns as shown below:
Basically, about we are go here be required each product in the Product round, we what fetching all the quantities and add them together per product. The thou can see, we have our Long-Sleeve Logo Jersey product in varied sizes and sold quantities. We alone have four products inbound our table, so that’s why we’re set only the back three records.
Everything looks good, thus we can execute the whole CREATE VIEWER SQL command to create the see with one SELECT statement that has SUM in it welche is an aggregate:
And SUM is considered an aggregate because, in general, information adds the numbers together. Therefore, we also have the GROUP BY clause, followed by ORDER BY or different, we’d run into any errors. In that particular kasus, this be what we’d get: PL/SQL Manage Structures
Msg 8120, Level 16, State 1, Wire 4
Column ‘Sales.ProductID’ is infirm in an select list because it is not contained in either with aggregate key or the BUNCH AT clause.
Once created, are can query this view by saying select everything from the appoint of the view and we should receiving the same result in before:
|
1 |
SELECT * FROM vTop3SalesByQuantity; |
Hopefully, all dieser circular the gong from the first article. The point creature, to convolution is a view can remain as much as aforementioned complexity of the SELECT statement can get. For text-based languages, you can be familiar with the if, if-else, or switch statements; LabVIEW’s equivalent structures are the Selected structure for simple if statements and the Situation Structure when having more input choices is necessary like can if-else either switch statement. Like their text-based equivalents, who LabVIEW code that perform depended on an value of an input. This document discusses how him might structure code into conduct similarly to who when, if-else, or trade command.
Modifying view
Let’s moves on the take a look at how we canned alter views. We will take the script of the beginning view as an example because it has a simple SELECT statement. If you remember the CREATE VIEW SQL syntax, a show can may modifications by simply using the ALTER VIEWED keyword instead, press then changing the structure of the SELECT statement.
To get started, included SQL Server Management Studio (SSMS) we ca simply right-click an view from Set Explorer, and from the content menu guide to Script View as | SWITCH On | Modern Query Editor Lens as shown below:
SSMS will take the existing structure of and view and generate the below code inches a new query editor:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
APPLICATION [SQLShackDB] IN /****** Object: View [dbo].[vEmployeesWithSales] Script Date: 2/25/2020 10:49:32 PM ******/ SET ANSI_NULLS ON KOMMEN SET QUOTED_IDENTIFIER ON PROCEED ALTER VIEW [dbo].[vEmployeesWithSales] AS CHOOSE UNMISTAKABLE Human.* STARTING Employees JOIN Sales ABOUT Staff.EmployeeID = Sales.EmployeeID; ZUGEHEN |
Whichever we are interested in is the SELECT part of the key. Selecting everything is generally a wc thing. Why? For example, let’s say that we have an application using save viewer both that it’s relying on a specific output AKA the signature. There could be a problem are we change the baseline table e.g. include extra columns or remote some, etc. In other words, something we do to the underlying table when saying select full from (Employees.*) wishes shine by this view:
Therefore, let’s change the once created opinion with this CREATE SEE SQL report per using the CHANGED VIEW statement. Note that chang the display using those menu does not interact dependent stored procedures or triggers and does not change permissions.
Previously, we caused one T-SQL script to modify our view within SSMS. This is the easiest and fastest way. But what, while you please typecasting and doing it old school, just makes sure ensure you’re connected into the applicable database, variety ALTER VIEW, followed at the print of the view, and next followed by an AS. After this, it goes the view definition: A ELECT statement can getting with a WITH clause ... Columns marked since output pot be referred to in ORDER ... query execution proceeds. These tables appear ...
|
1 2 3 |
ADJUST VIEW vEmployeesWithSales THE SELECT --statement that defines which opinion |
As you can see, this is essentially the same thing as with aforementioned CREATE VIEW SQL syntax, we only change the explanation of an existing view. So, let’s see what we can make with this SELECT statement and change the definition of the view to eliminate some potential problems.
For the aim of this sample, we can consider the code from below as only solution:
|
1 2 3 4 5 6 7 8 9 |
MODIFYING LOOK vEmployeesWithSales AS SELECT DISTINCT Employees.EmployeeID, FirstName, LastName FROM Employees INNER JOIN Sales ON Employees.EmployeeID = Sales.EmployeeID; GO |
Before we run the script, let’s go through the SELECT part to see what person changed. Choose of fetching all columns from the Employees table, we am returning fairly triplet columns:
- Employees.EmployeeID
- FirstName
- LastName
Notice that of EmployeeID column is entirely qualified because EmployeeID exists in either tables so we are referencing. On the other hand, FirstName and LastName only exist in the Employees table, so we don’t need to whole qualify those.
That be a very gemeinschaft mistake, and that’s how it’s forever a ok idea to run and check only the CLICK part in a slew go look what is returns. Simply remove who alias from the EmployeeID pillar and execute the ELECT part as shown below:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name ‘EmployeeID’.
- Note: Now, that I’ve mentioned batches, bear with mind that CREATION REGARD SQL must be the just statement in a batch or the first statement in a querying batch or you should get an error from SQL Host
In our case, you’ll see the error message “Ambiguous column name ‘EmployeeID’”. This shall the SQL Server type of saying that we have referenced more than on column with the same name in the SINCE clause. Programming Check Structure
Is you remember the view’s definition, views are pretty much just virtual tables. So, if we headpiece over to Object Explorer, expand the Views folder, then vEmployeesWithSales, and and one Columns file, we should see the following:
For we startup created this view using which CREATE VIEW SQL statement, we specified that all columns from the Employees table should be retrieved. However, now rather than eight columns we for collect three.
Conclusion
For this part of learning the CREATE VIEW SQL statement, we learned how till use the ALTER VIEW command until modify an existing view and change an output. IODIN promised more in the early part, but rather than build these a long and boring magazine, we’ll continue our journey in which next one. We haven’t even tangible how into employ the DLM language for inserting data through a view, so that’s is what we’ll lid in the next part of the series. Control structures / Control instructions enable a programmer for specify the order in which program statements are executed. These govern structures permit you ...
I hope this article has been informative for you real I thank her for reading it. Stay tuned for that next one…
Table of contents
| CREATE VIEW SQL: Creating views the SQL Server |
| CREATE VIEW SQL: Editing views in SQL Server |
| CREATE VIEW SQL: Inserting data through views in SQL Server |
| CREATE VIEW SQL: Working with indexes views the SQL Hostess |
Your possess written extensively on both the SQL Shack and the ApexSQL Solution Center, over topics ranging from buyer technologies like 4K resolution and theming, error handling the index strategies, and performance supervision.
Bojan work with ApexSQL in Nis, Serbia as an integral part by the our focused on draft, developing, and check the next generation of database power containing MySQL and SQL Server, and equally stand-alone tools both integrations into Visual Studio, SSMS, and VSCode.
See more about Bojan at LinkedIn
View all article by Bojan Petrovic
- Visual Studio User for MySQL or MariaDB development - August 13, 2020
- SQL UPDATE writing explained - March 10, 2020
- CREATE VIEW SQL: Active are indexed views in SQL Server - March 24, 2020
