Author: way0utwest

The T-SQL Tuesday party #7 is coming next Tuesday, June 8th. Since it’s TechEd week, and I’ll be there along with lots of you, so now’s the time to schedule a post and get it ready.

The rules have changed, so be sure to read them, and be sure you use this image in your post.

SQL Chicken is hosting this month’s topic, and it’s your favorite new feature in SQL Server 2008 R2 or SQL Server 2008. Code and samples are welcome.

Get your posts ready, and be sure you schedule them between 00:00:00 GMT on Tuesday the 8h of June and 00:00:00 GMT.

I found a cool series of scripts from Jeremiah Peschka on getting disk space from SQL Server. They’re useful queries, and I think for the most part I like getting the information from sys.sysaltfiles for information on my databases.

However, I thought the comment for this query was misnamed:

— how full is each drive?
SELECT drive_letter ,
       SUM(size_in_mb) AS size_in_mb
FROM   #db_files AS df
GROUP BY drive_letter
ORDER BY drive_letter ;

Sys.sysaltfiles doesn’t have the drive size. In fact. it’s hard to get the drive size in T-SQL.

Finding the drive size is complex. I’ve seen lots of people using sp_OA procedures, or the CLR, or even xp_cmdshell. Powershell makes it easy, but not everyone has that installed (yet).

I’m amazed that xp_fixeddrives doesn’t give this. I know it’s undocumented, but why doesn’t it grab mount points and total size? Why isn’t it documented? That’s a topic for another day, but I’d have thought that at some point we could get more information about the OS from SQL Server easily.

For now, I’d use the xp_fixeddrives to get the local disk drive name and then the TotalSize property from the FileSystemObject using sp_OA procedures as outlined in the first link above.

I was never a big fan of temp tables, mostly because there were serious contention issues in SQL Server v6.5. I know things were better in v7.0, but I managed to learn to code without them, so I have avoided them for years.

When table variables came, I was in the habit of not using temp space, so I didn’t use them much, but I have seen them used more and more and decided to experiment a little with the recently.

I looked at the Table Variables article on SQLServerCentral as well as this article on Table Variables and a KB article. Without digging into pros/cons, etc. here’s a little code:

I can create a table like this:

This gives me two rows back, as you would expect. I can do the same thing with a table variable:

And just like a table, I can actually insert data from another table:

This can be handy for small data sets. I think I’d use this if I quickly needed to store some data in a series of steps instead of a temp table, and if I couldn’t materialize a CTE, this would work well.

You can’t index these, and just like temp tables, these will get loaded into tempdb if needed, but it’s a handy construct that can be returned from a function (TVF). Definitely something that I need to play with a little.

I woke up this morning to find Kathi Kellenberger disagreeing with Andy Leonard’s blog on picking the pre/post conference speakers. I read through both, and found myself in the middle of the debate.

I sense from frustration from Andy, and I certainly have felt that way. A few years ago, three or four, there were very few pre or post conference sessions to attend. I had a friend speaking in one of them and as we had a few drinks after his long day, he mentioned he made five figures, in US dollars, for an 8 hour workday.

I was stunned, jealous, and a little annoyed that I hadn’t seen a call for those speakers. If I knew about it, I might have submitted something. Heck, I feel like I should submit something for basic SQL Server training, and see if I can draw an audience.

The last couple years at least, there have been a lot of pre/post con choices, and there have been a lot of speakers. I think PASS has realized this is a decent revenue stream and ought to promote it, so they’ve expanded the choices. Good for them, and good for us. People like the sessions, and after sitting in on a part of one, I have to say that I think it’s worth the $400 for a day of training. Just be sure you take notes, lots of them.

However the incentive (a big payday), and the demand from more people to speak, means that PASS can’t just cherry pick Kalen Delaney and Kimberly Tripp as speakers. They need to formalize the requirements, as Andy has called for, and I think the list of 9 requirements is a start. You need to meet 4 of them, and they look like they’re tailored to be sure that certain people meet requirements. They’re not that bad, though since I meet 4 exactly.

The Intent

These pre/post conference sessions are worth money. Both to speakers and PASS, and I think that the selection has to balance a few things. They have to pick speakers whose names are known. They have to pick topics that people will pay for. I probably meet one of those requirements (the former), though I can perhaps come up with something for the latter. Now will people pay for training from me? Who knows.

And I’m not sure the selection committee knows. They’re volunteers, and I’m hoping that they use some type of data from surveys or data from some source. However my guess is they take a WAG.

While I can’t say I think that the selection process has been fair, or that it will be fair in the future, I do this this is a good move. It’s a move towards transparency, and I think that’s good. In the past, I think all speakers have been selected with vague requirements, and often based the committee’s personal feelings towards people, or their attitude towards people.

That’s human. I can’t say that I wouldn’t be biased towards picking Andy Leonard or Andy Warren for slots. They’re friends, and that’s natural. I do think they’re smart, I’ve seen their writing, and heard them talk, so I think they ARE good choices, but I don’t have data to back things up.

It seems that PASS is slowly opening up, publishing requirements, and hopefully will continue to publish more information on *WHY* they made decisions on things. I don’t think we need to know who voted how on each speaker, but we ought to get some explanations on why people/sessions were or were not picked. I’d like to see all the pre/post submissions and comments from the committee picking them.

Actually I might be on that committee. I know I’m on one of them, and I’ll disclose what I can on the process this year. I’ve complained before, so this is my chance to step up and do something about it.

I think that PASS has been biased, and not necessarily serving everyone in the community in the past, but as they grow, it seems they are slowly starting to make changes and I think the publication of this criteria is a good step.