Storing IP addresses in Microsoft SQL Server

Question

I know there's already a lot of questions on this topic but I might have a few extra questions.

I want to store IP addresses in a database (single column if possible). I want to be able to store either IPv4 or IPv6 addresses. I want to be able to differentiate between IPv4 and IPv6 addresses upon retrieval.

Approaches I have considered:

• Use VARCHAR(45) to store the IP addresses in textual format. The maximum length of the textual representation of an IPv4 address is 15 characters. The maximum length of the textual representation of an IPv6 address is 39 characters, although I read that they can actually be up to 45 when they're proxying an IPv4 address (not something I'm familiar with so I might be off here) - header files in some software seem to reflect this, specifying 46 as the maximum length of an IPv6 address (45 + \0). Differentiation between formats is trivial.

• Store the IP addresses in two bigint columns. This uses less space than text format. Differentiation between formats - if the first column is 0 or NULL (though bad for performance), it's an IPv4 address, otherwise IPv6?

• Store the IP addresses in a uniqueidentifier column. 128 bits, can store both address formats in the most compact way (we waste 96 bits on IPv4 addresses but this is unavoidable no matter what we do). This way would seem most preferable, but given any 128 bit integer, is it possible to know whether that 128 bit integer represents an IPv4 or IPv6 address?

If differentiation between IPv4 and IPv6 addresses represented as a single 128 bit integer is possible, I'd prefer to use the third approach barring any major issues with that solution. I guess my main question is whether that's possible, but hearing any thoughts on the advantages and disadvantages of different methods (including ones I have not listed here) would be helpful also.

Also... if it were necessary, would adding a bit column in combination with the third approach (to differentiate between IPv4 and IPv6) be a good idea? Is a bit column actually physically 1 bit, or is it 8?

Answer 1

I may store it in binary. An IPv6 address is 128bit -> 16 bytes long. If you store IPv4 address, you should store it at the last 4 bytes. When you use this field in application there are many built in options to convert from IPv6 to text and text back to IPv6 binary.

In IPv4 case: this address type has its first 80 bits (10 bytes) set to zero and the next 16 bits (2 bytes) set to one (0xFF), while its last 32 bits (4 bytes) are filled with the IPv4 address. The stack implementation usually differs, so you should make the conversions at client side (from IPv6 to IPv4). This address space is safe, so it is reserved for IPv4 addressing.

More info and more info.