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...
The primary issue they cause (for me) is with indexing. Using XML with query plans, because everyone's got'em, let's make a table:
SELECT TOP 1000
ID = IDENTITY(INT,1,1),
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)
It's only 1000 rows, but checking on the size...
sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'
It's over 40 MB for just 1000 rows. Assuming you add 40 MB every 1000 rows, that can get pretty ugly pretty quickly. What happens when you hit 1 million rows? That's just about 1 TB of data, there.
Any queries that need to use your clustered index now need to read all of that BLOB data into memory clarification: when the BLOB data column is referenced.
Can you think of better ways to use SQL Server memory than storing BLOBs? Because I sure can.
Expanding it to nonclustered indexes:
CREATE INDEX ix_noblob ON dbo.index_test (ID)
CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)
You can design your nonclustered indexes to largely avoid the BLOB column so regular queries can avoid the clustered index, but as soon as you need that BLOB column, you need the clustered index.
If you add it as an
INCLUDED column to a nonclustered index to avoid a key lookup scenario, you end up with gigantic nonclustered indexes:
More problems they cause:
- If anyone runs a
SELECT * query, they get all that BLOB data.
- They take up space in backups and restores, slowing them down
- They slow down
DBCC CHECKDB, because I know you're checking for corruption, right?
- And if you do any index maintenance, they slow that down as well.
Hope this helps!