This page has 0 threads | Add post

The Disadvantages of Views

Written by Andy Brown

In this tutorial

Disadvantage 1 - Sorting doesn't work (unless ...)
Disadvantage 2 - adding columns to tables can damage views
Disadvantage 3 - views lose formatting
Disadvantage 4 - views can only select data

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

SELECT

FilmId,

FilmName

FROM

tblFilm

ORDER BY

FilmName

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 TOP 100 PERCENT

FilmId,

FilmName

FROM

tblFilm

ORDER BY

FilmName

SELECT * FROM vwFilmOrderTest

Here are the first few rows returned by this SQL:

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:

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:

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

SELECT TOP 100 PERCENT

f.FilmName AS 'Name of film',

f.FilmRunTimeMinutes AS 'Run time',

s.StudioName AS 'Name of studio'

FROM

tblFilm AS f

INNER JOIN tblStudio AS s

ON f.FilmStudioID = s.StudioID

-- only show short films

WHERE

f.FilmRunTimeMinutes <>

ORDER BY

'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.

Previous
tutorial

Grouping data

Previous
part

Scripting views using SQL

Top of page

Next
part

Next
tutorial

Subqueries

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Views

The Disadvantages of Views

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

Disadvantage 2 - adding columns to tables can damage views

Disadvantage 3 - views lose formatting

Disadvantage 4 - views can only select data

Head office

London

Manchester