As I mentioned in a few previous blog entries, SQL Server 2012 runs the .NET 4 version inside of SQL Server. Your SQLCLR code may be affected by some behavior differences between .NET versions 2.0/3.5/3.5 SP1 and .NET 4.0. I like the way SQL Server chose to deal with these differences. Although the documentation on these differences […]

In SQL Server 2012, SQLCLR now loads .NET 4.0 code rather than .NET 2.0 as in previous versions of SQL Server. Note that the "main system" assemblies (e.g. mscorlib.dll, System.Data.dll) in .NET did not appear as a separate directory, between version 2.0 and 4.0; instead, the .NET 2.0 librares were updated. As an example, SQL […]

Once upon a time (well, now, currently), there were two memory allocators in SQL Server, the single-page allocator and the multi-page allocator. The single-page allocator was used for almost everything (data buffers, caches, etc); the multi-page allocator was used for somewhat more esoteric things. Like most of SQLCLR (that's CLR code that runs inside SQL […]

I've been trying out the windowing extensions (the OVER clause extensions and friends) with different kinds of functions (aggregate functions, first_value, last_value() etc), and thought I'd use a SQLCLR aggregate. After all, they're just "regular" aggregates, right? You can't build a user-defined ranking aggregate in SQLCLR, so they can't be confused with one of those. […]

In my blog entry from about a month and a half ago, I summarized my finding on using Visual Studio 2010 with SQLCLR, and the partial support of SQL Server 2008 new features. One feature (with the disclaimer that's it's not preferred/supported) that I thought I'd lost was "attach to process" style debugging with SQLCLR. […]

There's a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were […]

A friend of mine was asking about the affect of multi-targeting in Visual Studio 2008 on SQLCLR. For an explanation of how multi-targeting works, reference David Kean's blog entry on Visual Studio 2008 multi-targeting and FXCop. I happened to have an instance of SQL Server 2005 RTM (which uses .NET framework version 2.0.50727.42) on hand […]

When SQL Server 2008 shipped, Visual Studio 2008 SP1 and .NET 3.5 SP1 shipped a few days later. Visual Studio SP1 contains some neat enhancements that allow you to use SQL Server 2008 databases in Server Explorer and the related designers. There's support for SQL Server change tracking in the Sync Services designer. The EDM and LINQ […]

I came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and […]

.NET (and therefore SQLCLR) divides up running its code (even within the same process like the sqlserver.exe process) into appdomains. The appdomain is like a lightweight process used to enforce isolation between running .NET code within the same Windows process. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate execution of .NET code […]

Recently I had the occasion to load .NET 3.5 on to a machine that had an existing instance of SQL Server 2005. .NET 3.5 does not work by versioning the “main .NET assemblies” (e.g. there is no “version 3.5” of mscorlib.dll, System.dll) but by replacing the 2.0 versions of them. You can observe this by […]

One last SQLCLR feature I'd forgotton about but was quite highly publicized. This is extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You just the MaxByteSize of -1 in the appropriate attribute, like this: [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)] The serialization format is going to have to be UserDefined, the limt for Format.Native […]

Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valued function with an ORDER clause. If you have intimate knowledge that the function always returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clause in the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap […]

One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The […]

A couple of people have been asking and, in preparing for my upcoming talk on spatial data on SQL Server 2008 at ITForum in Barcelona in a few weeks I got to ask about using the new system UDTs types on the client. In an upcoming CTP release there will be an MSI installer file specifically to install […]

So, people always ask… now that .NET Framework 3.0 installed on my system and there's going to be a new version of .NET that includes LINQ, what version of the framework will SQL Server load now, in SQL Server 2008? Does 2.0 still load? Or does it load 3.0 or 3.5? Theoretically, and I've written […]

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup. I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE/TIME-related data […]

I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly […]

I was listening to a replay of the webcast recording on the HierarchyID by Michael Wang (thanks, Michael) and as he mentioned the considerations for the CLR-based type with respect to DDL, I thought it would be interesting to go back and see how this type showed up in the various facilities that we have […]

Just found out about this one today. This SQLCLR function works right now in CTP4 of SQL Server 2008. public static Nullable<int> AddTwo(Nullable<int> x, Nullable<int> y) { return x+y; } create function dbo.addtwo (@x int, @y int) returns int as external name asmname.[Mynamespace.Class1].AddTwo; select dbo.addtwo(2, null); -> null Great! Turns out, I suggested this back in […]

It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago. What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)? Some of the requirements came from […]

OK, SQLCLR wish list comes last. Mostly because I'm tired of being accused of being a SQLCLR bigot; I think it was because our book covered those topics first. Oh well. Default parameter values in SQLCLR procedures when called by T-SQL. You obviously can't/shouldn't support these when called from-CLR-to-CLR. Right now, they work, but the […]

When I did my first demonstration with the combined SqlClient and SqlServer provider in the April CTP version of SQL Server, I was a bit surprised. I wrote a simple stored procedure to run in the server, exactly the way I've always written it to run on the client (modulo sending results back to the […]

The last blog entry brings up the question of what I mean when I say something is "unsupported" in SQLCLR. Becuase I've said that J# is "unsupported". This doesn't mean that it won't ever work or that you couldn't actually get technical support for it, given enough time, energy, and money. Technically, its possible to […]

Looking at the Feb CTPNotes file again, there's another change that caught my eye. DROP ASSEMBLY has changed with respect to dependent assemblies. In past, you had to drop assemblies one at a time, so if assembly A called assembly B, you first dropped A then B. Now dropping A drops B automatically if B […]

The data access team (known as DataWorks) has started up a team blog. Some of the individual team members, like Angel Saenz-Badillos and Sushil Chordia, have been blogging for a while, this one has posts from all members. One of the first posts was information about the upcoming SqlClient and SqlServer provider unification by Pablo Castro. […]

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple […]

In the last blog entry I talked about using System.Transactions in SQLCLR code. But don't try this yet, the keyword here is *will* be used. I base this on a few bugs that I filed on System.Transactions/SQLCLR being closed as “this will be fixed in beta 3”. And a statement on a public newsgroup by […]

People (especially DBAs) want to see what those pesky appdomains are doing in SQLCLR. Back in beta1 there was a system function, master.sys.fn_appdomains(), that showed which appdomains were running and which assemblies were loaded in the appdomains, number of bytes used, etc. In beta2 this view stopped working and, although you can watch appdomains being […]

It turns out that the week before last, the SQL Server and ADO.NET teams (they may be part of the same team) made public the decision to merge the SqlClient data provider and in-proc SqlServer provider code into a single provider. Details are sketchy currently, but the reasoning behind this is that it's easier for […]

Syntactic changes are coming to the SQL Server in-proc provider. Compiling a stored procedure with the latest Dec CTP bits yielded the following message every time I used a method that started with "Get" from SqlContext: warning CS0618: 'System.Data.SqlServer.SqlContext.GetPipe()' is obsolete: 'Will be removed soon' Looking further with Reflector, it appears that SqlContext will now […]

I answered a question on the newsgroups on how *exactly* inheritence works when you use it implement UDTs in SQL Server 2005. Also wanted to record the explanation here…. It doesn't work like you'd expect inheritence to when to use T-SQL, because SQL Server is blissfully unaware of the inheritence relationships (they're not recorded in […]

A few weeks ago, I'd posted links to ADO.NET 2.0 articles I'd written for MSDN online. At that time, I mentioned there would be one more article in a series, but I'd save the subject as a "surprise". Well, the article just appeared: Tracing Data Access in ADO.NET 2.0. Did you know there was built-in […]

I was demonstrating SQLCLR appdomain usage (see previous post) to a class last week.Later on, I mentioned a different concept, that of dependent assemblies. This brought up the following question: If user A owns assembly A and B owns assembly B, what happens if B contains a routine that calls A? Are two versions of […]

A little-known behavior of SQLCLR (although we did mention it in our book) is that SQLCLR creates one appdomain per assembly owner, not one appdomain per database. One appdomain per database was the observed behavior in beta 1, although its important to remember that the algorithm for appdomain creation/destruction in SQLCLR is technically undocumented and subject […]

Looking for some cool SQLCLR and/or security-related features to show off. Before I hit the road again Friday, for a long stint in “airplane-land“ on way to Europe. Browsing through the SqlServer provider in Community Preview beta version of SQL Server 2005, I think I've found one. Came across SqlContext.GetWindowsIdentity. With this call you can […]

One more thing about getting an error 6522 wrapper from SQLCLR procedures. the workaround I spoke about last week (dummy catch block) works to propagate SQL Server errors to the client without wrapping them in the 6522. But it doesn't seem to work if you want to throw your own user error (error > 50000). […]

I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest […]

It turns out that you can force SQL Server to use FX libraries that are not on the approved list. Deploying user code that uses these through Visual Studio produces: Error: Assembly 'system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. But that's because Visual Studio uses CREATE ASSEMBLY "from bytes", passing in […]

After reading Niels’ comment about the framework class library list, a quick check proved he was right, System.Runtime.Remoting.dll was not on the approved list. Adding a reference to this library (using his cool Object Browser trick) and writing a one-line stored procedure: [SqlProcedure] public static void SomeTestProc() { TcpChannel t = new TcpChannel(); } proved […]

Back from vacation, drove from Ashland back to Portland Saturday, then Portland to Redmond Sunday. A lot of driving. The Michael Brundage XQuery book was even better the second reading. Joe Celko's book was excellent (what else?) also. So was OSF presentation of Henry VI, Much Ado About Nothing, and King Lear, but my favorite was […]

I just answered this question on the "public/private" sqlclr newsgroup, but now I'm wondering myself. What IS all that stuff doing in sys.assembly_files? If you've done autodeployment of a SQLCLR assembly lately, take a gander as the metadata view sys.assembly_files. There's not only the binary assembly code and the debug symbols (pdb file) but also source code. […]

There are a number of different ways that SQL Server 2005 checks to see whether on not you're trying to catalog or execute dangerous .NET code. This checking is enforced by SQL Server at CREATE ASSEMBLY time and also at execution time. During CREATE ASSEMBLY, SQL Server reflects on the assembly and check for certain coding practices […]

If you're developing procedural code, UDTs, or UDAggs in SQLCLR for SQL Server 2005, Visual Studio 2005 has these nice auto-deploy projects called SQL Server projects that you can use. Comes with templates for each SQL Server 2005 item you can develop, just choose a SQL Server and database at project creation and hit “Deploy” menu […]

To squeeze every ounce of performance out of the SqlServer data provider, the rule is to allocate the least number of ancillary object instances possible. In SQLCLR Optimizations 1, I mentioned a mechanism to keep from creating a SqlCommand and associated objects, just to initialize a SqlDefinition. Another performance increase comes by not allocating/using extra buffers. […]

Good T-SQL programmers are always looking to optimize their procedural code. Little optimizations can end up as big savings if the code will be executed many times or if it's used in many places in the application. Good SQLCLR programmers will be doing this too. Recently I ran into a micro-optimization that has promise. Everyone […]

I was talking with Dan Sullivan this week about UDTs in SQL Server 2005 and he's made an interesting discovery. About a week ago, I'd happened to mention to Dan that UDTs and UDAggs (user-defined aggregates) are supposed to have a pattern of "deserialize-do action-serialize". He thought I'd misunderstood. Then he went off to write […]

Came across something recently that I thought was odd (or a bug) when dealing with UDTs in SQL Server Management Studio. If I have a UDT, say ComplexNumber, I deploy it to SQL Server 2005. Use it as a column in a UDT table, complextab. Insert a row: INSERT complextab VALUES('1:1i') Then attempt to SELECT […]

Sometimes you have to hit me over the head to make me aware that something's changed. I've been working with Visual Studio 2005 beta 1 for about 10 days now, and just noticed that creating a SQLCLR project (that's [Language of Your Choice]/Database/SQL Server Project) no longer includes a reference to Microsoft.VisualStudio.DataTools.SqlAttributes.dll. This was the DLL that […]

When you get a brand new tool, programming language, or API, the first thing most programmers use it for is to write their favorite "rite of passage" program. This is an example that is so simple, it's only purpose is to illustrate the that compiler or tools are installed and working correctly and that "the […]