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.

  1. Views in SQL Server
  2. The View Designer
  3. Scripting views using SQL (this blog)
  4. The Pros of using Views in SQL Server
  5. 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:

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

 

-- 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:

Command completed successfully

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:

Refreshing 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:

Right-click to alter view

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:

Commands at top of altering query 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:

View in the view designer

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 table names

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!

Designing a view

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?

This blog has 0 threads Add post