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 ‘ID’, SUM(LineTotal) AS ‘Total’ FROM Sales.SalesOrderDetail GROUP BY ProductID ) AS p ; Here, the outer query will assume that ‘ID’ and ‘Total’ are strings, not the names of columns in sub-query. It’s really not pretty. The query runs, but doesn’t give the correct answers. … 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 check some of them out. The MSDN Forums are terrific. Lots of really good people hang out there, including many Microsoft staff. They’re effectively the new version of the public newsgroups. It’s definitely worth asking (and answering) questions here, and I should probably choose this … 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 drills through into itself, deeper and deeper into a hierarchy. Today I did a talk at the Adelaide SQL Server User Group, and mentioned this was possible (but didn’t have the time to demonstrate it properly). If you make a parameterized query in an MDX … 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 rewriting applications to take advantage of these features. It’s all well and good to see your customers on a map, but someone has to make that spatial data appear somewhere. This post is a callout for features that will benefit you (and your boss) as … 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 are plenty of options for this, and I’ll mention some of them a bit later. What I use most of the time is an actual table in the Data Warehouse, which I populate with a row for each date in the range I want to … 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. Therefore, they were all real numbers (that therefore matched the rules governing what made up a legitimate number), but they would be reordered at random amongst the people. 2/ Dates would be set randomly between the minimum and maximum dates available. 3/ Strings would become … 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 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

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 were really good. Very different to each other – SQL Down Under was held at a university campus in a country town, SQLBits was in a 5-star hotel with conference centre – but both great events. There’s something about having a conference with a dedicated … Continue reading Plane old trouble