Category Archives: 2655

T-SQL Tuesday #003 (Relationships): The round-up

Lots of blog posts for this month, for the first T-SQL Tuesday to leave the safe-haven of Adam Machanic’s blog. Some people obviously missed out, probably because they don’t read this blog, but I guess that’s the nature of the meme. I don’t know who is hosting next month yet, but I’ll be looking out for Adam to post something about it in early March.

All the posts had to appear as trackbacks or comments on the invitation to participate, but this post is a short summary for posterity.

As the second week of February involves Valentine’s Day (and a few days earlier, my wedding anniversary), I thought the topic of Relationships would be a nice one for this event. There was a good range of topics too, which I have ordered by the type of relationships chosen.

Special mention:

It wasn’t quite on the right day, but a big congratulations to Michelle Ufford who almost managed to time the birth of her new baby Chloe for T-SQL Tuesday.

We have “purely technical” posts:

Brad Schulz is keeping the bar way too high, and if you haven’t read his pieces from previous Tuesdays, then I recommend you go through the history of posts on his blog. This month he has written a letter of disappointment to the FROM clause. At some point Brad will likely be asked to compile these posts into a book, but until that happens, you’ll have to follow the link to his blog. It’s entertaining, but still fits in the “purely technical” category.

John Dunleavy demonstrated (complete with screenshots – something I should put more of in my posts) how foreign keys can be made so easily using the Diagram part of Management Studio. It’s not something I do much of, but I have to admit that reading posts like John’s can often inspire me to changing my ways.

Bryan Smith also talked about database diagrams, and how they can be used to discover relationships in a system.

Michael Coles officially missed the deadline, but I’m going to link to him anyway, demonstrating a nice trick for creating a Product aggregate based on the relationship between a number and its logarithm.

Allen White wrote about the fact that any RDBMS should have relationships to really be considered relational. Great reminder of some of the basics.

Marco Russo timed a piece on relating tables in DAX amazingly well, and only realised that it qualified for T-SQL Tuesday after he had initially posted it. Useful piece, which will appear in search engine results for years to come I’m sure.

Rob Farley wrote a pile of rubbish… hey, that’s me! I wrote about the importance of relationships in a database system to help the Query Optimizer do its job. I also surmised that Foreign Keys using candidate keys (rather than the primary key) might help many queries do away with needing joins. If you have thoughts on that I’d still like to hear them.

Some posts that were about less technical relationships:

Jen McCown wrote about how she left her husband Sean, in a piece called ‘I love you, I quit’. Actually, she was just leaving a job, but it’s a nice piece about the degradation of a employer-employee relationship. It rings too true for all of us I think, and I hope that as an employer I manage to ‘keep the mystery’ for my employees to stop them going through that same experience.

Jason Brimhall wrote about some of the different relationships in his life, particularly how he wants to make sure that the Parent-Child relationship in his life doesn’t become a Foreign relationship. Nicely done Jason.

Allen Kinsel recommends that relationships with professional organisations can be deepened, and that this can be very beneficial. I’m sure he’s right. I have a tendency to get extra-involved in groups, and I hope Allen’s sentiments are heard by many.

The link to Steve Jones’ article must’ve changed. The link that I followed today didn’t work, even though I’d read it successfully a couple of days ago. Still, I managed to find it, and I can thoroughly recommend reading about the relationships between Steve and his colleagues. He’s most definitely correct in pointing out that any effort in developing personal relationships with your colleagues will help you get things done!

And some “combination” posts:

Mike Walsh provided the first piece of the day, with an excellent run down of various types of relationships that are important, including a recommendation to read up on database design. Great advice, Mike. Thanks.

With Kalen Delaney’s post we almost got two for the price of one. A brilliant prelude talking about some of the personal relationships that have enhanced her SQL career, followed up by covering how foreign key relationships have developed through the versions of SQL Server. Kalen’s blog posts are always worth reading, as I’m sure everyone who is reading this post appreciates.

Stuart Ainsworth’s piece was on Maslow, drawing parallels between the hierarchy of needs of a person with the hierarchy of needs of a database system. It’s thought-provoking, and something that I feel could be made into a poster for database developers’ walls.

In summary

I’d like to thank everyone who has taken part, and for Adam for having introduced the T-SQL Tuesday concept to us. Keep your eye on his blog to find out what’s going on next month. If your name isn’t listed here, then I encourage you to write something for March.

Also remember that lots of these people are on Twitter and are very much followable. Look at the hashtag #tsql2sday for related posts, and make sure you follow the people who post blogs for these events.

Invitation for T-SQL Tuesday #003: Relationships

It’s time for the third of Adam Machanic’s T-SQL Tuesdays, and this time, I’m the host. The first one, last December was on the topic of date/time, and the second was on Puzzling Situations. Check them both out, along with the round-ups that Adam wrote about them. Lots of great topics, which is starting to make me anticipate the content that comes out on the second Tuesday of each month.

As an early volunteer to host, I have been given the honour of being the first person chosen to host one. I’d like to claim that this implies some sort of special relationship between myself and the SQL community as a whole, but it’s actually just a “first-in, best dressed” policy – although the ‘best dressed’ analogy is lost on me.

Theme

Valentine’s Day is coming up. Hopefully I don’t need to tell you that it’s on February 14th, but if you’ve read this far into the post then perhaps you’re involved with databases for some reason and may need reminding. Shopping centres around the world have signs up reminding us to buy flowers for our loved ones, but I know many people in IT circles who don’t tend to go to such places, lurking in dark corners of houses until all hours of the night, surviving on pizza. Hopefully this theme will not only prompt some interesting posts, but also prompt people to go out and invest in the meaningful relationships in their own lives. Actually, if you don’t know that Valentine’s Day is February 14th, I’m guessing you don’t have anyone in your life worth buying for. 😉

For me, Valentine’s Day is only three days after my wedding anniversary, so I can’t forget either – as if I would.

So the theme for this month’s T-SQL Tuesday is Relationships.

There are a massive number of options you could go with for this theme. You could talk about Foreign Keys in the relational world. You could wax lyrical about the benefits of attribute relationships in cube design. You could write a poem for your loved one, apologising for all those hours spent in front of a Management Studio window, trying to tune a query, rather than tuning your guitar to serenade her.

Other ideas include: Relationships between Devs & DBAs, Clients & Vendors, Entities, data types, concepts (eg: Report Model & Cube), and more… if you’re struggling to think of something, drop me a line (twitter, Msgr, email, whatever – a list of contact options is over on the left) and I can help.

But so long as you can loosely tie your post to both the theme and some aspect of SQL Server, that’s fine. Be creative, informative, reflective, and hopefully relevant.

Rules

Please note that the time zone for this Tuesday is UTC. For me, that means between 10:30am Tuesday and 10:30am Wednesday. For you, it might mean some time on Monday afternoon to some time on Tuesday afternoon. If you’re lucky (read ‘English’), then you can publish your post any time on Tuesday. It’s about when it’s published though, not when you write it. I encourage you to write your post in advance, in case you’re busy on the day.

So the rules are:

  • Your post must go live after 00:00:00 UTC on Tuesday, February 9, 2010, but before 00:00:00 UTC on Wednesday, February 10, 2010
  • Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
  • You are responsible for ensuring that a trackback or comment appears here so that I can find the posts

Follow the rules, and your post will be included in the roundup to be posted on a day or two later. Don’t follow the rules, and it won’t show up there. Simple as that!

Twitter

Follow the event on Twitter by watching for the #TSQL2sDay hash tag. (The ‘2’ refers to the fact that it’s the second Tuesday of the month, and nothing to do with the pronunciation of the word, which is more like “Choose-day” where I come from.)

Additional Notes

Please make sure you put a link in your post to this one, and post a comment here if the trackback doesn’t appear. I normally moderate comments to my blog (to avoid spam), but may consider changing that policy for 24 hours next week. I have noticed people trying to take part, but failing to make sure that a trackback/comment has appeared. So please check this.

Please check the time zone. It’s something that we’re used to here in Australia, but I know a few people missed out last month not realising that the event had shifted to UTC.

Let Adam Machanic know if you want to host. You can contact him in a variety of ways, as he mentioned last time. To host, you must have participated in two previous T-SQL Tuesday events, and your blog must have had at least one post a month for the prior six months. Let him know you’re keen even if you don’t meet these criteria, as I believe there’s a list, and you might be able to get those blog posts sorted before your turn is up. When I volunteered to host, I hadn’t participated in any yet…

If you want to host but don’t have the faintest clue on what topic to use, ask Adam for some ideas. I’ve suggested a few to him, and I think he’s putting a list together to maintain the longevity of all this. Similarly, let him know if you have some ideas – you might suggest something that catches people’s imagination like never before.

Feel free to contact Adam or myself if you have any comments or ideas for all this. I’ll probably refer you to Adam if it’s a general thing, or happily do my best to answer you if it’s about this month’s event in particular.

Have lots of fun! I look forward to reading your posts!

Edited: The round-up is at http://msmvps.com/blogs/robfarley/archive/2010/02/13/t-sql-tuesday-003-relationships-the-round-up.aspx

StreamInsight talk coming up at SQLBits

My talk on StreamInsight is up next. I’ll try to blog more about that later. For now, I want to mention more about SQLBits itself. This is by far the largest SQL-only conference I’ve attended (I haven’t been to SQL-PASS yet), and it’s great to be involved.

Yesterday I had an all-day seminar about the new items for Developers in SQL 2008. It was a good time – the delegates responded very positively, and many of them have caught up with me since.

But for me, the conference is being a great way of catching up with (and meeting for the first time) a bunch of SQL people that I rarely see. I’ve met people that lived only a few miles from where I grew up, and people that read my blog (Hi!), discovered people who have connections to Adelaide, and even found that my Adelaide friend Martin Cairney (who is also here) has a strange connection to Donald Farmer (of Microsoft), that their parents shared a back fence or something… Now Trevor Dwyer tells me a colleague of his knows me from somewhere… the world is very small here.

My StreamInsight talk will be interesting I hope. I have some stuff to show off, and I plan to involve the audience a little as well. If you’re at SQLBits and feel like being involved in an interactive session, then definitely come along. I want to hear from people in the audience who have dabbled with StreamInsight and also other vendors’ Complex Event Processing offerings. This is a brand new technology from Microsoft, and there will be a large range of adoption levels in the room.

SQLBits V, in Old South Wales

I recently gave a talk in New South Wales, so now I’m going to give one in Old South Wales. In Newport, to be precise.

As I’ve written before, I’ve been a big fan of the SQLBits conferences that is run by many UK-based friends of mine. Unfortunately for them, they had a presenter pull out recently, and unfortunately for them, I’m going to fill in.

Weather-wise, it’ll be a nice change from the scorching weather we’ve had in Adelaide recently. We’re setting new records for days over 30C here, the streak which will be broken on Monday if the temperature drops to 28C, before climbing again after that. I’ll be going to temperatures which are more like 40F than 40C. I think I’ll be the one wearing two jumpers and a coat.

I’ll be involved in all three days of the conference, doing a full day of SQL 2008 for Developers on the Thursday, and hour-long sessions on the next two, on the topics of StreamInsight and Query Simplification respectively.

It’s a great opportunity to be involved, and I’m sure it’ll be a good time. There are several tracks, and the quality is bound to be high. I’m planning to attend sessions by friends, lots of people I’ve never heard present before.

If you’re going to be in the UK on Nov 19-21, make sure you get along, and say hi! I’ll also be receiving delivery of my (signed) copies of the SQL Server MVP Deep Dives book, which is going to be good too.

A Tripp to Melbourne?

I feel like it’s been a while since I’ve been to Melbourne (although I did pass through the airport there on my way back from Wagga). I don’t know when I’ll be there next, but I have felt tempted to try to get there this week.

Partly it’s because my mum is about to turn sixty, but also because my friends Kimberly Tripp and Paul Randal are in Australia this week, and speaking at the Melbourne SQL Server User Group tomorrow night.

I won’t be there, but if you’re going to be in Melbourne, then make sure you register and get yourself there. It’s bound to be quite packed, as these guys are the world experts in their areas – so be early and tell them hi from me!

A busy month – a new book, a new car, a new phone

I don’t know why Septembers are always busy. This one feels like it’s been interesting, and I’m not sure life will be the same again.

But first, some of the biggest news – the book that I wrote a couple of chapters for is now available for purchase!

nielsen_cover150[1] A challenge was put out a while back for SQL MVPs to write a book for charity. Paul Nielsen spearheaded it, and I’m pleased to say that there was a massive response. I wrote two chapters, and this week we have had the notice that the book can now be purchased from Manning Press. If you go to http://www.SQLServerMVPDeepDives.com you will be able to buy the Early Access Edition, which will get you updated electronic copies as the chapters become available (final layouts, images, etc still appearing). All the royalties for this book go to charity rather than the authors, so buy up! I promise to sign any copy put in front of me, but if you go to the PASS conference in November, you can probably get at least 40 or so of the other authors to sign it instead.

I’m planning to get a signed copy brought back from the US, and will auction it off to members of the Adelaide SQL Server User Group, giving the money to charity too.

It feels good to have the book finished!

September has also busy for a number of reasons. The company is growing nicely, celebrating a year this week, and keeping my time somewhat occupied. We achieved Gold Partner status with Microsoft at the end of August, and are ticking along well. On a more negative note, the winter has taken its toll with flu in the family, which is lousy – but we also bought a new car (finally got the people mover we’ve been promising ourselves). I replaced my old phone with an iPhone (part of me thinks that I’ll go back to Windows Mobile next time), and bought my wife one too (plus a DS for her birthday). I feel like we’re more gadgety than ever!

Not to mention TechEd Australia, which was a fun time. Grant Paisley surfing at Dreamworld was a sight to behold, and I hope there are photos somewhere! I gave a talk on SQL Azure, which gave me a number of headaches leading up to the conference, finding new things I wanted to mention on a daily basis! I also gave a talk on the danger of scalar functions in SQL Server, which I will be repeating in just over a week at Wagga, a couple of days after presenting at the Albury/Wodonga .Net User Group. An email arrived about half an hour after my scalar functions talk, saying that someone in the audience had just applied the principles I showed and made some vital queries run thirty times faster! Terrific news I think.

Bringing another laptop (an HP Mini) home from TechEd has also added to the number of gadgets in the house…

Tomorrow I hope to be able to write that I have been awarded MVP status for another year, which will be a tremendous honour. I keep wondering how much longer I’ll be able to remain in the company of such a fantastically skilled and helpful crowd. Every time I receive the award I’m both humbled and proud, and feel amazingly blessed.

More SQL Conferences coming up, including SQL Bits and SQL Down Under

I know I won’t be there, as I’m a million miles away in Australia, but being from the UK myself, I always have an interest in the UK SQL community and in particular, events like SQL Bits.

This is the fifth SQL Bits conference, and they keep getting larger and larger. I’ve heard it’s now the largest SQL-focussed event in Europe. It’s going to be in South Wales (that’s OLD South Wales, not New South Wales), in November. I’m sure the area is lovely, good beaches ‘n all that… but considering it’s late November in Wales, I think you’ll be going for the SQL content, not the scenery.

Of course, if you are in New South Wales, then you ought to be thinking slightly earlier, in particular, the second weekend in October. The third SQL Code Camp is being held in Wagga, with many regular speakers (like myself) and quite a few new ones too.

These two events are clearly the significant SQL events in the last quarter of the year. I’m sure no-one cares about SQL PASS, after all. (I do wish I was going to this one, but I won’t be. I plan to go one year, but I was in the US that week last year, and I don’t plan to be away from home for two birthdays in a row. Maybe next year. It is the biggest SQL event in the world, with great speakers from everywhere, including many good friends of mine.)

No matter where you are in the world, there are SQL events that you should be going to. Professional development is really important for your career, and you shouldn’t neglect it. That being said, make sure you find me at TechEd Australia.

Four speaking engagements coming up

I’m just going to list them…

This Saturday (July 18th), at Code Camp SA. I’m going to be talking about functions in SQL, particularly those that involve BEGIN and END.

At the end of the month, at the ACS Branch Conference. I’m going to be part of a panel discussing Open Source v Closed Source.

In August, I’m going to be speaking at SharePoint Saturday (Adelaide), about the integration of Reporting Services and SharePoint.

In September, I’m going to be a presenting at TechEd Australia, about SQL Azure.

Be nice if there was more of an overlap in topics…

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.

Things You Know Now

This blog meme is doing the rounds… I’ve been tagged at least twice now (Jason Strate and Greg Linwood), so I suppose subconsciously I’ve been thinking about this stuff for a few weeks already.

Since I do a lot of training, I tend to explain these things to my students anyway. I have a lot of opportunity to stand up in front of people and tell them important stuff – so this kind of thing definitely comes up now and then.

Things I wish I had known years ago (career-wise that I would teach new people in the SQL field)

The Importance of technical communities

I remember when Craig Bailey wrote about his ideal role. It wasn’t new stuff – I had heard it all before, but it certainly got me thinking about how people can influence where they are in that Venn diagram. For Craig, he wanted his ideal role to be a job that he was good at and that he enjoyed. Obviously to be a job, someone has to be prepared to pay him sufficiently too.

Being good at something you enjoy isn’t hard, and you can invest your own time (outside of the job that you don’t enjoy) developing your skills. For people in IT, I suggest they pick a particular area they find interesting, and start getting their skills up. If they can become expert-level in that area, then great.

The next problem though, is moving that skill into something that lets you can earn money. Community can help that. Community can help you develop your skills, because you’re spending time with other people in your field. But as you become an expert, presenting at community events, developing a profile, you find yourself being differentiated from the rest. If nothing else, people know you have presentation skills. Every presentation can become like a job interview – showing your skills and ability to communicate information to clients, colleagues, whoever.

Presenting isn’t easy, but there are plenty of other communities that can help develop those skills. You can get along to a ToastMasters group, or offer to do presentations in a group to which you already belong.

You might be the best in the world at what you do – but you need to get out there. I enjoy the technical communities, and run the Adelaide SQL Server User Group because I enjoy it. But I can’t deny that it’s been useful for my career. Now, I’m wishing that I had got involved many years ago.

Enjoy public speaking

According to the old saying, more people are afraid of public speaking than death (so at a funeral, they’d rather be in the coffin than giving the eulogy). But it’s a useful skill to have, so learn to enjoy it.

Keep in touch with old friends

This isn’t quite so career-related, but is actually very important for your career nonetheless.

There are people that I haven’t seen in years, who I have no idea how to contact. Facebook (and the internet in general) has proved very useful for that, but still there are many people that I wish I could find. Most of them are just people I would like to spend time with now and then, but some are people that I’d happily offer to do some work for. And perhaps some of them would contact me to do some consulting if they knew how to reach me (clue, there’s contact information to the side of my blog site!).

Far too many people fall out our lives, and it’s sad. I’m still not great at it, but I do think I should take the time to write people letters now and then (emails, Facebook comments, Instant Messages are all fine too – I’m just talking about touching base to keep the contact there).

Certifications aren’t worth studying for (but they are worth taking)

I used to study for exams. I first became a Microsoft Certified Professional back in 1998, passing an exam called “Architecture I”. Since then I’ve passed over 30 exams, and earned plenty of certifications. But a few years ago I worked out at that it’s just not worth studying for these things.

A MCP exam is not like high school or university. If you fail, you can just try again. Fails don’t appear on your transcript, only the passes do. It’s like your driving test – if you fail, you just try again. Once you pass, you get access to the roads like everyone else.

If you spend weeks studying for a MCP exam, you probably won’t even improve your chances of passing – you’ll just be spending precious family time trying to learn those things to get you past the line. You might even start losing sleep over it.

Nowadays, I tell my students (and myself) to care less. Plenty of people say “No, you don’t understand – I can’t fail at anything.”, and I understand that. I’m not particularly good with failure either. But I’ve learned to not care so much. I don’t want to waste time sitting an exam only find that I fail (or spend $180 on the privilege), but I also don’t want to waste time studying for an exam that I could probably pass anyway. With the Second Shot offer that is often around you’ve paid for two attempts, so go into the first one blind.

The amount of time you invest in getting a certification is largely the study time. So if you can reduce that, the certification becomes a lot cheaper – in which case, it’s probably worth taking the few hours to give it a try. If you do fail, you know you have a weak area, so you can improve that with study – just don’t bother studying before the first try.

[Edited: I should make it very clear that I definitely approve of learning new skills, and preparation for an exam is a great prompt for this learning. Better still is learning for the sake of getting those new skills, with the focus being an upcoming project or new role. My advice above is focussed on people who have the skills necessary to pass an exam.]

Reading execution plans, and understanding indexes

I’ve always been good at solving problems with T-SQL (or PL/SQL for that matter) – I just took to it naturally when I got into databases. But it took me several years to actual venture into understanding what the query is actually doing when it runs. Now, I look at the execution plan for every query I write, as default behaviour, and I consider the indexes that I want up front.

Perhaps it’s because I was a programmer first, but I had always trusted the compiler to do things the right way. I had looked a bit past my code when studying Prolog at university, but it took me a long time to make that my default behaviour.

So when I find people who are just getting into T-SQL, I encourage them to look at the execution plans, and start getting a feel for what’s going on behind the scenes. You can often improve a query without looking at the execution plan, but if you want to write really good T-SQL and have well-performing queries, you need to make the execution plan part of the process.

The significance of BI to businesses

I was involved in data warehouses in some of my first projects when I left university, I just didn’t realise at the time. I first got involved in SQL Server in version 6.0, and quite early on I migrated a system to 6.5, and created a data warehouse to allow for various reports. In hindsight, I was making a data warehouse. I had an ETL process, calculated aggregates, considered the dimensions and granularity, all that. But it wasn’t called a data warehouse, and I only realised a few years later that it really was one.

If I had’ve realised, then I’m sure I would’ve jumped into the BI space much earlier. Companies love BI – it’s one of the most empowering areas of database technology for any business.

 

I’ve picked a few things here – and I hope people somehow get some benefit from reading it. I have put it in my ‘must read’ list to find other people’s responses, because I’m sure there are things that I’m still to learn.

Tagging some other people: Simon Sabin, Jamie Thomson, Deepak Kapoor, Grant Paisley