Month: October 2008

Ok, maybe I’m being a little harsh, but I just feel like it should be better. Let me show you the niceness of the way that missing indexes are handled in SQL Server 2008. Using AdventureWorks (not AdventureWorks2008) on a SQL Server 2008 install, if I show the Execution Plan from this simple query, I get a nice suggestion. My query… select productid, orderqty from sales.salesorderdetailwhere carriertrackingnumber = ‘FB88-4B92-82’; …could be improved through better indexing. It uses 1240 reads to get this data, which seems awful. The system shows me that it could be improved, and suggests an index. It’s … Continue reading Missing Index in SQL Server 2008 – should try harder!

The second of my tips from my talk (slides and scripts available) was about doing the maths to understand why the SQL Server optimiser behaves in the way that it does – particularly in relation to choosing whether to scan an index/heap, or whether to seek a less-suitable index for some of the data and then do a lookup to fetch the rest. This applies in SQL Server 2005 just fine, by the way, as do all the scripts from my talk. The basic gist of it is that the system will decide to use a plan which minimises the … Continue reading Doing the maths to understand SQL optimiser choices

I haven’t been to ADNUG for a while. I love that the group is there, but over the past year or more, I haven’t prioritised getting to the meetings. I’ve been to meetings of equivalent groups in Melbourne and Sydney, but not to the Adelaide .Net Group for a while. But this week I will! I’ve offered to be a stand-in presenter, and will present a few tips around T-SQL. I’m presenting a similar talk in Wagga this coming weekend, so it’ll give me a chance to work out my talk in advance. I regularly present and teach T-SQL things, … Continue reading Presenting at ADNUG this week

A good friend of mine from Perth got awarded MVP status this past week. Mitch Wheat runs the .Net User Group over there, and does a terrific job. He does a lot for the community, and is incredibly smart. I’m really pleased for him. PS: There are a bunch of other new MVPs this October as well (plus I got rewarded) – I’m just mentioning Mitch because he’s a good friend and I’m so pleased for him!

The OUTPUT clause has to be one of the best T-SQL features out there. It was new in SQL Server 2005, but it’s still remarkably little known. I guess like many of the features that were introduced in SQL 2005, many people just make do with the way they did things before. The basic point of the OUTPUT clause is to turn a data modification statement into one that returns data (although this data can be redirected to an existing table by simply adding “INTO tablename” after it), by providing a way of accessing the inserted and deleted tables that … Continue reading OUTPUT clause – knowing what goes in, and what you’ve accidentally taken out