Wednesday, November 16, 2005

Views and bit datatype

SQL Server 2000 views doesn't reflect the underlining data from linked server when the data type is changed from nvarchar to bit

In this article I am going to discuss the problem that I have encountered when creating a view based on a column with varchar data type and change the data type to bit.The problem happens when you try to query the veiw from a linked server. I have included scripts to replicate this issue for you to experiment. To configure a linked server please refer Configuring Linked Servers.

First run the following script to create a table with one of the column being defined as varchar data type.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrueFalses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TrueFalses]
GO

CREATE TABLE [dbo].[TrueFalses] (
[TrueFalseId] [int] IDENTITY (1, 1) NOT NULL ,
[TrueFalse] [nvarchar] (100) NULL
) ON [PRIMARY]
GO
Next insert data into the above table
INSERT INTO TrueFalses(TrueFalse)
SELECT 'True'
UNION all
SELECT 'False'
UNION all
SELECT 'True'
UNION all
SELECT 'True'
Next create view based on above table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[test]
CREATE view test
AS
select * from TrueFalses


Run the following two queries from a linked server

select * from [Linked ServerName].[Database Name].dbo.TrueFalses
select * from from [Linked ServerName].[Database Name].dbo.test

Both the above queries result in the same data.

TrueFalseId,TrueFalse
1,True
2,False
3,True
4,True


Now let us update the TrueFalses table.

UPDATE TrueFalses
SET TrueFalse = 0
WHERE TrueFalse = 'False'


UPDATE slst_TrueFalses
SET TrueFalse = 1
WHERE TrueFalse = 'True'

Again run the query based on table and view from linked server. You will get the same results with the value of TrueFalse changed to 1 and 0. See results below.

TrueFalseId,TrueFalse
1,1
2,0
3,1
4,1


Then change the data type of the field from varchar to bit data type and query the result from linked servers.

ALTER TABLE TrueFalses
ALTER COLUMN TrueFalse bit


The query from the linked server based on the table will yield the following result.
TrueFalseId,TrueFalse
1,1
2,0
3,1
4,1


The query from the linked server based on the view will yield the following result.
TrueFalseId,TrueFalse
1,True
2,False
3,True
4,True



The above behaviour can be corrected by running ALTER statement on a view.

alter view test
as

select * from TrueFalses

2 comments:

Rod said...

Nice blog. Your posts were interesting reading. I have a candle kit making site. You can find everything about candle kit making as well as candle supplies, gel candles, jar candles, and how to make your own candles. You'll find it very informative. Please try and visit it, see what you think.
Rod

Teshome Asfaw said...

Thanks for you comment.