SQL Server - Deleting a Table That Is Used by a View in SQL Server

Deleting a Table That Is Used by a View in SQL Server

Q

What Happens If You Delete a Table That Is Used by a View in SQL Server?

✍: FYIcenter.com

A

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 what happens to the view, when the underlying table is deleted:

USE FyiCenterData;
GO

SELECT * INTO fyi_links_copy 
   FROM fyi_links WHERE counts > 0;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
50015

DROP TABLE fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fyi_links_copy'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'fyi_links_view' 
   because of binding errors.

⇒Understanding and Managing Views in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-04, 593👍, 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, 672👍, 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, 664👍, 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, 626👍, 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, 594👍, 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.