DataZulu

So it turns out table-valued parameters can be optional with no problem. But it’s non-standard syntax. Normally, you’d have this:

The "= null" tells us it’s optional.

With table-valued ones, you have to specify “readonly” in any case (not related to optionality) and they are always also optional ... without specifying "= null". Like this:

This works and the @TblValParam does not have to be referenced during the call. It seems, conceptually, that the “table value” is always implicitly there, but if not passed in will just contain zero rows. You can still "select *" from it and get the header. Makes sense.

Treating it like a normal parameter, like this:

... gets you this fun error:

   Operand type clash: void type is incompatible with MyTableType

Fun.

I love when Microsoft products are internally inconsistent. (And by "love" I mean “hate with a passion.”)

MS SQL Server tsql

I know, this sounds like the start of a bad weight-loss ad. Well, except for me not earning a commission, it’s sorta like that…

In my adventures yesterday trying to free up some room, I found a couple of interesting thing I thought I’d share.

1. MS has a little utility, the “Windows Installer CleanUp” app which lets you remove old defunct ‘uninstalls’ from the Add/Remove. Unlike most things that just delete the Registry entry for it, this one deletes any garbage left over from it (the $PatchCache$ and such folders), which can actually recover some space.

You can grab it here: http://support.microsoft.com/kb/290301

2. If you go to where you install it, you’ll also see a command-line utility, “MsiZap.exe” which it uses under the hood. This utility is cool in its own right. (You can google it for more detailed description.) One thing it can do is auto-delete any “orphaned” uninstalls with all their garbage. To do that, you run it with the “G!” switches:

That step alone cleared up about 500-600 MB off my laptop.

Enjoy.

windows