Although table types do allow unnamed CHECK
constraints, it is not possible to use a user-defined function inside one.
The error message is:
Msg 2785 Level 16 State 1 Line 3
User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
Msg 1750 Level 16 State 0 Line 1
Could not create constraint. See previous errors.
See the db<>fiddle demo
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:
And the fourth, missing the compute scalar and any implicit conversion:
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.
Best Answer
You can do something like this,
You can create a simple function that does that too,