SQL Server FAQ - Determining Data Types of View Columns

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

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.




(Continued on next topic...)







What Are Views?
How To Create a View on an Existing Table?
How To See Existing Views?
How To Drop Existing Views from a Database?
How To Get a List of Columns in a View using "sys.columns"?
How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
How To Generate CREATE VIEW Script on an Existing View?
How To Get the Definition of a View Out of the SQL Server?
Can You Create a View with Data from Multiple Tables?
Can You Create a View using Data from Another View?
What Happens If You Delete a Table That Is Used by a View?
Can You Use ORDER BY When Defining a View?
How To Modify the Underlying Query of an Existing View?
Can You Insert Data into a View?
Can You Update Data in a View?
Can You Delete Data from a View?
How To Assign New Column Names in a View?
How Column Data Types Are Determined in a View?
How To Bind a View to the Schema of the Underlying Tables?
How To Create an Index on a View?

More...