SQL Server VIEWS - The Basics

General Information

You can think of a view as a lens looking at one or more tables. A view is really nothing more than a logical representation of one or more tables in a database. View offer the following benefits and functionality.

Creating a view, by example

The following is an example of a simple SQL Server view using the Student table. Note: You may have to update some of the rows to add data to see the effects of this view.

IF ( OBJECT_ID('dbo.vw_students1') IS NOT NULL ) 
   DROP VIEW dbo.vw_students1 
GO

CREATE VIEW dbo.vw_students1 
AS
  SELECT 
    lastname    , 
    firstname   , 
    (firstname + ' ' + lastname) as "Fullname_fl",
    (lastname + ', ' + firstname) as "Fullname_lf",
    birth_dttm,  
    DATEDIFF(yy, birth_dttm, GETDATE()) - 
    CASE 
       WHEN MONTH(birth_dttm) > MONTH(GETDATE()) 
            OR 
            (MONTH(birth_dttm) = MONTH(GETDATE()) 
             AND DAY(birth_dttm) > DAY(GETDATE())
            ) 
    THEN 1 ELSE 0 
    END as "Age"  
    FROM   Students        
GO  

In general, you should adopt some naming standard for your views. This standard is vw_<name of view>. The name should be somewhat reflective of the purpose of the view. You can clearly see that I did not do such a good job with this. Sometimes coming up with a short descriptive name for your view is easier said than done.

The syntax for creating a view is...

CREATE OR REPLACE VIEW `<your_view_name>` 

AS

...followed by a normal SQL SELECT. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. The scenarios are endless. It really depends on the purpose of the view.

As you can see in our view we are formatting the first and last name. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement. You can also see that we have take the birth date column and calculated age.

Executing a View

Execute an SQL View

The example below shows all of the code from the view. You could also do a SELECT *, or further restrict the columns you want to see. You can also add additional row restriction to the view as we have done.

SELECT  TOP 100
        lastname     , 
        firstname    , 
        Fullname_fl  , 
        Fullname_lf  , 
        birth_dttm   , 
        Age         
FROM    dbo.vw_students1 (NOLOCK) 
WHERE Age is not null
GO  
    

Creating a View containing one or more SQL Tables

Another key advantage of a view is that it allows us to join multiple tables together. 

IF ( OBJECT_ID('dbo.vw_occupied_seats_by_class') IS NOT NULL ) 
   DROP VIEW dbo.vw_occupied_seats_by_class 
GO

CREATE VIEW dbo.vw_occupied_seats_by_class
AS
SELECT 
       a.course_designater_fk     , 
       b.seat_num      , 
       (c.firstname + ' ' + c.lastname) as "Student"                     

 
FROM         dbo.Classes           a 
        JOIN dbo.ClassRegistration b 
             ON  a.classes_num = b.classes_num 
        JOIN dbo.Students c
             ON  c.student_id = b.student_id_fk          
GO     

Above is a simple view that provides us with a listing of occupied/unoccupied seats for our classes. As you can see from the examples below, we can use this view in a variety of different ways. Note that for each scenario that we did not need to join any tables. The grunt work is already done. 

Using our View

View a single class

SELECT  
        course_designater_fk  , 
        seat_num              , 
        Student              
FROM    dbo.vw_occupied_seats_by_class (NOLOCK) 
WHERE course_designater_fk = 'Perl100'
  and Student is not null
GO            
course_designater_fk  seat_num  Student          
-----------------------------------------------
Perl100               8         Bob JONES        
Perl100               9         Ted APPLEBEE     
Perl100               2         Robert FRAPPLES  
Perl100               3         Mary LAMACKER    
Perl100               11        Mary LAMACKER    
Perl100               4         Helga JOENS      
Perl100               10        Jon NESBITT      
Perl100               5         Maggie JOMOMMA   
Perl100               6         Mary MEIGH       
Perl100               1         Madge LOWDOWN 
   

Count open seats by class

SELECT  
        course_designater_fk  , 
        COUNT(seat_num) as "# Open Seats"                         
FROM    dbo.vw_occupied_seats_by_class (NOLOCK) 
WHERE  Student is null
GROUP BY course_designater_fk  
GO     
course_designater_fk  # Open Seats  
----------------------------------
dbOrchestra100        8
Perl100               1        

Column name considerations

The column name MUST be unique in a view. Note the following example.

CREATE OR REPLACE VIEW `vw_name_conflict`
AS
SELECT
a.classes_num ,
b.classes_num

FROM classes a
JOIN classregistration b
ON a.classes_num = b.classes_num

GO          

Duplicate column name 'classes_num'

Here is how to resolve this issue. Create a unique name using "as".

CREATE OR REPLACE VIEW `vw_name_conflict`
AS
SELECT
a.classes_num as "classes_classes_num" ,
b.classes_num as "classregistration_classes_num"

FROM classes a
JOIN classregistration b
ON a.classes_num = b.classes_num

GO         
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources