How to store both IPv4 or IPv6 in single column in SQL Server?

views:

answers:

+1 Q:

How to store both IPv4 or IPv6 in single column in SQL Server?

shall I use binary(16)? or varbinary(16)?

I know I can use getAddress() in java.net.InetAddress (Java) or System.Net.IPAddress (C#) to get a byte[] representation of both IPv4 and IPv6, but if I need to insert IPv4 i.e. binary(4) into a binary(16) field in SQL Server, do I need to worry about padding or anything?

Thanks

+4 A:

IF you store a binary(4) in a binary(16) column you'll get back, when you read it, a padded value of length 16. If you want to have dynamic length you must use a varbinary(16). This type retains the length of the data inserted, at the cost of adding extra 2 bytes on-disk (the actual length).

Remus Rusanu 2010-08-25 00:02:33

Since I expect majority of the IP will still be in IPv4, I guess varbinary(16) makes sense.

Henry 2010-08-25 00:03:46

And the cost of creating a variable length field

NullUserException 2010-08-25 00:04:16

For the exact cost details see http://msdn.microsoft.com/en-us/library/ms178085.aspx and/or http://sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx

Remus Rusanu 2010-08-25 00:16:22

Remus Rusanu, would u use binary(16) or varbinary(16)?

Henry 2010-08-25 07:29:58

Definitely varbinary(16)

Remus Rusanu 2010-08-25 14:44:12

+1 A:

Use v4-in-v6 address embedding to convert your ipv4 addresses to ipv6 format; then you can treat them all identically.

Zack 2010-08-25 00:11:48

wouldn't it be just like binary(4) with 12x zero byte padding in front?

Henry 2010-08-25 01:13:01

Presumably you expect some of the entries in this database column to be actual ipv6 addresses. The point of v4-in-v6 embedding is that more of your application code won't have to care about the difference (it can just pretend it has nothing but v6 addresses).

Zack 2010-08-25 01:17:20

ansaurus

tags:

views:

answers:

How to store both IPv4 or IPv6 in single column in SQL Server?

related questions