Apostrophes around column aliases

Far too often I see SQL code which uses apostrophes around column aliases, like this: SELECT ProductID, SUM(LineTotal) AS ‘Total’ FROM Sales.SalesOrderDetail GROUP BY ProductID; This is fine, but the worrying thing about this is if the user decides to use this alias in an outer query. SELECT ‘ID’, ‘Total’ FROM ( SELECT ProductID AS … Continue reading Apostrophes around column aliases

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 … Continue reading Useful SQL Question and Answer sites

Infinite Drill-through in a single SSRS report

Grant Paisley of Angry Koala and Report Surfer put me onto this a while back, and I have to admit I’m a bit of a fan. The idea comes from the fact the way that SQL Server Reporting Services (both 2005 and 2008) handles parameters with Analysis Services, and lets you make a report that … Continue reading Infinite Drill-through in a single SSRS report

High ROI items for SQL Server 2008

To persuade your boss to embrace an upgrade to SQL 2008, you need to know which features have high Return On Investment. They may have seen presentations talking about features like Spatial, or MERGE (and been quite impressed), but they may well have left those presentations thinking about the effort that’s would be involved in … Continue reading High ROI items for SQL Server 2008

T-SQL Tuesday — A date dimension table with computed columns

Quite a few people have asked me to blog about what I do for a date dimension table. I’m talking about a table that Analysis Services references for a Time dimension. It’s going to contain every date in a particular range, and be flexible enough to cater for public holidays and other custom details. There … Continue reading T-SQL Tuesday — A date dimension table with computed columns

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. … Continue reading Randomising data

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 … Continue reading Dangers of BEGIN and END

Plane old trouble

Speaking at two SQL conferences in the last two months (SQL Down Under in New South Wales, and SQLBits V in Old South Wales), I’ve had some flights to do. This isn’t normally a big deal, but both times I managed to have some stress getting home. Firstly, I should point out that both conferences … Continue reading Plane old trouble