unsigned ints

Question

unsigned ints

benjust

SSC Journeyman

Points: 96
More actions
June 4, 2003 at 10:19 pm

#80962

i recently worked with an ip allocation webpage, where i of course thought, whoopee, i can use the ip address (the actual 32bit value) as the primary key, then i can also do all sorts of things with the data quickly (let select a certain ip range - which would be hell working with varchar(xxx.xxx.xxx.xxx) format).
funny thing is, i never actually needed it til then, but i realised i couldn't define a field as an unsigned int... the simplest bloody thing..
i ended up having to make it a decimal.. if anyone has any info on it being possible (to create an unsigned int) please let me know.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

You could use a BINARY value. Same storage requirements as INT but you can use all the bits. You may find it cumbersome though, depending on your processing needs, but just an option to consider...

 

CREATE TABLE IPAllocations (

    IP BINARY(4) PRIMARY KEY,

    Octet1 AS convert(smallint, substring(ip,1,1)),

    Octet2 AS convert(smallint, substring(ip,2,1)),

    Octet3 AS convert(smallint, substring(ip,3,1)),

    Octet4 AS convert(smallint, substring(ip,4,1)),

)



INSERT IPAllocations VALUES (0xfffefdfc)

INSERT IPAllocations VALUES (CONVERT(BINARY(1), 192) + CONVERT(BINARY(1), 168) + CONVERT(BINARY(1), 0) + CONVERT(BINARY(1), 1))



select * from IPAllocations

Cheers,
- Mark

benjust SSC Journeyman Points: 96 More actions · Answer 2

thanks for the suggestion, but i was hoping there was a more elegant way.

i am a little disappointed in sql server because it's been so good to me til now.. who'da thunk it? all ints being forced signed except tinyint which is forced unsigned.. bah! 🙂

i spose it's always these little things that irk you.

jpipes SSChampion Points: 12230 More actions · Answer 3

I agree; bit of a pet peeve with me as well...What about using the BIGINT datatype, it's signed, but should give you the range you need. It's only supported in SQL2000, however.

Regards,

Jay

ps: if only mysql had stored procs and sqlserver had unsigned ints... 🙂

benjust SSC Journeyman Points: 96 More actions · Answer 4

yeah, it's sql server 7.0 🙂 so no bigint. even if it did support, still not nice, wasting space.

if only mssql has unsigned ints, and mysql had stored procs and views and better defaults, and rules and so on.. and so on.. 🙂

(don't get me wrong, i like the mysql - it's quick, easy and most importantly free - it's just got a long way to go in the feature department.)

jpipes SSChampion Points: 12230 More actions · Answer 5

jpipes

SSChampion

Points: 12230

June 11, 2003 at 6:23 am

#460739

You'll get no disagreement from me! 🙂