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

sql sql-server types ip-address ipv4
Shimmy Weitzhandler picture Shimmy Weitzhandler · Jun 24, 2009 · Viewed 70.5k times · Source

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.

Answer

David M picture David M · Jun 24, 2009

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).

Related questions

Difference between numeric, float and decimal in SQL Server

What are the differences between numeric, float and decimal datatypes and which should be used in which situations? For any kind of financial transaction (e.g. for salary field), which one is preferred and why?

Read More
How to store decimal values in SQL Server?

I'm trying to figure out decimal data type of a column in the SQL Server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc I assigned decimal(18, 0) to the column data type but this not allowing me to store …

Read More
What is SYSNAME data type in SQL Server?

What is the SQL Server SYSNAME data type for? BOL says: The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. but I don't really get that. Is there a use-case you …

Read More