Monthly Archives: January 2012

I am a “cyclist” #cycling

Thanks to Dave for this one, I thought I’d share it.

I must be a “biker” as I only have two bikes and they are certainly not feather weights, neither am I for that matter.

SQL Server: Read only views via GROUP BY and UPDATE FROM pitfalls #sql #sqlserver

I often hit a pitfall when doing an UPDATE FROM when it is from a table INNER JOINed with itself but I only want to update one side of the join. However SQL Server would complain that it doesn’t know which Table_1 is to be considered anyway. Why would I be doing this? Imagine a table with a parent child relationship, so an id column and a parent id column that contains the id of the parent record. If I wanted to update children based on some criteria of the parent I would look at doing an UPDATE FROM with the table inner joining to itself on id equals parent id with the where criteria I wanted.

I get around this by using views, but it can be tricky to make sure I only update the child records and not the parent records, so I employ a mix of table and view. Unfortunately there is no such thing as a read only query or a read only view, but you can do a few tricks to make them non-updatable. For a view a GROUP BY (or DISTINCT) is one way and that is what I’ll show too.

Here are a couple of examples of things that work and things that don’t, unfortunately I came up with the example before this article, so it isn’t specific to a parent child relationship, but still a good example:

CREATE TABLE [dbo].[Table_1](
	[id] [int] NULL,
	[val] [int] NULL

-- Three rows.
INSERT INTO Table_1 (id, val) VALUES (1, 5)
INSERT INTO Table_1 (id, val) VALUES (2, 10)
INSERT INTO Table_1 (id, val) VALUES (3, 15)

SELECT id, val FROM Table_1 GROUP BY id, val

-- Fails, because of GROUP BY, effectively the view is read only, without the group by this would work.
SET val = 1

-- Fails, which Table_1 is to be updated? It doesn't make sense does it?
UPDATE Table_1
SET val = 1
FROM Table_1 t1
INNER JOIN Table_1 t2
ON t1.Id != t2.Id

-- Only 1 row affected, View_2 is not updated but NOT because of GROUP BY, it is not updated because we are updating Table_1
-- The query actually returns two rows (or all three if you see what I mean), try it for yourself.
UPDATE Table_1
SET val = 1
FROM Table_1 t1
ON t1.Id != View_2.Id

-- The exception to this is if Table_1 isn't in the FROM part. So the following statement updates all rows even though you might think View_2 should make this fail; nasty!
UPDATE Table_1
SET val = 1
FROM View_2

An alternative to a view is to use the WITH statement, like so:

WITH v AS (SELECT id FROM Table_1)
UPDATE Table_1
SET val = 1
FROM Table_1 t1
ON t1.Id != v.Id

The hill and bicycle verses car rule

Today I formulated the “hill and bicycle verses car” rule, it’s been on my mind for a few months now, but an impatient driver this morning finally spurred me on.

I’ve notice on my commute there are a couple of small hills where my speed drops right down, there are no cycle paths (or footpaths for that matter) so I am stuck in the road, slogging it out up the hill. I’m happy with this, if the road is clear the other way, drivers overtake, fine. If the road is not clear there are two camps drivers fall into:

1) The patient driver. I salute you. For them I try my best to cycle up the hill as quickly as I can. They wait patiently for a space to overtake and they give me hope that one day all drivers will be the same.

2) The impatient driver. They honk their horn, shout abuse, rev their engine loudly etc. For goodness sake, if I could go up the hill quicker or cycle on a cycle path I would, anything to get away from bad drivers like them, however there is no option. My responses to them can vary, but tend to include:
a) Moving further out, thus discouraging dangerous overtaking that they are more likely to do (compared with a patient driver)
b) Slowing down further, I’m not going to bust a gut up a hill for them

To summarise, the patient driver wins.

This morning was a lovely frosty morning

This morning was the first frosty morning in while and we’ve had good weather till now, but I’ve been rolling on ice tyres for some time, all prepared for worse weather that never came.

So this morning I set off from my house, feeling chilled but starting to warm up by the time I reach the end of the road, I flew past unhappy looking people scraping the ice off their car windscreens and I was reminded of how much I enjoy the feeling of watching others scrap their cars and shiver in winter. They are running late, the car won’t start, the car is iced up, everyone is rushing, everyone is angry. I’m happy, warming up with every stroke and not long before I’m overheating and stripping off to the envious looks of the ice scraping car lovers.

It was -2 celsius but I was still wearing shorts, for some reason it didn’t seem cold enough for tights, but it probably was 🙂

Winter is a nice time to ride (though I say that about any time to be honest) but it does require careful and planned cycling. My Schwalbe Marathon Winter tyres really do help gripping a frosty road, but even so, the best form of defence is “to go careful”, slow movements, no sharp turns, plan your route, plan your speed (and reduce it) and so on.

Windows: Symbolic links, junction points, whatever you want to call them, how to and why #windows

Ever run out of disk space in Windows and want to move things to a different disk, but Windows (or the application) will break?
Yes, in Linux/Unix based systems you’ve got wonderfully simple symbolic links, I’ve always wanted windows to have some of that power. Junction points in the NTFS file system are a way to achieve something close to symbolic links.
First off you need to be an administrator, if you’re user has restricted access then you are doomed. It is also worth pointing out that this isn’t for beginners, care must be taken otherwise you could break your applications or even Windows.

If you have a directory already, move the contents to the other drive. For me let’s call this “C:\dir1\bigdir”, I’ll move that to my drive that has space, “F:\”. It is worth noting that bigdir will remain empty and will be turned into the junction point. Here’s the create junction point syntax (run cmd.exe a.k.a. command prompt with administrative privileges):

CD "C:\dir1"

Run MOUNTVOL with no arguments and you will get the list of volumes.

You can also use MOUNTVOL to remove a junction point (/D option), the directory you created will remain and appear as before (empty).

So you end up with something that conceptually I’d write as “C:\dir1\bigdir” -> “F:\”.

The limitation of this is you can only link to drives.
If you want to link to a subfolder e.g. “F:\mounted_folders\bigdir” then you will need to download the Windows SDK and use a program called linkd.

For more information see How to create and manipulate NTFS junction points and Microsoft Windows Software Development Kit

SQL Server: Convert a string into a hex string and back #sql #sqlserver

Worth noting this one down as it’s not entirely obvious how to convert a string into a hex string or back in SQL Server, fortunately it is easy, there is a built in function called fn_varbintohexstr to go one way, great, but nothing I know of going the other, searching online I see people writing their own.

SELECT MASTER.dbo.fn_varbintohexstr(CAST('Help' AS VARBINARY)) -- '0x48656C70'

But it is even easier than that in both ways, the above function is not required:


SELECT CONVERT(VARCHAR(MAX), 0x48656c70) -- 'Help'


SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70', 1)) -- Requires 0x, returns 'Help'
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '48656c70', 2)) -- Assumes 0x string, no 0x wanted, returns 'Help'

-- One to one pitfall

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x48656c70')) -- Same as adding 0 as last parameter, this is 1:1 conversion, so you get '0x48656c70' back

Schwalbe Marathon Winter Tyres losing their studs

I witnessed a nearly lost stud on my Schwalbe Marathon Winter tyre:

I simply pushed the stud back in. Apparently this is common until the studs bed in, but I think it’s a bit of a weak thing to say, there is clearly a design flaw with these tyres. That aside I keep buying these tyres as there isn’t much alternative here in the UK and I wouldn’t be without them. I try my best to minimize stud loss by checking my tyres regularly for nearly lost studs and also by avoiding dragging tyres and cornering too quickly (probably for the best in winter anyway).
I do have a nice stash of used studs in an old tyre, so I can replace any that do get lost, I also passed some onto a friend of mine who’d lost a few.

It is worth mentioning that these tyres are a real life saver in winter and I wouldn’t be commuting via bicycle in winter without them.