All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It’s a must.
And might I add, I’ve been the bad guy in Tim’s example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn’t work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone resolved to show this “ID 10 T” he didn’t know what he was talking about… Let me just say that after running some tests I did NOT enjoy the next phone call. Crow really tastes nasty.
Great post Tim. I’m looking forward to the book.
I work for an insurance company. If you think that maybe, we might be interested in the physical location of the things we insure, you’d be right. Actually, we’re an insurance company predicated on the idea that risk can be managed. That means that not only do we know where your factory is located. We know the wind zone, earthquake zone, flood zone, rain zone and temperature zone it’s in. We send engineers out to the site to inspect it and recommend upgrades. We track the upgrades and the condition of your facility.
With all that location specific information, just how important do you think it is that with SQL Server 2008 we’re finally getting a spatial data type? Yeah, exactly.
We’re in the process of launching our first full implementation of the spatial data type and, quite frankly, I was not ready. I had read several of the BOL entries and a few articles online, but nothing had given me enough information for me to say I understood how spatial data worked. Now I at least feel like I’ve got a basis for understanding. That’s because of Alistair Aitchison‘s book Beginning Spatial.
This was not an easy read for me. The first few chapters are frankly difficult. The concepts behind spatial data are not exactly simple. However, Mr. Aitchison did a great job of presenting the information in well explained, digestible chunks. I was able to get through. Coming out the other side, I’ve still got a lot of questions and concerns (especially around spatial indexes, he could have done another three or four chapters on just that topic), but I’m ready to support our project going forward now.
If you’re dealing with Spatial data and you’re not already a guru on the topic, I’d strongly suggest you pick this up. If you’re an expert, this book isn’t for you. There are plenty of examples and Mr. Aitchison walks you through some important concepts, such as importing spatial data. Did you realize that Microsoft didn’t include any mechanisms for dealing with in SSIS? Instead you either need to get creative with TSQL or, better still, use a third party product. I got a copy of Shape2SQL and I’ve tried it out. It seems to do what we need. We still might end up buying a commercial product (several were listed in the book). But it was the information in the book that told me what I needed to know to get started working with our spatial data.
I need to track down more info on spatial indexes now.
I have posted multiple times that I think Microsoft Connect is one of the best tools you can use to communicate with Microsoft. It works. Buck Woody is reinforcing my position from the Microsoft side of the fence.
Look, Microsoft is not a small nimble company like Red Gate. And again, unlike Red Gate, you might not find yourself in a conversation with the CEO of Microsoft on the floor of a conference like the PASS Summit. And short of going to PASS or TechEd to track down some of the developers and project managers (many of whom attend these conferences), you need to have a mechanism to communicate with the company. You can try standing on your front door step and screaming (trust me, the neighbors just call the police) or you can go to Connect.
Speaking of the PASS Summit, the Call for Speakers is open for a few more days. Please, don’t go over there and submit fantastic sessions that all of us will benefit from. Please, sit on your hands and ignore me. That way there’s less competition and I’ll get to present!
If you have three minutes to spare, swing by Paul Randal’s blog and answer his survey questions about the size and distribution of your database. The results are very interesting. I was most interested in the number of respondents to each of the questions. As each size category switched, fewer and fewer people responded. However, a lot more people responded than I expected. 94 last I looked had databases under 10gb in size, but 42 had databases over 1tb. Yeah, that’s only 1/2, but, holy cow, it’s 1/2.
I wish I had a database to manage that was over 1tb. Back in the 7.0/2000 days I was at a dot com that was getting close. When I left they had 700gb. I understand they got close to 850 before the company folded. Managing that much data in SQL Server 7 and 2000 was like wrestling a very serious bear. I’d love to see what it’s like now.
It’s not nearly as fun as my own speculations were, but it’s still interesting. Andy Warren explains that the because Microsoft cancelled the 2009 BI Conference and recommended the PASS Summit as an alternative, PASS wisely decided to extend the time to try to get those people to submit abstracts.