CODEWITHSUNDEEP
sql-serveruser-defined-functionsuser-defined-typesschemabinding
Kevin Martin
Kevin Martin

Reputation: 146

Using schema-bound SQL Server user-defined functions with alias types?

Consider the following T-SQL statements:

create type mytype from decimal(8,3)
go

create function f (@x int) 
returns mytype
with schemabinding
as 
begin
    return @x * @x
end

When I try this using SQL Server 2017 (v14.xx), I get this error:

Msg 2792, Level 16, State 1, Procedure f, Line 6 (Batch Start Line 2)
Cannot specify a SQL CLR type in a schema-bound object or a constraint expression.

The documentation for create type states that it

Creates an alias data type or a user-defined type [in a database]. [...] A user-defined type is implemented through a class of an assembly in [the CLR].

This sort of makes it sound like a "user-defined type" and a "CLR type" (as named in the error message) might just be two names for the same thing, but that an "alias data type" is (or could be) something else.

I don't want any CLR support, all I want to do is give the type a name so that it can be used consistently throughout several function definitions and any code written which calls these functions. I want the schema binding because it produces noticeable performance improvements in queries that use the functions.

If I look up mytype in sys.types it has is_user_defined set to 1, but is_assembly_type set to 0.

So, are a "user-defined type" and a "CLR type" two names for the same thing? Is an "alias data type" something different? They seem to have distinct syntax in create type. Is this error message just confusing by referring to a "CLR type" when it really means any user-defined type?

Can I do what I want or do I have to forego either the aliased type declaration or the schema-binding?

Upvotes: 1

Views: 583

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

The problem comes from the history of custom types:

  • In the beginning, the alias type you're using was called a user-defined data type.
  • When CLR came around, they called the custom types you could create there user-defined data types, and renamed the older ones alias types (now it's called a user-defined data type alias which is arguably even more confusing).

Not all of the people writing the documentation and error messages got the memo, so you'll likely see several cases like this where they're used interchangeably. And those largely can't be fixed because of backward compatibility (changing the text in an error message could break code that parses the message). It's not a good excuse but it's a consistent one.

As for your choice

You can either drop schemabinding or drop the alias type. I would do the latter and just stay away from alias types, full stop. You may think they save you some typing, but the price is pain. Schemabinding aside, just wait until you try to change that to decimal(10,3). There are definitely more (and more tangible) benefits to using schemabinding than there are to using alias types.

In all honesty, I don't think the behavior is expected; I can't think of a reason you can't schema-bind an alias type, since that's essentially the reverse behavior anyway. But you'll have to take that up with Microsoft to get it changed.

Upvotes: 1

Related Questions