How Column Data Types Are Determined in a View in SQL Server?

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.

 

⇒Understanding and Managing Views in SQL Server

⇒⇒SQL Server Database Tutorials