Dangers of BEGIN and END

I’ve presented this material at three conferences recently, so it’s about time I wrote a blog post on it… As programmers, we love modularisation – even in the SQL space. We make stored procedures, views, and functions to encapsulate our code. This improves maintainability, simplifies the development experience, and is generally useful. But there’s a time when it’s a bad thing for SQL Server. There’s an amazing component of SQL Server called the Query Optimizer (I always want to write Optimiser, but I’m assuming it’s a proper noun and putting up with the US spelling). When we write queries in … Continue reading Dangers of BEGIN and END

A busy month – a new book, a new car, a new phone

I don’t know why Septembers are always busy. This one feels like it’s been interesting, and I’m not sure life will be the same again. But first, some of the biggest news – the book that I wrote a couple of chapters for is now available for purchase! A challenge was put out a while back for SQL MVPs to write a book for charity. Paul Nielsen spearheaded it, and I’m pleased to say that there was a massive response. I wrote two chapters, and this week we have had the notice that the book can now be purchased from … Continue reading A busy month – a new book, a new car, a new phone

Four speaking engagements coming up

I’m just going to list them… This Saturday (July 18th), at Code Camp SA. I’m going to be talking about functions in SQL, particularly those that involve BEGIN and END. At the end of the month, at the ACS Branch Conference. I’m going to be part of a panel discussing Open Source v Closed Source. In August, I’m going to be speaking at SharePoint Saturday (Adelaide), about the integration of Reporting Services and SharePoint. In September, I’m going to be a presenting at TechEd Australia, about SQL Azure. Be nice if there was more of an overlap in topics…

Big events every month this quarter

A new Financial Year in Australia, and a bunch of technical events coming up. Of course there’s the usual monthly user groups, but there’s more – particularly if you’re in Adelaide. July sees CodeCampSA in Adelaide on the weekend of July 18/19. I’ve put my name into the hat for speakers, and will try to be there for a chunk of Saturday (Sundays are too busy for me). I’m sure at least one of my sons will want to come along as well, which will be fun. Big thanks to David Gardiner for putting the website together. August sees SharePoint … Continue reading Big events every month this quarter

GO – repeating batches

GO is very cool. More so than you might think, and in a couple of interesting ways. This is the 6th tip from my TechEd Australia talk, from which you can see my slides and scripts in other posts. In SQL Server, GO is the traditional batch separator. But what many people don’t know (but has been mentioned around the blogosphere several times over the past year or so), is that you can put a number after GO to make the batch run repeatedly. I often use this method to populate lots of sample data into a table. INSERT dbo.someTable … Continue reading GO – repeating batches

Ordered data requires ORDER BY

When you select data out of a database table, it might seem as if the system will give you data in the order of the clustered index that is on that table (assuming it has one), but this isn’t quite right. I showed this in the fifth of my T-SQL tips at TechEd Australia this year. If you don’t explicitly order data using the ORDER BY clause, the system makes no attempt to provide you with the data in any particular order. It will just give you the data in the fastest way possible. You might be lucky and get … Continue reading Ordered data requires ORDER BY

Unique Indexes with GROUP BY

Indexes are great, but if you don’t understand the significance of unique indexes, then you’re potentially missing out on some decent performance gains. I’ve been meaning to write this blog post for a long time. This is material that I’ve been teaching and presenting about all year, but somehow it’s never turned into a blog post. It was the third tip in my presentation at TechEd Australia (and at user group events in Adelaide and Melbourne). Today I’m flying back home from the US, so hopefully I’ll be able to spend some battery time getting it done. [Note: It’s ended … Continue reading Unique Indexes with GROUP BY

JOIN simplification in SQL Server

This is another of my tips/techniques that I demonstrated recently. It is not a new SQL Server 2008 feature – it works just fine in older versions – I demonstrated it running on SQL Server 2005 but using SQL Server 2008 Management Studio. [Note: It’s ended up appearing earlier than another post that I wrote on the plane. This one is Tip #4.] When you have a single-table query and then introduce a JOIN, there are a number of ways in which that JOIN impacts your query. I want to talk about four such JOIN effects that I’ve identified. Understanding … Continue reading JOIN simplification in SQL Server

Doing the maths to understand SQL optimiser choices

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

OUTPUT clause – knowing what goes in, and what you’ve accidentally taken out

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