There is an unusual behavior I need help understanding. Using SQL Server 15.0.2080.9 (2019 Express) 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]
GO
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?