BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Views in SQL Server
- The View Designer
- Scripting views using SQL (this blog)
- The Pros of using Views in SQL Server
- The Disadvantages of Views
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.
Scripting views using SQL
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.
Creating a view in script
Here's the basic syntax for creating a view:
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
-- 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.
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
-- 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.
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
-- ***** 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!
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?
- Views in SQL Server
- The View Designer
- Scripting views using SQL (this blog)
- The Pros of using Views in SQL Server
- The Disadvantages of Views