Datadude and the Text Filegroup

  • [アーティクル]

So, we had an interesting problem crop up from a European customer, and investigating it uncovered both a little known, um... feature in SQL Server, and a bit of a twist in our handling of it.

The customer had a table that he was Reverse Engineering from his source database into a DBPro project. The table contained nothing but integer columns. However, when he completed the import, the Create Table script that we generated had an entry at the bottom, after the ON [PRIMARY] clause, that said TEXTIMAGE_ON [PRIMARY].

If you've never run into this before, this clause is used when SQL finds there is some sort of Large Object (LOB) in your table: in SQL 2005, this could be an XML column, or an NVARCHAR(MAX), or any of a number of things that lack a predefined size. SQL allows you to put these Large Objects somewhere other than the main table, and even allows you to put them into a different filegroup. Therefore, every time it finds a LOB in a table, it makes an entry into a column in sys.tables called lob_dataspaces_id, recording what filegroup the column is going to live in. Even if it is just going to stay in the same space as the rest of the table.

You can easily see this filegroup if you right-click and select the Properties of a table in SQL Server Management Studio. At the very bottom of the Property dialog is an entry called Text Filegroup. If there is an LOB in the table, this will be populated with the name of a filegroup. If it is blank, then there are no LOBs in the table.

Datadude uses the existence of this entry to control whether or not we append the TEXTIMAGE_ON clause onto the end of our generated Create Table scripts. So what happened to this customer?

Well, it turns out that if a table used to have a LOB in it, then the Text Filegroup is preserved. Deleting the LOB column does not zero the entry out of the lob_dataspaces_id column in sys.table. Imagine that. So when we go to get the table, we return the information about the Text Filegroup, and automatically append the clause onto the Create Table statement.

The effect of this is an error when the Create Table statement gets validated inside the DBPro project:

Msg 1709, Level 16, State 1, Line 1

Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml or large user defined type columns.

And now your script is in an error state. And sure enough, the customer verified that their table did indeed previously have a LOB in it.

This is easy enough to fix. All you have to do is delete the offending clause in the editor, save the script, and the problem disappears. The only other wrinkle is if you are intending to deploy back to the original SQL Server database - the one with the lob_dataspaces_id still defined. During our Build and Deploy process, Datadude will see the change in the table script, and conclude that the table needs to be rebuilt. On deployment, it will create a new table with the same schema, move all the data from the original, and then swap the names around. Being a new table, this one will no longer have the old, residual Text Filegroup setting. Problem gone.

Of course, this isn't our final resolution - we never really intended this to work this way. Since SSMS is smart enough to figure out not to script out the TEXTIMAGE_ON clause in their Create Table scripts, we'll figure out how to suppress it in ours as well. But when we'll get around to it is not yet clear, so I thought I'd let everyone know about the workaround. Remember, you can accomplish the same thing outside our toolset too: just create a new table, move the data, and swap names.

Comments

  • Anonymous
    February 01, 2007
    Jeff Beehler on Try out our Virtual Labs. Noah Coad on TFS Work Item Hierarchy. The Team Foundation...