Moving LOB to different File Group

CREATE DATABASE SOURCE

alter database SOURCE ADD FILEGROUP FG1
ALTER DATABASE SOURCE ADD FILEGROUP FG2
GO


ALTER DATABASE SOURCE ADD FILE ( NAME = N'FG1', FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Data\FG1.NDF' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]
GO


ALTER DATABASE SOURCE ADD FILE ( NAME = N'FG2', FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Data\FG2.NDF' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG2]
GO


CREATE TABLE T_LOB (ID INT , NAME NVARCHAR(50), PICTURE NVARCHAR(MAX)) ON PRIMARY , TEXTIMAGE ON FG1

INSERT INTO T_LOB (Id, Name, PICTURE)
SELECT 10, 'Zee', BulkColumn
FROM Openrowset( Bulk 'L:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Data\photo.JPG', Single_Blob) as picture



INSERT INTO T_LOB
SELECT * from T_LOB
go 10 /* Do it 10 times*/

CREATE CLUSTERED INDEX PK_ID_T_LOB ON T_LOB (ID)

/*Moving the table cluster to different File Group*/
CREATE CLUSTERED INDEX PK_ID_T_LOB ON SOURCE.dbo.T_LOB(ID)
WITH(DROP_EXISTING=ON,Online=ON) on [FG1]
GO


/*Now when we check the storage of the table we find that the textimage is still stored in primary because of below reason*/
TEXTIMAGE_ON is not allowed if there are no large value columns in the table. TEXTIMAGE_ON cannot be specified if <partition_scheme> is specified.
If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup.
The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.


WORKAROUND:

  Only way to move the LOB data is to
1. create new table in new file group
2. Move the data from existing table to new table.
3. Drop the existing table.
4. Change the name of new table to Old table.