Computed Column(s) in table 1

[gus121]

i got excited when i read somewhere about the existence of computed columns in SQL Server. This is because i like using binary bitwise numbers to uniqely identify rows within my database.

So I tried creating a table and creating a fomula which calculates a unique binary mask fom the shippingID the IDDENTITY collum. This Saves no problem and i know the fomula works OK in VB. However when I inset data into this table 'Test1' i get some strange results. where Shppingid is 1 i get a shippingmaskid of 2 (when i would expect a 1) Where Shipping ID is 2 i get a mask of -1 when i would expect a 2?! etc..


CREATE TABLE Test1
{
ShippingID int IDENTITY NOT Null,
ShippingMaskID AS 2^([ShippingID] - 1),
ShippingDescription varChar(50) NULL
}

Any help appreciated. Is there a better way to do this?

thanks


-Gus

 

[JamesLean]

I've been testing this and found that when using the XOR operator it seems to ignore the parentheses. I'm not sure if this is a SQL Server bug?

If you use this it works as expected:

CREATE TABLE t (
	id int IDENTITY,
	idmask AS id - 1 ^ 2
)

--James

 

[gus121]

Hi James,

Thanks for your post.
Maybe it is me i sill got some strange reslts. What i really want to do is 2 to the POWER of id - 1. Not to use the XOR operator.

So i would expect:
id idmask
1 = 1
2 = 2
3 = 4
4 = 8
5 = 16

In VBscript the ^ operator calculates the power of i think.

thanks



-Gus

 

[JamesLean]

FYI, this only seems to be the case for computed columns. Doing the calculation directly in the query works as expected:

DECLARE @t table (
	id int,
	id2 AS 2 ^ (id - 1)
)

DECLARE @i int
SET @i = 1
WHILE @i <= 10
BEGIN
	INSERT @t (id) VALUES (@i)
	SET @i = @i + 1
END

SELECT *, 2 ^ (id - 1)
FROM @t
ORDER BY id

--James

 

[JamesLean]

OK. In SQL Server the ^ operator is XOR. You need the POWER function:

DECLARE @t table (
	id int,
	id2 AS POWER(2, id - 1)
)

--James

 

[gus121]

Hi James,

Thanks for your time and your help and some intresting observasions on XOR opperators.

thanks


-Gus