557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Creating views in T-SQL or in the view designer Part three 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.
This is part of our full tutorial online on SQL.Have a look also at our SQL training courses for businesses. |
The previous part of this blog looked at how you can create a view using the view designer, but diehard SQL programmers (like me) prefer to script our views.
Here's the basic syntax for creating a view:
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
-- 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',
'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.
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
-- 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
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.
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
-- ***** Object: View [dbo].[vwFilmByStudio] Script Date: 12/14/2012 11:45:33 *****
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!
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?
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.