Varbinary o binary de sql server

Varbinary o binar de sql server es el tipo de datos blob utilizados para almacenar mas archivos y convertir estos en binario. En este articulo te explico la utilidad de cada uno y como puedes insertar imagenes , pdf, csv o cualquier otro tipo de archivo desde sql server.

Ademas de las clasulas T-sql podras aprender como hacer desde Powershell, C# y python con una clase que te puede ser muy util.

¿Qué son VARBINARY y BINARY?

En SQL Server, VARBINARY y BINARY son tipos de datos utilizados para almacenar datos binarios. Ambos tipos de datos se utilizan comúnmente para almacenar imágenes, documentos, videos y otros tipos de archivos binarios.

La principal diferencia entre los dos tipos de datos es la forma en que se almacenan los datos. BINARY se utiliza para almacenar datos binarios de longitud fija, mientras que VARBINARY se utiliza para almacenar datos binarios de longitud variable.

los tipos de datos binarios de longitud fija

BINARY es un tipo de datos que se utiliza para almacenar datos binarios de longitud fija. Esto significa que cada valor de BINARY que se almacena en una tabla de SQL Server ocupa el mismo número de bytes, independientemente de la cantidad de datos que contenga.

Por ejemplo, si creamos una columna de tabla de SQL Server con el tipo de datos BINARY(10), cada valor de esa columna ocupará exactamente 10 bytes, incluso si la cantidad de datos binarios que contiene es menor a 10 bytes.

VARBINARY: datos binarios de longitud variable

VARBINARY, por otro lado, se utiliza para almacenar datos binarios de longitud variable. Esto significa que cada valor de VARBINARY que se almacena en una tabla de SQL Server puede ocupar un número variable de bytes, dependiendo de la cantidad de datos binarios que contenga.

Por ejemplo, si creamos una columna de tabla de SQL Server con el tipo de datos VARBINARY(10), cada valor de esa columna puede ocupar entre 0 y 10 bytes, dependiendo de la cantidad de datos binarios que contenga.

Varbinary o binary de sql server, cual debo usar ?

Los tipos de datos Binary y Varbinary en SQL Server almancenan archivos en forma de bytes su principal diferencia es la cacidad de almancenamiento:

• «binary» es un tipo de datos binario fijo que puede almacenar hasta N bytes, donde N es una longitud especificada al momento de crear la columna. Ejemplo: «binary(50)» significa que la columna puede almacenar hasta 50 bytes.
• «varbinary» es un tipo de datos binario variable que puede almacenar hasta una cantidad máxima de bytes especificados al momento de crear la columna. Ejemplo: «varbinary(max)» significa que la columna puede almacenar hasta un máximo de 2^31-1 bytes.

Usar «varbinary» es lo mas recomendable en lugar de «binary» si la longitud del contenido binario puede variar algo muy común, Este permite almacenar una cantidad más grande de datos sin necesidad de ajustar manualmente la longitud de la columna.

importar o exportar un archivo columna varbinary o binary?

Existen varios metodos para cargar informacion en tablas que poseen este tipo de columnas , desde las realizadas comun mentes por los lenguajes de programacion, hasta las que Sql server trae disponible desde T-sql.

Existen 2 formas muy comunes de importar o exportar informacion de estas columnas:

–Comandos T-sql : los mismo utilizan clausulas para insercion masiva o para lectura muy propios del sistema de gestion que permiten crear tareas automatizadas sin necesidad de conocer un lenguaje de programación.

–Lenguaje de programación: los lenguaje de programacion como C# ,vb.net o python posee clases y metodos que te permiten conectarte a un servidor mediante una cadena de conexion de sql server, convertir un archivo en formato binario e insertarlos en una columna

Cada forma tiene sus pro y contras por lo cual es importante entenderlas antes de seleccionar, tambien puede variar el alcance del trabajo a realizar.

T sql Openrowset para insertar en columna varbinary

Esta clausula la utilizamos para importarcion masiva de registros, pero en esta ocasion la vamos a utilizar para insertar un set de archivos o imagenes jpg o png a tu tabla de sql server. este metodo no requiere ninun conocimiento de programacion.

Sintaxis importar archivo desde sql

Varbinary a csv en sql server con xp_cmdshell

Puedes escribir los resultados de una consulta en un archivo en el sistema de archivos utilizando la instrucción xp_cmdshell de T-SQL. Aquí hay un ejemplo de código:

DECLARE @sqlCommand AS NVARCHAR(MAX);
DECLARE @filePath AS NVARCHAR(MAX);

SET @filePath = 'C:\temp\query_result.csv';

SET @sqlCommand = 'bcp "SELECT col1, col2, col3 FROM mi_tabla" queryout ' + @filePath + ' -c -T -S ' + @@SERVERNAME;

EXEC xp_cmdshell @sqlCommand;

Este código ejecuta la consulta SELECT col1, col2, col3 FROM mi_tabla y guarda los resultados en un archivo CSV en C:\temp\query_result.csv.

Tenga en cuenta que xp_cmdshell debe estar habilitado en la configuración de seguridad de SQL Server antes de utilizarlo. Además, se deben tener los permisos necesarios para escribir en la carpeta especificada.

Convertir BINARY/BLOBS a imagen jpg o png

El proceso descrito anteriormente se puede aplicar a una columna que almacena archivos JPG o cualquier otro tipo de archivo binario. Simplemente debes seleccionar los datos de la columna binaria y escribirlos en un archivo en el sistema de archivos utilizando xp_cmdshell.

El proceso para escribir un archivo JPG almacenado en una columna binaria en un sistema de archivos es similar al proceso descrito anteriormente:

DECLARE @sqlCommand AS NVARCHAR(MAX);
DECLARE @filePath AS NVARCHAR(MAX);
DECLARE @binaryData AS VARBINARY(MAX);

SET @filePath = 'C:\temp\image.jpg';

SELECT @binaryData = binary_column
FROM binary_table
WHERE id = 1;

SET @sqlCommand = 'echo ' + CAST(@binaryData AS NVARCHAR(MAX)) + ' > ' + @filePath;

EXEC xp_cmdshell @sqlCommand;

Este código selecciona los datos de la columna binaria de una fila específica de la tabla binary_table y los escribe en un archivo JPG en C:\temp\image.jpg.

Tenga en cuenta que los archivos JPG pueden tener un tamaño significativo, por lo que es posible que debas ajustar la configuración de la memoria y otros recursos de SQL Server antes de ejecutar la consulta. Además, debes tener cuidado al manejar archivos grandes, ya que esto puede afectar el rendimiento de la base de datos y del sistema de archivos.

Descargar Blob/Varbinary a un directorio

Los procesos anteriores funcionan siempre que la informacion a extraer se un solo registro pero que pasa si para nuestro caso requerimos extraer todos los registros de una tabla que contiene una columna binary a un directorio.

Puedes utilizar un bucle WHILE en T-SQL para escribir varios archivos JPG almacenados en una columna binaria en un sistema de archivos. Aquí hay un ejemplo:

DECLARE @sqlCommand AS NVARCHAR(MAX);
DECLARE @filePath AS NVARCHAR(MAX);
DECLARE @binaryData AS VARBINARY(MAX);
DECLARE @id AS INT;

SET @id = 1;

WHILE (1 = 1)
BEGIN
  SELECT @binaryData = binary_column
  FROM binary_table
  WHERE id = @id;

  IF @@ROWCOUNT = 0
  BEGIN
    BREAK;
  END;

  SET @filePath = 'C:\temp\image_' + CAST(@id AS NVARCHAR(MAX)) + '.jpg';

  SET @sqlCommand = 'echo ' + CAST(@binaryData AS NVARCHAR(MAX)) + ' > ' + @filePath;

  EXEC xp_cmdshell @sqlCommand;

  SET @id = @id + 1;
END;

Este código escribirá todos los archivos JPG de la tabla binary_table en la carpeta C:\temp. Cada archivo se escribirá con un nombre diferente, basado en su identificador único (id) en la tabla.

Tenga en cuenta que xp_cmdshell debe estar habilitado en la configuración de seguridad de SQL Server antes de utilizarlo. Además, se deben tener los permisos necesarios para escribir en la carpeta especificada.

Insertar desde powershell

Esto a diferencia de importar un archivo en csv a sql muestra como insertar en una columna un archivo que para este ejemplo es jpeg pero puede igualmente ser un archivo pdf o png. te recomiendo que Para insertar un archivo en una columna de tipo binary en SQL Server desde PowerShell, puedes seguir los siguientes pasos:

Crear una conexión a la base de datos de SQL Server:

$connectionString = "Data Source=localhost;Initial Catalog=databaseName;Integrated Security=True"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

Leer el archivo a insertar y almacenarlo en una variable de tipo Byte[]:

$filePath = "path\to\file.jpg"
$fileData = [System.IO.File]::ReadAllBytes($filePath)

Crear un comando de SQL para insertar el archivo en la base de datos:

$query = "INSERT INTO tableName (binaryColumn) VALUES (@binaryData)"
$command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
$command.Parameters.AddWithValue("@binaryData", [System.Data.SqlDbType]::Binary, $fileData)

Ejecutar el comando de SQL para insertar el archivo en la base de datos:

$command.ExecuteNonQuery()
$connection.Close()

Cerrar la conexión a la base de datos de SQL Server

Como hacer un upload desde C# a sql server?

Si pudiste ver todos los ejemplos anteriores sabras que podemos combinar los comandos de insert masivos que vimos con un codigo de C# para insertar el archivo indicado. Este ejemplo de código que muestra el OPENROWSET para insertar un archivo subido en una tabla de SQL Server desde la clase anterior:

public class FileUploadToSQL
{
    private readonly string _connectionString;
    private readonly long _fileSizeLimit;

    public FileUploadToSQL(string connectionString, long fileSizeLimit)
    {
        _connectionString = connectionString;
        _fileSizeLimit = fileSizeLimit;
    }

    public async Task<string> UploadFile(IFormFile file, string directory, string tableName, string columnName)
    {
        var fileUpload = new FileUpload(_fileSizeLimit);
        var filePath = await fileUpload.UploadFile(file, directory);

        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();

            var sql = $"INSERT INTO {tableName} ({columnName}) SELECT * FROM OPENROWSET(BULK N'{filePath}', SINGLE_BLOB)";
            using (var command = new SqlCommand(sql, connection))
            {
                await command.ExecuteNonQueryAsync();
            }
        }

        return filePath;
    }
}

En este ejemplo, la clase FileUploadToSQL utiliza la clase FileUpload para gestionar el upload del archivo. Si el tamaño del archivo es apropiado, se guarda en el directorio especificado y luego se inserta en la tabla de SQL Server mediante el uso de OPENROWSET.

La clase tiene una propiedad _connectionString para almacenar la cadena de conexión a la base de datos y una propiedad _fileSizeLimit para especificar el límite de tamaño de archivo permitido.

Pasos par ejecutar el upload desde tu aplicación

Aquí hay un ejemplo de código que muestra cómo crear una instancia de la clase FileUploadToSQL leyendo la cadena de conexión y el límite de tamaño de archivo desde el archivo appsettings.json en una aplicación .NET Core:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddControllersWithViews();

        var connectionString =   Configuration.GetConnectionString("DefaultConnection");
        var fileSizeLimit = Configuration.GetValue<long>("FileSizeLimit");
        services.AddSingleton(new FileUploadToSQL(connectionString, fileSizeLimit));
    }
    ...
}

En este ejemplo, la clase Startup tiene un constructor que recibe una instancia de IConfiguration y la almacena en la propiedad Configuration. Luego, en el método ConfigureServices, se obtiene la cadena de conexión y el límite de tamaño de archivo desde el archivo appsettings.json y se agrega una instancia de la clase FileUploadToSQL como un servicio singleton.

El archivo appsettings.json debería tener una sección como esta:

jsonCopy code{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=MyDB;Trusted_Connection=True;"
  },
  "FileSizeLimit": 5242880
}

En este ejemplo, se especifica la cadena de conexión a la base de datos DefaultConnection y se establece el límite de tamaño de archivo en 5 MB (5242880 bytes).

Preguntas frecuentes

Aquí te presento algunas preguntas frecuentes sobre VARBINARY y BINARY en SQL Server:

¿Qué tipos de datos binarios puedo almacenar en VARBINARY y BINARY en SQL Server? Ambos tipos de datos se pueden utilizar para almacenar cualquier tipo de datos binarios, como imágenes, videos, documentos, etc.

¿Qué pasa si intento almacenar datos binarios con una longitud diferente a la especificada en la definición de columna?

Si intentas almacenar datos binarios con una longitud diferente a la especificada en la definición de columna, SQL Server truncará o rellenará los datos con ceros para que se ajusten a la longitud especificada.

¿Puedo cambiar el tipo de datos de una columna existente de VARBINARY a BINARY o viceversa?

Sí, es posible cambiar el tipo de datos de una columna existente de VARBINARY a BINARY o viceversa. Sin embargo, debes tener cuidado de asegurarte de que los datos existentes se ajusten al nuevo tipo de datos antes de realizar la conversión.

¿Cuál es el tamaño máximo de datos que puedo almacenar en VARBINARY y BINARY en SQL Server?

El tamaño máximo de datos que se puede almacenar en VARBINARY y BINARY depende de la versión de SQL Server que estés utilizando y de la configuración de tu servidor. En SQL Server 2019, por ejemplo, el tamaño máximo de datos que se puede almacenar en VARBINARY y BINARY es de 2 GB.

¿Puedo indexar columnas de tipo VARBINARY o BINARY?

Sí, es posible indexar columnas de tipo VARBINARY o BINARY en SQL Server. Sin embargo, debes tener en cuenta que las consultas que involucran datos binarios pueden ser más lentas que las consultas que involucran datos de tipo de datos numéricos o de cadena.

En conclusion

Usar «varbinary» es lo mas recomendable en lugar de «binary» si la longitud del contenido binario puede variar, para convertir una archivo jpg , png o pdf desde una columna binary o blob es posible sin tener altos conocimientos de programacion. lo si debemos tomar en cuenta es el uso de la memoria que puede afectar el rendimiento de tu base de datos y tu servidor.

La diferencia entre ellos es que BINARY es de longitud fija y VARBINARY es de longitud variable. Cuando se define una columna BINARY, se especifica una longitud fija que no puede cambiar. Por otro lado, una columna VARBINARY permite almacenar una longitud variable de datos binarios, lo que significa que puede cambiar en función de la cantidad de datos almacenados en la columna.

Cuentano si esto fue de ayuda para ti!

Hasta la proxima gracias :D!!