July 2004 - Posts

Sites targeting SQL Server 2005 pops up "left right and centre". One site that looks very promising is http://www.yukonxml.com, it can also be reached through: http://www.ss2005.com.

The marketing blurb says it is dedicated to all things SQL Server 2005. I would assume we'll see quite a lot of SQl 2k5 XML related stuff here. From a brief browse through, it looks really promising!

They also give away a copy of our book (hmm, I hope they have paid for them <g>). So sign up and win "A First Look at Microsoft SQL Server 2005 for Developers".

So, I'm now testing BlogJet (again) as a blogger tool.

What tools are you guys using out there to blog with. I have gone from the built in blog editors of the various engines, to my home grown InfoPath editor, to BlogJet, to VisualBlogger 2004, to BlogJet.

Comments please!!!

In an earlier post I discussed ALTER ASSEMBLY to an extent, and Angel and I had a discussion regarding a post he wrote about deployment of UDT's via Visual Studio.

In my post I wrote you didn't have to drop SQL objects created against methods in the original assembly if you use ALTER ASSEMBLY and follow certain rules as per the BOL (SQL Server 2005 Books On Line). However the BOL is not entirely correct and in our book in Chapter 2 we probably could have covered it a bit more. The reason we didn't was because ALTER ASSEMBLY wasn't fully implemented at the time of writing. So let's look at ALTER ASSEMBLY and various types of SQL objects created against methods in a CLR assembly.

CLR methods catalogued as Stored Procedures: As long as the method signature doesn't change ALTER ASSEMBLY works fine, and it will re-bind against the latest implementation. One caveat though: At the moment it seems to be a problem with CLR methods with ref or out params. I.e. methods catalogued as procs with out params.

CLR methods catalogued as Triggers: Works as stated and during ALTER ASSEMBLY it'll re-bind against the latest implementation.

CLR methods catalogued as UDF's: Now it starts to get interesting. Unless you have a direct or indirect schema bound reference for a persisted table column, index or check constraint against the UDF, it works exactly like a procedure or trigger. However if, for example, the UDF is part of a column definition where the column is created as PERSISTED, you'll get an error when executing ALTER ASSEMBLY. The error says there exists a dependency against the function, and you need to run ALTER ASSEMBLY with the UNCHECKED DATA option (more about UNCHECKED DATA later):


2FROM 'assembly_path'

Notice that you'll get the error regardless if there exists any data etc, or if you have changed the CLR implementation of the method in question.

CLR classes catalogued as UDT's: This depends upon the Format of the UDT. Remember from Angels posts (here and here), how a UDT can either be Format.Native (where SQL Server is reponsible for the storage of the data) or it can be Format.UserDefined (where the developer is responsible for defining how the data should be stored).

Format.Native: Unless you do any changes to the type, and you do not have any functions using the type against persisted columns, ALTER ASSEMBLY works. In other words; you can have tables with columns of that particular type and ALTER ASSEMBLY works OK, if you do  not change the type. However if you do changes to the type, ALTER ASSEMBLY will fail.

If you don't have any tables with columns of that particular type and no functions using the type against persisted columns you can change the type as you wish and ALTER ASSEMBLY will work.

If you have functions using the type against persisted columns, you need to use UNCHECKED DATA for ALTER ASSEMBLY to work.

Format.UserDefined: If you have tables with columns of the type, regardless if you do any changes you need to run ALTER ASSEMBLY with the UNCHECKED DATA option. If you do changes, it will still work if you use UNCHECKED DATA!

For functions using the type it works the same way as with Format.Native.

Finally UNCHECKED DATA: During ALTER ASSEMBLY, SQL Server does extensive per-row validation of the data. This validation can be costly timewise so by using UNCHECKED DATA the user can tell SQL to avoid this costly validation. The danger is that data integrity can be lost and therefore SQL gives you a warning when you run ALTER ASSMBLY ... WITH UNCHECKED data.

I hope this makes some sort of sense. If you want code examples that shows some of these issues, please let me know. Comments are always welcome!!

Earlier today I mentioned briefly ALTER ASSEMBLY. AA is IMHO both cool and somewhat dangerous. The coolness is in the fact that it allows you to redeploy an assembly without having to drop the SQL objects bound to that assembly beforehand, if you haven't changed signature of your existing methods. So you may have a method in your assembly looking like so:

1public static int Adder(int x, int y) {

2 return x + y;

Your have also deployed the assembly to SQL Server, using CREATE ASSEMBLY and created a SQL Function against the Adder method through CREATE FUNCTION. You now add a new method to your assembly, and you want to re-deploy the assembly. In Beta 1 you had to drop the existing function, drop the assembly and then CREATE ASSEMBLY again, plus ofcourse CREATE FUNCTION etc. In Beta 2 you can instead call ALTER ASSEMBLY:


2FROM 'path_to_assembly'

this without having to drop existing functions etc.  After ALTER ASSEMBLY you just CREATE FUNCTION/PROCEDURE/TRIGGER against the newly added methods. So what about the existing methods, what happens to them?

Well here is the dangerous part, it turns out that SQL Server re-binds existing UDF's/sp's/triggers against the methods in the new assembly, which can cause side effects if the developer doesn't know what he/she is doing. In other words, the developer may have changed the original Adder method above to something like:

1public static int Adder(int x, int y) {

2 return x + y + 100;

In this scenario, SQL Server would happily go along and re-bind the method. This may have been intended - in which case it is goodness - but it might have been un-intended and that's where the danger comes in.

Anyway, there are more things to discuss about ALTER ASSEMBLY, and I'll do that later today or tomorrow.

Angel Saenz-Badillos wrote a post about creating and deploying SQL Server 2005 UDT's from Visual Studio. He pointed put a couple of "shortcomings" in the deploument story of UDT's from Visual Studio.

First of all I want to say that Angel's blog is really worth subscribing to. If you are interested in new features of SQL Server 2005 and/or new transactional stuff in Whidbey - go off and subscribe!!

Secondly; the "shortcomings" he pointed out is mostly in Visual Studio. SQL Server Yukon supports now in Beta 2 the ALTER ASSEMBLY syntax. This means that if you use ALTER ASSEMBLY to redeploy an assembly, you do not need to drop the SQL objects (subject to certain rules) that are based on the methods/types in the assembly. Nor do you need to drop SQL objects with schema bindings against before mentioned SQL objects. As I said, this is based on certain rules, and you can read more about that in SQL Server Books Online. However, Visual Studio does not support ALTER ASSEMBLY, therefore Angels's post.

Finally, as some of you may know; for Beta 1 and some of the interim releases of SQL Server 2005, I created my own deployment tasks, in order not to have to rely on the deployment features of Visual Studio. I have now uploaded the latest release to http://staff.develop.com/nielsb/code/yukondeploy_beta2.zip (when I have worked out how to upload it to the SQL Server 2005 workspace on GotDotNet it'll be there as well). Some of the changes are:

  • Partial support of ALTER ASSEMBLY (read the README.txt file to see the caveats)
  • Instead of using my own custom deployment attributes the tasks now use the attributes from System.Data.Sql namespace:
    • SQLFacetAttribute
    • SQLFunctionAttribute
    • SQLProcedureAttribute
    • SQLTriggerAttribute
  • Support for deploying the same assembly several times but with different names.

If you are interested, download it. I suggest you read the README.txt first, followed by the deploytask.doc document in the docs directory. If you have any comments, suggestions,please post it here or email me!

So the party has begun - SQL Server 2005 is released, as many out in the blog-sphere has already reported. And Whidbey (excuse me Visual studio 2005) Beta 1 is also released.

You may notice that the CLR versions of VS and SQL2K5 are different. VS is 40607.16 whereas SQL2K5 is .42. This can have consequences when installing the stuff. To be on the safe side, install SQL2K5 first followed by VS. That way the latest CLR (.42) will be installed, which SQL2K5 requires, and VS will play just nicely with this build. If you however already have installed VS, then SQL will complain about not having the right CLR version. If this is the case, un-install the CLR and then install SQL, followed by VS.

Have Fun!!

The SQL Express team at Microsoft has a post saying Beta 2 is on it's way. I don't care that much about SQL Express, but what makes this interesting is that the release of SQL Express Beta 2 may indicate that Beta 2 of SQL Server 2005 is on the horizon!! In fact, I am almost willing to wager that's the case :-)!



I'm back! Hopefully I'll be able to be a bit more active in posting than what I have been before. Some of the reasons for my absence is loads and loads of travel for DevelopMentor, mostly doing the Yukon class. The last months I have been in: India, Japan (twice), New York and Munich (twice). In addition to this, I have been doing gigs here in UK as well.

However, some great news: The Book is done and published!!! The book in question is:  "A First look at Microsoft SQL Server 2005 for Developers". I co-authored it together with Bob Beauchemin and Dan Sullivan, fellow instructors at DevelopMentor. Bob has, by the way, finally given in and started a blog. If you are interested in anything database related, you should definitely subscribe!

The book is supposed to cover the upcoming Beta 2 release of SQL Server 2005. Obviously we had to finish it way before the final build(s) of beta 2 so there are definitely things in the book that doesn't match up with "reality" (they did when we wrote about it), one thing for example our chapter of ObjectSpaces! Anyway, Bob keeps errata and changes for the book here. If you read the book and find any errors, please report it to us.

Just testing Visual Blogger Beta 3