UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies

Question

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly.

Step by step:

1. Assembly:

   CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH PERMISSION_SET = SAFE
   

2. Type:

   CREATE TYPE [dbo].[TParams] EXTERNAL NAME [UDT.TParams].[Sql.UDT.TParams]
   

3. Table:

   CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
       [Id] [tinyint] NOT NULL,
       [Group_Id] [tinyint] NOT NULL,
       [Name] [nvarchar](50) NOT NULL,
       [Code] [varchar](25) NOT NULL,
       [PersonTypes_IDs] [varchar](50) NOT NULL,
       [Unique] [bit] NULL,
       [SortIndex] [tinyint] NOT NULL,
       [FieldName]  AS (replace([Code],':','_')),
       [GUI:Edit:Type] [varchar](64) NULL,
       [GUI:Edit:Params] [dbo].[TParams] NULL,
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   

4. Statistics (it's auto-created by SQL Server):

   CREATE STATISTICS [weirdStatistics] ON
         [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params])
   

5. Then we have scheduled weekly updates on all statistics with FULLSCAN:

   UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN
   

Getting this error after above command:

Msg 1038, Level 15, State 4, Line 3
An object or column name is missing or empty.
For SELECT INTO statements, verify each column has a name.
For other statements, look for empty alias names.
Aliases defined as "" or [] are not allowed.
Change the alias to a valid name.

How do I fix this error, or do I just skip the statistics update?

UPDATE: Opening properties windows of [weirdStatistics] in SSMS, will show error: DataReader.GetFieldType(0) returned null. (SqlManagerUI). Any other statistics for table opens normally.

Answer 1

I do not believe there is any issue here with regards to the User-Defined Type (UDT) as I am able to execute this successfully:

Please note that I am using the Free version of the SQL# library (which I wrote) to test with:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [SQL#].[Type_HashTable] NULL
);

CREATE STATISTICS [weirdStatistics] ON [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params]);

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN;

No errors. However, if I remove the stats object name (the error did mention a missing object name) but keep the square brackets, then I am able to reproduce the error:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [] WITH FULLSCAN;

returns:

Msg 1038, Level 15, State 4, Line 28
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

And no square brackets at all works just fine (since it updates all stats on the table):

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] WITH FULLSCAN;

So it really needs to be empty square brackets. My guess is that your weekly Update Stats job (I am assuming it is a SQL Agent Job since you said "weekly") is somehow leaving out the stats object name.

AND, since the question actually mentions auto-created statistics, that should be tested as well, just to be sure that there isn't an issue with those (there shouldn't be, but it's still best to test and know for sure):

-- get rid of user-created statistics object:
DROP STATISTICS [dbo].[Dic:Table:With&Weird:Name].[weirdStatistics];

-- add one row so that we can query it:
INSERT INTO dbo.[Dic:Table:With&Weird:Name]
VALUES (1, 2, N'ddfg', 'df:uiui', 'dfgdfgd', 0, 44, 'type', N'key1=SomeVal');

-- get SQL Server to auto-create the statistics object on this column:
SELECT *
FROM   dbo.[Dic:Table:With&Weird:Name]
WHERE  [GUI:Edit:Params] IS NOT NULL;

-- get the dynamically generated statistics object name and use it for UPDATE STATS:
DECLARE @StatName sysname;

SELECT @StatName = st.[name]
FROM sys.stats st
INNER JOIN sys.stats_columns sc
        ON sc.[object_id] = st.[object_id]
       AND sc.[stats_id] = st.[stats_id]
INNER JOIN sys.columns col
        ON col.[object_id] = sc.[object_id]
       AND col.[column_id] = sc.[column_id]
WHERE st.[object_id] = OBJECT_ID(N'dbo.Dic:Table:With&Weird:Name')
AND   col.[name] = N'GUI:Edit:Params';

SELECT @StatName AS [StatisticsName];

EXEC (N'UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [' + @StatName
      + N'] WITH FULLSCAN;');

The code above also completes without errors :-).

---

The following test should be done to separate the UDT from this particular usage of it (i.e. this table). The example below again uses SQL#, but you should replace [SQL#].[Type_HashTable] with [dbo].[TParams] and then replace the string value in the first INSERT statement with the appropriately formatted initialization text. If this does not error then the issue is with that table, possibly with the column name itself, such as having a hidden character in it. If it does error then we need to look at the definition of that UDT.

CREATE TABLE [dbo].[tmpTestStats](
    [Id] [tinyint] NOT NULL,
    [UDT] [SQL#].[Type_HashTable] NULL
);

INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (1, N'k1=v1&k2=v2');
INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (2, NULL);

CREATE STATISTICS [tmpTestStats_UDT] ON [dbo].[tmpTestStats]([UDT]);

UPDATE STATISTICS [dbo].[tmpTestStats] [tmpTestStats_UDT] WITH FULLSCAN;