Tipo de datos para almacenar direcciones IP en SQL Server

¿Qué tipo de datos debo elegir para almacenar una dirección IP en un servidor SQL?

Al seleccionar el tipo de datos correcto, ¿sería bastante fácil filtrar por dirección IP?

La forma técnicamente correcta de almacenar IPv4 es Binario (4), ya que eso es lo que realmente es (no, ni siquiera un INT32 / INT (4)), la forma textual numérica que todos conocemos y amamos (255.255.255.255) siendo solo la conversión de visualización de su contenido binario.

Si lo hace de esta manera, querrá que las funciones se conviertan hacia y desde el formato de visualización de texto:

A continuación, se explica cómo convertir el formulario de visualización de texto a binario:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4) AS BEGIN DECLARE @bin AS BINARY(4) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) RETURN @bin END go 

Y aquí está cómo convertir el binario a la forma de visualización de texto:

 CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15) AS BEGIN DECLARE @str AS VARCHAR(15) SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.' + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) ); RETURN @str END; go 

Aquí hay una demostración de cómo usarlos:

 SELECT dbo.fnBinaryIPv4('192.65.68.201') --should return 0xC04144C9 go SELECT dbo.fnDisplayIPv4( 0xC04144C9 ) -- should return '192.65.68.201' go 

Finalmente, al hacer búsquedas y comparar, siempre use la forma binaria si desea poder aprovechar sus índices.


ACTUALIZAR:

Quería agregar que una forma de abordar los problemas de rendimiento inherentes de las UDF escalares en SQL Server, pero aún conservar la reutilización de código de una función es usar una función de valor de tabla en línea (iTVF). Así es como la primera función anterior (de cadena a binaria) se puede volver a escribir como un iTVF:

 CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE AS RETURN ( SELECT CAST( CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) AS BINARY(4)) As bin ) go 

Aquí está en el ejemplo:

 SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201') --should return 0xC04144C9 go 

Y así es como lo usarías en un INSERT

 INSERT INTo myIpTable SELECT {other_column_values,...}, (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201')) 

Puedes usar varchar. La duración de IPv4 es estática, pero la de IPv6 puede ser muy variable.

A menos que tenga una buena razón para almacenarlo como binario, quédese con un tipo de cadena (textual).

Aquí hay un código para convertir IPV4 o IPv6 en formato varchar a binario (16) y viceversa. Esta es la forma más pequeña que se me ocurre. Debe indexar bien y proporcionar una forma relativamente fácil de filtrar subredes. Requiere SQL Server 2005 o posterior. No estoy seguro de que sea totalmente a prueba de balas. Espero que esto ayude.

 -- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2') -- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2') -- SELECT dbo.fn_ConvertIpAddressToBinary('bogus') ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary ( @ipAddress VARCHAR(39) ) RETURNS BINARY(16) AS BEGIN DECLARE @bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2) , @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT , @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4) SELECT @delim = '.' , @prevColIndex = 0 , @limit = 4 , @vbytes = 0x , @parts = 0 , @colIndex = CHARINDEX(@delim, @ipAddress) IF @colIndex = 0 BEGIN SELECT @delim = ':' , @limit = 8 , @colIndex = CHARINDEX(@delim, @ipAddress) WHILE @colIndex > 0 SELECT @parts = @parts + 1 , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) SET @colIndex = CHARINDEX(@delim, @ipAddress) IF @colIndex = 0 RETURN NULL END SET @ipAddress = @ipAddress + @delim WHILE @colIndex > 0 BEGIN SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1) IF @delim = ':' BEGIN SET @zone = RIGHT('0000' + @token, 4) SELECT @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)') , @vbytes = @vbytes + @vbzone IF @token = '' WHILE @parts + 1 < @limit SELECT @vbytes = @vbytes + @vbzone , @parts = @parts + 1 END ELSE BEGIN SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2) SELECT @vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)') , @vbytes = @vbytes + @vbzone END SELECT @prevColIndex = @colIndex , @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1) END SET @bytes = CASE @delim WHEN ':' THEN @vbytes ELSE 0x000000000000000000000000 + @vbytes END RETURN @bytes END 
 -- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2) -- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF) ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress] ( @bytes BINARY(16) ) RETURNS VARCHAR(39) AS BEGIN DECLARE @part VARBINARY(2) , @colIndex TINYINT , @ipAddress VARCHAR(39) SET @ipAddress = '' IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000 BEGIN SET @colIndex = 13 WHILE @colIndex < = 16 SELECT @part = SUBSTRING(@bytes, @colIndex, 1) , @ipAddress = @ipAddress + CAST(CAST(@part AS TINYINT) AS VARCHAR(3)) + CASE @colIndex WHEN 16 THEN '' ELSE '.' END , @colIndex = @colIndex + 1 IF @ipAddress = '0.0.0.1' SET @ipAddress = '::1' END ELSE BEGIN SET @colIndex = 1 WHILE @colIndex <= 16 BEGIN SET @part = SUBSTRING(@bytes, @colIndex, 2) SELECT @ipAddress = @ipAddress + CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)') + CASE @colIndex WHEN 15 THEN '' ELSE ':' END , @colIndex = @colIndex + 2 END END RETURN @ipAddress END 

Como quiero manejar tanto IPv4 como IPv6 , estoy usando VARBINARY(16) y las siguientes funciones SQL CLR para convertir la presentación de la dirección IP de text a bytes y viceversa:

 [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBytes GetIPAddressBytesFromString (SqlString value) { IPAddress IP; if (IPAddress.TryParse(value.Value, out IP)) { return new SqlBytes(IP.GetAddressBytes()); } else { return new SqlBytes(); } } [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlString GetIPAddressStringFromBytes(SqlBytes value) { string output; if (value.IsNull) { output = ""; } else { IPAddress IP = new IPAddress(value.Value); output = IP.ToString(); } return new SqlString(output); } 

sys.dm_exec_connections utiliza varchar (48) después de SQL Server 2005 SP1. Suena lo suficientemente bueno para mí, especialmente si quieres usarlo en comparación con tu valor.

De manera realista, no verá IPv6 como la stream principal por un tiempo, por lo que preferiría la ruta 4 tinyint. Dicho esto, estoy usando varchar (48) porque tengo que usar sys.dm_exec_connections

De otra manera. La respuesta de Mark Redman menciona una pregunta anterior de debate SO.

Usualmente uso un viejo filtrado VARCHAR para una IPAddress que funciona bien.

Si quieres filtrar los rangos de la dirección IP, la dividiría en cuatro enteros.

Gracias RBarry. Estoy armando un sistema de asignación de bloques IP y el almacenamiento como binario es el único camino a seguir.

Estoy almacenando la representación CIDR (por ejemplo, 192.168.1.0/24) del bloque IP en un campo varchar, y usando 2 campos calculados para mantener la forma binaria del inicio y el final del bloque. A partir de ahí, puedo ejecutar consultas rápidas para ver si un bloque determinado ya se ha asignado o es libre de asignar.

Modifiqué tu función para calcular la dirección IP final así:

 CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4) AS BEGIN DECLARE @bin AS BINARY(4) DECLARE @ip AS VARCHAR(15) DECLARE @size AS INT SELECT @ip = Left(@block, Len(@block)-3) SELECT @size = Right(@block, 2) SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1)) + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1)) SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4)) RETURN @bin END; go 

Para las personas que usan .NET pueden usar la clase IPAddress para analizar la cadena IPv4 / IPv6 y almacenarla como un VARBINARY(16) . Puede usar la misma clase para convertir byte[] en cadena. Si quieres convertir el VARBINARY en SQL:

 --SELECT -- dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4, -- dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6 --ALTER CREATE FUNCTION dbo.varbinaryToIpString ( @varbinaryValue VARBINARY(16) ) RETURNS VARCHAR(39) AS BEGIN IF @varbinaryValue IS NULL RETURN NULL IF DATALENGTH(@varbinaryValue) = 4 BEGIN RETURN CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' + CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' + CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' + CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1))) END IF DATALENGTH(@varbinaryValue) = 16 BEGIN RETURN sys.fn_varbintohexsubstring(0, @varbinaryValue, 1, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 3, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 5, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 7, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 9, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' + sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2) END RETURN 'Invalid' END 

Me gustan las funciones de SandRock. Pero encontré un error en el código de dbo.fn_ConvertIpAddressToBinary . El parámetro entrante de @ipAddress VARCHAR (39) es demasiado pequeño cuando concat la @delim a él.

 SET @ipAddress = @ipAddress + @delim 

Puede boostlo a 40. O mejor aún, usar una nueva variable que sea más grande y usarla internamente. De esta forma no perderás el último par en números grandes.

 SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff') 

Estoy usando Varchar (15) hasta ahora todo está funcionando para mí. Insertar, actualizar, seleccionar. Acabo de iniciar una aplicación que tiene direcciones IP, aunque todavía no he hecho demasiados trabajos de desarrollo.

Aquí está la statement selecta:

Seleccione * De dbo.Servidor donde [IP] = (‘132.46.151.181’) Vaya