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:
- 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
Using Management Studio, generate a CREATE script of that table. The
TEXTIMAGE_ON [PRIMARY]will be added, since the table has a geography column
Drop the geography column
ALTER TABLE [dbo].[TestTable] DROP COLUMN [GeoLocation]
- 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
Generate the CREATE script again for that table. It will still be generated as
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.
TEXTIMAGE_ON [PRIMARY]from the creation script and run it again. TestTable2 will be created successfully
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?