Attributes for SQL Server CLR Integration Database Projects and Database Objects

This attribute must be applied to all assemblies that are deployed to a SQL Server database. This attribute has no parameters. It is added to the AssemblyInfo file when you create a SQL Server project.

This attribute must be applied to user-defined aggregate objects. This attribute has two properties: Format andMaxByteSize.

Format

Required. The storage format of the aggregate. The supported formats are as follows:

Native – Specifies that SQL Server uses an efficient native representation on disk. This format option is the most compact and provides the best performance. The requirements for this format are as follows:

• The StructLayout.LayoutKindSequential attribute must be applied to the aggregate.

• All the fields of the aggregate must be blittable, that is, they must have a common representation in both managed and unmanaged memory and not require special handling by the interop marshaler.

• The aggregate must not specify a value for MaxByteSize.

• The aggregate must not have any [NonSerialized] fields.

• Fields must not be marked as an explicit layout.

UserDefined - Specifies that the user has full control over the binary format. The requirements for this format are as follows:

• The aggregate must implement IBinarySerialize.

• The aggregate must specify a value for MaxByteSize.

MaxByteSize

The maximum size of an instance of this aggregate, in bytes. Required only if the Format is set to UserDefined. Must not be specified when the Format is set to Native.

This example specifies that the Format of the aggregate is Native.

[SqlUserDefinedAggregate(Format.Native)]
public class SampleAggregate
{
   //...
}

This attribute must be applied to stored procedure objects. This attribute has the following parameter:

• Name - Optional. Specifies the name that is used on the SQL Server to reference the stored procedure.

This example specifies that the stored procedure is referenced using the name sp_sqlName.

public partial class StoredProcedures
{
    [SqlProcedure(Name="sp_sqlName")]
    public static void SampleProcedure(SqlString s)
    {
        //...
    }
}

This attribute must be applied to trigger objects. This attribute has the following parameters:

• Name - Optional. Specifies the name that is used on the SQL Server to reference the trigger.

• Target - Required. Specifies the target to which the trigger applies. The type of target depends on the type of target that you create. You can create DDL, DML, or LOGON triggers. The most common type of trigger is applied to tables.

• Event - Required. Specifies the action that activates the trigger.

This example specifies that the trigger is activated by updating existing data (UPDATE) in the table authors.

public partial class Triggers
{
    [SqlTrigger(Target="authors", Event="FOR UPDATE")]
    public static void AuthorsUpdateTrigger()
    {
        //...
    }
}

This example specifies that the trigger is referenced using the name trig_onpubinsert. The trigger is activated by adding new data (INSERT) to the table publishers.

public partial class Triggers
{
    [SqlTrigger(Name="trig_onpubinsert", Target="publishers", Event="FOR INSERT")]
    public static void PublishersInsertTrigger()
    {
        //...
    }
}

This attribute must be applied to user-defined function objects, which return either a scalar value or a table. This attribute has the following parameters:

• Name - Optional. Specifies the name that is used on the SQL Server to reference the user-defined function.

  Note
  For functions that return a table value, you must specify a value for the TableDefinition property, which contains the Transact-SQL representation of the definition of the returned table.

This example specifies that the user-defined function is referenced using the name sp_scalarFunc.

public partial class UserDefinedFunctions
{
    [SqlFunction(Name="sp_scalarFunc")]
    public static SqlString SampleScalarFunction(SqlString s)
    {
        //...
        return "";
    }
}

This example specifies that the user-defined function is referenced using the name sp_tableFunc. The TableDefinition property has the value letter nchar(1).

public partial class UserDefinedFunctions
{
    [SqlFunction(Name="sp_tableFunc", TableDefinition="letter nchar(1)")]
    public static IEnumerable SampleTableFunction(SqlString s)
    {
        //...
        return new ArrayList(new char[3] {'a', 'b', 'c'});
    }
}

This attribute must be applied to user-defined type objects. This attribute has four properties: Format, MaxByteSize, IsFixedLength, and IsByteOrdered.

Format

Required. The storage format of the user-defined type. The supported formats are as follows:

Native – Specifies that SQL Server uses an efficient native representation on disk. This is the most compact option that typically results in the best performance. The requirements for this format are as follows: 

• The StructLayout.LayoutKindSequential attribute must be applied to the type.

• All the fields of the user-defined type must be blittable, that is, they must have a common representation in both managed and unmanaged memory and not require special handling by the interop marshaler.

• The type must not specify a value for MaxByteSize.

• The type must not have any [NonSerialized] fields.

• Fields must not be marked as an explicit layout.

UserDefined - Specifies that the user has full control over the binary format. The requirements for this format are as follows:

• The type must implement IBinarySerialize.

• The type must specify a value for MaxByteSize.

MaxByteSize

Required. The maximum size of an instance of this type, in bytes.

IsFixedLength

Optional. Specifies whether all instances of the type are the same length. The default is false.

IsByteOrdered

Optional. Specifies whether the binary representation of this type is ordered, that is, whether it can be used to compare instances of this type. The default is false.

This example specifies that the Format of the user-defined type is SerializedDataWithMetadata and the MaxByteSize is 8000 bytes.

[SqlUserDefinedType(Format.Native, MaxByteSize=8000)]
public class SampleType
{
   //...
}

Tasks

Concepts