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.
No comments:
Post a Comment