This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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

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.

Posted by Andy Brown on 17 December 2012

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

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

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]

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

-- ***** Object: View [dbo].[vwFilmByStudio] Script Date: 12/14/2012 11:45:33 *****

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER VIEW [dbo].[vwFilmByStudio]

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]

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?

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR