Представления и табличные объекты
Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.
Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.
Для создания представления используется команда CREATE VIEW , которая имеет следующую форму:
CREATE VIEW название_представления [(столбец_1, столбец_2, . )] AS выражение_SELECT
Например, пусть у нас есть три связанных таблицы:
CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL ); CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL ); CREATE TABLE Orders ( Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );
Теперь добавим в базу данных, в которой содержатся данные таблицы, следующее представление:
CREATE VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName As Product FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:
Теперь используем созданное выше представление для получения данных:
SELECT * FROM OrdersProductsCustomers
При создании представлений следует учитывать, что представления, как и таблицы, должны иметь уникальные имена в рамках той же базы данных.
Представления могут иметь не более 1024 столбцов и могут обращаться не более чем к 256 таблицам.
Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.
Команда SELECT , используемая в представлении, не может включать выражения INTO или ORDER BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET ). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде SELECT, которая извлекает данные из представления.
Также при создании представления можно определить набор его столбцов:
CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product) AS SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Изменение представления
Для изменения представления используется команда ALTER VIEW . Эта команда имеет практически тот же самый синтаксис, что и CREATE VIEW :
ALTER VIEW название_представления [(столбец_1, столбец_2, . )] AS выражение_SELECT
Например, изменим выше созданное представление OrdersProductsCustomers:
ALTER VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName AS Product, Products.Manufacturer AS Manufacturer FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Удаление представления
Для удаления представления вызывается команда DROP VIEW :
DROP VIEW OrdersProductsCustomers
Также стоит отметить, что при удалении таблиц также следует удалить и представления, которые используют эти таблицы.
Удаление базы данных
В этой статье описывается удаление пользовательской базы данных в SQL Server с помощью SQL Server Management Studio или Transact-SQL.
Предварительные условия
- Удалите все моментальные снимки базы данных, которые существуют для базы. Дополнительные сведения см. в разделе «Удаление моментального снимка базы данных» (Transact-SQL).
- Если база данных участвует в доставке журналов, удалите доставку журналов.
- Если база данных публикуется для репликации транзакций, опубликована или подписана на репликацию слиянием, удалите репликацию из базы данных.
Прежде чем удалить ее, рассмотрите возможность создания полной резервной копии базы данных. Удаленная база данных может быть повторно создана только путем восстановления полной резервной копии. Дополнительные сведения см . в кратком руководстве по резервному копированию и восстановлению локальной базы данных SQL Server.
Безопасность
Разрешения
Для выполнения инструкции DROP DATABASE пользователь должен, как минимум, иметь разрешение CONTROL на базу данных.
Использование Azure Data Studio (предварительная версия)
Удаление базы данных
- В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server, а затем разверните этот экземпляр.
- Разверните базы данных, щелкните правой кнопкой мыши базу данных, чтобы удалить, а затем нажмите кнопку «Удалить«.
- Чтобы подтвердить удаление базы данных, нажмите кнопку «Да«.
Использование среды SQL Server Management Studio
Удаление базы данных
- В обозревателе объектов подключитесь к экземпляру ядра СУБД SQL Server, а затем разверните этот экземпляр.
- Разверните базы данных, щелкните правой кнопкой мыши базу данных, чтобы удалить, а затем нажмите кнопку «Удалить«.
- Убедитесь, что выбрана правильная база данных, а затем нажмите кнопку «ОК«.
Использование Transact-SQL
Дополнительные сведения см. в разделе DROP DATABASE (Transact-SQL).
Удаление базы данных
- Соединитесь с ядром СУБД .
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере удаляются Sales базы данных и NewSales базы данных.
USE master ; GO DROP DATABASE Sales, NewSales ; GO Дальнейшие действия. После удаления базы данных
Создать резервную копию базы данных master . Если master необходимо восстановить, любая база данных, которая была удалена с момента последней резервной master копии, по-прежнему будет содержать ссылки в представлениях системного каталога и может привести к возникновению сообщений об ошибках.
Ограничения
- Системные базы данных не могут быть удалены.
- Дополнительные сведения см. в разделе DROP DATABASE (Transact-SQL).
Далее
- CREATE DATABASE (SQL Server Transact-SQL)
- ALTER DATABASE (Transact-SQL)
- DROP DATABASE (Transact-SQL)
- Обзор восстановления и восстановления (SQL Server)
What is an SQL View?
A view is a well-known feature in SQL. It allows you to create a virtual table based on an SQL query referring to other tables in the database. A view stores an SQL query that is executed whenever you refer to the view. This is a convenient way to get the desired data because it is easier to run a query stored in a view than to type a query from scratch. Hence, it is essential to be able to apply this feature efficiently.
This article introduces the fundamentals of SQL views. First, we’ll go through the definition, usage, and working principles of a view. Then, we’ll dive into examples of how to create, modify, and drop a view. At the end of the article, you’ll find real-world application examples of SQL views. In some situations, using a view is inevitable, and we’ll discuss this in detail.
Diving Into SQL Views
To put it simply, a view is a stored SQL query. Every time a view is used, it executes its stored query and creates a result set consisting of rows and columns.
An SQL view is called a virtual table because it does not store the rows and columns on the disk like a concrete table. Instead, it just contains the SQL query. Let’s look at the diagram below to get a better grasp of what a view is.
The diagram above presents a view called EntertainmentView . It stores a simple query that selects the Name columns from the Theater , Opera , and Cinema tables. When this EntertainmentView is used (executed), it returns a result set consisting of a column AllEntertainmentPlaces with rows from all three tables.
To create the EntertainmentView view presented above, we would use the following syntax:
CREATE VIEW EntertainmentView AS SELECT Name AS AllEntertainmentPlaces FROM Theater UNION SELECT Name FROM Oper UNION SELECT Name FROM Cinema;
After creating EntertainmentView , we can now run the following query instead of typing out the query above:
SELECT * FROM EntertainmentView;
SQL views are commonly used in relational databases. Relational databases offer various features, including SQL constraints like primary and foreign keys or indexing. These features can lead to complex queries.
Hence, it is handy to create views to store complex queries. You can use a view as if it were a table storing the data returned by a complex query.
In case you missed our articles on SQL Constraints, Primary Keys, and Foreign Keys, you can catch up by visiting these links!
How To Use Views in an SQL Query
Although SQL views are considered virtual tables, their usage does not differ from concrete tables. You can use a view just like any other table.
Let’s look at an example using a view called FriendView that is built on top of the Person table. See the Person table below:
| Id | Name | FriendId |
|---|---|---|
| … | … | … |
To create the FriendView view, we would use the following query:
CREATE VIEW FriendView AS SELECT person.Id as Id, person.Name as Name, friend.Name as Friend FROM Person person JOIN Person friend ON person.FriendId = friend.Id;
Then, we could run a query that treats the FriendView view like a normal table as shown below:
SELECT Name, Friend FROM FriendView WHERE Name LIKE 'F%';
The query above outputs two columns from the FriendView view. The rows included in the result set must fulfill the WHERE clause condition indicating that the person’s name must start with the letter “F.”
How Does an SQL View Work?
In the previous section, we learned that an SQL view can be treated like a normal table. However, under the hood, there is a difference between views and tables.
A table (concrete table) stores its data in columns and rows in the database. A view (virtual table) is built on top of the concrete table(s) it fetches data from and does not store any data of its own in the database. A view only contains the SQL query that is used to fetch the data.
To summarize, the result set of a view is not materialized on the disk, and the query stored by the view is run every time we call the view. Let’s look at the diagram below to understand the difference between concrete and virtual tables.
Let’s Create Some SQL Views
Now that we know what SQL views are and how to use them, let’s look at the syntax for creating views in SQL. See the basic query below:
CREATE VIEW view_name AS SELECT columns_list FROM tables_list;
The CREATE VIEW command creates a view named view_name . The AS clause is followed by an SQL query that will be stored in a view.
Now, let’s look at an example that uses the Food and Animals tables to create the AnimalFoodView view. See the Food and Animals tables below:
| Id | FoodName |
|---|---|
| 1 | Meat |
| 2 | Algae |
| 3 | Seeds |
| AnimalId | AnimalType | FoodId |
|---|---|---|
| 1 | Lion | 1 |
| 2 | Fish | 2 |
| 3 | Bird | 3 |
Let’s create a view called AnimalFoodView based on the tables defined above:
CREATE VIEW AnimalFoodView AS SELECT a.AnimalType, f.FoodName FROM Animals a JOIN Food f ON a.FoodId=f.Id;
Now that the view is created, we can treat it as a normal table. Please note that the view will generate its result set only when its name is called as shown in the sample query below:
SELECT * from AnimalFoodView;
The output below is equivalent to the output of the query stored in the view:
| AnimalType | FoodName |
|---|---|
| Lion | Meat |
| Fish | Algae |
| Bird | Seeds |
There could be a situation in which you want to create a view or replace it if it already exists. The syntax below shows a way to do this:
CREATE OR REPLACE VIEW view_name AS SELECT columns_list FROM tables_list;
Let’s replace our AnimalFoodView such that it would show only Fish and Bird rows.
CREATE OR REPLACE VIEW AnimalFoodView AS SELECT a.AnimalType, f.FoodName FROM Animals a JOIN Food f ON a.FoodId=f.Id WHERE AnimalType='Fish' OR AnimalType='Bird';
The original query that was saved in AnimalFoodView is now replaced with the above query. Let’s run another SELECT statement to make sure the replacement of the view took place:
SELECT * from AnimalFoodView;
The output below is again equivalent to the output of the query stored in the view:
| AnimalType | FoodName |
|---|---|
| Fish | Algae |
| Bird | Seeds |
With training and practice, it’s easy to work with SQL views. Our Working with Views course is a great way to do this!
What Else Can Be Done with SQL Views?
Modifying a View
It is also possible to modify a view after it is created. Let’s use the AnimalFoodView view defined in the previous section. If we want to use the AnimalId column instead of the AnimalName column, we can use an ALTER VIEW statement as follows:
ALTER VIEW AnimalFoodView AS SELECT a.AnimalId, f.FoodName FROM Animals a JOIN Food f ON a.FoodId=f.Id;
Now, the SELECT statement returns the animal IDs instead of the names.
SELECT * from AnimalFoodView;
| AnimalId | FoodName |
|---|---|
| 1 | Meat |
| 2 | Algae |
| 3 | Seeds |
If the view exists in the database, the CREATE OR REPLACE VIEW and ALTER VIEW commands have the same effect.
Dropping a View
If you want to delete a view, simply use the DROP VIEW command as shown below:
DROP VIEW AnimalFoodView;
After executing the DROP VIEW command, the view does not exist in the database anymore.
When Should You Use an SQL View?
Scenario I
One of the simplest reasons to use an SQL view is when you have a long and complex query. To save time on retyping and running the query, you can store it in a view and simply run the SELECT statement on that view.
Let’s look at an example that uses the Customers and Orders tables to create the CustomerOrderView view. See the Customers and Orders tables below:
| Id | CustName | OrderId |
|---|---|---|
| 1 | Anne | 1 |
| 2 | Carl | 2 |
| 3 | Taylor | 3 |
| Id | Product | Quantity |
|---|---|---|
| 1 | Apple | 4 |
| 2 | Apple | 7 |
| 3 | Pear | 9 |
Let’s create a CustomerOrderView view on top of the tables defined above:
CREATE VIEW CustomerOrderView AS SELECT Id, CustName FROM Customers WHERE OrderId IN ( SELECT Id FROM Orders WHERE Quantity>5 AND (Product='Apple' OR Product='Pear'));
Now, instead of calling the complex query stored in this view, you can use a simple SELECT statement as shown below:
Scenario II
The access control to the data stored in the database is another reason why you should use SQL views. Usually, the rule of least privilege is followed (i.e., a user is given as limited privileges as possible that satisfy his/her requirements.) For example, if a user needs to read from table A, only the read privilege should be assigned to him/her and not the read/write privilege.
Let’s assume that we have a Clients table that contains sensitive data about the company’s clients. The administrative employees of this company should be able to see the clients’ names and email addresses but nothing else, and they should not have any direct access to the Clients table to read from or write to it.
How do we resolve this? Views! We can create an SQL view and allow all the administrative employees to read from this view.
See the Clients table below:
| Id | FirstName | LastName | PhoneNo | SSN | DateOfBirth | |
|---|---|---|---|---|---|---|
| 1 | Ben | Reynolds | ben.r@email.com | 04447773333 | 12764309764 | 02-02-1987 |
| 2 | Betty | Conney | betty.c@email.com | 03336661111 | 56234587346 | 12-08-1985 |
| 3 | Alice | Kenford | alice.k@email.com | 04445558888 | 45236783654 | 30-09-1977 |
The query for creating a view that exposes the Id , FirstName , LastName , and Email columns of the Clients table is shown below:
CREATE VIEW ClientsView AS SELECT Id, FirstName, LastName, Email FROM Clients;
And now, we can assign permission to read from this view to all the administrative employees.
SELECT * FROM ClientsView;
| Id | FirstName | LastName | |
|---|---|---|---|
| 1 | Ben | Reynolds | ben.r@email.com |
| 2 | Betty | Conney | betty.c@email.com |
| 3 | Alice | Kenford | alice.k@email.com |
Scenario III
SQL views are also helpful when refactoring a database. To understand the role of SQL views during the database refactoring process, let’s look at the examples below.
Before refactoring, the View1 view is built on top of the Table1 and Table2 tables, and the View2 view is built on top of the Table2 and Table3 tables. The user operates only on View1 and View2 .
The database administrator decides to refactor the database so that the Table2 table contains the data of both the Table2 and Table3 tables. After such a refactoring process, the user still uses View1 and View2 as shown below, although the query of View2 was modified while refactoring. Hence, the user is not interrupted by the refactoring process.
If the user operates directly on the Table1 table, and the database administrator decides to refactor this table, a view could be introduced in place of this table. The user would not know the difference because views are like normal tables.
The decision about whether to use tables directly or views in place of tables is crucial at the beginning of creating the database structure. Check out our course track on Creating Database Structure to learn more!
Let’s look at a real-world example of refactoring database tables. Imagine that we have a Cars table that does not conform to the database normal forms but is nevertheless used by the database users. See the Cars table below:
| Id | CarNameModel | ProductionYears |
|---|---|---|
| 1 | Ford Kuga | 2010, 2014, 2019 |
| 2 | Mercedes Benz | 2013, 2017 |
| 3 | Toyota Yaris | 2007, 2009 |
We could introduce a CarView view in place of the Cars table. We would then be able to refactor the table to make it conform to the database normal forms.
CREATE VIEW CarView AS SELECT * FROM Cars;
Now, the CarView view can be used by the user instead of the Cars table, and the Cars table can undergo the refactoring process. Because views are used just like normal tables, the users will not experience any side effects of using a view in place of a table.
Conclusion
To summarize our journey through SQL views, they are relatively easy to use and can benefit the database in various ways. We can use them to save time and effort and to avoid any side effects of database administrative tasks.
It is essential to know that an SQL view is not a concrete table with data: it is just a saved SQL query. However, when used in SQL queries, it behaves exactly like a normal table. Hence, it is considered to be a powerful feature of SQL that should be understood by anyone interested in database design.
Delete all views from Sql Server
I know it’s possible to delete all tables at once. Is there a similar statement for views? I tried this hoping to be lucky: EXEC sp_msforeachview ‘DROP VIEW ?’ but it doesn’t work!
13.1k 14 14 gold badges 48 48 silver badges 73 73 bronze badges
asked Jul 27, 2012 at 14:11
3,127 19 19 gold badges 53 53 silver badges 55 55 bronze badges
7 Answers 7
Here you have, no cursor needed:
DECLARE @sql VARCHAR(MAX) = '' , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ; SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf FROM sys.views v PRINT @sql; EXEC(@sql); answered Jul 27, 2012 at 14:16
6,514 10 10 gold badges 50 50 silver badges 90 90 bronze badges
If you have a view in another schema (other than dbo) this script will break.
Mar 14, 2013 at 7:45
Also if you have special characters in your names, it will also break.
Mar 14, 2013 at 7:52
Adding some square brackets will help to deal with spaces, special characters, etc. SELECT @sql=@sql+’DROP VIEW [‘+name +’];’ FROM sys.views;
Apr 3, 2013 at 2:21
It depends, if you use ‘Bind to Schema’ you will run into trouble. See my answer for a solution
Nov 7, 2014 at 9:14
I love the simplicity of this over the various other looping solutions here, it might not be perfect but I still think it’s great! Thanks for sharing.
May 3, 2017 at 22:16
declare @SQL nvarchar(max) set @SQL = ( select 'drop view '+name+'; ' from sys.views for xml path('') ) exec (@SQL) answered Jul 27, 2012 at 14:17
Mikael Eriksson Mikael Eriksson
138k 22 22 gold badges 218 218 silver badges 287 287 bronze badges
I wanted a script to drop schema bound views in the correct dependency order, and I wanted it to run on sql azure where sys.dm_sql_referencing_entities is not available. I also wanted to be able to view the sql being run before I actually ran it — which you can’t do with the script in the answer by @RicNet. So I wrote this recursive query that use the other answers here as a foundation
DECLARE @sql VARCHAR(MAX) = '' DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10); ;WITH allviews as ( --just combining schema and name SELECT object_id, '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS viewname FROM sys.views ), dependents AS ( SELECT referencing.viewname dependentname, referenced.viewname dependenton FROM sys.sql_expression_dependencies r INNER JOIN allviews referencing ON referencing.object_id = r.referencing_id INNER JOIN allviews referenced ON referenced.object_id = r.referenced_id ) , nodependents AS ( SELECT viewname name FROM allviews v LEFT JOIN dependents d ON d.dependentname = viewname WHERE d.dependentname IS NULL ) ,hierarchy AS ( --the hierarchy recurses the dependencies SELECT d.dependenton, d.dependentname, 1 tier FROM dependents d UNION ALL SELECT d.dependenton, d.dependentname, h.tier + 1 FROM dependents d INNER JOIN hierarchy h ON h.dependenton = d.dependentname --best thing I could think to stop the recursion was to --stop when we reached an item with no dependents WHERE h.dependenton NOT IN (SELECT name FROM nodependents) ), combined as ( --need to add item with no dependents back in SELECT 0 tier, name FROM nodependents UNION SELECT tier, dependentname FROM hierarchy ) SELECT @sql = @sql + 'DROP VIEW ' + name + ';' + @crlf FROM combined GROUP BY name --need to group because of multiple dependency paths ORDER BY MAX(tier) desc PRINT @sql; --commented out until I'm confident I want to run it --EXEC(@sql)