Odd that you can't create a filtered index on a deterministic persisted calculated column

On a client site the other day, I came across a situation (unfortunately too common) where a column in a table was being used for two purposes. It could either hold an integer value or a string. Only about 100 rows out of many millions had the integer value. Some of the client code needed to calculate the maximum value when it was an integer. First step I tried was to add a persisted calculated column like so:

CREATE TABLE dbo.LousyTable

( ColumnWithMixedValues varchar(20),

  SomeOtherColumn varchar(10),

  MixedValueColumnAsInt AS

    CASE WHEN ISNUMERIC(ColumnWithMixedValues) = 1

         THEN CAST(ColumnWithMixedValues AS int)

         ELSE NULL



After indexing the calculated column, all was good. But I then thought I should create a filtered index instead:

CREATE INDEX IndexAttempt1 ON dbo.LousyTable (MixedValueColumnAsInt)

WHERE MixedValueColumnAsInt IS NOT NULL;

but this fails with:

Msg 10609, Level 16, State 1, Line 1

Filtered index 'IX_LousyTable' cannot be created on table 'dbo.LousyTable' because the column 'MixedValueColumnAsInt' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

I was discussing this with fellow MVP Rob Farley and we tried some other options such as:

CREATE INDEX IndexAttempt2 ON dbo.LousyTable (MixedValueColumnAsInt)

WHERE ISNUMERIC(ColumnWithMixedValues) = 1;


CREATE INDEX IndexAttempt3 ON dbo.LousyTable(MixedValueColumnAsInt)

WHERE CASE WHEN ISNUMERIC(ColumnWithMixedValues) = 1

           THEN CAST(ColumnWithMixedValues AS int)

           ELSE NULL


Regardless, there's no option to do this. I really think there should be. It's hard to imagine why it isn't permitted.

If you think so too, here's the connect item to vote on:


Book: Wally McClure – Building iPhone and iPod touch Applications for the .NET/C# Developer with MonoTouch

The title says it all. An old buddy of mine Wally McClure has just released a short new eBook on Wrox that covers how to get started building iPhone and iPod Touch applications using MonoTouch. That should be of interest to the C#/.NET developers out there. You'll find it here:


Just bought a copy myself and was pleasantly surprised. It's 42 pages and $6.99 and comes as a PDF download.

SQL Down Under show 45 – Jamie Thomson – SQL Server Integration Services (SSIS)

Sorry that shows have been slow coming this year. That's about to change (famous last words).

The good news is that show 45 with guest SQL Server MVP Jamie Thomson discussing SQL Server Integration Services (SSIS) is now available for download from http://www.sqldownunder.com. In the show, Jamie discusses lessons he learned while moving from DTS to SSIS and covers off many best practices for working with SSIS.


Should IT Professionals Refuse to Implement Scams (and #MYOBFail)

Not long back, it became very common for dodgy domain registration companies to send letters to domain owners that they weren't dealing with. Rather than saying "hey we're a great registrar and here's why you should switch to us", the common ploy was to send out something that looked like a renewal notice, hoping that the receiver would just authorize and pay it without thinking. In Australia, the ACCC (basically the consumer watchdog) has been chasing down and taking action against such practices.

This has me thinking though about the role that we as IT professionals play in enabling these sorts of scams. If we ever want IT to be taken seriously, we have to stop being the ones that enable this nonsense. Recently I was doing work for a client that would greatly enhance their ability to send out bulk emails. Jokingly, I was suggesting inserting a rule that would always omit my email addresses from any of their systems. But it again left me torn as to whether I should be helping them or not.

What really concerns me though is I'm now seeing companies that I would have previously regarded as reputable heading further and further down this path. One I received today particularly annoyed me. Some time back, we purchased an MYOB license for our company. We were offered an annual maintenance subscription and declined. Ever since then, I've been constantly contacted by MYOB-related salespeople trying to get me to purchase a maintenance subscription. Each and every time, I say "I have no interest in this. Please don't call again." What makes it worse, is I'm often paying for global roaming fees on the phone calls they insist on making. But today they've gone a step further. Look at the following letter:


It's hard to perceive this as anything but an attempt to get someone to try to inadvertently pay for a maintenance subscription that they didn't order. (I'm sure the MYOB folk would try to justify it somehow and I'm sure it's borderline legal). It is designed to look like an overdue account, not like an offer to provide the maintenance that I've constantly told them I don't want. The irony is that instead of making me consider maintenance, it will now make me never wish to deal with the company again and to endlessly tell others why I don't want to. Overall, it will have a much worse outcome than what they could have achieved. #MYOBFAIL

But the bigger question is whether we as IT professionals should be helping generate this sort of thing. Clearly, IT folk are deeply involved in enabling this. Alternately, is the desperation for work all that matters and "if I don't do it, someone else will" ?

OT Book: On the Origin of Species – Charles Darwin

Given my interest in science, I'm ashamed to say that I've only just got around to reading Charles Darwin's "On the Origin of Species by Means of Natural Selection or the Preservation of Favored Races in the Struggle for Life". What prompted me to read it this time was watching two episodes of "The Genius of Charles Darwin" while flying across to the U.S. It's an excellent documentary by Richard Dawkins. Dawkins can see overbearing at times but in this series, he's measured, accurate and inspiring. In the documentary, he showed one of his most prized possessions: a first edition copy of this book.

The best ideas always seem to have three common attributes:

  • They appear totally obvious once someone else points them out
  • They are deceptively simple
  • It's hard to comprehend why someone hadn't noticed them before

The ideas described in this book are an amazing example of these qualities.

I have not the slightest doubt that in the future, this will be still regarded as a stunningly influential book and will have started the biggest change in human thinking.

Recommended! (Actually Required Reading!)

Book: SQL Server MVP Deep Dives – Launch at PASS Summit USA

This is no ordinary book. Paul Nielsen took up Bill Gates' challenge at a recent MVP summit to do something notable to give back to the community. He organised a large group of SQL Server MVPs to create a unique book and worked with Manning to get it published. The money made on the book was to go directly to a charity and the charity chosen was WarChild.

I had the privilege (along with Kalen Delaney, Adam Machanic, Kimberley Tripp and Paul Randall) to be one of the editors on this book but the magic contribution has come from the 53 SQL Server MVPs involved with the book.

The book will be launched at the US PASS Summit on Thursday next week. I can't wait for it to be out and I'm really looking forward to seeing so many of the MVPs at that launch event. I know that many of you like to get your books signed by the authors. Buying a copy of the book at the summit is your best chance of having it signed by as many of the MVPs that wrote it as possible. Don't miss this chance.


Kevin Kline's Travel Meme

Our buddy Kevin Kline has started a travel meme to provide ideas for people travelling to PASS, given many don't travel very often. He's after some good solid travel tips. I'm one of the people he called out so here are a few that come to mind immediately:

1. Delays and mishaps occur. If you don't allow enough time between flight connections, etc. then you are asking for problems, regardless of what the airline says. For example, I *never* allow an international to domestic connection through Los Angeles of less than three hours and prefer four hours. The further you're travelling from, the more you can get delayed. Airlines will let you book a two hour connection there. Don't do it. If you're feeling frustrated with delays, get over it.

2. Given you'll be spending time in the airports or on planes as per point #1, don't waste your time. Take something to read. You'll have long periods where you can't use anything electronic.

3. If there are problems, don't take it out on the counter staff. Doing that can only hurt your chances. My favorite story on this was about a guy standing in line watching another guy in front of him yelling at the lady behind the baggage counter. The whole time she was smiling and being pleasant. When it came his turn, the guy said "I don't know how you do it. How can you keep your composure like that?". She smiled again and said "I just keep focussing my mind on that fact that tomorrow he'll be in Denver and his bag will be in Boston".

4. It's tempting while sitting in a flight at cruising level to undo your seatbelt. Don't do that unless you need to go to the restroom or when you take a bit of a walk around the plane. When the airlines warn you to keep your seatbelt loosely fastened while seated, they aren't kidding. Just loosen it a bit.

5. Try to avoid checked luggage but if you're doing so, make sure your baggage is small enough and light enough to be accepted. I see countless arguments where passengers have oversized carry-on baggage. This has increased markedly since some airlines have started charging for checked baggage.

6. If you are travelling with your partner, split your luggage between his/her checked baggage and your own. This will increase your chance of having *some* baggage at the other end. Reasonable connection times will also help with this. See point #1.

7. Don't lock your baggage if travelling through a US port. The TSA will simply cut your locks off or your bags open. I've had them do this when it wasn't even locked and it wasn't obvious to them how to open the bag. (Make sure it's obvious or get another bag).

8. Noise-cancelling headsets are a gift from Heaven. Get some. Don't whinge about the baby crying behind or near you. Clearly the kid doesn't want to be there either and has no idea why his/her ears are suddenly so sore.

9. If going to a conference, take a flat soft bag inside your checked bag. You can then go with one bag and come back with two, with all the swag you got at the conference.

10. If travelling internationally, I try to get into the right timezone a bit before I leave home. Most importantly though, when you arrive, no matter how tired you are, try to stay awake till evening at your destination. You have much more chance of adjusting quickly and avoid severe jetlag.

I'm sure that more will come to mind but this should be a start.

SQL Down Under show transcripts now coming online

I've had quite a few requests from people for some form of searchability for the content in the SQL Down Under shows. We've looked into having show transcripts posted and after a few false starts, I'm pleased to announce that they are starting to come online now in the "Previous Shows" section at: http://www.sqldownunder.com

While not perfect, they certainly can help. If you are reading them and find any glaring errors, please just let us know and we'll fix them. We're working backwards through the shows and the first three transcripts are available now.