TEXTIMAGE_ON and Computed geography column

Antonio Matos 21 Reputation points
2021-07-29T18:08:30.417+00:00

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?

Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2021-07-30T21:30:38.693+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2021-07-29T20:48:39.313+00:00

    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.

    0 comments No comments

  2. MelissaMa-MSFT 24,221 Reputation points
    2021-07-30T05:43:21.253+00:00

    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.


  3. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2021-07-30T17:20:48.303+00:00

    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.


  4. Antonio Matos 21 Reputation points
    2021-07-30T17:26:33.053+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.