Recently there has been some discussions about SQL Server attributes in the beta newsgroups for SQL Server 2005 and VS 2005. Having tried to answer them, I thought it might be a good idea to write a blog entry about it as well.
When you create a new SQL Server project in VS 2005, you can add items for Stored Procedures User Defined Functions, Triggers. User Defined Types and User Defined Aggregates. When you add, for example, a stored procedure you get some skeleton code looking something like this,
[SqlProcedure]
public static void YourProc() {
// Put your code here
}
where SqlProcedure is the attribute. These attributes are used by VS 2005 during deployment and by SQL Server at runtime. The discussions in the newsgroups has been about which attributes are used when. The available attributes are:
- SqlProcedure
- SqlTrigger
- SqlFunction
- SqlUserDefinedType
- SqlUserDefinedAggregate
- SqlMethod
- SqlFacet
The differences and they way they are used is (as far as I can tell) as follows:
SqlProcedure and SqlTrigger are used only at deployment time by VS (and third party tools like my Yukon build task).
SqlFunction is used both during deployment as well as runtime. At runtime it indicates if the function is doing any data access and also tells SQL Server if the function is deterministic and/or precise.
SqlUserDefinedType and SqlUserDefinedAggregate is used at deployment time as well as runtime.
SqlMethod is used only at runtime, and can only be used by instance methods in a User Defined Type to indicate whether the method is doing data access or not. It inherits from SqlFunction, but you can not apply it to a method which is being used as a User Defined Function.
SqlFacet finally is only used during deployment and in VS it can only be used to indicate the return type of a UDT expression. In my Yukon Build Task however, it is used to indicate parameter types for any type of method as well as well as return types for functions, regardless if it is a UDT or not.
You who were around during Beta 1 of SQL 2005 and the Alpha of Whidbey may remember the SqlFunc attribute and wonder where that went. That was a pure VS 2005 thing and was used when deploying User Defined Functions. In Beta 2 of SQL and Beta 1 of Whidbey it was replaced by the SqlFunction attribute.