TEXTIMAGE_ON and Computed geography column

Question

TEXTIMAGE_ON and Computed geography column

Antonio Matos 21

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?

Accepted answer

4 additional answers

Your answer

Answer 1

Important to mention this happens only in case a table ever had a LOB column before (and was removed.

I see.

In sys.tables, there is the column lob_data_space_id, and this columns is populated when you create a LOB column. And it seems that when you drop the LOB column, the value never changes back 0. So when you script the table, SMO sees 1) a LOB column 2) a non-zero lob_data_space_id.

I can see how resolving this issue can lead to a lot of finger-pointing. :-)

And I will have to admit the fairly specific scenario and the existence of a workaround, that this will never make it to the triage bar. But by all means, file a bug! I've been wrong before.

Answer 2

It seems like there is a limitation here. I tried this:

CREATE TABLE margit  (id int NOT NULL,
 Säffle nvarchar(20) NOT NULL,
 Grums AS replicate(cast(Säffle as nvarchar(MAX)), 8000) PERSISTED
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and I got the same error.

Now, I wonder what happens if there is a regular LOB column and you specify TEXTIMAGE_ON on another filegroup? Will SQL Server place the persisted computed column on that filegroup? Or will be the same filegroup as the regular columns?

By the way, I note that you have an outdated version of SQL 2019. You should download and install Cumulative Update 2019 to be up to date. It's not going to change this particular issue, though. I tested on CU11.

Answer 3

MelissaMa-MSFT 24,221

Hi @Antonio Matos ,

Welcome to Microsoft Q&A!

I also tried with below and got the same error.

CREATE TABLE [dbo].[TestTable](  
	[ID] [int] IDENTITY(1,1) NOT NULL,  
	[Latitude] [decimal](9, 6) NULL,  
	[Longitude] [decimal](9, 6) NULL,  
	[GeoLocation] AS (CONVERT([geography],case when [Latitude] IS NOT NULL AND [Longitude] IS NOT NULL then [geography]::Point([Latitude],[Longitude],(4236))  end)) PERSISTED  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO

It seems that the TEXTIMAGE_ON will be added only when the data type of the column(except computed column) is defined as text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns.

I also did a lot of searches about this and unfortunately I was not able to find any related official documents mentioning this.

It is still recommended to define the column as geography instead of a computed column.

If you still have some concern about this, I recommend you to submit your issue to the Microsoft feedback . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Antonio Matos 21 Reputation points

2021-07-30T16:50:23.593+00:00

Hi Melissa,

Thanks for your answer and for the worm welcome!

I also thought about using CONVERT like you did, to "force" a specific type for that computed column, but as you mentioned, it still errored out. I also checked SMO library and the logic that checks for those fields looks for plain "geography" type. Since the computed column is inferred as geography, that's why the SMO generates the TEXTIMAGE_ON. It seems a little bit contradictory, since the SQL query engine can't run it, that's why I thought something might be wrong.

You mentioned creating a geography column instead, but how can I achieve the same result I have with the computed column? Meaning, checking for Lat/Long being null, creating the geography point, how would I do that with a plain geography column? I tried to "force" the computed column type but I found out it is not possible and type is always inferred from the logic.

Appreciate your time and help!

Antonio Matos
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-07-30T16:54:50.067+00:00

You mentioned creating a geography column instead, but how can I achieve the same result I have with the computed column?

You could do that with a trigger. But it makes little sense, as long as you don't actually want a specific location for your computed column.

While there is a disconnect somewhere, I don't see that this is a major problem. Or is there something I'm missing?
Antonio Matos 21 Reputation points

2021-07-30T17:15:20.737+00:00

Hi Erland,

There is no real customization in terms of filegroups, everything is kept to default (PRIMARY), so the trigger would be indeed an option.

Like you mentioned, there is a disconnect somewhere, and that's what I'm curious about. Since the computed column is persisted, we should be able to choose which filegroup to store the data for that column, right? But in this case, we can't since creation query won't run.

This is a major issue for us, since we have a customized replication tool written in C#, which uses SMO libraries to generate the schema scripts and migrate data. Those columns cause the schema scripts to fail when creating the new database. We implemented a workaround to drop those columns before running the migration and recreate them in the destination database. There is no relevant side effect on doing that, since we are dealing with a computed column and there is no harm on recreating it.

But the discussion is still valid in terms of why this is happening, as I really like to go deep and find out the root cause of those problems and understand what happens "under the hood".

Thanks for your comment.

Antonio Matos

Answer 4

Erland Sommarskog 121.9K MVP Volunteer Moderator

As for what is happening, i there is a simple three-letter answer: b-u-g. Or well, maybe slightly few more characters: l-i-m-i-t-a-t-i-o-n.

Now, whether this limitation/bug is in SQL Server which does not permit this, or whether it is in SMO that does not understand what the engine is doing, is something I leave to Microsoft to sort out.

It seems that you have a workaround. If you want to let Microsoft know of this problem, you can report it here: https://feedback.azure.com/forums/908035-sql-server. They will triage with other bugs they get, and may or may not fix it.

If this would be a blocking issue for you, the right path would be to open a support case and press for a fix.

Antonio Matos 21 Reputation points

2021-07-30T17:28:31.03+00:00

Yes, I'll report a feedback you and @MelissaMa-MSFT suggested. At least there is a workaround, so nothing critical for us.

Answer 5

Antonio Matos 21

Important to mention this happens only in case a table ever had a LOB column before (and was removed), and then a computed column is added later (the exact step by step I mentioned to reproduce the issue).

If we create the table from scratch using the computed column only, the SMO will not generate the TEXTIMAGE_ON for the creation script. For products that are in the market for quite some time, it's a fairly common scenario, since tables keep on changing.

MelissaMa-MSFT 24,221 Reputation points

2021-08-02T01:28:44.967+00:00

Hi @Antonio Matos ,

You could refer what Erland said.

In addition, report a feedback could be a good workround.

Maybe you could consider to ask professional engineers for help, and they will deal with your problem separately and confidentially:
https://support.microsoft.com/en-us/assistedsupportproducts

Best regards,
Melissa

Share via

TEXTIMAGE_ON and Computed geography column

4 additional answers

Your answer