TEXTIMAGE_ON and Computed geography column

Question

There is an unusual behavior I need help understanding. Using SQL Server 2019 Express (v15.0.2080.9) and Management Studio.

Here is the test I'm running:

1. Create a table with a geography column. This will create the table with Text filegroup PRIMARY (by default)

CREATE TABLE [dbo].[TestTable]
(
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [Latitude] [decimal](9, 6) NULL,
     [Longitude] [decimal](9, 6) NULL,
     [GeoLocation] [geography]
) ON [PRIMARY]
GO

2. Using Management Studio, generate a CREATE script of that table. The TEXTIMAGE_ON [PRIMARY] will be added, since the table has a geography column

3. Drop the geography column

ALTER TABLE [dbo].[TestTable] 
    DROP COLUMN [GeoLocation]

4. Add another column, but this time as a Computed column that uses lat/long to calculate a point. The column type will be inferred as geography

ALTER TABLE [dbo].[TestTable] 
    ADD [GeoLocation] AS (CASE 
                              WHEN [Latitude] IS NOT NULL AND [Longitude] IS NOT NULL 
                                  THEN [geography]::Point([Latitude], [Longitude], (4236))
                          END) PERSISTED
GO

5. Generate the CREATE script again for that table. It will still be generated as TEXTIMAGE_ON [PRIMARY]

6. Change the table name in the script for something else (like TestTable2) and try running the script. It will fail with the following message:

Msg 1709, Level 16, State 1, Line 1
Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), non-FILESTREAM varbinary(max), xml or large CLR type columns.

7. Remove the TEXTIMAGE_ON [PRIMARY] from the creation script and run it again. TestTable2 will be created successfully

8. Generate the CREATE script again for the TestTable2. This time, TEXTIMAGE_ON [PRIMARY] won't be generated

I don't understand why step 6 failed, since we do have a geography column (it's computed, but it's persisted). Shouldn't it be possible to specify the TEXTIMAGE_ON filegroup for a computed column with geography type inferred?

Answer 1

I just had a similar situation, you did the right thing and it is working.

For more information have a look here and here.