How To Create an Index on a View?

If you need to search and sort data in a view with a large number of row, you may want to create an index on the view to speed up your search process.

The tutorial exercise below shows you how to create a unique clustered index on a view.

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, UrlReversed)
   AS SELECT id, REVERSE(url)
   FROM fyi_links_copy WHERE counts > 1000;
GO

CREATE UNIQUE CLUSTERED INDEX date_string 
   ON fyi_links_view (ID);
GO
Cannot create index on view 'fyi_links_view' 
   because the view is not schema bound.

ALTER VIEW fyi_links_view (ID, UrlReversed)
   WITH SCHEMABINDING
   AS SELECT id, REVERSE(url)
   FROM dbo.fyi_links_copy WHERE counts > 1000;
GO

CREATE UNIQUE CLUSTERED INDEX date_string 
   ON fyi_links_view (ID);
GO

EXEC SP_HELP fyi_links_view;
GO
index_name  index_description                    index_keys
----------- ------------------------------------ ----------
date_string clustered, unique located on PRIMARY ID

How To Bind a View to the Schema of the Underlying Tables?

By default, views are not bound to the schema of the underlying tables. This means that SQL Server will allow you to change underlying table's schema any time. For example, you can drop the underlying table while keep the view. Of course, this will make the view not valid any more.

If you don't want anyone to change underlying table's schema once a view has been defined, you can create a binding view with the "WITH SCHEMABINDING" clause to bind the view to the schema of underlying tables. A binding view has the following features:

• Changing of underlying table's schema is not allowed as long as there exists one binding view.
• Indexes can be created only on binding views.

The tutorial exercise below shows you how to create a binding with "WITH SCHEMABINDING":

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM fyi_links_copy WHERE counts > 1000;
GO
Msg 4512, Level 16, State 3, Procedure fyi_links_view, 
   Line 3
Cannot schema bind view 'fyi_links_view' because name 
   'fyi_links_copy' is invalid for schema binding. 
   Names must be in two-part format and an object 
   cannot reference itself.

CREATE VIEW fyi_links_view (ID, DateString, CountUrl)
   WITH SCHEMABINDING
   AS SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM dbo.fyi_links_copy WHERE counts > 1000;
GO
Command(s) completed successfully.

DROP TABLE fyi_links_copy;
GO
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'fyi_links_copy' because it is being 
   referenced by object 'fyi_links_view'.

The error received in the first CREATE VIEW statement says that you must prefix the table name with the schema name.

The error received in the DROP TABLE statement proves that you can not change the underlying tables if there is binding view.

How Column Data Types Are Determined in a View?

When you define a view, its columns are defined through a list of expressions in the underlying SELECT statement. Their data types will be determined implicitly by the expressions.

For example, if the column expression is a column name of a underlying table, the data type of the view column will be the same of the underlying table column.

If the column expression is a function, the data type of the view column will be the function return data type.

If the column expression is an operation, the data type of the view column will be the expression result data type.

The following tutorial exercise shows you some examples of view column data types:

DROP VIEW fyi_links_view;
GO

CREATE VIEW fyi_links_view (ID, DateString, CountUrl) AS
   SELECT id, CONVERT(VARCHAR(16), created, 107), 
      CONVERT(VARCHAR(20),counts)+' - '+url
   FROM fyi_links WHERE counts > 1000
GO

SELECT TOP 3 * FROM fyi_links_view;
GO
ID     DateString    CountUrl
------ ------------- ---------------------------------------
7600   Jun 06, 1891  237946 -   eyfndw jdt  lee ztejeyx l q 
19437  May 30, 1833  222337 -   eypx u x
55924  Dec 29, 1956  1877 -   eyq ntohxe i rtnlu riwaskzp  c

EXEC SP_HELP fyi_links_view;
GO
Column_name  Type     Length      Prec  Scale 
------------ -------- ----------- ----- ----- 
ID           int      4           10    0     
DateString   varchar  16                      
CountUrl     varchar  103                     

In view, fyi_links_view, defined in this exercise:


Column "ID" has a data type INT, same as the data type of "id" in the underlying table.
Column "DateString" has a data type of VARCHAR(16), returned by the CONVERT() function.

Column "CountUlr" has a data type of VARCHAR(103), returned by the concatenation 
operations.

How To Assign New Column Names in a View?

By default, column names in a view are provided by the underlying SELECT statement.

But sometimes, the underlying SELECT statement can not provide names for output columns that specified as expressions with functions and operations. In this case, you need to assign new names for the view's columns. The tutorial exercise below creates a view to merge several table columns into a single view column with a format called CSV (Comma Separated Values):

CREATE VIEW fyi_links_dump AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ', ' + CONVERT(VARCHAR(20),counts) 
      + ', ''' + url + ''''
   FROM fyi_links WHERE counts > 1000
GO
Msg 4511, Level 16, State 1, Procedure fyi_links_dump, 
   Line 2
Create View or Function failed because no column name 
   was specified for column 1.

CREATE VIEW fyi_links_dump (Line) AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ', ' + CONVERT(VARCHAR(20),counts) 
      + ', ''' + url + ''''
   FROM fyi_links WHERE counts > 1000
GO

SELECT TOP 3 * FROM fyi_links_dump
GO
Line
------------------------------------------------------------
7600, 237946, '  eyfndw jdt  lee ztejeyx l q  jdh k '
19437, 222337, '  eypx u x'
55924, 1877, '  eyq ntohxe i rtnlu riwaskzp  cucoa dva c rc'

The first CREATE VIEW gives you an error, because the SELECT statement returns no column for the concatenated value, and no view column name is specified explicitly.

Can You Delete Data from a View?

Can you delete data in a view? The answer is no.

But if the question is "Can you delete data from the underlying table through view?" The answer is then yes. SQL Server will allow you to delete data from the underlying table through a view. The tutorial exercise below is a good example:

DELETE FROM fyi_links_top WHERE id = 100001;
GO

SELECT * FROM fyi_links_top;
GO
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url                                         ...
------ ------------------------------------------- ...
36470  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb ... 

Can You Update Data in a View?

Can you update data in a view? The answer is no.

But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exercise below is a good example:

UPDATE fyi_links_top SET url = REVERSE(url) 
   WHERE id = 100001;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 moc.retneciyf.abd
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 moc.retneciyf.abd NULL  1000001     2007-05-19

Can You Insert Data into a View?

Can you insert data into a view? The answer is no.

But if the question is "Can you insert data into the underlying table through view?" The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition:

• The insert columns must be limited to columns of a single underlying table.

The tutorial exercise below shows you how to insert data into a underlying table through a view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

INSERT INTO fyi_links_top 
   VALUES(100001, 1000001, 'dba.fyicenter.com');
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
100001 1000001 dba.fyicenter.com
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 

SELECT TOP 1 * FROM fyi_links ORDER BY counts DESC;
GO
id     url               notes counts      created
------ ----------------- ----- ----------- ----------
100001 dba.fyicenter.com NULL  1000001     2007-05-19

How To Modify the Underlying Query of an Existing View?

If you have an existing view, and want to change the underlying SELECT statement, you can use the "ALTER VIEW ..." statement to redefine the view. The tutorial exercise below shows you how modify an existing view:

USE FyiCenterData;
GO

ALTER VIEW fyi_links_top AS
   SELECT TOP 3 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od

Can You Use ORDER BY When Defining a View?

Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view.

But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise below shows you what error you will get when using ORDER BY in a CREATE VIEW statement:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_top AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO
Msg 1033, Level 15, State 1, Procedure fyi_links_top, Line 4
The ORDER BY clause is invalid in views, inline functions, 
   derived tables, subqueries, and common table expressions,
   unless TOP or FOR XML is also specified.

CREATE VIEW fyi_links_top AS
   SELECT TOP 100 id, counts, url FROM fyi_links 
   WHERE counts > 100
   ORDER BY counts DESC;
GO

SELECT TOP 3 * FROM fyi_links_top;
GO
id     counts  url
------ ------- -------------------------------------------
36470  999966  dgqnvmy   pyjqd toqcoupuxortasdtzvcae jonfb
12292  999953     qebmw v qqmywe q  kza  wskxqns jnb 
6192   999943   p o qisvrakk hk od 

The view seems to be sorted correctly.

What Happens If You Delete a Table That Is Used by a View?

Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble.

But that view will become invalid. The tutorial exercise below shows you what happens to the view, when the underlying table is deleted:

USE FyiCenterData;
GO

SELECT * INTO fyi_links_copy 
   FROM fyi_links WHERE counts > 0;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
50015

DROP TABLE fyi_links_copy;
GO

SELECT COUNT(*) FROM fyi_links_view;
GO
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fyi_links_copy'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'fyi_links_view' 
   because of binding errors.

Can You Create a View using Data from Another View?

Can You Create a View with Data from Another View? The answer is yes. A view can be used as a table to build other views. The tutorial exercise below shows you how to create a view using data from another view:

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderTop AS
   SELECT SalesOrderNumber, TotalDue, CompanyName
   FROM SalesOrderView 
   WHERE TotalDue > 10000.0
GO

SELECT TOP 10 * FROM SalesOrderTop;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71780          42452.6519  Nearby Cycle Shop
SO71782          43962.7901  Professional Sales and Service
SO71783          92663.5609  Eastside Department Store
SO71784          119960.824  Action Bicycle Specialists
SO71796          63686.2708  Extreme Riding Supplies
SO71797          86222.8072  Riding Cycles
SO71832          39531.6085  Closest Bicycle Store
SO71845          45992.3665  Trailblazing Sports
SO71858          15275.1977  Thrilling Bike Tours
SO71897          14017.9083  Paints and Solvents Company

Can You Create a View with Data from Multiple Tables?

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
   SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
      c.FirstName, c.LastName, c.CompanyName
   FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
   WHERE o.CustomerID = c.CustomerID
GO

SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName 
   FROM SalesOrderView;
GO
SalesOrderNumber TotalDue    CompanyName
---------------- ----------- ------------------------------
SO71915          2361.6403   Aerobic Exercise Company
SO71938          98138.2131  Bulk Discount Store
SO71783          92663.5609  Eastside Department Store
SO71899          2669.3183   Coalition Bike Company
SO71898          70698.9922  Instruments and Parts Company
SO71902          81834.9826  Many Bikes Store
SO71832          39531.6085  Closest Bicycle Store
SO71776          87.0851     West Side Mart
SO71797          86222.8072  Riding Cycles
SO71895          272.6468    Futuristic Bikes

How To Get the Definition of a View Out of the SQL Server?

If you want get the definition of an existing view back from the SQL Server, you can use the system view called sys.sql_modules, which stores defitions of views and procedures.

The sys.sql_modules holds view definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of view, "fyi_link_view" by joining sys.sql_modules and sys.views:

USE FyiCenterData;
GO

SELECT m.definition 
   FROM sys.sql_modules m, sys.views v
   WHERE m.object_id = v.object_id
   AND v.name = 'fyi_links_top';   
GO
definition
-------------------------------------------
CREATE VIEW fyi_links_top (LinkText) AS
   SELECT CONVERT(VARCHAR(20),id) 
      + ' - ' + CONVERT(VARCHAR(20),counts) 
      + ' - ' + url 
   FROM fyi_links WHERE counts > 1000
(1 row(s) affected)

How To Generate CREATE VIEW Script on an Existing View?

If you want to know how an existing view was created, you can use SQL Server Management Studio to automatically generate a "CREATE VIEW" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > FyiCenterData > Views > dbo.fyi_links_top.

3. Click right mouse button on dbo.fyi_links_top. The context menu shows up.

4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [FyiCenterData]
GO
/****** Object:  View [dbo].[fyi_links_top]
    Script Date: 05/19/2007 15:07:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[fyi_links_top] AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100;

How To Get a List of Columns in a View using the "sp_help" Stored Procedure?

Another way to get a list of columns from a view is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the view information, the column information, the identity column, the row GUID column. The tutorial exercise belwo shows you what you will get with sp_help:

EXEC SP_HELP fyi_links_top;
GO
Name           Owner  Type  Created_datetime
-------------- ------ ----- -----------------------
fyi_links_top  dbo    view  2007-05-19 13:43:46.983
 
Column_name  Type     Computed  Length  Prec  Scale Nullable
------------ -------- --------- ------- ----- ----- --------
id           int      no        4       10    0     yes     
counts       int      no        4       10    0     yes     
url          varchar  no        80                  no      
 
Identity                   
---------------------------
No identity column defined.

RowGuidCol
-----------------------------
No rowguidcol column defined.

How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?

If you have an existing table, but you don't remember what are the columns defined in the view, you can use the "sp_columns" stored procedure to get a list of all columns of the specified view. The following tutorial script shows you a good example:

EXEC SP_COLUMNS fyi_links_top;
GO
TABLE_OWNER TABLE_NAME    COLUMN_NAME TYPE_NAME LENGTH
----------- ------------- ----------- --------- ------
dbo         fyi_links_top id          int       4     
dbo         fyi_links_top counts      int       4     
dbo         fyi_links_top url         varchar   80    

(3 row(s) affected)

The "sp_columns" stored procedure returns a long list of properties for each column of the specified view.

How To Get a List of Columns in a View using "sys.columns"?

If you have an existing view, but you don't remember what are the columns defined in the view, you can use the "sys.columns" system view to get a list of all columns of all views in the current database.

In order to a list of columns of a single view, you need to join sys.columns and sys.views as shown in the tutorial example below:

SELECT * FROM sys.columns c, sys.views v
   WHERE c.object_id = v.object_id
   AND t.name = 'fyi_links_top'
GO
object_id   name    column_id  user_type_id max_length
----------- ------- ---------- ------------ ----------
1205579333  id      1          56           4         
1205579333  counts  2          56           4         
1205579333  url     3          167          80        

You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.

How To Drop Existing Views from a Database?

If you don't need a specific view any more, you can use the DROP VIEW statement to delete it from the database. The following tutorial exercise shows you how to delete the view, fyi_links_view:

USE FyiCenterData;
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_view  1189579276  1          V    VIEW      
fyi_links_top   1205579333  1          V    VIEW      
(2 row(s) affected)

DROP VIEW fyi_links_view; 
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_top   1205579333  1          V    VIEW      
(1 row(s) affected)

How To See Existing Views?

If you want to know how many views you have created in a database, you use the system view called sys.views to get a list of views defined in the current database. The tutorial exercise shows you how many views in database FyiCenterData:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_view AS
   SELECT * FROM fyi_links;
GO

SELECT * FROM sys.views;
GO
name            object_id   schema_id  type type_desc 
--------------- ----------- ---------- ---- ----------
fyi_links_view  1189579276  1          V    VIEW      
fyi_links_top   1205579333  1          V    VIEW      
(2 row(s) affected)

How To Create a View on an Existing Table?

If you want to a view on an existing table, you can use the CREATE VIEW statement in a simple syntax:

CREATE VIEW view_name AS SELECT ...

The tutorial exercise below shows you how to create a view to represent sub set of data stored in fyi_links table:

USE FyiCenterData;
GO

CREATE VIEW fyi_links_top AS
   SELECT id, counts, url FROM fyi_links 
   WHERE counts > 100;
GO

SELECT TOP 10 * FROM fyi_link_top;
GO
id     counts  url
------ ------- -----------------------------------------
7600   237946  eyfndw jdt  lee ztejeyx l q  jdh k 
19437  222337  eypx u x
55924  1877    eyq ntohxe i rtnlu riwaskzp  cucoa dva c 
63742  121330  ezdaeh mmgmo vaai  meytbjjv f jixfsdjw pw
92455  945262  ezlmyenrw   dyeb
36391  41386   f 
87433  977726  f
7180   559314  f   kqbqlej   s xixuurcgg lh r dqqvqsstxw
2252   702033  f  bkh  jy   sqrkttuoarxmfp idqyhyy tme d
1228   146283  f  m asukh