Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: *unsigned* 32-bit column type?

    I need to stored lots of unsigned 32-bit values. (I'm actually storing IPv4 addresses). These values will frequently exceed 2^31 but never exceed 2^32

    My options are:
    • Use bigint. This is undesirable since this wastes 32-bits of space for every value.
    • Use int and use the negative value range to get full 32-bits worth of data. In C, it is easy and fast to cast a signed -1 to an unsigned 2^32-1. In SQL, it will be more expensive: Must cast to 64-bit and if val < 0 then val = 2^32 + val.


    Is there a better alternative?

    Is there a fast (binary) way to cast a value > 2^31 to a negative signed value?

    DECLARE @value BIGINT
    SET @value = 3000000000

    SELECT CONVERT(INT, @value) -- causes error
    SELECT CAST(@value AS INT) -- causes error

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.

    -PatP

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by rdjabarov
    If it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.
    Quote Originally Posted by Pat Phelan
    The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.
    Thanks guys. I'll stick with the signed 32-bit column and some ugly code to convert between unsigned 32-bit values.

    Using 4 columns makes for uglier joins and queries. And Pat, I don't quite follow your approach.

    Code:
    CREATE FUNCTION parseIPv4(@ipString VARCHAR(30))  
    RETURNS INT AS  
    BEGIN
    	DECLARE @dotIndex1 INT
    	SET @dotIndex1 = CHARINDEX('.', @ipString)
    	DECLARE @dotIndex2 INT
    	SET @dotIndex2 = CHARINDEX('.', @ipString, @dotIndex1 + 1)
    	DECLARE @dotIndex3 INT
    	SET @dotIndex3 = CHARINDEX('.', @ipString, @dotIndex2 + 1)
    
    	IF @dotIndex3 < 1 RETURN 0
    	
    	DECLARE @section1 BIGINT
    	SET @section1 = CAST(SUBSTRING(@ipString, 1, @dotIndex1 - 1) AS BIGINT)
    	DECLARE @section2 BIGINT
    	SET @section2 = CAST(SUBSTRING(@ipString, @dotIndex1 + 1, @dotIndex2 - @dotIndex1 - 1) AS BIGINT)
    	DECLARE @section3 BIGINT
    	SET @section3 = CAST(SUBSTRING(@ipString, @dotIndex2 + 1, @dotIndex3 - @dotIndex2 - 1) AS BIGINT)
    	DECLARE @section4 BIGINT
    	SET @section4 = CAST(RIGHT(@ipString, LEN(@ipString) - @dotIndex3) AS BIGINT)
    
    	DECLARE @result BIGINT
    	SET @result = (@section1 * 0x1000000) | (@section2 * 0x10000) | (@section3 * 0x100) | (@section4)
    
    	DECLARE @t INT
    
    	-- If the value can fit in a signed 32-bit value
    	IF @result < 2147483648 RETURN @result
    
    	-- The value can't fit in a signed 32-bit value. Use negative space.
    	-- This is what would happen if the value was binary cast from unsigned to signed.
    	RETURN CAST(@result - 4294967296 AS INT)
    END

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What I meant was:
    Code:
    //  ptp  200408021738  demo for dbforums.com
    
    #include	<stdio.h>
    
    void main()
    
    {
    	union bar {
    		unsigned int	u;
    		int				s;
    	} foo;
    
    	foo.u = 4294967293;
    	printf("%d\n", foo.s);			// outputs "-3"
    }
    -PatP

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Pat Phelan
    What I meant was:
    Ah, I see what you are saying. In C you can also just do a binary cast:

    Code:
    unsigned int value = 0xFFFFFFFF;
    int signedValue = (int) value; // will be -1
    In a T-SQL stored proc, I don't see an equivalent. I'm stuck using if statements and subtraction:

    Code:
    DECLARE @value BIGINT
    DECLARE @signedValue INT
    
    SET @value = 4294967295
    
    IF @value < 2147483648 SET @signedValue = @value
    ELSE SET @signedValue = @value - 4294967296
    
    SELECT @signedValue -- Will be -1
    This works but I had a nagging feeling that there was a cleaner solution... Maybe not.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Uglier joins and queries? Have you tried it?
    Code:
    begin tran
    go
    create function fn_RogerWilco(
       @section1 bigint ,
       @section2 bigint ,
       @section3 bigint ,
       @section4 bigint ) returns bigint
    as begin
       declare @result bigint
       set @result = (@section1 * 0x1000000) | (@section2 * 0x10000) | (@section3 * 0x100) | (@section4)
       return @result
    end
    go
    create table RogerWilco (
    section1 tinyint not null,
    section2 tinyint not null,
    section3 tinyint not null,
    section4 tinyint not null,
    IPAddress as dbo.fn_RogerWilco(section1, section2, section3, section4) )
    insert RogerWilco (section1, section2, section3, section4) values (192,168,1,1)
    select * from RogerWilco
    rollback tran
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by RogerWilco
    In C you can also just do a binary cast:

    Code:
    unsigned int value = 0xFFFFFFFF;
    int signedValue = (int) value; // will be -1
    I think that you are making this orders of magnitude more difficult than it needs to be. Any flavor of four-byte int reaches the SQL Server as a signed int... It is the only way that they come. If you send any four-byte int down the wire, you'll get back the same bit pattern regardless of how you reference it within your client code. If you use either %d or %u to format it, you'll format the strings as signed or unsigned, regardless of how the int values are declared. The only reason that signed/unsigned makes any difference at all is if you want to do math on the server and have it match math done on the client, if you don't care about that, then nothing else matters!

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i prefer the rdjabarov solution myself
    i worked on a ip storage question where they had stored it as varchar...(yuck, first timers)
    you can however write a pretty complex little function to break apart an ip address using substring and case.
    either way i would be very interested to see the storage in a single column with a simple way to use the decimal storage inplace and in the context of an ip address not a decimal.

    "The suspense is terrible. I hope it will last." Willy Wonka

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm confused. You see value in the database engine being able to address the octets of an IP address? So would a BINARY(4) be what you want, or four separate TINYINT columns, or something different?

    I guess I'd never given real thought to allowing the database engine to manipulate the IP addresses directly. I know that it can be done, I'm just not sure that I can see the value in doing it.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    I'm confused...I'm just not sure that I can see the value in doing it.
    Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Answering the question as given, you could use numeric (11, 0). In the database, it would not look like an IP address as such. Is your application going to decode the data?

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    free at last, free at last....thank god almighty....

    well you get the idea....

    ahem....

    anyone want to suggest that an ip address is NOT a number.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?
    There are ways you can manipulate IP addresses to obtain useful information if you know the network topology, such as knowing for any address A.B.C.D in your network, its router will be A.B.C.1 or similar operations. You might also know that for a device of class X, there will be a device of class Y at the IP address of X + 2 (for example in a cluster configuration).

    I was thinking that RogerWilco wanted to ensure that the addresses computed would match between the client and the server. For that usage, since the server must use signed ints, it would help to be able to have signed ints available on the client too, but that is the only reason I can imagine.

    Oh well, maybe RogerWilco will weigh in with some details that my feeble brain can't find at the moment!

    -PatP

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    anyone want to suggest that an ip address is NOT a number.....
    What else is an IP address but a number? Over the years, folks have put some kind of funky spins on how it is used, but if you go back to Metcalfe's original writings you'll see that it really truly is just a number!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •