Category Archives: Uncategorized

A moving time…

For those of you who read my blog using RSS, you probably won’t even see this post, but either way.

I’ve decided to move my blog to sqlblog.com. I have reposted some of my recent posts, but will not be posting any further content here at msmvps.com.

Both the feed from msmvps.com and sqlblog.com use feedburner.com, so the old feed will continue working, but I recommend changing to the new one in case my blog disappears from msmvps one day.

So farewell, msmvps.com (and thanks Susan!), it’s been fun. I’m sure I will still have many posts which link back here…

Useful SQL Question and Answer sites

There are so many places to ask a question these days. I get plenty of questions via MSN Msgr and email, and do my best to answer those of course. But there are many others too. I figured I’d list some of the ones that I frequent, and challenge some of the readers here to check some of them out.

The MSDN Forums are terrific. Lots of really good people hang out there, including many Microsoft staff. They’re effectively the new version of the public newsgroups. It’s definitely worth asking (and answering) questions here, and I should probably choose this option more for answering questions myself.

Experts-Exchange is a much-maligned site, largely because to ask questions you need to have points. You can get points through a paid subscription, but you can also get points by answering questions. If you answer just a few questions each month, you can become a recognised Expert on the site, which lets you ask as many questions as you like, and also gives you the option of a “ad-free” environment. Many people still joke about the way that the site reads if you ignore the hyphen, but if you are an expert, this site is definitely worth hanging out on. You can register for free (getting you no points to ask questions until you’ve started answering them) at http://www.experts-exchange.com/registerFree2.jsp, so why not go there and register, so that you can start answering questions. They have a facility so that Designated Experts can get emails for neglected questions, giving you a much better chance of an answer than many other sites around. (Note – if you are a SQL MVP, or a MS employee, and you want to be fast-tracked into receiving the Neglected Questions notices, drop me a line and I’ll see what I can do for you)

Stack Overflow is a current favourite amongst many, because of the number of people that seem to frequent the site. It’s clean (very few adverts hanging around), and people seem to rush to answer questions as soon as possible. From a purely SQL perspective, I find that there is too much weighting on the iterative languages there, so many of the SQL responses seem to be provided by people who aren’t really SQL specialists. But it doesn’t mean that you won’t pick up some good tips there. I got started there by answering a question that has even ended up in the source for the site – which I’m still hoping will reach the magical “100 up-votes”, and I’ve continued to keep my eye out for questions there that need answering.

Server Fault is the system administrator cousin to Stack Overflow. If you have DBA-style questions rather than developer-style, then this site is very useful.

Using the same interface as Stack Overflow and Server Fault, but purely focussed on SQL Server is Ask SCC, run by the guys from sqlservercentral.com. It’s a new players on the scene, but I think will turn into a very useful site. The Stack Overflow engine isn’t bad at all, and the quality of answer at Ask SCC is excellent. I would love to see more people hang out there, as it serves a useful market for SQL specialists. At the moment it doesn’t do much traffic, but many of the people there are good SQL experts, and I’m convinced that you’ll get an excellent answer if you ask a question there. At the moment it doesn’t seem to be collecting poor answers as much as many of the other sites, so the ratio of good answers to poor ones puts you in a good position as an asker. I’ve posted my Ask SCC and Stack Overflow ‘flairs’ here, so that you can compare the two. If the numbers on the Ask SCC one have reached as high as the Stack Overflow one, then you’ll have a good indication that the traffic on Ask SCC has increased nicely.

 

In many ways, I tend to find that my efforts are focussed more on the questions that aren’t getting answered, rather than trying to catch the newest questions. On many of these sites, I’d rather find the one that the asker has had trouble with, hoping to provide the elusive answer rather than the obvious one. That question that got me started on Stack Overflow was an exception because I didn’t feel like any of the previous answers had really solved the question properly, but on the whole, my approach to Stack Overflow doesn’t really fit with most of the answerers on the site. I like EE because there really seems to be a focus on getting those elusive answers for people, and I know that Microsoft really focuses on getting answered questions sorted on their forums.

My challenge to you is to give back to the community this Christmas. Make it a resolution for 2010 if you will. Why not try to answer a question every week? And better still, make it one that everyone else has had trouble answering. Go to the lists of unanswered questions, and help someone out. Next time it might be you asking, and you’ll hope that someone takes the time to find your elusive question.

Plus, you might learn something!

Randomising data

I recently needed to randomise some data to keep some information secret. The idea was that it looked roughly similar to the real data, but was sufficiently different to avoid any identifying features.

After discussing it with the client, it was agreed that…

1/ ID numbers would be mixed around the people in the list. Therefore, they were all real numbers (that therefore matched the rules governing what made up a legitimate number), but they would be reordered at random amongst the people.

2/ Dates would be set randomly between the minimum and maximum dates available.

3/ Strings would become a series of random letters, but the same length as the original.

4/ Genders would be assigned a random value of M, F or N (Not Specified).

5/ Numeric fields (such as salaries) would be multiplied by somewhere between 0.1 and 10, with 1 being the median value used.

Here’s how I did it.

1/ I used row_number of this, twice. I used one ordered by the original ID field, and one ordered by newid() (which is a good-enough random order). I could then perform a self-join, and do the update.

with twonums as
(
select row_number() over (order by id) as orig_rownum,
      row_number() over (order by newid()) as new_rownum,
      *
from dbo.People
)
update t1 set id = t2.id
from twonums t1
      join
      twonums t2
      on t1.orig_rownum = t2.new_rownum
;

This mechanism takes advantage of the fact that you can update a CTE. The fact that row_number() assigns each number exactly once means that I update every row, and no row gets updated twice.

2/ To generate a random positive value less than some number N, I use abs(checksum(newid())) % N. Apparently this gives a good distribution of values. If N is the number of days between two dates (plus one, in case the two dates are identical), then the result can be added back onto the first date to get a random date between the two.

update dbo.theTable
set theDate = (
  
select
  
dateadd(day, 
         abs(checksum(newid())) %
            datediff(day, min(theDate), max(theDate)) + 1,
         min(theDate)
         ) 
   from dbo.theTable
);

If you prefer, you could populate variables @startDateRange and @endDateRange and then use them instead of having dbo.theTable in the sub-query like this. The Query Optimizer should be able to kick in and make work out those values for you once though (which is did when I checked the Execution Plan).

3/ Without stepping through each character in a string, it doesn’t seem particularly trivial to change each one to something different. For this, I took advantage of SQL 2005’s ability to use expressions with the TOP clause, and the string concatenation feature available from FOR XML PATH(”).

Using any table with sufficient rows in it in my FROM clause, I generated random letters by converting a number from 0 to 25 to a letter. Adding the number to ascii(‘A’) and converting back to a character did the trick. Restricting the number of rows returned to the number of characters in the name gave me a set of characters, which I could easily concatenate using FOR XML PATH(”)

select top (len(isnull(GivenNames,”))) char(abs(checksum(newid())) % 26 + ascii(‘A’))
from sys.all_objects
for xml path(”)

4/ Assigning a random gender to a row was very easy. I simply took a random value between 0 and 2 and used it with CASE.

case abs(checksum(newid())) % 3 when 0 then ‘M’ when 1 then ‘F’ else ‘N’ end

5/ Finally, multiplying by a value between 0.1 and 10. It’s easy to generate a value between 0 and 99, add one to it and divide by 10.0 to get values in this range, but this isn’t really what’s desired, as it would give a distribution centred around five. The distribution that I want is actually logarithmic, giving roughly as many values less than 1 as there are greater.

Really what I wanted was to get a number between –1 and 1, and use 10^N, as 10^(-1) is 0.1, 10^0 is 0, and 10^1 is 10. This seemed quite easy, except that the POWER() function in SQL only uses integers. I could easily generate a value in the range –1 to 1, I simply used checksum(newid()) % 1001 (ignoring the ABS() function), and divided by 1000.0. But then to find 10 to the power of this value, I remember the logarithm function from school, which said that x^y was the same as the exponent of log(x) * y. Therefore, I used:

exp(log(10) * (checksum(newid()) % 1000 / 1000.))

…which did the trick nicely.

The client verified that the data was sufficiently random (as well as expressing some surprised over there being a practical use for log() and exp()), and I had an environment to which I could grant developers access.

Book review: Programming Dynamics CRM 4.0

I don’t consider myself really in the Dynamics CRM space. My area is SQL Server. Currently I’m spending most of my time in the Business Intelligence space, with plenty of stuff with relational databases as well. But that didn’t stop me from picking up a CRM book recently.

Jim Steiger’s Programming Dynamics CRM 4.0 was the book in question, and I was pleasantly surprised. It seems to be very comprehensive, and well written as well.

I can’t say that I tried all the examples – that would be lying, but I do feel that I have a much better understanding of what’s involved in programming against CRM, and that I have a really good resource available in this book.

I do keep wondering about how much I’m allowed to hack into the database structure with CRM. This book makes it very clear (as do other CRM experts I know) that I shouldn’t, but I keep looking out for a situation that will persuade me to start playing. I’m not talking about massive changes, but small things, like additional indexes for example. This book is likely to persuade me not to dabble, as I want to do things its way. Whilst nothing jumped out at me as being a really good argument not to (although I might’ve missed it somewhere), I felt myself being guided down the proper way of programming CRM, and now think that if this book recommends a particular path, I’m probably going to follow it as closely as possible.

So now I’m wondering how long it’s going to be before I find myself involved in a CRM project, and wondering whether I’ll consider myself ‘learned’ or not. I still know there is a lot to learn with CRM, but I also know I have one of the best resources out there on my bookshelf.

Poorly scheduled downtime

Not me… someone else, but it did make me think.

If you need downtime, you schedule it carefully. If your server needs a reboot for some reason (maybe some patch), then you find an appropriate window in which to place it. Typically this ends up being between 2am and 3am, but working out a time when a backup won’t be interrupted, or when overseas customers need the system to be up, and so on.

I want to rebuild my laptop soon, but I want to make sure I do it at a time when I have a few days up my sleeve – time when I don’t need my laptop. Turns out that might be this weekend, as I’m going to have a small operation at lunchtime tomorrow (elective surgery, potentially – but hopefully not – involving two bricks). I’m likely to be wanting to rest for a couple of days, so it could be a good opportunity to find the right pile of installation DVDs and do a system rebuild.

Funnily enough, I was just reading about an instance of poorly scheduled downtime by another company. ITV were showing a 4th round FA Cup match ‘live’ (well, almost) a few hours ago involving one of the oldest rivalries in sport – Everton v Liverpool. There were about three minutes left in the game, which was looking like going to penalties, so they thought they’d sneak in a quick commercial. A bit of ‘downtime’ if you like. Except that during those few seconds away, Everton scored a goal. Already there are articles popping up about the incident, and ITV are looking like idiots.

Whatever industry you’re in, if you need some downtime, please plan it carefully. Imagine what happens in the ‘worst-case’ scenario. And wish me ‘luck’ tomorrow.

A few changes…

It’s been an interesting month. Today I’ve just come home from hospital having had an inflamed appendix removed. I’m not talking about the back of the book that I set on fire – rather it’s was the reason for a large amount of gut-ache on Monday. I went to the doctor on Tuesday morning, and he sent me to the hospital, where they found the earliest opportunity to remove it (which was Wednesday – at least it hadn’t burst yet). They did this by going through my belly-button, blowing a bunch of air in to inflate my abdomen, and then using a camera and telescopic tools to remove it. They made two other holes to help, but the keyhole approach meant I could come home today (Thursday). I’m still feeling rather lousy, and having trouble concentrating, but I’m essentially fine, and will be fit to catch flights again by the weekend. Annoyingly, I didn’t get to keep the appendix (in a jar, to give to trick-or-treaters tomorrow night), because it’s been sent off for tests. They’ll let me know if the inflammation was caused by anything more sinister. Chances are minimal though, and I’m sure God will make sure everything’s fine.

It still ruined my week though – I had things I was planning to get done.

So that’s one change…

lp_smallThe other major one is that my employment situation has changed. I’m still very much a mentor at SQLskills Australia (previously known as Solid Quality Learning Pty Ltd), but I’m no longer an employee there. I have set up my own company, called LobsterPot Solutions, and will be operating through that. I will continue to teach through SQLskills, and do not expect to be in competition with them in any way. I hope that I can help them achieve as much success under this arrangement as I could as an employee.

Through LobsterPot Solutions I will be available for consulting and mentoring assignments, primarily in the SQL Server space – but will probably expand the range of offerings to include web (including Silverlight), SharePoint, Dynamics CRM, and others – areas that I’ve worked in over the years and wouldn’t mind getting back to. In time I will bring on staff, so that I can broaden my capabilities, and you’ll probably see my kids doing work experience in a few years.

I’m sure SQL Server will always be where I spend most of time, and continue to maintain my expertise. I don’t plan let my interest in other areas dampen my SQL passion at all, but rather to enhance that ‘usefulness of data’ aspect which I have always enjoyed.

As for those things I was planning to get done this week… I need to finish off the website, finish organising business cards, chase down some charities, write up some more of my T-SQL Tips, work on some book chapters, and a few other things besides. But I guess a hospital stay is a reasonable excuse.

Busy times recently

Time seems short at the moment. I’m putting the finishing touches on a presentation for TechEd Australia 2008. I’m not on the list of speakers at the moment, but I will be when they’re next updated (there are a few blanks still). I’m giving a talk about T-SQL techniques, which should be a lot of fun. There was a similar talk given in the US this year, but this is very much my own. I’m going to be following a similar format as the US talk, but there’s remarkably little overlap in the two talks.

The US talk seemed very good. It was called “SQL Tricks: Insights from Microsoft IT”, but I’m not so big on tricks. Sure, I know plenty, but my talk is going to be focussed more on techniques that I use when writing or fixing T-SQL. Ok, some may be tricks, but the idea is to get you seeing some of the power in T-SQL. The talk will be called “Improving Your T-SQL Arsenal“, which points to the weaponry you have in T-SQL, but also gives a nod to my North London friends.

Most of the things in the talk are things that are just as relevant to SQL 2005 as SQL 2008, which seems odd to mention today, since SQL Server 2008 RTM has become available in the last 24 hours. When I reboot my laptop, the installation will be complete and I will be checking that all my scripts still work. This will include my entry in the Demos Happen Here competition.

The DHH competition is not something I’m expecting to win, but I have an entry in nonetheless – a demonstration of the Resource Governor. I think the Gov is a great feature to demonstrate, and I hope the judges see it this way too. But I also think a lot of Dave Gardiner‘s demo, and I actually hope he wins the SA final next week.

It’s been a busy month – my todo list is being stretched rather than shrunk – and I’m not sure when it’s due to ease off. I was asked to be in the US next week helping put the new SQL 2008 exams together, but my schedule is just too full. I haven’t even had time to look at the betas, although I was pleased to see that a blog has formed to announce beta exams. And of course, TechEd Australia is less than four weeks away. I’ll be assisting in the Hands On Labs area as well as presenting. I also want to be spending as much time as I can around the UNICEF project, which I think gives me about 20 seconds for toilet breaks. If anyone knows a way to put an extra several hours into a day (without inducing sleep deprivation), please let me know.

Transfer SQL Server Object Task fixed

It’s nice to give Microsoft feedback and get the product changed!

I first mentioned this in a post last month and logged a connect.microsoft.com entry. Microsoft checked it out, invesigtaed, and have now put a new property into SQL Server 2008 to resolve it! They write:

Thanks a lot for your feedback. We are taking a look at this issue.
Posted by Microsoft on 2/27/2008 at 8:54 AM

Greetings Rob,

First, let me thank you for your feedback on SQL Server; we really appreciate your willingness to spend time and write up the issue as you have, and to send it to us.

As you’ve described, the Defaults query that is run when you expand this property is just looking for defaults you’ve created via CREATE DEFAULT, not the more typical (imo) default constraints on table columns. It does by enumerating the SQL Server Management Object’s (SMO) Database.Defaults collection, which in turn uses SQL as you’ve seen through the profiler. This however does not allow you to choose individual column’s DEFAULT constraints to be copied. In fact, the current implementation of the Transfer Objects Task doesn’t allow many kinds of column constraints to be copied.

For SQL Server 2008, we’ve just added a new property to the task, CopyAllDRIObjects, which corresponds to SMO’s “DriAll” property. This will cause all data referential integrity objects, including constraints, on objects you’ve selected, to be included when copying. So for example if you’ve selected three tables to be copied and they each have columns with DEFAULT (or other types) of constraints, and you enable CopyAllDRIObjects, this will cause their constraints to be copied as well to the destination. DRI objects for objects not selected, i.e. constraints on columns in tables you have not selected, will not be copied.

Thanks again for your post to connect; we look forward to hearing from you in the future. Take care!

SSIS Team

Posted by Microsoft on 3/6/2008 at 5:47 PM

I think the key words here are “on objects you’ve selected” – so that you’re not trying to transfer things you don’t want.

The fact that they took any kind of action is great, and definitely shows that it’s worth jumping onto the connect.microsoft.com/sql site and putting in feedback about SQL Server. If you identify something that should be changed (and it’s easy enough!), then you could have a very quick fix!

User Groups in Second Life

The morning after the Adelaide launch of the 2008 products, I got to enjoy breakfast with quite a handful other Adelaide community leaders, and few of the Microsoft DPE team. Conversation was remarkably varied, and one of the things that came up was Community Credit and the Second Life .Net User Group.  I’ve never done the Second Life thing, and I’m not sure I want to. It’s an interesting concept though – the idea of attending a user group within a game. I guess it’s a cross between an online community and a face-to-face one. Perhaps it’s onlineface-to-onlineface? It’s not my thing, but it might suit people who can’t do face-to-face for one reason or another. Is there a Second Life SQL User Group?