Sql-server – User defined data types causing implicit conversions

sql serversql-server-2008-r2type conversionuser-defined-type

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into that var from a column that uses that user defined data type, it's showing it as an implicit conversion. Is this how it functions normally?

Added

ok so under Programmability -> Types -> User-Defined Data Types: there is

dbo.BOOL(char(1), not null)

and

ImageAccessibleFLAG TYPE_NAME is BOOL

New test

I can make an implicit conversion happen by doing the following
reasonforcreatetype is char(1) in the database

declare @test char(1)
set @test = (select top 1 reasonforcreatetype from result)
select @test

<ScalarOperator ScalarString="CONVERT_IMPLICIT(char(1),[Result].[ReasonForCreateType],0)">

Now I create a new table with a char(1) and a bool.
Run the same query and I don't get the implicit conversion.

Update 2

Ok when adding a new column under the same table having the issue I get the following

Warning: Columns have different ANSI_PADDING settings.
New columns will be created with ANSI_PADDING 'on'.

I'm assuming this has something to do with it now but I'm not sure the proper way to fix or correct these issues.

Best Answer

Okay, here is a repro:

USE tempdb;
GO
-- create one table with padding off:
SET ANSI_PADDING OFF;
GO
CREATE TABLE dbo.ap_off(a CHAR(1),
 CONSTRAINT ck_apOff CHECK (a IN ('N','Y')));
GO
-- and other with padding on:
SET ANSI_PADDING ON;
GO
CREATE TABLE dbo.ap_on(a CHAR(1),
 CONSTRAINT ck_apOn CHECK (a IN ('N','Y')));
GO

-- now, let's test queries 
-- with padding on or off
SET ANSI_PADDING OFF;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);

SET ANSI_PADDING ON;
GO
-- implicit conversion:
DECLARE @off CHAR(1) = (SELECT a FROM dbo.ap_off);
-- NO implicit conversion:
DECLARE @on  CHAR(1) = (SELECT a FROM dbo.ap_on);
GO

DROP TABLE dbo.ap_off, dbo.ap_on;

Here's the same version of the plan for the first three queries:

enter image description here

And the fourth, missing the compute scalar and any implicit conversion:

enter image description here

So, as I suggested above, this doesn't have anything to do with alias types, but rather the fact that ANSI_PADDING is problematic in a whole bunch of ways (here's a timely article, a Stack Overflow question, and Microsoft's own documentation from SQL Server 2005 telling you to stop using it).

My suggested approach is to build new versions of any table with these non-ANSI_PADDING columns (this time with ANSI_PADDING ON of course), migrate the data, drop the old tables, and rename the new tables. Or do the same type of thing with just the individual columns (in either case it's going to be a complicated and disruptive thing that you'll likely want to do during a maintenance window).

You can identify the affected tables with:

SELECT s.name, t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND EXISTS (SELECT 1 FROM sys.columns
 WHERE [object_id] = t.[object_id]
 AND is_ansi_padded = 0);

I would do this with one table or columns first, to see if it's worth it. See, in addition to eliminating the implicit conversions (which you think might cause performance issues), you should also demonstrate that performance will actually be different without them - and different enough to justify the effort, risk, and potential downtime. Otherwise, is it worth doing? We can't answer that, only you and your stakeholders can.

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO

Postgresql – Display user-defined types and their details

Does this get you started?

SELECT n.nspname AS schema,
        pg_catalog.format_type ( t.oid, NULL ) AS name,
        t.typname AS internal_name,
        CASE
            WHEN t.typrelid != 0
            THEN CAST ( 'tuple' AS pg_catalog.text )
            WHEN t.typlen < 0
            THEN CAST ( 'var' AS pg_catalog.text )
            ELSE CAST ( t.typlen AS pg_catalog.text )
        END AS size,
        pg_catalog.array_to_string (
            ARRAY( SELECT e.enumlabel
                    FROM pg_catalog.pg_enum e
                    WHERE e.enumtypid = t.oid
                    ORDER BY e.oid ), E'\n'
            ) AS elements,
        pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
    FROM pg_catalog.pg_type t
    LEFT JOIN pg_catalog.pg_namespace n
        ON n.oid = t.typnamespace
    WHERE ( t.typrelid = 0
            OR ( SELECT c.relkind = 'c'
                    FROM pg_catalog.pg_class c
                    WHERE c.oid = t.typrelid
                )
        )
        AND NOT EXISTS
            ( SELECT 1
                FROM pg_catalog.pg_type el
                WHERE el.oid = t.typelem
                    AND el.typarray = t.oid
            )
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible ( t.oid )
    ORDER BY 1, 2;

In psql you can \set ECHO_HIDDEN on to make psql show you the queries used to generate the output of the \d... commands. I've found these queries to be very useful as a starting point when digging metadata out of databases.

Update: 2019-12-16

For composite types, the columns metadata can be determined using something like the following:

WITH types AS (
    SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
        FROM pg_catalog.pg_type t
        JOIN pg_catalog.pg_namespace n
            ON n.oid = t.typnamespace
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = 'c'
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                    SELECT 1
                        FROM pg_catalog.pg_type el
                        WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
),
cols AS (
    SELECT n.nspname::text AS schema_name,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            a.attname::text AS column_name,
            pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
            a.attnotnull AS is_required,
            a.attnum AS ordinal_position,
            pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
        FROM pg_catalog.pg_attribute a
        JOIN pg_catalog.pg_type t
            ON a.attrelid = t.typrelid
        JOIN pg_catalog.pg_namespace n
            ON ( n.oid = t.typnamespace )
        JOIN types
            ON ( types.nspname = n.nspname
                AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
)
SELECT cols.schema_name,
        cols.obj_name,
        cols.column_name,
        cols.data_type,
        cols.ordinal_position,
        cols.is_required,
        coalesce ( cols.description, '' ) AS description
    FROM cols
    ORDER BY cols.schema_name,
            cols.obj_name,
            cols.ordinal_position ;

Best Answer

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

Postgresql – Display user-defined types and their details

Related Question