Skip to content

Frequently Asked Questions - SQL Server Views

Can I use a CASE statement in a view? When I create a view in Enterprise Manager with a CASE statement, it's giving me an error of "The Query Designer does not support the CASE SQL construct.

Yes, you can use a CASE statement in a view but you have to create the view using Query Analyzer and not SQL Server Enterprise Manager. There is a limitation in creating views in Enterprise Manager when the view contains a CASE statement.

Can I use a UNION or UNION ALL in a view? When I create a view in Enterprise Manager with a UNION or UNION ALL operator, it's giving me an error of "The Query Designer does not support the UNION SQL construct."

Yes, you can use a UNION or UNION ALL operator in a view but you have to create the view using Query Analyzer and not SQL Server Enterprise Manager. There is a limitation in creating views in Enterprise Manager when the view contains a UNION or UNION ALL operator.

I added a new column in a table that is being referenced by a view. How come I cannot see that new column when I do a SELECT on the view?

When a new column is added to a table being referenced by a view, the new column will not automatically be reflected to the view, especially if the view is doing a SELECT * from the table. For the new column to be reflected on the view, you have to refresh the definition of the view using the sp_refreshview system stored procedure.

EXECUTE sp_refreshview 'View Name'

The sp_refreshview system stored procedure refreshes the metadata for the specified view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

To illustrate, suppose you have the following table and view:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]    INT,
    [CustomerName]  VARCHAR(100),
    [Address]       VARCHAR(100)
)
GO

CREATE TABLE [dbo].[CustomersView] 
AS
SELECT * FROM [dbo].[Customers]
GO

Doing a SELECT * FROM [dbo].[CustomersView] will give all records from the view with all the columns of [dbo].[Customers] table. Now let's say you want to add a Gender column to the [dbo].[Customers] table:

ALTER TABLE [dbo].[Customers] ADD [Gender] CHAR(1)

Doing the same SELECT * FROM [dbo].[CustomersView] view will not include the new [Gender] column in the output. To make the view include the new column you have to execute the sp_refreshview system stored procedure:

EXECUTE sp_refreshview '[dbo].[CustomersView]'

After issuing this statement, the view will now include the new [Gender] column in the output.

How can I create a view that combines the records from 2 tables where the tables have different number of columns?

To create a view that combines the records from 2 tables, you will be using the UNION ALL operator.

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT [EmployeeID], [Name]
FROM [dbo].[OldEmployees]
UNION ALL
SELECT [EmployeeID], [Name]
FROM [dbo].[Employees]
GO

Just make sure that the output of both SELECT statements involved in the UNION ALL operator have the same number of columns. If they don't have the same number of columns, you will get the following error message:

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT [EmployeeID], [Name], [LastEmploymentDate]
FROM [dbo].[OldEmployees]
UNION ALL
SELECT [EmployeeID], [Name]
FROM [dbo].[Employees]
GO

Server: Msg 8157, Level 16, State 1, Procedure AllEmployees, Line 4
All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists.

If the tables you are trying to combine doesn't have the same number of columns just like the example above, what you can do is to put in fillers in place of those extra columns from the other table. In the example above, since the [dbo].[Employees] table only contain active employees and therefore don't have a column for the [LastEmploymentDate], you can simply return a NULL value for that column as the filler:

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT [EmployeeID], [Name], [LastEmploymentDate]
FROM [dbo].[OldEmployees]
UNION ALL
SELECT [EmployeeID], [Name], NULL AS [LastEmploymentDate]
FROM [dbo].[Employees]
GO

Do this for each column that does not exist in the other table. You can also do it for both tables if a column in the second table does not exist in the first table.

How do I determine if a particular object is a view and not a table?

To determine if a particular object is a view, you have to use the OBJECTPROPERTY metadata function.

OBJECTPROPERTY ( id, property )

The OBJECTPROPERTY metadata function, which returns information about objects in the current database, accepts two parameters, namely the id, which is the ID of the object in the current database, and the property, which is the information to be returned for the object specified by id. To determine if an object is a view, you will use the IsView property.

IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Customers]' ), 'IsView' ) = 1
    PRINT 'Object is a view'

In this script, the OBJECTPROPERTY metadata function will return a value of 1 if the object is a view. Otherwise, it will return a value of 0. If the object passed to the function is not a valid object in the current database, the function will return a value of NULL.