Post reply

User Defined Table Type with calculated columns possible index corruption message

  • September 14, 2015 at 8:40 pm

    #320536

    HI folks,

    A few weeks ago one of my colleagues asked me about a “peculiar” message when tried to use a table variable declared as the user defined table type. The table type had a couple of computed columns. One of the columns had a CASE statement in its definition.

    The message was:

    “Metadata stored on disk for computed column '<Columm_name>' in table '<table_variable_name>' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column.”

    Note: I found a number of posts on Google about similar messages related to the db migration between servers and possible computed columns metadata mismatch.

    Since I’ve never come across this message I thought it would be interesting to investigate and try to understand what is that all about.

    Let’s first try to recreate the warning/message.

    The test environment params:

    •SQL Server version: Microsoft SQL Server 2014 - Developer Edition (64-bit)

    •Server Instance collation: Latin1_General_BIN

    Create test database. The database has different collation than the server instance.

    CREATE DATABASE TestUdtTableType

    COLLATE Latin1_General_CI_AS

    GO

    USE TestUdtTableType

    GO

    Now, create a test User defined table type. (script #1)

    IF EXISTS(SELECT 1 FROM sys.types t WHERE t.name = N'TestUDT')

    DROP TYPE dbo.TestUDT

    GO

    --Create User Defined Table type

    CREATE TYPE dbo.TestUDT AS TABLE (

    col1 NVARCHAR(100)

    ,col2 BIT

    ,colcal1 AS

    CASE ISNULL(col2,0)

    WHEN 1 THEN N'Yes'

    ELSE 'No'

    END

    ,colcal2 AS col1 +' ..this is not unicode suffix');

    GO

    And finally the message(or warning) shows up after inserting the test values into a table variable declared as the user defined table type dbo.TestUDT (script #2)

    DECLARE @tc AS dbo.TestUDT

    INSERT INTO @tc (col1, col2)

    SELECT N'Test unicode string',1

    UNION ALL

    SELECT N'and another unicode str. ',0

    SELECT * FROM @tc

    The messages

    Metadata stored on disk for computed column 'colcal1' in table '@tc' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column.

    Metadata stored on disk for computed column 'colcal1' in table '@tc' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column.

    (2 row(s) affected)

    (2 row(s) affected)

    NOTES:

    - If we run script#2 again the message(s) disappears. It may be worthwhile to explore the proc. cache. later 😀

    - The message suggests dropping and recreating the computed column. ALTER/DROP COLUMN cannot be used with the table variables.

    - BOL:User defined Table types is the definition of a table structure that can be used to structure sp/function READONLY table parameters and/or to declare table variables within a batch, stored proc. or function

    The resultset

    The first metadata message/warning is related to the INSERT statement and the second one to the following SELECT statement

    The test case analysis:

    - Available metadata related to the UDT table

    Drop and Create the UDT (script #1)

    The query below shows the metadata related to the UDT and the calculated columns.

    The metadata related to the user defined table type:

    SELECT tab.name AS

    ,TYPE_NAME(tab.system_type_id) AS [System type]

    ,TYPE_NAME(tab.user_type_id) AS [User type]

    ,tab.collation_name

    ,tab.type_table_object_id

    FROM sys.table_types tab

    WHERE tab.name ='TestUDT'

    The metadata related to the calculated fields used in the UDT definition (Script #3):

    SELECT OBJECT_NAME(scc.object_id) AS [Internal UDT name]

    ,scc.column_id AS [Calculated column ordinal position]

    ,TYPE_NAME(scc.system_type_id) AS [System type name]

    ,TYPE_NAME(scc.user_type_id) AS [User type name]

    ,scc.collation_name

    ,scc.uses_database_collation AS [Collation: 1- database default]

    ,scc.[definition]

    FROM sys.computed_columns scc

    WHERE scc.name IN ('colcal1','colcal2')

    The latest shows:

    - Both computed columns have the current DB default collation which is Latin1_General_CI_AS. That was expected since, if not specified, the UDT table columns will use the current DB collation. This is applicable to char, varchar ,text, nchar, nvarchar and ntext column data types

    - The calculated column colcal1 is of the type NVARCHAR.

    Calculated column colcal1 type is the result of the CASE statement expression. The column’s data type will be the data type with the highest precedence of all possible result values in the CASE Statement. In this case the CASE statement returns one of the two possible results: ‘Yes’ of type NVARCHAR and ‘No’ of type VARCHAR. The expression uses NVARCHAR because it has higher precedence than VARCHAR

    - Table variable metadata

    The tsql table variable metadata is stored in the tempdb database (similar to the temp tables). We need to query the metadata within the same batch since the scope of the tsql variables is from the point it’s declared until the end of the batch (or the hosting stored procedure).

    --the test (Script #4)

    DECLARE @tc AS dbo.TestUDT

    INSERT INTO @tc (col1, col2)

    SELECT N'Test unicode string',1

    UNION ALL

    SELECT N'and another unicode str. ',0

    --the metadata that shows the columns’ collation

    SELECT c.name

    ,c.collation_name

    FROM tempdb.sys.sysobjects so

    INNER JOIN tempdb.sys.columns c

    ON so.id = c.object_id

    WHERE c.name IN('col1','col2','colcal1','colcal2')

    The results:

    The metadata stored in tempdb shows that the collation corresponds to the current database default collation. (The instance collation is Latin1_General_BIN).

    The table variable definition(Script #4) seems to match the variable’s template(Script #3) - the user defined table type. The metadata seems to be in order - opposed to what the message says.

    As mentioned before, if we run the script again the message disappears. Ok, the column’s metadata wasn’t a good match in the first run, but that changed in the subsequent runs

    Clear the DB cache and try it again.

    DECLARE @pInt int = DB_ID('TestUdtTableType')

    DBCC FLUSHPROCINDB (@pInt)

    GO

    Run the

    And yep, the message popped up again.

    Just because it’s fun I made a detour to find out the table’s variable cached data – plan cache.

    --clean the database query cache and run the exact same query 5 times.

    DECLARE @tc AS dbo.TestUDT

    INSERT INTO @tc (col1, col2)

    SELECT N'Test unicode string',1

    UNION ALL

    SELECT N'and another unicode str. ',0

    --SELECT * FROM @tc

    GO 5

    --check the Adhoc cache

    SELECT cp.bucketid

    ,cp.refcounts

    ,cp.usecounts

    ,cp.size_in_bytes

    ,cp.cacheobjtype

    ,cp.objtype

    ,txt.[text]

    ,cp.plan_handle

    --,cp.parent_plan_handle

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text (plan_handle) txt

    WHERE objtype ='Adhoc'

    AND txt.text not like '%sys.dm_exec_cached_plans%'

    AND txt.dbid = DB_ID('TestUdtTableType')

    As expected, the plan has been cached as Adhoc compiled plan. (The option Optimize for ad hoc workloads is set to 0).

    NOTE: If Optimize for ad hoc workloads is set to 1 the two set of messages will show up. The first related to the compiled query stub, and the second one will be related to the actual compiled plan. The compiled plan will be used 4 times outputting one message.

    The message popped up only after the first batch and then, when sql server started to use the cached plans for the next 4 batches the message disappeared.

    My assumption is that the metadata related to the user defined table type are compared in terms of the collation with the table value metadata during the construction of the plan cache. For some “reason” the message pops up even if the collations are identical and cannot affect index corruption. 🙂

    There is another interesting thing I found when forced the computed column with the CASE statement to return sql_variant datatype.

    NOTE: sql_variant data type has the highest datatype president and will be used as the expression datatype

    IF EXISTS(SELECT 1 FROM sys.types t WHERE t.name = N'TestUDT')

    DROP TYPE dbo.TestUDT

    --Create User Defined Table type

    CREATE TYPE dbo.TestUDT AS TABLE (

    col1 NVARCHAR(100)

    ,col2 BIT

    ,colcal1 AS

    CASE ISNULL(col2,0)

    WHEN 1 THEN N'Yes'

    WHEN 0 then 'No'

    ELSE CAST(NULL AS sql_variant)

    END

    ,colcal2 AS col1 +' ..this is not an Unicode suffix');

    GO

    The computed columns metadata (script#3) will return

    .. and because the underlying base type of the sql_variant expression result is evaluated at run time the collation_name is NULL

    The interesting thing is the “uses_database_collation” column that has the value of 1 – the default DB collation, but when we use the data type the collation will be the instance collation.

    Now, when we run the query similar to (Script #4)

    DECLARE @tc AS dbo.TestUDT

    INSERT INTO @tc (col1, col2)

    SELECT N'Test unicode string',1

    UNION ALL

    SELECT N'and another unicode str. ',0

    SELECT *

    ,SQL_VARIANT_PROPERTY(colcal1,'Collation') [Base Type Collation]

    ,SQL_VARIANT_PROPERTY(colcal1,'BaseType') [Base Type]

    FROM @tc

    The message did not show up and the base type collation is the instance default rather than the database default as expected.

    And finally, if we explicitly specify collation in the CASE statement, the message will not show up

    IF EXISTS(SELECT 1 FROM sys.types t WHERE t.name = N'TestUDT')

    DROP TYPE dbo.TestUDT

    --Create User Defined Table type

    CREATE TYPE dbo.TestUDT AS TABLE (

    col1 NVARCHAR(100)

    ,col2 BIT

    ,colcal1 AS

    CASE ISNULL(col2,0)

    WHEN 1 THEN N'Yes'

    ELSE 'No' COLLATE Latin1_General_CI_AS

    END

    ,colcal2 AS col1 +' ..this is not an Unicode suffix');

    GO

    If anyone have an idea why the message is showing and how to explain the presented behavior, please comment.

    D.Mincic
    😀
    MCTS Sql Server 2008, Database Development

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy