I have a column on an Oracle db containing HTML data with embedded images that result in some records being over 2GB in size for that column. I have successfully migrated the rest of the database to SQL Server except for this CLOB column. With SQL Server varchar(max) unable to handle anything over 2GB in size, what are my options?
Normally I would recommend using the XML data type to store HTML but it too has the 2GB limit. This then leaves you with two options: FILESTREAM and FILETABLES. Be aware though, that both of these options require some additional configuration within Sql Server and your database before you are able to use them.
File tables are built on-top of
FILESTREAM and offer some benefits if you need to access the files using standard Windows API calls. File Tables are generally transparent to the consumer and easier to integrate into your application but lack transactional access. This means that the files can be removed from outside of the Sql Server context which may be undesirable.
FILESTREAM however must be done under a transaction context and therefore prevents anyone (with exception of administrators/people granted explicit permission to the
FILESTREAM folder) from removing them outside of Sql Server. They do not have the 2GB limit which will allow you to get around the problem that you are currently facing; however, accessing the data can be a little more complicated. Luckily, the documentation does a pretty decent job of explaining how to perform CRUD operations with
If I had to pick one, based on what you have provided above, I’d go with
FILESTREAM as this will offer you better consistency and control over your data.