What is the most appropriate data type for storing an IP address in SQL server?

What should be the most recommended datatype for storing an IPv4 address in SQL server?

Or maybe someone has already created a user SQL data-type (.Net assembly) for it?

I don't need sorting.

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).

I normally just use varchar(15) for IPv4 addresses - but sorting them is a pain unless you pad zeros.

I've also stored them as an INT in the past. System.Net.IPAddress has a GetAddressBytes method that will return the IP address as an array of the 4 bytes that represent the IP address. You can use the following C# code to convert an IPAddress to an int...

var ipAsInt = BitConverter.ToInt32(ip.GetAddressBytes(), 0);

I had used that because I had to do a lot of searching for dupe addresses, and wanted the indexes to be as small & quick as possible. Then to pull the address back out of the int and into an IPAddress object in .net, use the GetBytes method on BitConverter to get the int as a byte array. Pass that byte array to the constructor for IPAddress that takes a byte array, and you end back up with the IPAddress that you started with.

var myIp = new IPAddress(BitConverter.GetBytes(ipAsInt));

Regarding this comment in the accepted answer

> sorting them is a pain unless you pad > zeros.

Here's a trick for SQL Server 2008 (From Itzik Ben-Gan 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)

Returns

ip_address
-------------
2.12.4.4
2.12.4.169
131.33.2.201
131.33.2.202
131.107.2.201

For space efficient storage and when the values are to be processed (matched or compared to a range), I use an int. The IP address really is just a 32 bit value.

For a simple solution where you just want to store the value to view it, I use a varchar(15) to store the string representation of the IP adress.

Don't forget about IPv6 - you need a lot more room if you need to store them - 128bits compares to IPv4's 32.

I'd go for bigint, though you will need some helper code to translate to human friendly versions.

One of my favorite articles talks about why you shouldn't use regular expressions to parse IP addresses. Most of what they're talking about is really explaining why you should be very careful with textual representations of IP addresses. I suggest you read it before deciding what datatype to use in your database, and probably also for whatever handling your app will be doing (even though the article is written about Perl, it's useful for any language).

I think in the end a 32 bit datatype (or four 8-bit datatypes) would be the best choice.

I'm reading a lot of similar questions on here, and none of the replies in this one mention the number one answer in others: "For IPv4 addresses, you may want to store them as an int unsigned and use the INET_ATON() and INET_NTOA() functions to return the IP address from its numeric value, and vice versa." I think this is what I'm going to go with in my db, unless I decide to use the php functions mentioned above.

IPV4? int? or tinyint x 4?

It really depends on whether it's just storage and retrieval or if it's going to be a ranged search criteria.

It depends on your purpose. If you want best storage and probably performance too and for the most part, store it as int, storing it as varchar etc. would cost more performance than just a simple innocent int.
You can also search by IP by having the search parameter as the desired int too.

There is a property IPAddress.Address but it's obsolete, I don't know why, since if you don't need sorting or control over the IP classes, the best way is to store it as unsigned integer (that has a max value of 0xffffffff which equals to 255.255.255.255 in decimal representation.
Using EF Core, you can use a converter to have it automatically converted to and from IPAddress.

Also the IPAddress class has a constructor that accepts a long argument.

And according to VS debugger visualizer, that IPAddress class itself stores its internal variable as one number (not byte array).

Read more on workarounds storing a unit in MS SQL Server:

• 4 byte unsigned int in ms sql?
• Sql server 4 byte unsigned int

Since an IP address has 32 bits in it, can you just use a LONG to store the numerical value?
It wouldn't be as space-wasteful as using VARCHAR, but then you'd have to decode it back to an IP before you use it, every time, and the delay and overhead that costs might not be worth it.

I've had some success with making four smallint (or whatever smallish integer datatype you prefer) columns -- one for each octet. Then, you can make a view which smashes them together as a char string (for display) or then you can write simple operators to determine who all is in what subnet etc.

It is quite fast (provided you do proper indexing) and also allows for really easy querying (no string manipulation!).

Quoting this:

>Store IP addresses in a CHAR(15) column. Depending on how much data you're storing, this can be quite wasteful (why do we need to store the dots?). I

I'm newbie @ php,sql , but i think fastest way to store something in sql db is to convert it to int value and save as int.

I used function in php -

function ip_convert() {
    $ip = $_SERVER['REMOTE_ADDR'];
    $intip = str_replace(".","0",$ip);
    return $intip;
}

And then i just replace all dots with zeros. Then if i need use this ip from sql.. if($ip == ip_convert())

But this only if you use PHP.