(numbered right to left).
2. I usually varchar(15) for IPv4 addresses - but they are a pain to sort unless you pad zeros. I also save them as past interpretations.System.Net.IPAddress has the GetAddressBytes method that will return the IP address as an array of 4-byte representations of the IP address. You have the following C# code for converting IPAddress to int.... .var ipAsInt = BitConverter.ToInt32(ip.GetAddressBytes(), 0);
Me, I had to do a lot of looking for the deduplicated address and wanted the metrics to be as small & fast as possible. Then to pull the address back out of the int and into an object in IPAddressNET, use GetBytes in the method BitConverter to get the int as a byte array. Through the constructor of this byte array IPAddress this takes a byte array that you end up backing up with the IPAddress you started with. var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));
3. For this in the accepted answers they sort of are It's a pain unless you pad it with zeros. Here's a SQL Server 2008 trick (from Itzikpengan in this book) WITH ip_addresses as
(
SELECT '131.33.2.201' AS ip_address UNION ALL
SELECT '2.12.4.4' AS ip_address UNION ALL
SELECT '131.33.2.202' AS ip_address UNION ALL
SELECT '2.12.4.169' AS ip_address UNION ALL
SELECT ' 131.107.2.201' AS ip_address
)
SELECT ip_address
from ip_addresses
ORDER BY CAST('/' + ip_address + '/' AS hierarchyid )
return ip_address
-------------
2.12.4.4
2.12.4.169
131.33.2.201
131.33.2.202
131.107.2.201
4. My favorite article talks about why you shouldn't' regular expressions to resolve IP addresses. Most of what they are talking about is really explaining why you should be very careful with the textual representation of IP addresses. I recommend that you decide what data type you want in the database before reading it, probably also because of the kinds of processing your application will do (even if the article is written about Perl, it's in any language). I think at the end of the 32-bit data type (or four 8-bit data types) would be the best choice.
5. I read a lot of similar questions here and did not respond in this in someone else's number one answer: "For IPv4 addresses, you may want to save them as an unsigned integer for the IP address whose value is returned by the INET_ATON() and INET_NTOA() functions, and vice versa! ". I think that's where I'm going to go at my decibel unless I decide on it.
6. The best way (when sorting and other controls over the IP are not needed) is for it to be stored as an integer, storing it as a varchar, etc. will cost way more performance than just a simple innocent integer. There is a property IPAddress.Address but it is obsolete and I don't know why, because if you don't need sorting or other control over IP classes it is best to store it as an unsigned integer (i.e. there is a maximum value of 0xffffffffff which is equal to 255.255.255.255 in decimal representation. Also the IPAddress class has a construct that accepts long term and according to the VS debugger visualization tool, i.e. the IPAddress class itself stores its internal variables as a number (not an array of bytes). Learn more about the solution to store units in MS SQL Server: 4-byte unsigned integer in MS SQL? 4-byte unsigned integer in SQL Server
7. IPV4? INT? or TINYINT×4? It really depends on whether it's just stored and retrieved or if it's going to be a remote search condition.
8. Don't forget IPv6 - you need more room if you need to store them - 128 for IPv4 is 32. I'd go for the bigint but you'd need help translating the code to a human friendly version.
9. For space efficient storage and when the value is to be processed (matching the range), an int. IP address is really just a 32-bit value. For a simple solution where you just have to store the value to view it, a varchar(15) stores a string representation of the IP address form.
10. I would probably use a go to varchar or char. and set the size to 15.
11. PHP and then PHP5 have 2 built in conversion functions: ip2long and long2ip (or) which returns/converts signed integers
12. I'm tired of having to do four SMALLINT (or) other short integer data types. other short integer data types you like) columns - each octet. Then you can make a view, which falls apart together as a char string (display) or then you can write simple operators to determine who are waiting for what It's pretty fast (as long as you do the indexing correctly) and also allows easy querying (no string manipulation!) .
13. Since the IP address has 32 bits, it can you save the value for a long time? It would not be because of space wasting VARCHAR, but then you would receive to decode it back to an IP, each and every and the latency and overhead costs may not be worth it.
14. Quote like this: Storing IP addresses in CHAR (15) columns. Depending on how much data is to be stored, this can be very wasteful (why do we need to store points?).