August 12, 2026 at 1:24 pm
I have a table that ends in ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]. I'm about to partition this table and I'm wondering if I can stick the blobs onto a separate filegroup / file while partitioning the rest of the data. Or do I have to put the blogs on the individual partitions?
Here's the sanitized version of my table before partitioning:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[COLUMN1] [bigint] NOT NULL,
[COLUMN2] [tinyint] NULL,
[COLUMN3] [varchar](255) NULL,
[COLUMN4] [varchar](MAX) NULL,
[COLUMN5] [varchar](100) NULL,
[COLUMN6] [varchar](30) NULL,
[COLUMN7] [varchar](255) NULL,
[COLUMN8] [varchar](10) NULL,
[COLUMN9] [varchar](255) NULL,
[COLUMN10] [datetime] NULL,
[COLUMN11] [datetime] NOT NULL,
[COLUMN12] [datetime] NULL,
[COLUMN13] [varchar](50) NULL,
[COLUMN14] [tinyint] NOT NULL DEFAULT (0),
[COLUMN15] [datetime] NULL,
[COLUMN16] [varchar](900) NULL,
[COLUMN17] [varchar](36) NULL,
[COLUMN18] [varchar](10) NULL,
[COLUMN19] [varchar](16) NULL,
[COLUMN20] [varchar](16) NULL,
[COLUMN21] [varchar](16) NULL,
[COLUMN22] [varchar](10) NULL,
[COLUMN23] [tinyint] NOT NULL DEFAULT (0),
[COLUMN24] [bigint] NULL,
[COLUMN25] [datetime] NULL,
[COLUMN26] [varchar](5) NULL,
[COLUMN27] [tinyint] NOT NULL DEFAULT (0),
[COLUMN28] [tinyint] NOT NULL DEFAULT (0),
[COLUMN29] [varchar](40) NULL,
[COLUMN30] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
That last line before GO, would I be able to say something like:
ON [PartitionScheme] (PartitionColumn) TEXTIMAGE_ON MyBlobFileGroup
Thoughts would be appreciated.
August 12, 2026 at 2:11 pm
Apparently not for the old text/image/lobs.
With the ...(max) datatypes, you can specify it directly with your create statement !
Create the table without clustered key, nor partitioning information and point the textimmage_on to the filegroup of your choice.
After that , create a clustered index including the partitioning clause.
the clustered key columns cannot have ...(max) columns.
Worth the test !
ref: My perplexity search
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
August 12, 2026 at 2:46 pm
No, I don't think you would. I don't think TEXTIMAGE_ON clause is allowed is a partition scheme is used (based on MS documentation). Sorry, I don't have time to test this right now.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 12, 2026 at 4:39 pm
Yeah, I tried creating the table with the TEXTIMAGE_ON a separate file group. It failed spectacularly (ERROR: invalid partition scheme specified). And I don't trust the AI response that says to create the table first, then partition it.
August 12, 2026 at 4:56 pm
In fact, it wouldn't let me use TEXTIMAGE_ON at all. Not even on the partition scheme.
August 15, 2026 at 12:42 pm
Kimberly Tripp has a trick (I don't have the link handy... you can search for it) to move LOBs to a different file group but then you have to move the other data back and... it's done by doing a temporary partition with one partition. I don't see a reason why it couldn't work for multiple partitions.
Personally, though, I've simply moved the LOB data to a new database. It was data that could survive and ever increasing index from the original data in the original database... and.. the LOB data was what I partitioned into monthly tables... and it still exists... since march of 2010. The LOBs started out as type 4 Wav files but then the phone vendor screwed us and moved to OPUS files, which are 2 too 3 times the size.
And, yes... not my decision but they are telephone recordings. It turns out that it's a good thing that they're in the database because a about 10% of the call recordings on a separate set of disks were flat out missing and another 10% were corrupt and could not be used when I took this monster on.
My point, though, is, it can all be done but you're going to temporarily need a shedload of parallel disk space until you have it all transferred and can delete the original.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply