Attributes for SQL Server Projects and Database Objects

An attribute must be applied to each Microsoft SQL Server project and to all database object files that a project contains:

Project / File	Attribute that must be applied
Project	SqlAssemblyAttribute
Aggregate	SqlUserDefinedAggregateAttribute
Stored Procedure	SqlProcedureAttribute
Trigger	SqlTriggerAttribute
User-defined Function	SqlFunctionAttribute
User-defined Type	SqlUserDefinedTypeAttribute

SqlAssembly Attribute

This attribute must be applied to all assemblies that are deployed to a Microsoft SQL Server 2005 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 aggregate files. This attribute has two properties: Format andMaxByteSize.

• Format
  Required. The storage format of the aggregate. The supported formats are:

  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:

  • 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:

  • 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 files. 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 files. 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 table that the trigger applies to.

• 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 files, 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 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 files. This attribute has four properties: Format, MaxByteSize, IsFixedLength, and IsByteOrdered.

• Format
  Required. The storage format of the user-defined type. The supported formats are:

  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:

  • 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:

  • 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 SerializedDataWithMetadataand theMaxByteSize 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 SQL Server Project

How to: Create and Run a CLR SQL Server Stored Procedure

How to: Create and Run a CLR SQL Server Trigger

How to: Create and Run a CLR SQL Server Aggregate

How to: Create and Run a CLR SQL Server User-Defined Function

How to: Create and Run a CLR SQL Server User-Defined Type

Walkthrough: Creating a Stored Procedure in Managed Code

How to: Debug a SQL CLR Stored Procedure

Concepts

Introduction to SQL Server CLR Integration (ADO.NET)

Advantages of Using Managed Code to Create Database Objects

Item Templates for SQL Server Projects

Reference

Attributes for SQL Server Projects and Database Objects

Other Resources

SQL CLR Database Debugging

Change History

Date	History	Reason
July 2008	Updated the information regarding the MaxByteSize attribute for the SQLUserDefinedAggregate attribute.	Content bug fix.