IP Address datatype

Question

What is the ideal datatype to represent IP addresses in SQL Server? Is it better to use varchar(n)?

Here is a part of my database:

CREATE TABLE logUser 
(
    userId int primary key,
    userFirstName varchar(20),
    userLastName varchar(20),
    IPAddress real,
    city varchar(15),
    salary real 
)

CREATE TABLE Alert 
(
    AlertId varchar(10) primary key,
    alType varchar(20),
    aluserId int references logUser(userId)
)

CREATE TABLE GeoAnalytics
(
    AId int, --Analytics ID     
    AlertId varchar(10) references Alert(AlertId),
    Country varchar (20),
    City varchar(30),
    IPAddress real,

    CONSTRAINT pk PRIMARY KEY (AId, AlertId)
)

Answer 1

varchar(15) if you just want to store the IP and you don't have much data.

An IPv4 address is actually a 32 bit integer that can be converted to/from it's numeric form to display form. (The same is true for IPv6: it's a 128-bit integer).

If you stored it as binary(4) it would take up less space (4 bytes instead of 15); you could do IP range checks in your queries; and it would be faster to use as a condition in a select statement since the DB wouldnt have to do string comparisons.

The downside is you would need to convert the IP to it's integer form to use it with the DB; and you would have to convert it to it's display form before showing it to users. If you search a bit, I'm sure you'll find prewritten functions for this in whatever language you're using, but it's an extra step.

I would say either is ideal depending on your circumstances.