This past week I've been trying out Visual Studio 2010 for SQLCLR development. Verdict: A couple of nice things, a couple not so nice.
In the interest of keeping things somewhat positive around here, we'll start with the good stuff:
- Pre-deployment and post-deployment scripts are built in. This is great, especially if you're working with features such as ordered TVFs, which Visual Studio 2008 never properly supported. In 2010 you can stick the ALTER FUNCTION in a post-deployment script and you'll be all set. This feature will also be useful if you need to sign assemblies or do other deployment-time modifications such as dropping and re-creating an assembly that was created outside the scope of your current project.
- If you tried creating multiple SQLCLR projects with Visual Studio 2008 you no doubt learned quickly that it would, by default, name every assembly SqlClassLibrary. Thankfully, Visual Studio 2010 names the assembly whatever you named the project--as it should.
And now, the bad
- Visual Studio 2010 defaults the targeted .NET Framework version to 4.0. This creates an error when you try to deploy. The error? "Deploy failed". That's it--no other information is returned. If you're hitting this error, right-click on your project, click Properties, and change the "Target framework" setting to .NET 2.0 if you're developing against SQL Server 2005, and .NET 3.5 if you're developing against SQL Server 2008. That will probably fix things up, unless...
- If you're testing your solution against tempdb, deployment will completely fail. Itzik Ben-Gan discovered this one and the reason it fails is quite interesting: Visual Studio, during the deployment process, tries to enable vardecimal storage at the database level. And that's not allowed in tempdb. As with the Framework version error, this will throw a simple "Deploy failed" and not tell you much else. Unless you turn on Profiler and watch what's happening. Not a good situation, and the only fix I know of at this point in time is to edit the deploy file, with will be named [your project name].sql and will be output in the debug or release bin folder when you try to deploy.
- Anecdotally, deployment feels a lot slower. I haven't yet spent the time profiling to figure out where the time is being taken, but I can say that after a couple of years of development with Visual Studio 2008 I'm used to deployment taking a few seconds. It's taking up to 45 seconds on 2010, and it's definitely a major annoyance when you need to make and test a lot of small changes.
I'm not sure how the second issue will be impacted by various security settings on databases other than tempdb, but I suspect that we'll see other issues as a result of the attempt to turn on vardecimal. It's also concerning that the Visual Studio team would create a product that will silently enable a database feature, with no warning and, as far as I can tell, no reasoning. If anyone from the VS team is reading this, please reply in the comments and let us know why this decision was made.
All in all: The deployment scripts are a nice plus, but the better default assembly name is counter-balanced by the incorrect default framework version--you still need to remember to go in and tweak things. And while the tempdb limitation isn't a deal breaker, it's incredibly annoying. For now, especially due to the performance issue, I'm thinking I'll stick with Visual Studio 2008.
Hope this helps those of you who are doing SQLCLR development and are planning to upgrade to Visual Studio 2010 sooner rather than later. Please post in the comments here if you find other hurdles so that we can keep a running list of issues.