1. 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.
Answer:
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.
2. 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."
Answer:
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.
3. 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?
Answer:
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.
4. How can I
create a view that combines the records from 2 tables where the tables have
different number of columns?
Answer:
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.
5. How do I determine if a particular object is a view and not a table?
Answer:
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 OBJECTPROPERY 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.
6. What's the maximum number of columns can a view reference?
Answer:
7. Can I create a view on another database?
Answer:
|