Showing posts with label itemstable1. Show all posts
Showing posts with label itemstable1. Show all posts

Saturday, February 25, 2012

problem with varbinary(max) in SQL Server 2005

Hi, we have stucked with strange problem. Please read comments in the following script.

CREATE TABLE ItemsTable1(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (max) NULL
)

CREATE TABLE ItemsTable2(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (20) NULL
)

-- insert same values in both tables

INSERT INTO ItemsTable1(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

INSERT INTO ItemsTable2(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))

--query 1 returns nothing. SQL Server thinks that values are different. Why?
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemNameCode = it2.ItemNameCode

--query 2 returns 'Equals!!!'.
SELECT CASE WHEN it1.ItemNameCode = it2.ItemNameCode THEN 'Equals!!!'
ELSE 'Not equals!!!' END
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemId = it2.ItemId

--query 3 after casting all works fine.
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE CAST(it1.ItemNameCode as varbinary(20))
= CAST(it2.ItemNameCode as varbinary(20))

DROP TABLE ItemsTable1
DROP TABLE ItemsTable2

Note: If we use nvarchar(20) and nvarchar(max) all works fine. So may be this is a bug?
Looks like a bug to me|||This is a bug. And it is not fixed in a recent build of SQL Server 2005 SP1 either. Could you please file a bug for this in the MSDN Product Feedback Center? Thanks.