Skip to main content
Share Your Experience: Take the 2024 Developer Survey
52 votes

Should binary files be stored in the database?

Do not store files in a database. Everyone, without exception, that can run any RDBMS on the market already has a database specifically for storing files, and the RDBMS itself is using it! That ...
Evan Carroll's user avatar
  • 63.6k
27 votes
Accepted

Logical reads different when accessing the same LOB data

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case: ...
Paul White's user avatar
  • 85.8k
18 votes

Why is it recommended to store BLOBs in separate SQL Server tables?

While I disagree that BLOBs should just be in another table -- they should not be in the database at all. Store a pointer to where the file lives on disk, and then just get that from the database... ...
Erik Darling's user avatar
  • 41.1k
17 votes
Accepted

SQL Server LOB variables and memory usage

The 2GB LOB limit only applies to persistent storage (except FILESTREAM); variables have no limit. SQL Server starts with an pure in-memory implementation. It changes to a tempdb-based backup scheme ...
Paul White's user avatar
  • 85.8k
16 votes
Accepted

How to copy blob (binary data) from one DB to DB using a script?

I would use the "Generate Scripts" option in SQL Server Management Studio. In Object Explorer - highlight the database hosting the source table. Right-click menu - Tasks | Generate Scripts Select ...
Terry C's user avatar
  • 391
13 votes
Accepted

Why is it recommended to store BLOBs in separate SQL Server tables?

How large are these images, and how many do you expect to have? While I mostly agree with @sp_BlitzErik, I think there are some scenarios where it is ok to do this, and so it would help to have a ...
Solomon Rutzky's user avatar
13 votes
Accepted

Best way to shrink a DB after nulling data from varbinary(max)?

It looks to me like just updating the columns to NULL will release pages for reuse. Here's a Very Scottish® demo, to celebrate it being almost 5PM, EST. USE tempdb; DROP TABLE IF EXISTS dbo....
Erik Darling's user avatar
  • 41.1k
11 votes
Accepted

LOB_DATA, slow table scans, and some I/O questions

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It ...
Solomon Rutzky's user avatar
11 votes

How to export an image column to files in SQL Server?

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp ...
Jacob Dababneh's user avatar
10 votes

LOB_DATA, slow table scans, and some I/O questions

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not ...
Mikael Eriksson's user avatar
9 votes
Accepted

Slow DELETEs of LOB data in SQL Server

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, ...
Paul White's user avatar
  • 85.8k
9 votes

In what cases are BLOB and TEXT stored in-line on InnoDB?

Keep in mind that there are 4 "row formats". A main difference between then has to do with how wide columns are handled. The reference points to an Answer written early in 2010, a few ...
Rick James's user avatar
  • 78.4k
8 votes

Why is it recommended to store BLOBs in separate SQL Server tables?

If the data must be stored in SQL Server for whatever reason I can think of a few benefits to storing it in a separate table. Some are more convincing than others. Putting the data in a separate ...
Joe Obbish's user avatar
  • 32.2k
8 votes

How to retrieve a large text from inside a table?

There's another way similar to Aaron's, using the little-documented processing-instruction... thing. Using this gives you unchecked XML, which will won't replace predefined entities. Also, the column ...
Erik Darling's user avatar
  • 41.1k
7 votes
Accepted

How to show Large Objects (LOB) contents?

I ran SELECT expiration_policy, lo_get(authentication), services_granted_access_to FROM ticketgrantingticket LIMIT 2; after reading https://www.postgresql.org/docs/current/static/lo-funcs.html ...
48347's user avatar
  • 86
7 votes
Accepted

In what cases are BLOB and TEXT stored in-line on InnoDB?

For InnoDB the rule goes like this: A record can not be fragmented. At least two records must fit in a page. If a record size is less than ~7k, all field values are stored in-page. If the record size ...
akuzminsky's user avatar
  • 4,967
6 votes

How to retrieve a large text from inside a table?

You can't copy from text output, because it truncates the data. You can't copy from grid output, not only because it truncates the data, but it also mangles carriage returns, line feeds, and other ...
Aaron Bertrand's user avatar
5 votes
Accepted

How to `vacuumlo` an RDS PostgreSQL database?

Find here (in the GitHub mirror of the official PostgreSQL repo) sources of vacuumlo for the PostgreSQL data base version I was using at that very moment. You can imagine the rest: I just mimic what ...
48347's user avatar
  • 86
5 votes

How to show Large Objects (LOB) contents?

Because this question is nearing closure, I'll paste the links provided by Andriy M and a few more. Chapter 33. Large Objects, and Server-side Functions, make note that the functions aren't all in ...
Evan Carroll's user avatar
  • 63.6k
5 votes
Accepted

LOB in frequently queried table or separate table

is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is? Leave it. That's tiny, and not worth adding complexity....
David Browne - Microsoft's user avatar
5 votes
Accepted

Oracle SecureFiles LOBs Licencing

Securefile LOBs are free to use in any edition. Only the extra features of Securefile LOBs require buying the Advanced Compression or Advanced Security option, and these options are available only in ...
Balazs Papp's user avatar
  • 40.7k
5 votes

Reducing Table Size after Clearing TEXT Column Precautions

The downsides of your options seem to be: Copy and Renaming tables can mess up stored procedures and views, requiring recompilation. It also requires double the disk space. Exporting using bcp and ...
Charlieface's user avatar
  • 13.2k
4 votes

How can I store a pdf in PostgreSQL

Don't store PDFs in the database There are no database features that can edit or process a PDF. Storing them is likely to lock up your db-session for extended periods. There is absolutely no reason ...
Evan Carroll's user avatar
  • 63.6k
4 votes
Accepted

What is the 131072 in the call to lo_open()

This variable is called the "mode parameter" and it's the int representation of INV_READ (x'20000') -- lo_open(lobjId oid, mode integer) returns integer -- The mode parameter to lo_open uses two ...
Evan Carroll's user avatar
  • 63.6k
4 votes
Accepted

How to retrieve a large text from inside a table?

You should be able to accomplish this with the Stored Procedure I provided in the following answer (here on DBA.SE): How to output more than 4000 characters in sqlcmd Since you are using NVARCHAR(...
Solomon Rutzky's user avatar
4 votes
Accepted

Please enlighten me about FILESTREAM and BLOB files

Based on the whitepaper written by Paul Randal - BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database. BLOBs larger than 1 megabyte (MB) are best stored ...
Kin Shah's user avatar
  • 62.3k
3 votes

Query on Shrinking Table - Oracle

I assume you have a Basicfile LOB, because shrink is not supported with Securefile LOBs. The segment is not being moved, so the default tablespace and the tablespace of the segment is not relevant now....
Balazs Papp's user avatar
  • 40.7k
3 votes

How to insert big blob data in Oracle?

When you use string literals, Oracle performs an implicit conversion. So actually you just set the value of varchar which is converted to BLOB upon insertion. That results in unnecessary restrictions (...
Paul's user avatar
  • 178
3 votes

How to insert big blob data in Oracle?

If you are doing this from an application (VB.Net or C++ or php or whatever), then you must load the data in chunks, if the data is larger than 4000 bytes (thanks, mustaccio) OR if the data contains ...
CaM's user avatar
  • 2,183
3 votes
Accepted

oid vs bytea in postgres

oid as a type is a 32-bit unsigned integer that stands for object id, and is used by the system as an all-purpose surrogate key to refer to different objects and sometimes rows in pg_catalog. Being ...
Daniel Vérité's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible