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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kevin Martin
  • 116
  • 8
  • Why use a user defined *scalar* type anyway here? Why not just define a `decimal(8,3)` for the `RETURN` value? – Thom A Sep 28 '21 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++. – Kevin Martin Sep 28 '21 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. – Aaron Bertrand Sep 28 '21 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. – Thom A Sep 28 '21 at 15:37

1 Answers1

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 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. – Kevin Martin Sep 28 '21 at 15:39
  • Sadly we don't live in a perfect world, so sometimes we have to have trade-offs. Personally, I think [the benefits of schemabinding](https://www.mssqltips.com/sqlservertip/4673/benefits-of-schemabinding-in-sql-server/) 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. – Aaron Bertrand Sep 28 '21 at 15:46
  • 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. ¯\\_(ツ)_/¯ – Aaron Bertrand Sep 28 '21 at 15:51
  • 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. – Kevin Martin Sep 29 '21 at 11:10
  • 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. – Aaron Bertrand Sep 29 '21 at 12:02
  • Even without a named type, if you are changing the type you pretty much have to alter or drop/recreate all the objects anyway. This manual process is tedious and error-prone, and takes coding time proportional to database complexity. With a named type you can definitively find all references to the type, and all the changes can be automated by code using the information_schema or sys views, and takes constant coding time. I feel the latter (O(1) time and no missed changes) to be better than the former (O(n) time and the chance to miss some changes resulting in latent bugs). As they say, ymmv. – Kevin Martin Sep 29 '21 at 13:40
  • @Kevin altering a parameter or column using a built-in type (e.g. to a larger type) doesn't have to happen everywhere in one shot (if there is any thought to backward compatibility as part of the change), and can often be an online operation. Whereas you have to eliminate every last instance of the alias type to a native type before you can change anything, then change everything back. So you actually have to do two separate changes to every object. And if those are columns, they might not be able to be online. I might suggest extended properties as a better way to identify all associations. – Aaron Bertrand Sep 29 '21 at 14:00