Topics for coffee meetings

Nick Randolph made an interesting point.

But first let me say that it's great that the Perth .Net group are doing this at all. It's a great move to develop a stronger community, and one that I would love to see happen in Adelaide some time. My problem with organising something like this is that I'm not in the city often enough, but I will try to encourage other people in the Adelaide user-groups to set up something like this.

Back to Nick's point though… In case you've just read through his post and are wondering what I'm talking about, he's put low numbers down to a "lack of a prepared theme". But I think this is exactly the point of these meetings. If you want a prepared theme, you go to a user-group meeting. Cafe meetings are for people to gather without talking about a specific topic. I suppose it could be argued that the main difference between a cafe meeting and a UG meeting is the opportunity to discuss, rather than be lectured to, but I think the informality and social aspect is the key – you're meeting for the sake of meeting. Sure, the conversations (please realise that's in the plural) will come around to technical points, and people will probably interrupt their own conversations to join other ones that are about topics they're interested in – but this is just a natural progression from having a gathering of technophiles (just like how the conversations at the Christian group at uni would often be about Christian stuff). There shouldn't be topics. People should meet because they like each other. I wonder if Mauricio has topics.

Paul Turner

Paul Turner is an Adelaide guy I know well. He's been a trainer at Kaz for quite a while, is on the local ACS executive committee with me and has been involved in the local .Net user-group too. He's had a couple of major lifestyle changes in the last couple of weeks. Most significantly, he's become a father, but also he's become a Readifarian (and word is getting out!). All very cool, and Paul is justified in being excited. I think the two changes will work hand-in-hand very nicely, as Readify will probably have him doing a lot of his work from home.

Scripting database objects a different way

Sometimes you want to make changes to your SQL Server objects (like tables, indexes, whatever), and you figure that it will be worth recreating them with a different set of options. One way of doing this is to generate a script, and then use a text manipulator to change the script in a way that will make it just what you want.

But I quite like querying the object catalog views – you know, those tables like sys.objects. Here's an example of a script which will generate a create statement for each of the clustered indexes on user-tables in my database. Except that I've left out the word 'clustered', because when I run this on my new database, I don't want these indexes to be clustered any more. Don't ask me why, it's just an example. You will notice that I have left out other things like the fill factor, etc… that's just because this is JUST AN EXAMPLE. I have left ",*" in the select statement too, because it's worth being aware of what other columns you have at your disposal here.

select 'create index ' + quotename( + ' on ' + quotename( + '.' + quotename( + ' (' +
    stuff((select ',' + quotename(
    from sys.index_columns sic
        sys.columns sc
            on sc.column_id = sic.column_id
    where so.object_id = sic.object_id
    and sic.index_id = si.index_id
    and sc.object_id = so.object_id
    order by sic.key_ordinal
    for xml path(")
    ),1,1,") + ');' as cmd
from sys.indexes si
    sys.objects so
        on so.object_id = si.object_id
    sys.schemas ss
        on ss.schema_id = so.schema_id
where so.type = 'U'
and si.type = 1

I should point out that I'm using the FOR XML PATH(") method to concatenate my list of columns. This is something that's definitely worth knowing about if you intend to do this. I could've done something similar with sys.columns to create a script to generate CREATE TABLE statements too.

Coffee and Community

It's amazing how coffee can be conducive to community. Community is about sharing, it's about developing passion and enthusiasm, it's about friendship. And coffee is too. The age-old invite – "Let's do coffee" – is a great way to sit down with someone. It's cheaper and less formal than lunch, and it doesn't stop you driving home like beer would.

I think it's really exciting that the Perth .Net Community of Practice (mainly Nick Randolph, Alastair Waddell and Mitch Wheat) have set up a weekly cafe catch-up. More about it here.

Mauricio Freitas has often written about the group of coffee-drinkers he gathers each week in Wellington, and I know this works. The ACS in Adelaide have a regular Curry SIG. When I was at university, the Christian group I belonged to had a regular hang-out place, to the extent that you could go down there any time and find people you knew. It made the group stronger. I'm sure that if Mauricio could still get work done, he would hang out at the cafe all the time, making it a sort of office.

I sometimes wonder if a community could be built around a shared office space. Could a group of independent consultants (who would normally work from home) set up a shared office space and work from there, letting other people from within the community drop by any time for coffee, etc? Obviously there would need to be times when people would put 'Do not disturb' signs up, or disappear into offices for phone-calls to clients, but could an arrangement like this be conducive to both work and community? Could a business benefit from having a community built around it like this? Clearly there would have to be some rules, like "Don't steal each other's clients if they come by", and "Remember there are people working here too" – but could it work?

4 questions to evaluate people's passion

I'm going to find this topic very interesting when I'm training people more and more. Kathy Sierra gives us four questions to judge someone's passion for their work:

  • When was the last time you read a trade/professional journal or book related to your work? (can substitute "attended an industry conference or took a course")
  • Name at least two of the key people in your field.
  • If you had to, would you spend your own money to buy tools or other materials that would improve the quality of your work?
  • If you did not do this for work, would you still do it (or something related to it) as a hobby?

Some of this comes down to Mitch Denny's concept of a night-programmer, but Kathy has expanded it to cover any field. I guess if I'm asking these questions of people in courses I'm running, then the answer to the first question is "I'm in one", but I think it's very different to if I were to ask the people who attend my user-group.

Some people who attend courses are there because they want to be, but others might just be there because their employer has sent them. User-group attendees are generally taking their own time to be there, which generally makes them more passionate. I hope that even if the attendees of my courses came simply because they were made to, they will catch some passion and leave being able to answer those questions better. Perhaps they'll even do another course soon because they will have caught the bug of Professional Development.

These questions also rank right up there in ways to evaluate whether a potential employee is a good match or not. If you hire someone who is passionate about their work, then you might get challenged as an employer occasionally, but you'll probably also get a better worker, someone who comes to work wanting to do a good job, rather than someone who just sees each day as waiting for the weekend. I'm all for 'working to live rather than living to work', but if you're passionate about your work, then you probably enjoy your work more and probably have a better work ethic to boot.

Jim Gray search abandoned

Many of us have been searching though images for Jim Gray since he went missing three weeks ago. But now his family and friends have suspended the search. It's very sad, and I'm sure we will all maintain a bit of hope that he's still alive somehow. He was a legend in the database world, one of the greats.

More information can be found at

Malware distributed by MSN Messenger banner ads

I'm sure this won't be the case for long, Microsoft tend to be good about addressing things like this.

Fellow MVP Sandi Hardmeier has put out a very detailed post about the problem, I suggest you read about it there. It does raise some interesting questions. Not least, how are we supposed to protect ourselves against these things? I think her suggestion about making sure that you close unexpected windows using the 'x' in the corner is a good one. And making sure that you have firewalls and virus checkers is an absolute must. Using the 'hosts file' protection against this particular problem will help too.

Querying the total number of records, but only returning a page of them

A lot of people seem to have been asking about this recently. They have a T-SQL query which uses a call to row_number() within a CTE or derived table, and uses this row_number() field to filter the results to a single page – but they also want to know how many rows were in the whole set (rather than just in the page). @@rowcount doesn't do it for them, because this will return the number of rows in that page.

Something like this:

select *
  (select *, row_number() over (order by employeeid) as rn from dbo.Employees) e
where rn between 11 and 20

One good way of doing this is to use an aggregate with the OVER() clause. Like this:

select *
  (select *, row_number() over (order by employeeid) as rn, count(*) over() as cnt from dbo.Employees) e
where rn between 11 and 20

This is a way of having an aggregate as part of your resultset, without having to restrict your results with a GROUP BY statement. Now your code can access the total number of rows without having to re-query the data. And furthermore, this method can work out the aggregation using the single pass of the data that was already being used.

Of course, there is the downside of returning an field which repeats all the way through – but this may well be 'less bad' than re-querying the data to get the record count.

My first Solid Quality course is scheduled

If you're in Melbourne during the last week of March and want to learn about how to effectively write queries for SQL Server, then this course is for you.

The course was written by Itzik Ben-Gan, who is one of the legends in the SQL Server world (and now my colleague!), so it's a very high quality course. And of course, it's not as if I'm new to the material myself, having been involved in helping people learn about T-SQL for many years myself. I'm really pleased that this is the first course I'll be teaching under the banner of Solid Quality Learning, and if you want to join me in celebrating, then register soon!