Attributes for SQL Server Projects and Database Objects 

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.

This attribute must be applied to aggregate files. This attribute has two properties: Format and MaxByteSize.

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

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

This example specifies that the Format of the aggregate is Native and the MaxByteSize is 8000 bytes.

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

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.

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

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 object - Required. Specifies the table that the trigger applies to.

• Trigger activation - 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 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.

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 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 SerializedDataWithMetadata and the MaxByteSize is 8000 bytes.

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

Tasks

Reference

Concepts

Other Resources