I can't say why exactly it would be so much more inefficient to delete a VARBINARY(MAX) compared to file stream but one idea you could consider if you're just trying to avoid time outs from your web application when deleting these LOBS. You could store the VARBINARY(MAX) values in a separate table (lets call it tblLOB) that is referenced by the original table (lets call this tblParent).
From here when you delete a record you can just delete it from the parent record and then have an occasional garbage collection process to go in and clean up the records in the LOB table. There may be additional hard drive activity during this garbage collection process but it will at least be separate from the front end web and can be performed during non-peak times.
To make things clear, we could take the first statement which is a very fundamental one:
A table can contain a maximum of 8,060 bytes per row.
In other words: each row must fit into one page (8 kB). And if possible, many rows can fit into one page - but never the other way round.
How about binary large objects (TEXT, NTEXT, BLOB)? Data in these columns is stored in a special place. The row just holds a pointer to the data, hence the fundamental statement above still holds: a table row must fit into one page.
Binary large data is stored outside of the table.
Each table row just holds a pointer in each BLOB field which actually points to the data.
Having this said, the new varchar(max) feature is special and behaves as follows:
- varchar(n): a usual text fields stored in row, row data must fit into one page.
- varchar(max), small data content: stored in row as long as all row data fits into one page.
- varchar(max), big data content: as soon as the data does not fit into one page anymore, a pointer is stored in row and varchar(max) data is stored separately. In this case, varchar(max) behaves similar to the text data type.
Note the big data content depends on all the other columns of the table. Furthermore, varchar(max) can store text in compressed form such that more than 8,060 characters might fit into one data page.
In my opinion, the statement The length of each one of these columns must still fall within the limit of 8,000 bytes seems not to be precise, possibly even incorrect (a varchar(max) column can in fact hold 2 GB of data, though not being stored in row).
Best Answer
It is not supported in Azure SQL Databases, but you have two other options. One is to simply use a VM with a SQL Server installed and the other one, and recommended, is to use the newly announced SQL Server Managed Instances.
https://azure.microsoft.com/en-us/blog/migrate-your-databases-to-a-fully-managed-service-with-azure-sql-database-managed-instance/