One of our tech guys published this article on our company blog last week. I thought I’d pass the information along to my tech readers.
By David Poole, 2007/04/02
What is the best way to store an IP Address in SQL Server
Imagine that you are asked to design a database for a data analysis team to perform web site traffic analysis. This brief will have many requirements and amongst them will be the need to store IP addresses.
Search engines such as Google have a fixed range of IP addresses so the team can easily separate out traffic from true visitors Vs traffic from bots. Although the team have a good rudimentary knowledge of T-SQL the data must be relatively simple to query. So with regard to IP addresses the requirement is as follows:
- Store IP addresses efficiently
- Allow retrieval of IP addresses in a machine readable format
- Allow simple querying on a range or ranges of IP addresses
If traffic to your web site is high then the choice of data types is going to be important. You could keep the IP address as a VARCHAR(15) but given the nature of what an IP address actually is, 4 integers in the range 0 to 255, this seems a trifle wasteful.
My initial thought was to use techniques described in Lee Everest’s article Introduction to bitmasking in SQL2005 but if you read the forum discussion of the article you will see an interesting suggestion by Joe Celko.
So what possiblities do we have? Let us consider the ip address 192.168.0.5
Method: VARCHAR(15)
Storage: Between 7 and 15 bytes
Comment: Stores the IP address in human readable but this is wasteful.
Method: BIGINT
Storage: 8 bytes
Comment: We can represent our IP address as 192168000005. It is stretching the definition of human readability somewhat but this depends on your audience.
Method: INT
Storage: 4 bytes
Comment: Our IP address is no longer human readable being represented as 1084751877.
Method: Four separate TINYINT fields
Storage: 4 bytes
Comment: Our address is now both efficient and human readable just as Joe Celko pointed out.
SQL 2005 CLR User Defined Types
SQL2005 provides us with one other possiblity. The .NET assembly user defined type.
I was fortunate to go on the Microsoft “Updating Your Database Development Skills to Microsoft SQL Server 2005″ (Course 2734B) which included an IP address UDT. As I am not sure of the copyright issues surrounding the code for the UDT I am not including the source code here but the UDT provided the following functionality.
- Accept an ip address in the form nnn.nnn.nnn.nnn
- Return the individual bytes of an IP address
- Return the string representation of the IP address
- Return a varbinary representation of the IP address
- Return a string with the PING command and the IP address. I removed this from the code as it was irrelevant.
Get David’s full article here.
Feel free to share and enjoy this article: