January 17, 2008 at 8:51 am
We have 2 databases with the same table structures. We created a view on both the databases. One of the columns in the view is a derived column. Based on a column of data type char(1), the values are derived either as 'Yes' or 'No' or the value in the column itself. One of the databases shows the data type of this derived field as char(3) and the other database as varchar(3). What could be the reason for this difference?
January 17, 2008 at 9:25 am
Can you post the DDL for both tables and views? Generate the script from SSMS.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 17, 2008 at 9:39 am
Here is the table DDL. It does have other columns in addition to these
CREATE TABLE delivered_service
(
delivered_service_id int IDENTITY(1,1) NOT NULL,
release_note_ind char(1) NOT NULL DEFAULT ('F'),
CONSTRAINT delivered_service_pk PRIMARY KEY CLUSTERED
(
delivered_service_id ASC
)
)
ALTER TABLE delivered_service WITH CHECK ADD CONSTRAINT dlsrv_release_note_chk CHECK ((release_note_ind='F' OR release_note_ind='T'))
Here is the View script. The view has additional joins. I am just providing the column of concern:
create view delivered_service_detail as
select
ds.delivered_service_id as delivered_service_id,
ds.release_note_ind as release_note_ind,
case ds.release_note_ind
when 'T' then 'Yes'
when 'F' then 'No'
else ds.release_note_ind
end as release_note_desc
from
delivered_service ds
OUTPUT from INFORMATION_SCHEMA.COLUMNS in Database A and B:
In Database A:
table_name column_name data_typecharacter_maximum_length
delivered_service_detailrelease_note_ind char 1
delivered_service_detailrelease_note_desc varchar 3
In Database B:
table_name column_name data_typecharacter_maximum_length
delivered_service_detailrelease_note_ind char 1
delivered_service_detailrelease_note_desc char 3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply