Menu
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.
565
As with MS SQL Server you can store IP address, both IPv4 and IPv6, either as varchar or as numeric. As already mentioned, the binary representation is much more preferable than any other as it reflects the true nature of an IP address.
IP Network Address Data Types. IPV4 and IPV6 are abstract data types that store IPv4 and IPv6 host addresses, respectively, in binary format. IPV4 is a 4-byte host address in dotted-decimal notation (four decimal numbers, each ranging from 0 to 255, separated by dots).
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format.
To do this, create a new connection in SSMS and use the "tcp:" prefix with the server name. Then re-run either query and you'll get the IP address. A clarification may be useful here.
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).
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
