Big events every month this quarter

A new Financial Year in Australia, and a bunch of technical events coming up.

Of course there’s the usual monthly user groups, but there’s more – particularly if you’re in Adelaide.

July sees CodeCampSA in Adelaide on the weekend of July 18/19. I’ve put my name into the hat for speakers, and will try to be there for a chunk of Saturday (Sundays are too busy for me). I’m sure at least one of my sons will want to come along as well, which will be fun. Big thanks to David Gardiner for putting the website together.

Map picture

August sees SharePoint Saturday come to Sydney (8th) and Adelaide (15th). You may not agree with Aaron about what SharePoint is, but if you’re into SharePoint, I’m sure you’ll get a lot out of these events.

September brings Australian geeks to the Gold Coast again for the Microsoft’s annual TechEd Australia, this year with the added incentive of an HP Mini for attendees (conditions apply of course). It’s the 2140, which is a discontinued line, but that doesn’t make it any less attractive a machine. I’m sure this will help persuade people to get themselves over to Queensland.

image  

Map picture

And in case you hadn’t realised, I’ve recently discovered how easy it is to put maps into blogs using Windows Live Writer… just so that you can all see the beach, and understand how poorly attended the sessions would be if they ran TechEd Australia in the summer.

40,000 days since the beginning of ‘time’

Next Tuesday feels like a significant day. Not only is it 7/7, and the fourth anniversary of the London bombings (in 2005), but it’s 40,000 days since the beginning of time. Well, not proper time, but since the start of the 1900s.

SELECT DATEDIFF(day,’19000101′,’20090707′)+1 AS DaysOf1900s

So if Day 1 is Jan 1, 1900 (as many systems tend to use – SQL actually considers it Day 0), Day 40000 is 7/7/2009.

I actually rate the start of the twentieth century as a very significant event in the human race. In 1900 the world was changing faster than it had ever done before, and no-one can deny the significance of the twentieth century in history. I’m sure change will continue to become faster, but I found it interesting recently when I noticed that we were approaching this ‘milestone’ of sorts.

Disabling the beep

I thought I’d blogged about this before, but when talking about with Simon Sabin just now, I couldn’t find it.

Simon’s just blogged a couple of ways of stopping the beep from the command line. Here’s one using a User Interface.

Go to Device Manager, and make sure that “Show Hidden Devices” is checked in the View menu.

DeviceManager

Now you can find the Beep in the “Non-Plug and Play Drivers” section and edit its properties (setting it to Disabled).

A review – SQL Server 2008 Internals

I’m reading SQL Server 2008 Internals at the moment. I say ‘reading’, because I think it’s going to be long-term thing. It’s just so full of useful information, that I’m sure I’ll be reading it over and over for a long time yet.

Kalen Delaney’s books are always great, but in this one she has help from Paul Randal & Kimberly Tripp, Conor Cunningham and Adam Machanic – all SQL Server legends in their own right. The book they have made is just excellent, and should be read by everyone who wants to get deeper into SQL Server. There are some sections I’ve only skimmed over so far, whilst others I’ve read thoroughly. In time I think I will have read every page multiple times, but this is definitely a resource that can be read that way (yes, Kimberly, it has a good index).

It covers so much useful stuff it’s hard to think of a better resource for SQL Server 2008. It doesn’t go into design very much, but it will affect your design decisions. It doesn’t go into writing queries, but it will affect the queries you write. I really think this book would be an asset to anyone who wants to know more about SQL Server.

[Updated: This link should take you to where you can find and buy the book. MSPress have 50% off their books until June 30, 2009, which makes this book an even better investment]

Access’ DISTINCTROW keyword explained – it means WHERE EXISTS

Steve Koop spoke recently at the Adelaide SQL Server User Group, talking about things which don’t convert particularly nicely when upsizing from Microsoft Access to SQL Server 2008. I think this is a really important thing for SQL people to know, as there seem to be many Access databases living in even the largest organisations.

One of the things he mentioned was DISTINCTROW. I’ve never really known what DISTINCTROW does, so I asked him. He sent me a link which explained the difference between DISTINCTROW and DISTINCT, and it described as “DISTINCTROW works on records, not just individual fields”. This might be good for some people, but I wanted to know a little more.

http://msdn.microsoft.com/en-us/library/aa140015.aspx says “The DISTINCTROW keyword is similar to the DISTINCT keyword except that it is based on entire rows, not just individual fields.” – but it also goes on to say a little more, confirming my suspicions. “It is useful only when based on multiple tables, and only when you select fields from some, but not all, of the tables.”

So DISTINCTROW is more about the JOIN type than anything else. It only applies if you are querying multiple tables, but not returning fields from all of them. So it’s a SEMI JOIN to the unused tables, which you write SQL Server using a WHERE EXISTS clause. It’s not really like DISTINCT at all – it’s about doing a Join without seeing the ‘duplication’ effect, clearly only feasible if you’re not returning columns from the other table.

If you’re not sure what a Semi Join is, then just think about the WHERE EXISTS clause, and it should become clear. If you look at the execution plan of a query in SQL Server that uses WHERE EXISTS, you’ll see that it’s doing a Semi Join. And if you’re looking at queries which use DISTINCTROW, consider changing them to WHERE EXISTS instead.

Putting a Calculated Member into a Display Folder

There are some blog posts that are there to inform other people – this isn’t one of these. This is something I always seem to forget, and I’m hoping that writing it in here will cause me to never forget again. It’s the knot in my handkerchief, or the writing on my hand.

I put an SSAS (2005) Calculated Member in a cube, and then have trouble trying to get it into a Display Folder, or associating it with a Measure Group…

I always hit F4 and go hunting through the properties list… repeatedly pull down the drop down that says [Measures], looking for it… until I eventually remember the extra little button on the toolbar. The one in the picture on the right.image It’s the button between the Script button and the Check Syntax button… the button that always seems to escape out of my head, driving me crazy every few months.