Refreshing a View

When create a view, SQL Server stores metadata information describing the view, its

columns, security, dependencies, and so on. Schema changes in underlying objects are not

reflected in the view’s metadata information. After applying such schema changes,

 it's a good practice to refresh the view's metadata information using the sp_refreshview stored procedure so that the changes are reflected in the view.

Example:

Create table:

USE tempdb;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;

CREATE TABLE dbo.Table1(col1 INT, col2 INT);

INSERT INTO dbo.Table1(col1, col2) VALUES(1, 2);

GO

Create View

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

Go

CREATE VIEW dbo.RefreshView

AS

SELECT * FROM dbo.Table1;

GO

select * from RefreshView

col1      col2

1          2

Add Column to the table

ALTER TABLE dbo.Table1 ADD col3 INT;

The schema change in T1 was not reflected in the view’s metadata information

select * from RefreshView

col1      col2

1          2

To refresh the view’s metadata information, run the sp_refreshview stored procedure

EXEC sp_refreshview 'dbo.RefreshView';

select * from RefreshView

col1      col2      col3

1          2          NULL

When done delete the table and view.

IF OBJECT_ID('dbo.RefreshView', 'V') IS NOT NULL

DROP VIEW dbo.RefreshView;

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL

DROP TABLE dbo.Table1;

Video link:

Order by in View

An ORDER BY clause without TOP or FOR XML specification is not allowed in the query

defining the view because a view is supposed to represent a table. A table is a logical entity

that has no order to its rows.

So names must be unique in valid table and the names must be unique.

Ex:

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

CREATE VIEW VCustomers

AS

SELECT custid, custname

FROM Customers

order by custid

GO

It throws error below

Msg 1033, Level 15, State 1, Procedure VCustomers, Line 5 [Batch Start Line 3]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

TOP and ORDER BY or ORDER BY and FOR XML are allowed in a view definition, whereas ORDER BY alone is not.

Now modify the view as below.

CREATE VIEW VCustomers

AS

SELECT top(10) percent custid, custname

FROM Customers

order by custid

GO

Now run the below script

When you are done run the below script to delete the view.

IF OBJECT_ID('VCustomers', 'V') IS NOT NULL

DROP VIEW VCustomers;

GO

For More details watch the video below.