We haven’t even touched how to use the DLM language for inserting data through a view, so that’s is what we’ll cover in the next part of the series. I promised more in the first part, but rather than making this a long and boring article, we’ll continue our journey in the next one. In this part of learning the CREATE VIEW SQL statement, we learned how to use the ALTER VIEW command to modify an existing view and change the output. However, now rather than eight columns we only fetch three. When we initially created this view using the CREATE VIEW SQL statement, we specified that all columns from theĮmployees table should be retrieved. Object Explorer, expand the Views folder, then vEmployeesWithSales, and then the Columns folder, we should see the following: If you remember the view’s definition, views are pretty much just virtual tables. This is the SQL Server way of saying that we have referenced more than one column with the same name in the FROM clause. In our case, you’ll see the error message “Ambiguous column name ‘EmployeeID’”. Note: Now, that I’ve mentioned batches, bear in mind that CREATE VIEW SQL must be the only statement in a batch or the first statement in a query batch or you might get an error from SQL Server.Simply remove the alias from the EmployeeID column and execute the This is a very common mistake, and that’s why it’s always a good idea to run and check only the SELECT part in aīatch to see what it returns. Only exist in the Employees table, so we don’t need to fully qualify those. On the other hand, FirstName and LastName Notice that the EmployeeID column is fully qualified because EmployeeID exists inīoth tables that we are referencing. Instead of fetching all columnsįrom the Employees table, we are returning just three columns: After this, it goes the view definition:īefore we run the script, let’s go through the SELECT part to see what we changed. Type ALTER VIEW, followed by the name of the view, and then followed by an AS. Hey, if you like typing and doing it old school, just make sure that you’re connected to the appropriate database, Previously, we generated a T-SQL script to modify our view within SSMS. Note that changing the view using this command does not affect dependent stored procedures or triggers and does not change permissions. Therefore, let’s change the previously created view with the CREATE VIEW SQL statement by using the ALTER VIEW Other words, anything we do to the underlying table when saying select everything from (Employees.*) will shine There could be a problem if we change the underlying table e.g. Why? ForĮxample, let’s say that we have an application using this view and that it’s relying on a specific output AKA the Selecting everything is generally a bad thing. What we are interested in is the SELECT part of the code. Once created, we can query this view by saying select everything from the name of the view and we should get the In this particular case, this is whatĬolumn ‘Sales.ProductID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. GROUP BY clause, followed by ORDER BY or otherwise, we’d run into some errors. The SUM is considered an aggregate because, in general, it adds the numbers together. Statement that has SUM in it which is an aggregate: We only have four products in our table, so that’s why we’re selecting only the top three records.Įverything looks good, so we can execute the whole CREATE VIEW SQL statement to create the view with the SELECT As you can see, we have our Long-Sleeve Logo Jersey product in different sizes and sold quantities. Syntax again since T-SQL is exactly the same as in CREATE VIEW SQL statement except the fact that instead of theĪs I mentioned earlier, let’s use the code from below to create a bit more complex view:īut before we run the script, we can again just highlight the SELECT statement and see what it returns as shownīasically, what we are doing here is for each product in the Product table, we are fetching all the quantities and add them together per product. Ironically, before modifying a view, we will create another view with a bit more complex T-SQL using aggregates in it rather than having a simple SELECT statement that is pulling everything from a table. A view is based on the result set from a query, and this command allows us to change the structure and definition of a query. The primary goal will be to get familiar with the ALTER VIEW command used to modify views and change the output. In the first write-up so in order to follow along, head over and read the Creating views in SQL Server We will continue using examples on a sample database and data created In this one, we are moving on and focusing on how to modify views. In my previous article, we looked at how to use the CREATE VIEW SQL statement to create views.
0 Comments
Leave a Reply. |