SQL Server View does not show newly added columns

Few months back, this was experienced by one of my clients and I was asked the reason for this. Although I wanted to make a post on it, I could not and forgot but this popped up again while I was going through notes related to my class.

How can this be happened? Assume that you have created a view that references a table called Customer using SELECT * statement. When you access the view with SELECT statement, it returns all columns in Customer table.

USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) Primary key
 , Name varchar(200) not null
);
GO

CREATE OR ALTER VIEW dbo.vwCustomer
AS
SELECT * FROM dbo.Customer;
GO

SELECT * FROM dbo.vwCustomer;

Next, you add a new column to the table and you expect to see all columns including the newly added one when retrieve records using the view.

ALTER TABLE dbo.Customer
ADD CreditLimit decimal(16, 4) not null;
GO

SELECT * FROM dbo.vwCustomer;

As you see, result does not have the newly added column. What is the reason?

This is because of;

1. We have used SELECT * statement inside the view.
2. Metadata of the view has not be updated/refreshed.

If you avoid SELECT * statement when you create views, obviously you do not see this issue because view it is with a defined column set. If you have used SELECT *, then only option is either ALTER the view or refresh the view.

EXEC sp_refreshview 'dbo.vwCustomer';

Once the view is refreshed, new column will be appeared in the result.

Ordering the result of Views – SS SLUG Dec 2013 – Brain Bashers - Demo VIII

First of all, Happy New Year 2014! May your all dreams come true in 2014!

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This topic is very old but still it needs to be discussed for beginners; it is all about having ORDER BY with VIEWs.

Here is the question related to this demo;

Does SQL Server sort the resultset returning from a view based on ORDER BY clause added?

Answer is “NO”. Have a look on below code and then see the explanation.

USE AdventureWorks2012

GO

-- this throws an error

-- read the error properly

CREATE VIEW v_SalesOrdersDetails

AS

SELECT SalesOrderID, SalesOrderDetailID

    , CarrierTrackingNumber, OrderQty

    , ProductID, LineTotal

FROM Sales.SalesOrderDetail

ORDER BY ProductID DESC

GO

-- this creates the view

CREATE VIEW v_SalesOrdersDetails

AS

SELECT TOP 100 PERCENT 

    SalesOrderID, SalesOrderDetailID

    , CarrierTrackingNumber, OrderQty

    , ProductID, LineTotal

FROM Sales.SalesOrderDetail

ORDER BY ProductID DESC

GO

-- this query does not sort the

-- result by product id

SELECT * FROM v_SalesOrdersDetails 

-- this sorts as we have added

-- to the query

SELECT * FROM v_SalesOrdersDetails 

ORDER BY ProductID DESC 

Here is the result;

As you see, the ORDER BY clause we have added to the view has no effects.

Let’s try to understand this behavior. Remember the error thrown from first try. It clearly says that ORDER BY cannot be used with VIEWs. Looks weird :)? There is nothing wrong with it. View is supposed to represent a table. A table is a logical entity that does not hold ordered rows, hence view cannot hold ordered rows too. However SQL Server allows to use ORDER BY clause with views when TOP or FOR XML is used. Again, remember, though you have used ORDER BY with TOP, it does not sort the result, it uses ORDER BY for completing TOP operation only (Note: Generally it uses ORDER BY for two purposes; Which rows to be picked for the TOP, and order the result).

There is a misconception that adding TOP 99.99 PERCENT sorts the result set based on ORDER BY added. The fact is, it is wrong too, SQL Server does not guarantee a sorted resultset. Not only that, the result set might not contain all records returned.