Share via

Custom Attributes for CLR Routines

  • Article

The attributes listed can be applied to common language runtime (CLR) routines, user-defined types, and user-defined aggregates that are registered in Microsoft SQL Server. If the attribute is not applied, SQL Server assumes the default value. The attributes listed are defined in the Microsoft.SqlServer.Server namespace.

The SqlUserDefinedAggregate Attribute

The SqlUserDefinedAggregate attribute indicates that the method should be registered as a user-defined aggregate. Every user-defined aggregate must be annotated with this attribute.

SqlUserDefinedAggregate[(aggregate-attribute [,...])]
aggregate-attribute::=
Format={Native | UserDefined}
IsInvariantToDuplicates= {true | false}
IsInvariantToNulls= {true | false}
IsInvariantToOrder= {true | false}
IsNullIfEmpty= {true | false}
| MaxByteSize= n

This attribute uses the following properties:

Property Description

Format

Specifies the serialization format, which can be Format.Native or Format.UserDefined.

IsInvariantToDuplicates

Used by the query processor, this property returns true if the aggregate is invariant to duplicates. That is, the aggregate of S, {X} is the same as aggregate of S when X is already in S. For example, aggregate functions such as MIN and MAX satisfy this property, while SUM does not.

IsInvariantToNulls

Used by the query processor, this property returns true if the aggregate is invariant to nulls. That is, the aggregate of S, {NULL} is the same as aggregate of S. For example, aggregate functions such as MIN and MAX satisfy this property while, COUNT(*) does not.

IsInvariantToOrder

Reserved for future use. This property is not currently used by the query processor: order is currently not guaranteed.

IsNullIfEmpty

Used by the query processor, this property returns true if the aggregate returns null if no values have been accumulated.

MaxByteSize

Specifies the maximum size of the instance, in bytes. You must specify MaxByteSize with the UserDefined serialization format. The maximum allowed value is 8000.

For an aggregate with user-defined serialization specified, MaxByteSize refers to the total size of the serialized data. Consider an aggregate serializing a string of 10 characters (System.Char). When the string is serialized using a BinaryWriter, the total size of the serialized string is 22 bytes: 2 bytes per Unicode UTF-16 character, multiplied by the maximum number of characters, plus 2 control bytes of overhead incurred from serializing a binary stream. So, when determining the value of MaxByteSize, the total size of the serialized data must be considered: the size of the data serialized in binary form plus the overhead incurred by serialization.

The SqlFunction Attribute

The SqlFunction attribute indicates the method should be registered as a function, with the appropriate function attributes set.

SqlFunction[(function-attribute [,...])]
function-attribute::=
IsDeterministic= {true | false}
| DataAccess = { DataAccessKind.None | DataAccessKind.Read }
| SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }
| IsPrecise = { true | false }
| FillRowMethodName= string
| Name= string
| TableDefinition= string

This attribute uses the following properties:

Property

Description

IsDeterministic

A user-defined function is said to be deterministic if it always produces the same output values given the same input values and the same database state. You can mark a function as deterministic by setting the IsDeterministic property to true.

The IsDeterministic property is also useful for indexing the result of the function in the form of indexed computed columns and indexed views. If this property is not specified, the function is assumed to be non-deterministic.

Unlike SQL Server 2000, in this version of SQL Server the definition of determinism is refined to allow functions that access local data to be deterministic. The data access characteristic is captured separately by the DataAccess and SystemDataAccess properties.

Note that data access to remote servers is available in user-defined functions. For example, this is the case if you use a SqlConnection to connect to another SQL Server instance. However, you must still honor the IsDeterministic declaration. If the CLR function is marked as deterministic, it should not cause side-effects in the remote server. While side-effects against the context connection are restricted, SQL Server does not enforce the restriction for side-effects over remote connections.

The default value of this attribute is false.

ms131050.note(en-US,SQL.90).gifNote:

Do not mark a function as deterministic if the function does not always produces the same output values, given the same input values and the same database state. Marking a function as deterministic when the function is not truly deterministic can result in corrupted indexed views and computed columns.

DataAccess

The DataAccess property determines if the function involves access to user data stored in the local SQL Server. It is also required when connecting to remote servers if transactions integration is required (the default). This property can have one of two values:

  • DataAccessKind.None: Does not access data.
  • DataAccessKind.Read: Only reads user data.

ms131050.note(en-US,SQL.90).gifNote:

If a Transact-SQL query is executed from inside a CLR routine, the DataAccessKind.Read property should be set.

SystemDataAccess

This property indicates if the function requires access to data stored in the system catalogs or virtual system tables of SQL Server. This property can have one of two values:

  • SystemDataAccessKind.None: Does not access system data. This is the default.
  • SystemDataAccessKind.Read: Only reads system data.

IsPrecise

This is a Boolean-valued property that indicates whether the routine involves imprecise computations such as floating point operations. Precision of a function is one of the properties used to determine if computed columns that use this function can be indexed. The default value of this attribute is false.

FillRowMethodName

This attribute is relevant for table-valued functions (TVFs). This is a string that specifies the name of a method in the same class as the TVF that is used by the TVF contract. For more information, see CLR Table-Valued Functions.

Name

This represents the name under which the function should be registered in SQL Server. This attribute is used only by Microsoft Visual Studio to register the specified method as a user-defined function automatically; it is not used by SQL Server.

TableDefinition

This is a string that represents the table definition of the results, if this method is used as a TVF. This attribute is used only by Visual Studio to register the specified method as a TVF automatically; it is not used by SQL Server.

The following example illustrates how this attribute can be used to define a TVF that returns two columns of type int and nvarchar(4000) from Visual Studio.

[C#]

[SqlFunction(FillRowMethodName="FillRow1", TableDefinition = "Testid int, Testname nvarchar(4000)")]
public static IEnumerable TableFunctionExecute1(string str)

[Visual Basic]

<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow1", TableDefinition:="Testid int, Testname nvarchar(4000)")>
Public Shared Function TableFunctionExecute1(ByVal str As String) As IEnumerable

The SqlFacet Attribute

The SqlFacet attribute is used to return information about the return type of a user-defined type (UDT) expression. This attribute may only be specified on non-void return values. This attribute is used only to derive information about the return type, and is not intended to be a "constraint" specification on what can be stored in the type. Thus, if a field has a SqlFacet indicating its size to be 2 characters, then the SQL Server type of the field access expression is of size 2, but assignments into the field are not restricted by this facet.

SqlFacet[(facet-attribute [,...])]
facet-attribute::=
IsFixedLength= {true | false}
| MaxSize= { n }
| Precision = { n}
| Scale = { n}
| IsNullable = { true | false }
IsFixedLength

True if return type is of a fixed length. Must be set to FALSE if MaxSize is set to -1. Default is FALSE.

MaxSize

The maximum size in logical units for the underlying field type. Logical unit is bytes (in the range 1 - 8000) for the binary field types, and the number of Unicode characters (in the range 1 - 4000) for the character field types. The value -1 is reserved for large character and binary types. The default is 4000 for Unicode character types and 8000 for binary types.

Precision

Precision of the return type. Valid only for numeric types. Scale must also be specified when setting the Precision property. Maximum value is 38. Default value is 38.

Scale

Scale of return type. Valid only for decimal types. Precision must also be specified when setting the Scale property. The maximum value is 38. Default value is 0.

IsNullable

True if return value can be NULL. Default value is TRUE.

Valid Properties for SqlFacet Field Types

The following table captures the matrix of valid values for the various properties for specific field types. In this table, "Y" indicates that the property is valid, and "N" indicates that the property is not valid.

The specified SqlFacet must be compatible with the field type. If the property is not valid, type registration reports an error if the user specifies a non-default value for the property. The maximum values for precision and scale is 38. For MaxSize, the value should be in the range of 1-8000 for binary types, 1-4000 for character types, or -1. No other values are valid.

Type IsFixedLength MaxSize Precision Scale IsNullable

SqlBoolean

N

N

N

N

Y

SqlByte

N

N

N

N

Y

SqlInt16

N

N

N

N

Y

SqlInt32

N

N

N

N

Y

SqlInt64

N

N

N

N

Y

SqlSingle

N

N

N

N

Y

SqlDouble

N

N

N

N

Y

SqlDateTime

N

N

N

N

Y

SqlMoney

N

N

N

N

Y

SqlGuid

N

N

N

N

Y

SqlDecimal

N

N

Y

Y

Y

SqlString

Y

Y

N

N

Y

SqlBinary

Y

Y

N

N

Y

SqlXml

N

N

N

N

Y

SqlBytes

Y

Y

N

N

Y

SqlChars

Y

Y

N

N

Y

SqlDate

N

N

N

N

Y

SqlTime

N

N

N

N

Y

Embedded UDTs

N

N

N

N

Y

string

Y

Y

N

N

Y

Byte[]

Y

Y

N

N

Y

Char[]

Y

Y

N

N

Y

decimal

N

N

Y

Y

N

The SqlProcedure Attribute

The SqlProcedure attribute indicates the method should be registered as a stored procedure. This attribute is used only by Visual Studio to register the specified method as a stored procedure automatically; it is not used by SQL Server.

SqlProcedure [(procedure-attribute[ ,... ])]
procedure-attribute::=
Name = "procedure name"

This attribute uses the following property:

Name

The Name property is optional, and specifies the name of the stored procedure.

Note

The SqlProcedure attribute is optional outside of the Visual Studio development environment.

The SqlTrigger Attribute

The SqlTrigger attribute indicates the method should be registered as a trigger.

SqlTrigger [(trigger-attribute[ ,... ])]

trigger-attribute::=
    Target = " table-name "  
  | Event = "trigger-type update-action [, ...]"

trigger-type::=
    FOR | AFTER | INSTEAD OF

update-action::=
    UPDATE | DELETE | INSERT

This attribute uses the following properties:

Target

This required property specifies the table to which the trigger applies.

Event

This required property specifies the type of the trigger and what data manipulation language (DML) action activates the trigger: an UPDATE, a DELETE, or an INSERT action. The type of the trigger can be AFTER or INSTEAD OF. Specifying FOR for the trigger type is the same as specifying AFTER.

The SqlUserDefinedTypeAttribute

You can apply the SqlUserDefinedTypeAttribute to a class definition in the assembly. It causes SQL Server to create a user-defined type that is bound to the class definition which has this custom attribute.

SqlUserDefinedTypeAttribute[(udt-property [,...])]

udt-property::=
    Format={Native | UserDefined}
  | MaxByteSize= n 
  | IsByteOrdered= {true | false}
  | ValidationMethod=<validate_method_name>
  | IsFixedLength = {true | false}
  | Name = <SQL_type_name>

This attribute uses the following properties:

Property Description

Format

Specifies the serialization format, which can be Format.Native or Format.UserDefined. Native serialization depends on the data types of the UDT.

MaxByteSize

The maximum size of the instance, in bytes. You must specify MaxByteSize with the UserDefined serialization format. MaxByteSize must be between 1 and 8000. This attribute should not be used with Native serialization format.

For a UDT with user-defined serialization specified, MaxByteSize refers to the total size of the UDT in its serialized form as defined by the user. Consider a UDT with a property of a string of 10 characters (System.Char). When the UDT is serialized using a BinaryWriter, the total size of the serialized string is 22 bytes: 2 bytes per Unicode UTF-16 character, multiplied by the maximum number of characters, plus 2 control bytes of overhead incurred from serializing a binary stream. So, when determining the value of MaxByteSize, the total size of the serialized UDT must be considered: the size of the data serialized in binary form plus the overhead incurred by serialization.

IsByteOrdered

A Boolean value that determines how SQL Server performs binary comparisons on the UDT. If true, SQL Server assumes that the UDT is ordered in the same way as its byte representation. This property must be set to true to allow creation of indexes over columns of the attributed type.

ValidationMethodName

The method specified by this attribute is used to validate instances of the UDT, when the UDT has been deserialized from a binary value that is not trusted.

IsFixedLength

A Boolean value that determines if all instances of this type are the same length. If set to true, all instances of UDTs corresponding to this CLR type must have a length in bytes exactly equal to MaxByteSize. This attribute is only relevant for UDTs with UserDefined serialization format.

Name

The SQL Server name of the type. This is not used within SQL Server, but is used by the Visual Studio .NET project system.

The SqlMethod Attribute

The SqlMethod attribute is used to indicate the determinism and data access properties of a method or a property on a UDT. For a property, the SqlMethod attribute should be used on the setter or the getter directly. The SqlMethod attribute contains a set of properties that include all the SqlFunction properties, and adds two new properties.

Note

   The SqlMethodAttribute class inherits from the SqlFunctionAttribute class, so SqlMethodAttribute inherits the FillRowMethodName and TableDefinition fields from SqlFunctionAttribute. This implies that it is possible to write a table-valued method, which is not the case. The method compiles and the assembly deploys, but an error about the IEnumerable return type is raised at runtime with the following message: "Method, property, or field '<name>' in class '<class>' in assembly '<assembly>' has invalid return type."

SqlMethod [ ( method-attribute [ ,... ] ) ]
method-attribute::= 
    IsDeterministic= { true | false }, default is false
  | IsPrecise= {true | false}
  | IsMutator = { true | false }
  | OnNullCall = { true | false }
  | InvokeIfReceiverIsNull= { true | false }, default is false
  | DataAccess = { DataAccessKind.None | DataAccessKind.Read }
  | SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }
  | FillRowMethodName= string
  | Name= string
  | TableDefinition= string

If IsDeterministic is set to true, the method or property is marked accordingly. The default is false.

If OnNullCall is specified, the value true indicates the method is called when NULL arguments are supplied in the method invocation. False indicates a NULL value should be assumed as the result of the method if any of its inputs is NULL. The default value of OnNullCall is true.

If the IsMutator property is set to true and the return type of the method is void, SQL Server marks the method as a mutator. A mutator method is one that causes a state change in the UDT instance. Mutator methods can be called in assignment statements or data modification statements, but cannot be used in queries. If a method is marked as a mutator but does not return void, then CREATE TYPE does not fail with an error. Even though returning a value other than void does not raise an error, the returned value is not accessible and cannot be used. The default value is false. A property can be a mutator if the SqlMethod attribute is used on the setter and IsMutator is set to TRUE. However, a property setter is implicitly treated as a mutator and it is not necessary to set the IsMutator property of the SqlMethod attribute to TRUE.

The default value for InvokeIfReceiverIsNull is false. That is, the method is not invoked on a null instance. If InvokeIfReceiverIsNull is true, the return value of the method depends upon its type. If the return type of the method is nullable, the distinguished null value for the type is returned. If the return type is non-nullable, the default CLR value for the type is returned. The default value for reference types is null, while the default value for value types is the result of calling the default constructor for the type.

See Also

Concepts

CLR User-Defined Types
CLR Stored Procedures
CLR Triggers

Other Resources

CLR User-Defined Aggregates
CLR User-Defined Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Updated content:
  • Added information to the SqlMethod Attribute section, stating that the CREATE TYPE statement does not fail if a mutator method returns a value other than void.
  • Added note on the danger of marking a function as being deterministic, using the IsDeterministic property, when it isn't truly deterministic.