The previous part of this tutorial looked at how you can create a view using the view designer, but diehard SQL programmers (like me) prefer to script our views.
Creating a view in script
Here's the basic syntax for creating a view:
CREATE VIEW NameOfView AS
SELECT something
The nice thing about this way of creating views is that you can use indentation and comments. Here's how you could create the view in the previous part of this blog:
CREATE VIEW vwFilmByStudio
AS
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
WHERE
f.FilmRunTimeMinutes <>
ORDER BY
'Name of studio',
'Name of film'
When you run this script, you would get the following output:
The output from creating the view shows that ... you created it!
To see the view itself, you'll almost certainly have to refresh your current list of views:
Your new view won't usually appear in the list until you refresh it.
Altering views
Having created a view in script, how can you change it? The answer is to alter it. To do this, right-click on the view and choose the following option:
Choose to script the view to a new window, for alterations.
At this point, you can delete the commands which Management Studio adds at the top of your script:
I always delete these commands. They don't do any harm, but nor are they necessary.
What you're left with is an instruction to alter your view to do something different. if you just wanted to change the final sort order, you could do this as follows:
ALTER VIEW [dbo].[vwFilmByStudio]
AS
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
WHERE
f.FilmRunTimeMinutes <>
ORDER BY
'Name of studio' DESC,
'Name of film' DESC
SQL Server will throw away any old version of the view when you run the script above, and replace it with this new version.
This rather odd way of making changes to things in SQL Server means that once you've successfully run a SQL script to create a view, you can close it down without saving it. If you need to get the script back, you can just right-click on the view to recreate it.
The Mickey Mouse (or at least, Mickey Owl) way to create views
Here's how I really created the view above. First I went into the view designer and created a view:
Part of the view, as created in the view designer.
I saved and closed this view, then right-clicked on it as shown above to script it for alterations to get:
USE [Movies]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwFilmByStudio]
AS
SELECT TOP (100) PERCENT dbo.tblFilm.FilmName AS [Name of film], dbo.tblFilm.FilmRunTimeMinutes AS [Run time], dbo.tblStudio.StudioName AS [Name of studio]
FROM dbo.tblFilm INNER JOIN
dbo.tblStudio ON dbo.tblFilm.FilmStudioID = dbo.tblStudio.StudioID
WHERE (dbo.tblFilm.FilmRunTimeMinutes <>
ORDER BY [Name of studio], [Name of film]
GO
I then tidied this up to get the neat script shown above. It often helps to press CTRL + H to replace table names with their aliases:
Replacing the long table name with the short alias.
The big advantage of this approach is that you don't have to remember how to create joins in SQL!
Be careful not to go back into the designer
Once you've scripted a view (adding comments, indentation and blank lines to make it easier to read), DON'T go back into the view designer!
Once you go back into the view designer and save changes, you'll lose all of your beautiful formatting (which can be soul-destroying!).
We've now looked at designing and scripting views, but perhaps it's time to ask a more fundamental question: should you be using views at all, as opposed to writing the more powerful stored procedures)? This page will concentrate on the good side of views - we'll consider the disadvantages in the next part of this tutorial.
If the above sounds like a recommendation to avoid using views, it's anything but - the first advantage trumps all of the disadvantages.
Advantage 1 - you can base views, reports and stored procedures on views
You can base queries on views as well as on tables:
Whether you're writing views in SSMS or creating reports in SSRS, you can always use a view in place of a table.
Suppose that you spend your life working with movies data (perhaps you work for a film company), and you frequently want to work with a subset of the complete dataset (perhaps just the films made by Steven Spielberg). Almost every query that you create will probably:
Link a few tables together to get the studio, language, country and certificate for each film.
Apply a filter so that you only see Spielberg films.
To avoid having to keep creating the same SQL, you could create a view, and base all subsequent queries on this view. Here's what the view would look like:
CREATE VIEW vwSpielbergFilms
AS
SELECT
tblFilm.*,
tblLanguage.Language,
tblCountry.CountryName,
tblCertificate.Certificate,
tblStudio.StudioName
FROM
tblCertificate
INNER JOIN tblFilm ON
tblCertificate.CertificateID = tblFilm.FilmCertificateID
INNER JOIN tblCountry ON
tblFilm.FilmCountryID = tblCountry.CountryID
INNER JOIN tblDirector ON
tblFilm.FilmDirectorID = tblDirector.DirectorID
INNER JOIN tblLanguage ON
tblFilm.FilmLanguageID = tblLanguage.LanguageID
INNER JOIN tblStudio ON
tblFilm.FilmStudioID = tblStudio.StudioID
WHERE
tblDirector.DirectorName = 'Steven Spielberg'
Quite a mouthful!
Apologies for using the * to include all columns from the films table - you might want to be pickier than this, and specify exactly which columns the view should list.
Here's what a query to analyse Spielberg film Oscars would look like:
SELECT
FilmName,
FilmOscarWins,
FilmOscarNominations
FROM
vwSpielbergFilms
ORDER BY
FilmOscarWins DESC
Here's what this would return:
I think this data predates Schindler's List. Sorry, Steven!
I don't know how many times I've stressed to people on our SQL courses the importance of creating views as above to avoid reinventing wheels for every query that you create.
One thing to stress: the query above will also rerun the vwSpielbergFilms view to get the latest version of the data. If speed is a big issue for you, you'll probably want to consider creating temporary tables of data instead.
Advantage 2 - View don't need the same access rights as stored procedures
Here's some of the things stored procedures can do:
Your IT department may be deeply reluctant to give you permission to create stored procedures, but they may give you the right to create views (since these can only ever select data).
That ends the case for the defence; time now to look at the case for the prosecution!
