Putting It All Together
Now that we've walked through the basics of creating .NET UDFs, let's look at the third integrated sample we developed for you. From the project files available on the Web, open the WindowsAppTestCompression.sln file, where you'll find Windows application projects that can insert and retrieve documents from the Adventure-Works database.To make things a little easier to visualize (and to avoid modifying the original AdventureWorks schema), the sample uses a copy of the Production. Document table.The table is called Document-CompressEg, and it has three additional columns to track document size (before and after compression) along with the method of compression used.
To create this table (and the stored procedures that the Windows application uses), run the CreateTableAndSps.sql script file located in the project's Test Scripts folder. You can then insert and retrieve both compressed and uncompressed documents and see the amount of compression achieved for each document, as Figure 2 shows.
Where Do You Go from Here?
SQL Server 2005 provides greater flexibility and choice in deployment, storage, and programming options. For the project we've described, SQL Server 2005's CLR integration gave us the power to deploy complex compression logic to our database server, using a sophisticated programming language. In addition,the new varbinary(max) data type helped us easily manipulate, store, and retrieve large binary objects. Of course, you don't want to run out and start moving all complex business and technical logic to the database server. But with these enhancements, you now have more choices when designing and architecting your systems.