Attributes for SQL Server CLR Integration Database Projects and Database Objects

An attribute must be applied to each SQL Server project and to all database objects that a project contains. You can use the SQL Server Common Language Run-time integration (SQL CLR) to create the following types of objects:

SqlAssembly Attribute

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.

SqlUserDefinedAggregate Attribute

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
    '...
End Class

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

SqlProcedure Attribute

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.

Partial Public Class StoredProcedures

    <SqlProcedure(Name:="sp_sqlName")>
    Public Shared Sub SampleProcedure(ByVal s As SqlString)
        '...
    End Sub
End Class

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

SqlTrigger Attribute

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.

Partial Public Class Triggers

    <SqlTrigger(Target:="authors", Event:="FOR UPDATE")>
    Public Shared Sub AuthorsUpdateTrigger()

        '...
    End Sub
End Class

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.

Partial Public Class Triggers

    <SqlTrigger(Name:="trig_onpubinsert", Target:="publishers", Event:="FOR INSERT")>
    Public Shared Sub PublishersInsertTrigger()

        '...
    End Sub
End Class

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

SqlFunction Attribute

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.

Partial Public Class UserDefinedFunctions

    <SqlFunction(Name:="sp_scalarFunc")> 
    Public Shared Function SampleScalarFunction(ByVal s As SqlString) As SqlString

        '...
        Return ""
    End Function
End Class

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).

Partial Public Class UserDefinedFunctions

    <SqlFunction(Name:="sp_tableFunc", TableDefinition:="letter nchar(1)")> 
    Public Shared Function SampleTableFunction(ByVal s As SqlString) As IEnumerable

        '...
        Return New Char(2) {"a"c, "b"c, "c"c}
    End Function
End Class

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'});
    }
}

SqlUserDefinedType Attribute

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

   '...
End Class

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

See Also

Tasks

How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration

How to: Create and Run a SQL Server Stored Procedure by using Common Language Run-time Integration

How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration

How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration

How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration

How to: Create and Run a SQL Server User-Defined Type by using Common Language Run-time Integration

Walkthrough: Creating a Stored Procedure in Managed Code

How to: Debug a SQL Server CLR Integration Stored Procedure

Concepts

Introduction to SQL Server CLR Integration (ADO.NET)

Advantages of Using Managed Code to Create Database Objects