Microsoft Fabric Blog details how new support for VARCHAR(MAX) and VARBINARY(MAX) in Fabric Data Warehouse and SQL analytics endpoints enables developers and data teams to handle large text and binary data efficiently.

Large Object Data Support in Microsoft Fabric Data Warehouse and SQL Analytics Endpoint

Microsoft Fabric has introduced support for large string and binary data using VARCHAR(MAX) and VARBINARY(MAX) types across Fabric Data Warehouse and SQL analytics endpoints for mirrored items. This removes previous limits on storing, processing, and analyzing large objects such as descriptive text, logs, JSON, and spatial data.

Key Highlights

  • New Data Types: Columns can now be defined as VARCHAR(MAX) (for large strings) and VARBINARY(MAX) (for binary data), supporting objects up to 16 MB per cell (Parquet).
  • Data Ingestion: Large columns can be loaded using COPY INTO or OPENROWSET(), with practical per-cell and per-row limits that differ for Parquet and CSV file formats:
    • Parquet: Up to 16 MB per cell, 1 GB per row
    • CSV: Up to 1 MB per cell, 16 MB per row
  • Query Support: Native string operations, including LIKE and JSON queries, work with the new large data columns exactly as with standard column types.
  • Performance Note: Performance depends on the actual data processed, not the declared column type.

SQL Analytics Endpoint and Mirrored Items

  • No More Truncation: SQL analytics endpoints for mirrored Azure SQL Database and Cosmos DB now read large values without the previous 8 KB truncation limit—key for avoiding JSON corruption, especially when mirroring Cosmos DB.
  • Column Mapping Logic:
    • Large string columns (VARCHAR(N) where N ≥ 2000) now map to VARCHAR(MAX)
    • Smaller string columns (N < 2000) map to VARCHAR(4*N)
  • Size Limits by Source Type:
    • SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL Database in Fabric: Up to 1 MB for string or binary values
    • Azure Cosmos DB and Cosmos DB in Fabric: Up to 2 MB for values
  • Automatic Adoption: New tables use these types by default; existing tables upgrade automatically during schema changes, or can be migrated manually.

See autogenerated data type mapping documentation and limits per mirrored item type for further guidance.

Practical Considerations

  • Use the new types to safely store logs, descriptive text, JSON, and spatial formats without risk of truncation or corruption.
  • Check your ingestion format and mirrored source to understand actual limits on per-cell and per-row value size.
  • Performance is primarily tied to processed data amounts, not column types.

References

Conclusion

With VARCHAR(MAX) and VARBINARY(MAX) now available in Fabric Data Warehouse and SQL analytics endpoints, Microsoft Fabric users can ingest, store, and query large text and binary data efficiently across all major supported sources.

This post appeared first on “Microsoft Fabric Blog”. Read the entire article here