title: Storing IP addresses in Microsoft SQL Server
body: <p>I know there's already a lot of questions on this topic but I might have a few extra questions.</p>
  
  <p>I want to store IP addresses in a database (single column if possible). I want to be able to store either IPv4 or IPv6 addresses. I want to be able to differentiate between IPv4 and IPv6 addresses upon retrieval.</p>
  
  <p>Approaches I have considered:</p>
  
  <ul>
  <li><p>Use VARCHAR(45) to store the IP addresses in textual format. The maximum length of the textual representation of an IPv4 address is <strong>15</strong> characters. The maximum length of the textual representation of an IPv6 address is <strong>39</strong> characters, although I read that they can actually be up to <strong>45</strong> when they're proxying an IPv4 address (not something I'm familiar with so I might be off here) - header files in some software seem to reflect this, specifying <strong>46</strong> as the maximum length of an IPv6 address (45 + \0). Differentiation between formats is trivial.</p></li>
  <li><p>Store the IP addresses in two bigint columns. This uses less space than text format. Differentiation between formats - if the first column is 0 or NULL (though bad for performance), it's an IPv4 address, otherwise IPv6?</p></li>
  <li><p>Store the IP addresses in a uniqueidentifier column. 128 bits, can store both address formats in the most compact way (we waste 96 bits on IPv4 addresses but this is unavoidable no matter what we do). This way would seem most preferable, but given any 128 bit integer, is it possible to know whether that 128 bit integer represents an IPv4 or IPv6 address?</p></li>
  </ul>
  
  <p>If differentiation between IPv4 and IPv6 addresses represented as a single 128 bit integer is possible, I'd prefer to use the third approach barring any major issues with that solution. I guess my main question is whether that's possible, but hearing any thoughts on the advantages and disadvantages of different methods (including ones I have not listed here) would be helpful also.</p>
  
  <p>Also... if it were necessary, would adding a bit column in combination with the third approach (to differentiate between IPv4 and IPv6) be a good idea? Is a bit column actually physically 1 bit, or is it 8?</p>