What is a view?
The View is a saved SQL statement and referred as a virtual table. When you create a view, Microsoft SQL Server stores only metadata information about the view, describing the object, its columns, security, dependencies, and so on.
Some limitations to be aware
-
ORDER BY cannot be used in the view's query unless there is also a TOP or FOR XML specification in the definition.
-
All result columns must have names.
-
All result column names must be unique.
-
A SELECT INTO statement cannot be used in view declaration statement.
-
A view can be created only in the current database.
-
The view doesn’t support input/output parameters.
Example
CREATE VIEW vwCustomer
AS
SELECT Id, CName, Phone
FROM Customers
Go
The above example creates a view called “vwCustomer” where the data is derived from the columns of the base “Customer” table.
You can query data from the view in the same way you do from a table.
SELECT * FROM vwCustomer
The output of the SELECT statement is:
(3 rows affected)
ORDER BY Clause
Let’s try to use the ORDER BY clause in a view:
CREATE VIEW vwCustomer
AS
SELECT id, CName, Phone
FROM Customers
ORDER BY CName
Go
If you try to run the above example to create the view, you will get the following error because, as said above, the ORDER BY clause can be used only if “TOP” or “FOR XML” is specified.
Msg 1033, Level 15, State 1, Procedure Customer, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
But you can still use ORDER BY clause when you query data from the view as illustrated below:
SELECT Id, CName, Phone
FROM vwCustomer
ORDER BY CName
Or you can use ORDER BY clause inside the view if you specify TOP 100 PERCENT as illustrated below:
CREATE VIEW vwCustomer
AS
SELECT TOP 100 PERCENT Id, CName, Phone
FROM Customers
ORDER BY CName
Go
The above view should return the rows as ordered so you do not need to re-order them again:
SELECT Id, CName, Phone
FROM vwCustomer
Refreshing Views
After creating a view and if there is any column changes in its table, your view will not reflect those changes unless you refresh it.
Example
Create a view first
CREATE VIEW vwCustomer
AS
SELECT *
FROM Customers
Go
List all the columns using the view
SELECT * FROM vwCustomer
Then execute the following code to add a new field in the table
ALTER TABLE dbo.Customers ADD Address varchar(100);
Now query the view again and see if the new column is there
SELECT * FROM vwCustomer
The above query will show the id, cname, phone fields only. The newly created “Address” field will not be displayed because the schema change in Customers table was not reflected in the view's metadata information. As far as SQL Server is concerned, the view still has just two columns.
To refresh the Meta data information of the view, you can use the following command
Syntax
sp_refreshview sp_refreshview 'vwCustomer' go
Now run the view again and the new column should be there:
SELECT * FROM vwCustomer
Dropping Views
You can delete the view from your database by using the DROP VIEW command as below
Syntax
DROP VIEW
Example
IF OBJECT_ID('vwCustomer') IS NOT NULL
DROP VIEW dbo.vwCustomer
Command(s) completed successfully.