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.


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.