Roadshow Recap (Including Some Answers!)

  • Article

On Monday, Chris, Bob, and I wrapped up the latest edition of the Northeast Roadshow in Waltham.  Over the past couple of weeks, we’ve crisscrossed the region a couple of times (ok, not exactly like the map below, but you get the idea).  In total, we visited five cities and had a chance to speak to over 500 of you in the process.  As always, it was great fun for us, and we’re thrilled to see so many of you taking a day out of your busy schedules to join us.

Northeast Roadshow Triangle!

If you misplaced the URL for the slides and demos, grab them here.  We hope to provide a few screen casts as well over the coming weeks, and I’ll let you know when those are available.

During my sessions on SQL Server for Developers and .NET Debugging Tips and Tricks, I accumulated a number of questions across the venues and promised to follow up on them; so here goes.  If I missed something, please let me know!

  1. What’s the upper limit on error numbers in SQL Server?
  2. How do CLR constructs show up in the query plan?
  3. Can I use F# in SQL Server 2008?
  4. I tried to use the Step Into Specific… debugging option, but it’s not there!
  5. Are there improvements planned for LINQ debugging support?
  6. When I execute code from the Watch Window, breakpoints are not triggered, but when I execute from the Immediate Window, they are.  What’s up with that?
  7. What is the scope of variable references created in the Immediate Window?
  8. Can I override the setting Step Over Properties and Operators on an ad hoc basis?

What’s the upper limit on error numbers in SQL Server?

As you probably guessed, since the datatype of the error column in sys.sysmessages is int, the maximum value of an error number is 2,147,483,647 (2^31 – 1).  The stored procedure sp_addmessage can be used to add custom messages (with numbers 50001 and greater) to your server.

How do CLR constructs show up in the query plan?

They’ll appear just as other T-SQL constructs appear; the .NET types can be indexed just like native types, so the query plans will reflect their use.  Likewise, stored procedures, aggregates, and other modules you may build in C# and Visual Basic will appear in the plan. 

In terms of additional monitoring, this article on MSDN is a great resource.  Of course, since these constructs are executing in the CLR, you may wonder whether you can use something like the CLR Profiler to get even more information.  From some quick analysis, it seems to work; CLR Profiler will enable you to profile both an application and a service.  Note though, that CLR Profiler is fairly invasive, so you may want to optimize your CLR objects outside of SQL Server.  Lastly, I did run across a blog posting that provides some insight on using a third party product, AQtime, to help profile CLR assemblies hosted by SQL Server. [N.B., this is not an endorsement of that product or any other].

Can I use F# in SQL Server 2008?

Well, my initial answer was sure, it’s all CLR under the covers.  Visual Studio 2008 does provide templates for C# and Visual Basic database projects, but those are more for convenience than required.   As this article documents, you can register your assembly and CLR methods via T-SQL or SQL Management Studio.

But it’s a bit trickier than that.  I gave it a shot using Lewis Bruck’s blog post as a starting point. First of all, you need to register the core F# assembly with SQL Server; I used the following T-SQL within SQL Server Management Studio.

 alter database AdventureWorks2008 set trustworthy on
go
create assembly fslib from
    'C:\Program Files (x86)\FSharp-1.9.6.2\bin\Fsharp.core.dll' 
   with permission_set = unsafe
go

Note that the assembly is marked unsafe.  This is a requirement since F#’s core library implementation makes use of static fields that have not been marked as read-only, something not allowed at the safe level.

So, then I coded my simplistic F# procedure in a new F# library project.  The procedure accepts a string argument and returns “Hello, <string>” via the SqlContext (a construct used in CLR objects hosted in SQL Server to communicate back to the client).image

In this case, I gave my code file the name Sqlclr.fs, which is important since I use it to register the assembly via T-SQL below.  Here, I did not declare it as unsafe, since my simple implementation doesn’t ‘break any rules’; your mileage may vary depending on the IL constructs that get generated from your F# implementation.

 create assembly fsharp from 'c:\MyFSharpLib.dll'
go

I then followed this up with a T-SQL declaration of the procedure.  [The C# and Visual Basic database projects would automatically do this, and also register the assembly for you by essentially issuing the same statements under the covers.]

 create procedure sayHello (@name nvarchar(max))
    as external name fsharp.Sqlclr.sayHello
go

Note the external name is composed of the

  • assembly name I created in the previous step,
  • module (the name I gave my F# file), and
  • the method implementing my procedure

Now, I can execute the procedure, and get the expected output (Hello, Jim):

 exec sayHello 'Jim'
go

This obviously just scratches the surface, and questions remain on whether all constructs you might use in C# and Visual Basic are supported.  Since one of the sweet spots for F# is easier development of parallelizable constructs, I’m curious to as to how (or even if) SQL Server will negotiate the parallel extensions to .NET.

I tried to use the Step Into Specific… debugging option, but it’s not there!  

Thanks to Sean for pointing out a need for clarification on my debugging session.  We covered a lot of options in the debugger, but not all options are currently available in all environments, and I wasn’t as explicit as I could have been in that regard.  Here’s an attempt to rectify that.

  1. The Step Into Specific… option was introduced with Visual Studio 2008 SP1, so you need the service pack to see it.
  2. The Object Id capability, in which you can create a reference (like 1#) to an object that does not go out of scope, is available only for C#.
  3. In C#, I can create a new object in the Immediate Window (see below) and get a reference to $x that I can use elsewhere in the debugger; however, in Visual Basic you cannot create explicit variable declarations in the Immediate Window.  You can create implicit variables; however, you won’t get a reference (the “$x”) that you can use in the Watch Window.image

Are there improvements planned for LINQ debugging support?

There are some limitations in what you can do when debugging LINQ statements.  For instance, you can’t step into predicate code and for the most part you can’t use Edit and Continue (although Visual Basic is a little more lenient on this that C#).

A few improvements in the debugging experience, somewhat related to LINQ, were included in Visual Studio 2008 SP1 and are documented here.  I’m not sure what the future, namely Visual Studio 2010, holds.

When I execute code from the Watch Window, breakpoints are not triggered, but when I execute from the Immediate Window, they are. What’s up with that?

I took this one to the guru, John Robbins.  The reason goes back to when Visual Basic was brought into Visual Studio 2002.  Visual Basic folks were quite used to using the Immediate Window as a debugging tool, breakpoint triggering and all, and were pretty dismayed to have that functionality not get carried along.  The Visual Studio team added the breakpoint capability back into the Immediate Window with Visual Studio 2003, and considered doing the same thing in the Watch Window, but that would have unexpectedly changed the experience for folks used to breakpoints not triggering when executing code there.   So, what we have is essentially a compromise, and you can get either behavior regardless of your language (Visual Basic or C#)

What is the scope of variable references created in the Immediate Window?

As noted in item 4 above, you can create variable references in C# within the Immediate Window.  Those variables will appear in the Locals window (with the $ preface), but they never go out of scope.

Can I override the setting Step Over Properties and Operators on an ad hoc basis?

The behavior to step over properties and operators defaults to ‘on ‘ with Visual Studio 2008 SP1 and can be modified in Tools>Options>Debugging>General, but it’s either always on or always off.  If you have the default set and find you’d like to step into a property setting or reference on occasion, use the Step Into Specific… mechanism (which was also introduced with Visual Studio 2008 SP1).