1

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?

Improve this question
4
  • Why use a user defined scalar type anyway here? Why not just define a decimal(8,3) for the RETURN value? Commented Sep 28, 2021 at 15:01
  • So that whoever is using this function (and its friends) can declare columns, local variables, or their own function parameter and return types using the named type rather than having to remember that it happens to be decimal(8, 3). In other words, the same reason you might code "typedef unsigned int tableId;" in c/c++. Commented Sep 28, 2021 at 15:31
  • SQL Server isn't designed for dynamically changing data types for developer convenience. You can work around that, or demand (and wait a long time for) a fix, or you can change developer expectations. I can't imagine you're changing this specific definition often enough to benefit from obfuscating it, and if you are, you'll pay for that change. This is why we have documentation, data dictionaries, and oodles of metadata functionality. Commented Sep 28, 2021 at 15:35
  • Truthfully, I just don't see the point. If you can't remember what data type a function returns, there are plenty of different ways to find out. Look at the definition, use dm_exec_describe_first_result_set, use the sys objects (the RETURN value is defined in sys.parameters and has parameter_id of 0), etc. Don't try to treat SQL like a programming language; it isn't one. Commented Sep 28, 2021 at 15:37

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

The alias type was not to save typing, but to ensure consistency. Indeed, changing the definition of the type would require at the least altering or dropping/re-creating all the objects that use the named type, but at least the code for these would not have to change. So that is less work, and less error-prone, than tracking down all the occurrences of decimal(8,3), deciding which ones have to change, changing them, then altering or dropping/re-creating the objects.
Sadly we don't live in a perfect world, so sometimes we have to have trade-offs. Personally, I think the benefits of schemabinding far outweigh what you're describing, which is essentially just trying to prevent developers from being sloppy (in the hopefully rare event this type has to change in the first place!), but the beauty here is that you can prioritize differently. Your question was about why the behavior is this way; I can't tell you how you should handle it.
It feels like you are favoring an optimization for a change that might never happen, at the cost of both losing the performance benefits of schemabinding and at making it a much more painful process to manage that change. ¯\_(ツ)_/¯
You misunderstand me. I only found out about the interaction of type aliases and schema binding this week, and clearly the way to go is with the schema binding. However, up until that point, in our particular programming and development environment, use of the type alias was a no-brainer having no downsides.
So require at the least altering or dropping/re-creating all the objects that use the named type is not a downside? That would be pretty intrusive in my shop, even in dev.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.