SQL Server Project (1)

Visual Studio 2005 provides a template for creating SQL Server Project, which you can create stored procedures, functions, triggers and more. When the project is deployed to a SQL server, all the SQL database objects defined in the project are created on the server. The project is a class library. Therefore, the assembly is also created on the SQL server database. All the database objects defined in the project are dependent on the assembly.

There are some articles on this issue on the web. The following are introduction ones good for beginners.

• Managed Stored Procedures and User Defined Functions - Part I


• Writing CLR Stored Procedures in C# - Introduction to C# (Part 1 & 2)

However, if you want to get into depth of real implementation for your business cases, you will encounter many issues and there are not any good resources available, at least I have not found out yet. I started to work on SQL server project from the idea to call .Net assembly classes from SQL server stored procedure. When I googled this topic. I found the above links. That's actually what I want to do. It looks very attractive. I jumped in the ocean and that has been quite good experience.

Any way, I found out many issues I have to resolve in order to create SQL database objects for real business cases. The links above give you very good start point and some very basic skills such as to create connection to SQL server, to get data, to execute SQL commands, to insert or to delete data, and to send result back to SQL server.

The cool thing of using .Net to create SQL database objects (stored procedure or SP, function and triggers) is that you can use the current context connection to a SQL server since the SP is running in the SQL server. Then you can execute almost any SQL commands. It is very fast. All of these can be done in .Net, for example, c#.

I tried to query data and to insert data with SQL database tables, and compared to the same simple SQL SP scripts as well. For a query of repeating 1000 times, the .Net SP is a little bit faster than the SQL scripts, 00:12:00 compared to 00:12:17. For the insert statement, the SQP scripts is faster than .Net SP, by a very little difference (just seconds for 1000 rows). However if you think in more perspective view, .Net objects are much more attractive than SQL scripts.

• .Net SQL Sever Project is very similar to PL/SQL's package. You can deploy only the SPs you want to expose, for example, and to hide all the other classes or related methods data in the library. While SQL SP scripts are public only. If you write several SPs and they have dependent relationship, you cannot hide the dependent SPs in a SQL server database. I wrote a calculation SP. Then I separate many codes to other SPs and user defined functions. As a result, the calculation SP is dependent on several other SPs and functions. Those dependent SPs and Function are not supposed to be used for public directly, but I have no way to hide them. This makes the management and usage of SPs and functions very difficult.


• .Net SQL Server Project separates source codes from SQL Server. What you have to do is to deploy your assembly and dependent assemblies to a SQL Server Database. All the assemblies, SPs and functions are in binary forms. This avoids other people to change them directly. All the source codes can be saved in a source code repository such as SVN.


• Since .Net SQL Server Project is written in .Net managed codes such as C#, the performance of SPs could be improved. For example, in SQL scripts, there are very limited way to cache data. One way I found to share a group of data between SPs is to use temporary table. I read some articles on this technique. It is not recommended by many people. A temporary table is created in local hard disk. If you constantly access to it such as querying, deleting and saving to the table, your script's performance would be affected. However, in a .Net project, you can easily cache data in memory. For some small amount of data, which will be used constantly during the process, I can run one query to get the data and save them in cache as a class object. Then it can be used by other classes. You don't need to do query again or read from a temporary table. That's a great improvement in terms of performance since less query is needed.


• By using .Net Server Project, some of existing libraries can be reused. Those reused resources are hidden in assembly form or referenced to, so you can improve or update those resources without changing the public interface of deployed SPs. However, those dependent resources or library assembly files if referenced have to be registered on the SQL server database. I'll continue to discuss on this issue in later articles.


• The .Net Server Project should provide very simple interface for deploying SQL server database objects such as SPs, Functions and Triggers. Those codes should contain SQL server related classes and objects. All the other business logic, services, and resources should be in another assembly files. In this way, the same codes can be reused in other projects such as Windows applications, console applications, and generic libraries.

I have mentioned so many advantages about SQL Server Project. However, when you get your hands dirty or do it for a real business case, you may find out it's not so easy as you do in other .Net projects. There is a difference how these assemblies are used or executed in a SQL server database or just an application in Windows. There are many issues to be resolved as well before you can fully take the advantages of .Net. For example, you may find out that there are very little references available you can add to your project at the first time. How can you reuse your libraries, third party libraries, even many other Microsoft libraries? That's the next topic for this series.