I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual IP address in other tables.

Generally, I am going to create a stored procedure that is returning the ID for given IP address or (if the address was not found) insert the address and return the generated ID.

I am expecting to have many records (I cannot tell exactly how many), but I need the stored procedure above to be executed as fast as possible. So, I am wondering how to stored the actual IP address - in text or bytes format. Which is going to be better?

I have already written SQL CLR functions for transforming IP address bytes to string and the reverse, so transformation is not an issue (working with both IPv4 and IPv6).

I guess I need to create an index to optimize the search, but I am not sure should I include the IP address field to the clustered index, or to create a separate index and with which type the search will be faster?

  • 2
    For IPv4 at least, why not 4 tinyints? Then they're actually human readable and you don't have to perform any conversions. You can also create all kinds of persisted computed columns to represent specific types of searches (exact match, subnet, etc). Mar 30, 2015 at 20:38
  • If it the case was only for IPv4 I guess I would convert the address to INT and used the field as index key. But for IPv6 I need to use two BIGINT fields and I prefer to store the value in one field - seems to me more natural.
    – gotqn
    Mar 30, 2015 at 21:17
  • 1
    Still don't understand why INT instead of 4 TINYINTs? Same storage, easier debugging, less nonsense, IMHO. If you have two completely different types with different validation and meaning, why do they need to use the same column? If you are betting that a single column is simpler, why not just use SQL_VARIANT, then you don't have to worry about anything. You can store dates and strings and numbers and everybody can have a big party in one gigantic, useless column... Mar 30, 2015 at 21:43
  • Where are the IP Addresses coming from? Will they ever include the mask/subnet (i.e. I have seen this come through from web server logs and does not translate easily to BIGINT (INT won't work as the calculation requires an unsigned INT, unless of course, you incorporate that normalization to assume 0 is really -2.14xxxx billion). I guess the subnet mask could just be an additional TINYINT field. But I do understand wanting to store as BIGINT if wanting to match that up to a DB of latitude/longitude to map them out. But as Aaron mentioned, that can be a persisted computed col. Mar 30, 2015 at 22:08

2 Answers 2


how to stored the actual IP address - in text or bytes format. Which is going to be better?

Since "text" here refers to VARCHAR(45) and "bytes" refers to VARBINARY(16), I would say: neither.

Given the following information (from Wikipedia article on IPv6):

Address representation
The 128 bits of an IPv6 address are represented in 8 groups of 16 bits each. Each group is written as 4 hexadecimal digits and the groups are separated by colons (:). The address 2001:0db8:0000:0000:0000:ff00:0042:8329 is an example of this representation.

For convenience, an IPv6 address may be abbreviated to shorter notations by application of the following rules, where possible.

  • One or more leading zeroes from any groups of hexadecimal digits are removed; this is usually done to either all or none of the leading zeroes. For example, the group 0042 is converted to 42.
  • Consecutive sections of zeroes are replaced with a double colon (::). The double colon may only be used once in an address, as multiple use would render the address indeterminate. RFC 5952 recommends that a double colon must not be used to denote an omitted single section of zeroes.[41]

An example of application of these rules:

        Initial address: 2001:0db8:0000:0000:0000:ff00:0042:8329
        After removing all leading zeroes in each group: 2001:db8:0:0:0:ff00:42:8329
        After omitting consecutive sections of zeroes: 2001:db8::ff00:42:8329

I would start by using 8 VARBINARY(2) fields to represent the 8 groups. The fields for Groups 5 - 8 should be NULL as they will only be used for IPv6 addresses. The fields for Groups 1 - 4 should be NOT NULL as they will be used for both IPv4 and IPv6 addresses.

By keeping each group independent (as opposed to combining them into either a VARCHAR(45) or a VARBINARY(16) or even two BIGINT fields) you get two main benefits:

  1. It is much easier to reconstruct the address into any particular representation. Otherwise, in order to replace consecutive groups of zeroes with (::) you would have to parse it out. Keeping them separate allows for simple IF / IIF / CASE statements to facilitate this.
  2. You will save a ton of space on IPv6 addresses by enabling either ROW COMPRESSION or PAGE COMPRESSION. Since both types of COMPRESSION will allow for fields that are 0x00 to take up 0 bytes, all of those groups of zeroes will now not cost you anything. On the other hand, if you stored the example address from above (in the Wikipedia quote), then the 3 sets of all zeroes in the middle would take up their full amount of space (unless you were doing the VARCHAR(45) and went with the reduced notation, but that might not work well for indexing and would require special parsing to reconstruct it to the full format, so let's assume that is not an option ;-).

IF you need to capture the Network, create a TINYINT field for that called, um, [Network] :-)

For more info on the Network value, here is some info from another Wikipedia article on the IPv6 address:


An IPv6 network uses an address block that is a contiguous group of IPv6 addresses of a size that is a power of two. The leading set of bits of the addresses are identical for all hosts in a given network, and are called the network's address or routing prefix.

Network address ranges are written in CIDR notation. A network is denoted by the first address in the block (ending in all zeroes), a slash (/), and a decimal value equal to the size in bits of the prefix. For example, the network written as 2001:db8:1234::/48 starts at address 2001:db8:1234:0000:0000:0000:0000:0000 and ends at 2001:db8:1234:ffff:ffff:ffff:ffff:ffff.

The routing prefix of an interface address may be directly indicated with the address by CIDR notation. For example, the configuration of an interface with address 2001:db8:a::123 connected to subnet 2001:db8:a::/64 is written as 2001:db8:a::123/64.

For indexing, I would say create a Non-Clustered index on the 8 Group fields, and possibly the Network field if you decide to include that.

End result should be something like the following:

  IPAddressID INT          NOT NULL IDENTITY(-2147483648, 1),
  Group8      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group7      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group6      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group5      VARBINARY(2) NULL, -- IPv6 only, NULL for IPv4
  Group4      VARBINARY(2) NOT NULL, -- both
  Group3      VARBINARY(2) NOT NULL, -- both
  Group2      VARBINARY(2) NOT NULL, -- both
  Group1      VARBINARY(2) NOT NULL, -- both
  Network     TINYINT      NULL

  (IPAddressID ASC)

  ON [IPAddress] (Group1 ASC, Group2 ASC, Group3 ASC, Group4 ASC,
         Group5 ASC, Group6 ASC, Group7 ASC, Group8 ASC, Network ASC)


  • I recognize that you plan on using BIGINT for the ID field, but do you really expect to capture more than 4,294,967,295 unique values? If so then just change the field to be BIGINT and you can then even change the seed value to be 0. But otherwise you are better off using INT and starting with the minimum value so that you can make use of the entire range of that datatype.
  • If desired, you can add one or more NONpersisted Computed Columns to this table to return text representations of the IPAddress.
  • The Group* fields are arranged purposefully going down, from 8 to 1, in the table so that doing SELECT * will return the fields in the expected order. But the index has them going up, from 1 to 8, as that is how they are filled out.
  • An example (unfinished) of a computed column to represent the values in text form is:

    ALTER TABLE [IPAddress]
      ADD TextAddress AS (
    IIF([Group8] IS NULL,
        -- IPv4
        CONCAT(CONVERT(TINYINT, [Group4]), '.', CONVERT(TINYINT, [Group3]), '.',
          CONVERT(TINYINT, [Group2]), '.', CONVERT(TINYINT, [Group1]),
          IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')),
        -- IPv6
          CONVERT(VARCHAR(4), [Group8], 2), ':', CONVERT(VARCHAR(4), [Group7], 2), ':',
          CONVERT(VARCHAR(4), [Group6], 2), ':', CONVERT(VARCHAR(4), [Group5], 2), ':',
          CONVERT(VARCHAR(4), [Group4], 2), ':', CONVERT(VARCHAR(4), [Group3], 2), ':',
          CONVERT(VARCHAR(4), [Group2], 2), ':', CONVERT(VARCHAR(4), [Group1], 2),
          IIF([Network] IS NOT NULL, CONCAT('/', [Network]), '')
       ) -- end of IIF


    INSERT INTO IPAddress VALUES (127, 0, 0, 0, 4, 22, 222, 63, NULL); -- IPv6
    INSERT INTO IPAddress VALUES (27, 10, 1234, 0, 45673, 200, 1, 6363, 48); -- IPv6
    INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 2, 63, NULL); -- v4
    INSERT INTO IPAddress VALUES (NULL, NULL, NULL, NULL, 192, 168, 137, 29, 16); -- v4
    SELECT [IPAddressID], [Group8], [Group1], [Network], [TextAddress]
    FROM IPAddress ORDER BY [IPAddressID];


    IPAddressID   Group8   Group1   Network  TextAddress
    -----------   ------   ------   -------  ---------------------
    -2147483646   0x007F   0x003F   NULL     007f:0000:0000:0000:0004:0016:00de:003f
    -2147483645   0x001B   0x18DB   48       001b:000a:04d2:0000:b269:00c8:0001:18db/48
    -2147483644   NULL     0x003F   NULL
    -2147483643   NULL     0x001D   16
  • For SQL Server 2005, would defining the columns as VARDECIMAL over VARBINARY since DATA_COMPRESSION isn't available?
    – Matt Borja
    Mar 25, 2016 at 17:43
  • @SolomonRutzky Thank you for the detailed explanation. I'm curious, how would I search between address ranges? For example, I have a data vendor providing IP Geolocation data in the form of a start and end IP address. I need to find which range a given IP falls in.
    – J Weezy
    Feb 25, 2019 at 22:56
  • @JWeezy You are welcome :). How are the start and end IP addresses being stored? Are you using IPv4 or v6 addresses? Feb 26, 2019 at 15:23
  • @SolomonRutzky Both. IPv4 isn't a problem because I can store it as an integer. Unfortunately, there is no 128 bit integer or number related data type in SQL Server large enough to handle it. So, for IPv6 I am storing it in VARBINARY(16) and then I use the BETWEEN operator to search between ranges. But, I am getting multiple results on IP ranges, which I do not think are correct. I would like to use the same data type for both IPv4 and IPv6 if possible.
    – J Weezy
    Feb 26, 2019 at 15:44
  • @JWeezy I was going to suggest BINARY(16) ;-). Can you please give me an example with a start / end range and at least two rows that you get back, one valid and at least one invalid? It might be that VARbinary shortens some values. Feb 26, 2019 at 15:49

Smaller is always going to be faster. With smaller values you can fit more of them into a single page, therefore less IO, potentially shallower B-Trees etc.

All other things (translation overhead, readability, compatibility, CPU load, index sargability etc.) being equal, of course.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.