ALTER VIEW - Modifying Existing Views in SQL Server

Q

How To Modify the Underlying Query of an Existing View in SQL Server?

✍: FYIcenter.com

A

If you have an existing view, and want to change the underlying SELECT statement, you can use the "ALTER VIEW ..." statement to redefine the view. The tutorial exercise below shows you how modify an existing view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od

Â

â‡’Understanding and Managing Views in SQL Server

â‡’â‡’SQL Server Database Tutorials

2016-11-04, 1037👍, 0💬

sys.sql_modules - Getting View Definitions Back in SQL Server
How To Get the Definition of a View Out of the SQL Server in SQL Server? If you want get the definition of an existing view back from the SQL Server, you can use the system view called sys.sql_modules, which stores defitions of views and procedures. The sys.sql_modules holds view definitions identif... 2016-11-05, 1273👍, 0💬

Assigning New Column Names in a View in SQL Server
How To Assign New Column Names in a View in SQL Server? By default, column names in a view are provided by the underlying SELECT statement. But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this cas... 2016-11-03, 1067👍, 0💬

ALTER VIEW - Modifying Existing Views in SQL Server
How To Modify the Underlying Query of an Existing View in SQL Server? If you have an existing view, and want to change the underlying SELECT statement, you can use the "ALTER VIEW ..." statement to redefine the view. The tutorial exercise below shows you how modify an existing view: USE FyiCenterDat... 2016-11-04, 1038👍, 0💬

Using ORDER BY to Define a View in SQL Server
Can You Use ORDER BY When Defining a View in SQL Server? Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view. But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise ... 2016-11-04, 1002👍, 0💬

Updating Data in a View in SQL Server
Can You Update Data in a View in SQL Server? Can you update data in a view? The answer is no. But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exerc... 2016-11-04, 997👍, 0💬

Creating a View with Data from Another View in SQL Server
Can You Create a View using Data from Another View in SQL Server? Can You Create a View with Data from Another View? The answer is yes. A view can be used as a table to build other views. The tutorial exercise below shows you how to create a view using data from another view: USE AdventureWorksLT; G... 2016-11-05, 934👍, 0💬

Determining Data Types of View Columns in SQL Server
How Column Data Types Are Determined in a View in SQL Server? When you define a view, its columns are defined through a list of expressions in the underlying SELECT statement. Their data types will be determined implicitly by the expressions. For example, if the column expression is a column name of... 2016-11-03, 932👍, 0💬

Deleting a Table That Is Used by a View in SQL Server
What Happens If You Delete a Table That Is Used by a View in SQL Server? Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble. But that view will become invalid. The tutorial exercise below shows you wh... 2016-11-04, 908👍, 0💬

Deleting Data from a View in SQL Server
Can You Delete Data from a View in SQL Server? Can you delete data in a view? The answer is no. But if the question is "Can you delete data from the underlying table through view?" The answer is then yes. SQL Server will allow you to delete data from the underlying table through a view. The tutorial... 2016-11-03, 865👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.