BUG: The return data type depends on the location of a UNION operation where char and varchar data types are combined in SQL Server 2000 (897886)

The information in this article applies to:

Microsoft SQL Server 2000 Desktop Engine (MSDE)
Microsoft SQL Server 2000 Developer Edition
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Personal Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 Enterprise Edition 64-bit
Microsoft SQL Server 2000 Developer Edition 64 bit

BUG #: 474025 (SQL Server 8.0)

SYMPTOMS

Consider the following scenario. In Microsoft SQL Server 2000, char and varchar data types are combined in a UNION operation. If the UNION operation is included in a view or in a subquery, the return data type of the combined column is char. If the UNION operation is a stand-alone query, the return data type of the combined column is varchar.

WORKAROUND

To work around this problem, manually convert the data types from char to varchar by using the cast function or the convert function. For example, use the following SQL statement instead of the SQL statement that is used in the "More Information" section:

create view View12 as 
select cast ([data] as varchar (10)) as data from [Table1]
union 
select [data] from [Table2]

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the behavior

Create table1 and table2 by using the following SQL statement:

CREATE TABLE [Table1] ( [id] [int] NOT NULL , [data] [char] (10))

CREATE TABLE [Table2] ( [id] [int] NOT NULL , [data] [varchar] (10))

Create a view that is called view12 by using the following SQL statement:

create view View12 as 
select [data] from [Table1]
union 
select [data] from [Table2]

Run the following stored procedure:
```
sp_help View12
```
Notice that the data type of the data column is char.

Modification Type:	Major	Last Reviewed:	4/27/2005
Keywords:	kbSQL kbDatabase kbtshoot kbbug KB897886 kbAudDeveloper kbAudITPRO