Creating views in T-SQL or in the view designer
Part five of a five-part series of blogs

Views are queries that you can save and reuse; but you can also use them as a basis for further queries. They do have their foibles too, as this blog explores.

  1. Views in SQL Server
  2. The View Designer
  3. Scripting views using SQL
  4. The Pros of using Views in SQL Server
  5. The Disadvantages of Views (this blog)

This is part of our full tutorial online on SQL.Have a look also at our SQL training courses for businesses.

Posted by Andy Brown on 17 December 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The Disadvantages of Views

I've looked at why views are a good thing; what are the pitfalls?

Disadvantage 1 - Sorting doesn't work (unless ...)

Even if you put an ORDER BY clause in a view, it won't order the data correctly unless you also specify how many rows you want to display.

When i first heard this, I found this almost impossible to believe.  If views don't sort correctly, what do they do?  But it's true: if you're returning data from a view to your application, don't assume that it will be sorted as you requested unless you used TOP 100 PERCENT.  And even then, as you'll see below, ordering isn't guaranteed.

Sometimes you get a warning about this problem.  For example, supposing you try to create a view with ordering, but no TOP keyword:

CREATE VIEW vwFilmOrderTest AS


-- list films in name order








If you run this SQL, you get the following error:

Msg 1033, Level 15, State 1, Procedure vwFilmOrderTest, Line 10

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.

The solution is to add TOP 100 PERCENT to your view, but even this doesn't guarantee that the rows are returned sorted.  For example, the following query creates a sorted view then returns all of the rows from it:

CREATE VIEW vwFilmOrderTest AS


-- list films in name order











SELECT * FROM vwFilmOrderTest

Here are the first few rows returned by this SQL:

Films not in any particular order

Despite the ordering clause, the films still don't appear in name order!


The safest thing to do is to either apply sorting to data in your final application, or to use stored procedures.

Disadvantage 2 - adding columns to tables can damage views

I've had this happen many times to me in the past, but can't reproduce it at the moment.  Sometimes you want to add columns to a table:

New column added

Here we've added a new column to our table.


You would think that this couldn't possibly have any effect on existing views, but sometimes it does:

View with column aliases

In a view depending on this table, all of the aliases can be shuffled down one row, making the entire view unusable until you correct it.


You have been warned!

Disadvantage 3 - views lose formatting

I've mentioned this already, but it's worth repeating because it can be so annoying.  Imagine that you've spent ages indenting, commenting and spacing out your view script:

ALTER VIEW [dbo].[vwFilmByStudio]



-- show the film name, length and studio


f.FilmName AS 'Name of film',

f.FilmRunTimeMinutes AS 'Run time',

s.StudioName AS 'Name of studio'


tblFilm AS f

INNER JOIN tblStudio AS s

ON f.FilmStudioID = s.StudioID


-- only show short films


f.FilmRunTimeMinutes <>


'Name of studio' DESC,

'Name of film' DESC

You then accidentally right click on the view and go into design view, save your changes … and you've lost all of the formatting above and are back to this:

ALTER VIEW [dbo].[vwFilmByStudio]


SELECT TOP (100) PERCENT f.FilmName AS [Name of film], f.FilmRunTimeMinutes AS [Run time], s.StudioName AS [Name of studio]

FROM dbo.tblFilm AS f INNER JOIN

dbo.tblStudio AS s ON f.FilmStudioID = s.StudioID

WHERE (f.FilmRunTimeMinutes <>

ORDER BY 'Name of studio' DESC, 'Name of film' DESC

Disadvantage 4 - views can only select data

All a view can do is to select data.  For example, you can't use a view to:

  • create variables
  • create and populate temporary tables
  • use common table expressions to make coding easier

For all of these - and for any SQL programming beyond simple SELECT statements - it might be time to learn stored procedures.

This blog has 0 threads Add post