Saturday, May 25, 2013

Sql trivia: BINARY_CHECKSUM

You know how in SQL Books online the doco tells you to be wary of using CHECKSUM and BINARY_CHECKSUM functions because they will miss some updates. Here's a trivial example:

select 
 BINARY_CHECKSUM(A),
 BiggestInt,
 BINARY_CHECKSUM(BiggestInt)
from (
 select cast(null as int) as A,
 Power(cast(2 as bigint),31) -1 as BiggestInt
) x

Which returns 2147483647 in all 3 cases. So essentially BINARY_CHECKSUM is blind to the difference between a null and an int(max value). Which is fair enough... but does illustrate the point that even if you are only doing a checksum on a single nullable 4 byte field, you can't stuff it into 32 bits without getting at least one collision.

Thursday, March 14, 2013

Google Reader In Memoriam

So, Farewell Then Google Reader
Your feed
Has expired
Like Bloglines,
Which you killed
Off
I guess
Now
We'll just have to use
Something else
Instead
Google Reader was 7 1/2
(With apologies to E.J.Thribb)

Installing the Analysis Services Deployment Utility

The Analysis Services Deployment Utility is a utility that can be used to deploy Analysis Services build outputs (.asdatabase files) to a server, or to generate the XMLA for offline deployment.

I've often used this as part of an automated installation process to push releases out into an integration environment, but on this project I wanted to perform this installation as part of a nightly build. It failed - because the utility (and it's dependencies) weren't installed on the build server.

I wasn't entirely sure what I needed to get it installed (and I was attempting to install the minimum amount of stuff). It turns out this tool is distributed as part of Sql Management Studio, not the SSDT/BIDS projects (as I'd previously assumed). Not sure if the Basic or Complete option is required, because I picked 'complete' and that fixed it.

Also, for 2012 the path has changed, and the utility is now at:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe

Thursday, March 07, 2013

Installing BIDS project templates for SQL 2012

You can absolutely be forgiven for being confused about how to install the BIDS project templates for SQL 2012. They've moved to the new Sql Server Data Tools (SSDT), but Microsoft are themselves inconsistent about what SSDT is. To make matters worse, you've got Visual Studio 2010 and 2012 versions of each package, depending on what IDE you want to work in.

I'll attempt to clarify:

  • SSDT is only the updated SQL database project template, that replaces the Database Edition GDR / datadude stuff (and subsumes the SQL-CLR project type)
  • SSDT-BI is the other, ex-BIDS projects: SSIS, SSRS, SSAS
Unfortunately the SQL 2012 install media uses the term 'Sql Server Data Tools' to refer to both at the same time, and up-until-last-week the SSDT-BI project didn't exist outside of the SQL install media. Much confusion and delay. Hopefully the following guidance clears it up a bit:

If you only care about the SQL Server Database project


(eg: you are a C# developer, and your SQL database schema as a project in your solution)

Install the appropriate version of SSDT that matches the version of Visual Studio (2010 or 2012) you're using right now (or both if necessary):
Note these do not include the BIDS project templates (SSRS, SSIS, SSAS); they only include the new SQL Server Database project template.

If you are a BI developer, and want the lot

...in a Visual Studio 2010 Shell

Install the 'Sql Server Data Tools' component from the SQL 2012 install media. This gets you everything you need.
Optionally, also install the updated version of the SQL project template (only) by installing SSDT for Visual Studio 2010

...in a Visual Studio 2012 Shell

Install the standalone version of SSDT for Visual Studio 2012 (for the database project) and SSDT-BI for Visual Studio 2012 (for the SSRS, SSIS, SSAS templates)

...but don't know which shell to use

If you plan to create a single Visual Studio Solution (.sln) combining BIDS artifacts database projects and other project types (e.g. C# or VB projects), then that will determine your choice here. It's certainly easier working in just one IDE than having to have two open.

Otherwise just pick one. You might be swayed by some of the new VS 2012 features, then again you get the 2010 version already on the install media, so that option is less downloading. Given they shipped against one version, but now support the next as far as I can see they'll have to support both versions going forwards, at least for a couple of years.


Editors Note: This rewrite replaces the sarcastic rant I had here previously, which was quite cathartic, but not desperately helpful in navigating the landscape here.

Thursday, February 21, 2013

INotifyPropertyChanged: Worst. Interface. Ever

As any .Net UI developer will tell you, INotifyPropertyChanged is a fundamental part of 'binding' an object to a UI control. Without it binding is essentially one-way: changes in the control change the object, but if this has a ripple effect on other properties, or properties are changed by other 'below the UI' processes, the UI can't know to repaint. This is essentially an implementation of the Observer pattern[1].

Unfortunately it's not for free - you have to implement it yourself - and that's where the problems start. So much has been written on the pain of implementing INotifyPropertyChanged (INPC for short) that I need not repeat it all here. It's generated so many questions on StackOverflow you'd think it's due its own StackExchange site by now.

The principal complaints are around all the boilerplate code and magic strings required to implement, so for the sake of completeness I'll summarize some of the solutions available:

Pick one of these, stick with it and you're done. Ok, there's still a bit of griping about separation of concerns, and whether this is an anti-pattern, but you're done right?

No.

It's so much worse than that.

Do you remember the first time you implemented GetHashCode(), and later when you realized you'd done it wrong? And later when you really realized you'd done it wrong, that there was no good way you could override Equals() for a mutable object, and the sneaking realization that this whole problem existed only for the benefit of Hashtables? It's a bit like that.

What we have with INotifyPropertyChanged is an implicit contract, that is to say that a large part of the contract can't be formally defined in code. Which means you have to validate your implementation manually. In this case the implicit bit is about threading. INotifyPropertyChanged exists to support UI frameworks and (bizarrely, in this day-and-age) they are still single threaded, and can only execute on the thread that constructed them – including event handlers. Think about this a bit, and you will eventually conclude:
An object that implements INotifyPropertyChanged must raise the PropertyChanged event only on the thread that was originally used to construct any registered subscribers for that event

Now there's a problem[2].

Clearly this is something that's just not possible to check for at runtime, so your design has to cater for this. Passing objects that might have been bound to business logic that might mutate them? UI thread please. Adding an item into a collection that might be ObservableCollection? UI thread please. Doing some calculations in the background to pass back to an object that may have been bound? Marshal via UI thread please. And so on. And don't even get me started on what you do if you have two (or more) 'UI' threads[3].

This is a horrible, horrible creeping plague of uncertainty that spreads through your UI, where the validity of an operation can't be determined at the callsite, but must also take into account the underlying type of an object (violating polymorphism), where that object came from (violating encapsulation), and what thread is being used to process the call (violating all that is sacred). These are aspects that we just can't model or visualize well with current tooling, at least not at design time, and none of the solutions above will save you here.

So there you go. INotifyPropertyChanged: far, far worse than you imagined.


[1] ok, any use of .net events could be argued is Observer, but the intent here is the relevant bit: the object is explicitly signalling that it's changed state.
[2] Actually I've over-simplified, because you can have whole chains of objects listening to each other, and if any one of them is listened to by an object with some type of thread-affinity, that's the constraint you have to consider.
[3] Don’t try this at home. There are any number of lessons you’ll learn the hard way.

Wednesday, February 20, 2013

Crazy reference leakage using extension methods and generics

It appears that if you have an extension method, that is in-scope (i.e. namespace included), even if you don't use it you have to reference all the assemblies that are part of the generic type constraint.

This kinda sucks.

Normally if you use a type from another assembly, and that type has as part of its interface another type in another assembly, you have to reference both assembly. Fine. But only if you use it.

eg: if you reference assembly 'Animal' and use a class 'Cow' that has a property 'Color', and the type of Color is defined somewhere else (System.Windows.Forms) you have to reference that too. But if you get the cow via the IBovine interface, and that doesn't expose Color, you don't need the reference (at least not statically).

If, however, in the same namespace, there's an extension method that you're not using, and that extension method has some type constraints, you have to reference all the assemblies for all the type constraint parameters.

For example, if you put this in one assembly, in a namespace you merely import:


  public static class NotUsed{
        public static void DefinatelyNotUsed(this TContext context, Action thing)
            where TContext : DataContext
        {
            
        }
  }
... then you'll also have to pull in System.Data.Linq to get the 'importing' assembly to compile.

Doesn't this strike you as odd? I'm sure I can hear a gestalt Eric Lippert in my head explaining why, but I was certainly surprised.

Get-Member on empty collections

PowerShell's pipeline just loves to unravel collections, with the result that sometimes, when you want to do something on the collection itself, you can't. Like with Get-Member:
$blah.Catalogs | Get-Member Get-Member : No object has been specified to the get-member cmdlet.
What happened? Did $object.Catalogs return null, or did it return an empty IEnumerable? This has bitten me a few times, especially when poking around in an API for the first time (ie: at this point I have no idea what 'Catalogs' is, whether it's ICollection or whatever).

The answer, I realize, is to avoid the pipeline:
Get-Member -inputObject:$blah.Catalogs TypeName: BlahNamespace.BlahCollection Name MemberType Definition ---- ---------- ---------- Add Method System.Void Add(Microsoft.SqlServer.Management.IntegrationSer... Clear Method System.Void Clear()
Much better

Monday, February 04, 2013

SQL 2012 Data Tier Applications explained

You: So what are these DACPAC things then?
Microsoft:
"A DAC is a database lifecycle management and productivity tool that enables declarative database development to simplify deployment and management. A developer can author a database in SQL Server Data Tool database project and then build the database into a DACPAC for handoff to a DBA"
http://msdn.microsoft.com/en-us/library/ee210546.aspx 

You: Hey, that sounds familiar. How is that different from Visual Studio 2010 Database Edition aka DataDude aka GDR aka VSDBCMD.exe?
Microsoft: They're, like, totally different ok.

You: How so?
Microsoft: Well they just are. DACPACs replace all that GDR stuff. That was just crazy stuff the Visual Studio guys came up with you know. This is the real deal from the SQL product team. And we can package data too, in BACPACs.

You: Awesome. So this'll solve that problem about also upgrading reference data when I push out a new version of the schema?
Microsoft: Oh no. BACPACs can't be used to upgrade an existing database instance. Just to load data into new databases. They're for moving databases between servers.

You: Like a backup
Microsoft: Exactly like a backup, yes.

You: ...so... can't you just use a backup?
Microsoft: No. DACPACs and BACPACs don't just contain the database. They encapsulate the whole data tier application, so they include other items you'd need to deploy, like logins.

You: Cool. And agent jobs as well I guess?
Microsoft: Oh no. Just logins and ... well logins anyway. Try doing that with GDR. And you wouldn't be using sql_variant anyway would you? No.

You: Come again?
Microsoft: Oh nothing.


The author notes this conversation was imaginary, and any resemblance to reality is entirely coincidental

Tuesday, January 08, 2013

The Windows RT desktop. For why?

I still don't understand Windows 8 RT (aka Microsoft Surface, aka Windows on Arm), or more specifically it's desktop mode.

On Windows 8 Pro (the x86 version) it all makes sense: desktop mode opens the door to all the 'legacy' apps you know and love, whilst the device itself weans you onto the world of RT/store apps. As an enterprise developer you can target either, most likely running existing corporate apps on the desktop whilst mulling the trade-offs (and $) involved in rewriting the front end to target Metro.

But on Arm it's crippled: running only Microsoft-sanctioned apps (Office, Notepad and explorer) without jailbreaking. Why?

Clearly you can't expect it to run existing x86 apps, but for enterprise developers working in .Net this is a slap in the face. You're barred from running existing .net 4 desktop apps on the Surface RT desktop, and you can't build desktop apps using the Win RT runtime either.

This is a crazy situation that smacks of half-baked.

If the desktop mode on Arm is useful - and I'd argue it is - it should be possible for more than just Microsoft to write for it. Ideally enterprises could run existing .Net apps unmodified, but there's clearly advantages (re: capability, performance and battery life) in encouraging them to embrace the Win RT APIs.

Conversely if the desktop mode is redundant, Microsoft need to seriously pull their finger out replicating all that functionality in the Metro interface, including a Metro version.

I would like for the former to be the case. I suspect Microsoft's roadmap is the latter, that the desktop's just there till Office gets ported proper. Whichever way, we have a ridiculous situation where if an enterprise developer wants to target Windows 7 and both Windows 8's they have to ... write a web app. Way-to-go Microsoft![1]

I keenly await Xamarin's Mono-Surface[2], which will let you run .Net apps on the Microsoft Platform. Now that would be progress.


[1] Sure they gave the enterprise the finger with the phone too. I guess we shouldn't be surprised. But perhaps this is just Sinofsky's 'my way or the high way' showing through
[2] This is a fictitious product, and any resemblance to actual products planned or otherwise is entirely coincidental

Popular Posts