Rob Farley : sql

Apostrophes around column aliases

Rob Farley — Mon, 28 Dec 2009 22:14:00 GMT

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. Furthermore, if this had’ve been done in a GROUP BY clause, or a JOIN, etc, then the error may have been hidden some more. An error might have occurred, but only in certain circumstances.

What should have been done is to have used square brackets, like [Total], or even no brackets at all. Using the table alias in the outer query would have helped too.

SELECT p.ID, p.Total
FROM
(
SELECT ProductID AS ID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
) AS p
;

I’m not sure why SQL Server allows apostrophes to be used around column aliases, but unfortunately it does (and because it does now, it likely always will, if only to maintain backward-compatibility). So instead, any time you see code that uses apostrophes this way, please change it – just to help any developers that come after you who don’t understand where things can fall down.

Useful SQL Question and Answer sites

Rob Farley — Thu, 24 Dec 2009 05:14:00 GMT

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 option more for answering questions myself.

Experts-Exchange is a much-maligned site, largely because to ask questions you need to have points. You can get points through a paid subscription, but you can also get points by answering questions. If you answer just a few questions each month, you can become a recognised Expert on the site, which lets you ask as many questions as you like, and also gives you the option of a “ad-free” environment. Many people still joke about the way that the site reads if you ignore the hyphen, but if you are an expert, this site is definitely worth hanging out on. You can register for free (getting you no points to ask questions until you’ve started answering them) at http://www.experts-exchange.com/registerFree2.jsp, so why not go there and register, so that you can start answering questions. They have a facility so that Designated Experts can get emails for neglected questions, giving you a much better chance of an answer than many other sites around. (Note – if you are a SQL MVP, or a MS employee, and you want to be fast-tracked into receiving the Neglected Questions notices, drop me a line and I’ll see what I can do for you)

Stack Overflow is a current favourite amongst many, because of the number of people that seem to frequent the site. It’s clean (very few adverts hanging around), and people seem to rush to answer questions as soon as possible. From a purely SQL perspective, I find that there is too much weighting on the iterative languages there, so many of the SQL responses seem to be provided by people who aren’t really SQL specialists. But it doesn’t mean that you won’t pick up some good tips there. I got started there by answering a question that has even ended up in the source for the site – which I’m still hoping will reach the magical “100 up-votes”, and I’ve continued to keep my eye out for questions there that need answering.

Server Fault is the system administrator cousin to Stack Overflow. If you have DBA-style questions rather than developer-style, then this site is very useful.

Using the same interface as Stack Overflow and Server Fault, but purely focussed on SQL Server is Ask SCC, run by the guys from sqlservercentral.com. It’s a new players on the scene, but I think will turn into a very useful site. The Stack Overflow engine isn’t bad at all, and the quality of answer at Ask SCC is excellent. I would love to see more people hang out there, as it serves a useful market for SQL specialists. At the moment it doesn’t do much traffic, but many of the people there are good SQL experts, and I’m convinced that you’ll get an excellent answer if you ask a question there. At the moment it doesn’t seem to be collecting poor answers as much as many of the other sites, so the ratio of good answers to poor ones puts you in a good position as an asker. I’ve posted my Ask SCC and Stack Overflow ‘flairs’ here, so that you can compare the two. If the numbers on the Ask SCC one have reached as high as the Stack Overflow one, then you’ll have a good indication that the traffic on Ask SCC has increased nicely.

In many ways, I tend to find that my efforts are focussed more on the questions that aren’t getting answered, rather than trying to catch the newest questions. On many of these sites, I’d rather find the one that the asker has had trouble with, hoping to provide the elusive answer rather than the obvious one. That question that got me started on Stack Overflow was an exception because I didn’t feel like any of the previous answers had really solved the question properly, but on the whole, my approach to Stack Overflow doesn’t really fit with most of the answerers on the site. I like EE because there really seems to be a focus on getting those elusive answers for people, and I know that Microsoft really focuses on getting answered questions sorted on their forums.

My challenge to you is to give back to the community this Christmas. Make it a resolution for 2010 if you will. Why not try to answer a question every week? And better still, make it one that everyone else has had trouble answering. Go to the lists of unanswered questions, and help someone out. Next time it might be you asking, and you’ll hope that someone takes the time to find your elusive question.

Plus, you might learn something!

Infinite Drill-through in a single SSRS report

Rob Farley — Mon, 21 Dec 2009 10:18:15 GMT

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 query in SSRS, you use the STRTOMEMBER or STRTOSET function to handle this. But the MDX has no other indication of what dimension, hierarchy or level is being passed in. If you grab the children of whatever you’ve passed in, you can easily put this on the Rows axis and get one level down. Passing the UniqueName of whatever you’ve just provided back in as the next parameter, and you have infinite drill-through.

Look at the following MDX query:

WITH
MEMBER [Measures].[NextLevel] as StrToMember(@SomeDate).Hierarchy.CurrentMember.UniqueName
MEMBER [Measures].[NextLevel Name] as StrToMember(@SomeDate).Hierarchy.CurrentMember.Member_Name

SELECT
NON EMPTY { [Measures].[Internet Sales Amount], [Measures.NextLevel], [Measures].[NextLevel Name] } ON COLUMNS,
NON EMPTY { (StrToMember(@SomeDate).Children ) } ON ROWS

FROM [Adventure Works]

You see that I provide the UniqueName and Member_Name properties in known columns, so that I can easily reference them in my report. You’ll also notice that nowhere do I actually indicate which dimension I’m planning to drill down on, or to which hierarchy the @SomeDate parameter refers. I have suggested it’s a date, but only in name. At this point I also make sure that the Report Parameter is not restricted to values from a particular query, and I hide it from the user. I’m going to be passing in UniqueName values, which aren’t particular user-friendly.

If I start with [Date].[Fiscal].[Fiscal Year].&[2003], then my NextLevels will be [Date].[Fiscal].[Fiscal Semester].&[2003]&[1] and [Date].[Fiscal].[Fiscal Semester].&[2003]&[2]. This then continues down as far as I want it to go. I could always put a condition on my Action to pick up when there are no more levels, and potentially start down a different hierarchy. After all, I can always use a bunch of other parameters in the WHERE clause to slice the cube in other ways first, for placeholders. It really just comes down to MDX creativity to investigate different ways of drilling through the data.

Please bear in mind that other people may well have achieved the same sort of thing using a different query – I’m just posting what has worked for me. Hopefully by doing this, you can avoid making five drill-through reports just because your hierarchy has five levels. This might just remove 80% of your reporting effort!

High ROI items for SQL Server 2008

Rob Farley — Thu, 17 Dec 2009 23:02:00 GMT

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 soon as you do the upgrade (or soon after). And I welcome comments to list other items as well.

Block Computation (in SSAS – which reduces the effort in processing significantly, for no change in the application )
Transparent Data Encryption (in the Database Engine – which makes sure that data at rest is encrypted, with no change in the application)
Backup Compression (which reduces the size of backups, and can be set as the default so that existing backup scripts don’t need to change)
Data Compression (minimal change to turn on compression on tables which will compress nicely)
Filtered Indexes (because how far off is your next index creation, really?)
Auditing & Change Data Tracking (because it’s very easy to turn on and then review the data as you need it)
Export to Word in SSRS (because everyone’s wanted this for so long)
SSRS paging (because SSRS used to get _all_ the data for a report before rendering it – but not in 2008)
Resource Governor (easy to set up, nice to have in place for when you might want it)
Hot-add memory (so that you can just plug in more memory without having to do restarts)

I’m not suggesting that an upgrade should be done flippantly. You should still consider the effort of thoroughly testing your system under SQL 2008. But hopefully this list can highlight some of the things that I’ve found are good persuaders. A list of “What’s New in SQL 2008” can be found at http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

Like I said, you may have other items on your own list, and I invite you to comment on this. You may also have things in place to handle things like encryption, and you may be running Hyperbac or one of the other compression tools.

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

Rob Farley — Tue, 08 Dec 2009 08:31:00 GMT

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 consider. This range starts well before the earliest date I could want, and I don’t leave gaps either. Some people like to only use dates that have fact data, but I prefer to have the dates going back as far as I like.

Let’s talk about what the table looks like, and then how it can be created.

I have a primary key on an integer based on the date, in the format YYYYMMDD. So today would have the number 20091208. I haven’t tried using the date type that’s available in SQL Server 2008 for a date dimension yet – I generally try to use numbers for dimension keys, and haven’t tested the alternative yet. Using an integer like this for the key in a date dimension is generally considered best practice.

I also have a column which is the actual date itself. I will use this as the Value column for the dimension key in Analysis Services. I also have various representations of the date in string form, such as “Tuesday December 8th, 2009”, “08/12/2009”, “8-Dec-2009”. One of these will be the Name column, but I may have others available for other properties and translations. A “12/08/2009” option may be preferable for a US translation, for example.

Columns in my table should indicate which year it is, such as 2009. I’ll also throw in the start of the year (in a date format), and something which indicates which Financial Year it is. In Australia, this is most easily handled by adding six months onto the current date and considering the year of this adjusted date (our FY starts on July 1st). I can subtract the six months back again to work out what the start of the Financial Year is. I try to keep things in the code quite simple, as I leave this code with the client and hope they can maintain it as required. The trickiest I get is to use the DATEADD(month,DATEDIFF(month,0,ActualDate),0) technique for truncation, but I think this should be required knowledge when handling dates.

For months, quarters, semesters, weeks, and so on, I will also prefer to have an integer as the key. A Month Key would take the format 200912 for this month, or 201001 for next month. Quarters can be done using 20094 and 20101, and so on.

This may all seem quite complex, but it’s something you only need to do one time.

Let me explain…

My table only really contains one field. Yes, just one. More might be required for custom fields, but where possible, I will just populate one field and let all the rest be handled using computed columns.

Even the primary key will be a computed column.

CREATE TABLE dbo.Dates (
DateKey AS CONVERT(int, CONVERT(char(8), ActualDate, 112)) PERSISTED NOT NULL
,ActualDate DATETIME NOT NULL
,CalendarYearKey AS YEAR(ActualDate) PERSISTED NOT NULL
,CalendarYearName AS CONVERT(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
,CalendarYearStart AS DATEADD(year,DATEDIFF(year,0,ActualDate),0) PERSISTED NOT NULL
,FinancialYearKey AS YEAR(DATEADD(month,6,ActualDate)) PERSISTED NOT NULL
,FinancialYearName AS CONVERT(char(4),YEAR(DATEADD(month,6,ActualDate))-1) + '/' + RIGHT(CONVERT(char(4),YEAR(DATEADD(month,6,ActualDate))),2) PERSISTED NOT NULL
,FinancialYearStart AS DATEADD(month,-6,DATEADD(year,DATEDIFF(year,0,DATEADD(month,6,ActualDate)),0)) PERSISTED NOT NULL
,MonthKey AS CONVERT(int, CONVERT(char(6),ActualDate,112)) PERSISTED NOT NULL
,MonthName AS CASE MONTH(ActualDate)
                 WHEN 1 then 'Jan'
                 WHEN 2 then 'Feb'
                 WHEN 3 then 'Mar'
                 WHEN 4 then 'Apr'
                 WHEN 5 then 'May'
                 WHEN 6 then 'Jun'
                 WHEN 7 then 'Jul'
                 WHEN 8 then 'Aug'
                 WHEN 9 then 'Sep'
                 WHEN 10 then 'Oct'
                 WHEN 11 then 'Nov'
                 WHEN 12 then 'Dec'
               END
                  + ' ' + CONVERT(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
,FrenchMonthName AS CASE MONTH(ActualDate)
                 WHEN 1 THEN 'janv'
                 WHEN 2 THEN 'févr'
                 WHEN 3 THEN 'mars'
                 WHEN 4 THEN 'avr'
                 WHEN 5 THEN 'mai'
                 WHEN 6 THEN 'juin'
                 WHEN 7 THEN 'juil'
                 WHEN 8 THEN 'août'
                 WHEN 9 THEN 'sept'
                 WHEN 10 THEN 'oct'
                 WHEN 11 THEN 'nov'
                 WHEN 12 THEN 'déc'
               END
                  + ' ' + CONVERT(char(4), YEAR(ActualDate)) PERSISTED NOT NULL
--Many more columns following
);
GO

You will notice that I have used ugly long CASE statements for the MonthName columns. I do the same for the names of the days of the week. The reason is betrayed in the second example. DATENAME (or any kind of conversion that relies upon the language setting, such as CONVERT(char(3),ActualDate,100)) is non-deterministic, and therefore can’t be used in a persisted computed column (I do wish that CONVERT could take a Language setting, so that I could tell it to convert in English, French, etc, and make it deterministic). Why do I want them to persist? Well… I’m just more comfortable with them being persisted. After all, I could use a view for the whole thing at this stage, but I’m really not that comfortable with the table being generated on the fly when it comes to processing. The table is essentially read-only, after all.

As well as many computed columns like this, I will also have some that are not computed, such as a column to indicate if it’s a public holiday. This could be computed, at a push, as public holidays generally follow a system. Even Easter follows a formula that could be applied. But if the company takes a special day, or if government declares an extra day for some reason, then problems can start popping up. I find it convenient to have columns that can be updated directly (but which have defaults, of course).

One great thing about this method is that it can be populated very easily. The only field you insert data into is the ActualDate column. Generating a list of dates is as easy as using DATEADD() with a nums table, as I’ve written many times before, including this StackOverflow question. If you need more dates, just insert more.

As I mentioned before, a view could be used for this. It is very easy to generate a list of dates, and then all the other calculations could be done as other columns in the view. You could perform an OUTER JOIN into a table which lists public holidays and other special days. Analysis Services will happily handle this in much the same way. I just prefer to have it exist as a table, which I feel I have more control over.

This post has been part of T-SQL Tuesday, hosted this month by Adam Machanic. You should be able to see many other posts related to datetime mentioned as Trackbacks to Adam’s post.

Dangers of BEGIN and END

Rob Farley — Sat, 05 Dec 2009 11:32:47 GMT

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 T-SQL, it’s the Query Optimizer that works out how to actually run the query. It works out what indexes can be used to improve performance, what order tables (well, indexes and heaps) should be accessed, how to perform the joins, and so on. I find that a rough appreciation of the power of the Query Optimizer can really help query writers.

For example, the Query Optimizer will translate a correlated sub-query in the SELECT clause into a LEFT OUTER JOIN, so that you don’t have to. It will also work out when joins can be rendered pointless and thereby removed from the plan altogether. If you let these principles help you in your query design, you can see significant benefits. It also helps you write queries that are easier to maintain, as there’s little point in trying to be clever by writing a query in a different way if the Query Optimizer will handle it in the same way as before.

If you use a view in another query, the definition of the view is used in the query as if you had written it with a sub-query. A view is simply that – a stored sub-query. They are sometimes referred to as ‘virtual tables’, but I disagree. They are stored sub-queries. Sure, the analogy falls down when you start considering indexed views, but on the whole, a view should be seen as a stored sub-query. The Query Optimizer takes the view definition, applies it in the second query, simplifies it where possible, and works out the best way of executing it. If you’re only interested in a couple of columns out of the view, the Query Optimizer has an opportunity to take that into consideration.

Stored procedures are different. You can’t use a stored procedure in an outer query. The closest you can get to this is to use OPENROWSET to consume the results of a stored procedure in an outer query, but still the whole procedure runs. After all, it’s a procedure. A set of T-SQL commands, not a set of queries. I see the clue to this as the BEGIN and END that stored procedures generally use. I like stored procedures, but I do get frustrated if they’re returning more information than I need, since I have no way of letting the system know that maybe it doesn’t need to do as much work.

Functions are in between, and come in two varieties. A function can be inline, or it can be procedural. I don’t think you find this differentiation in many places – and normally people talk about this particular drawback as being associated with Scalar Functions as compared to Table-Valued Functions, but the problem is actually one of simplification.

An inline function must be a table-valued function at this point in time. It takes the form:

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS TABLE AS
RETURN
( SELECT …. );

It is always this form, with a sub-query enclosed in a RETURN statement. It can return many columns and many rows, but the definition of the table is implied by the SELECT clause. This is essentially a view that can take parameters.

The other form is one that involves BEGIN and END. Scalar functions (unfortunately) require this (but hopefully one day will not).

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS int AS
BEGIN
RETURN ( ... )
END;

As the RETURN statement is enclosed between a BEGIN and END, it can be preceded by other statements, used in working out what value should be returned.

Table-valued functions can use BEGIN and END, when multiple lines are required to calculate the rows in the table being returned.

CREATE FUNCTION dbo.fnFunctionName(<paramlist>) RETURNS @table TABLE (<fields>) AS
BEGIN
...
RETURN
END;

In this kind of function, the table variable is populated with data, and returned to the outer query when the RETURN command is reached.

But when the Query Optimizer comes across a procedural function, it cannot simplify it out and executes the function in a different context.

The execution plan will report that the cost of running the function is zero. But it’s lying. The way to see the impact of the function is to look in SQL Profiler, where you’ll see potentially many calls to the function, as it needs to work out the result for each different set of parameters it’s passed. The pain can be quite great, and you will never have noticed if you just look at the Execution Plans.

The moral of the story is to make sure that your functions are able to be simplified out by the Query Optimizer. Use inline table-valued functions even in place of scalar functions. You can always hook into them using CROSS/OUTER APPLY in your FROM clause, or even use them in your SELECT clause (not “SELECT Claws” – that would make it related to my company LobsterPot Solutions, and “SELECT Claus” is just a bit Christmassy) using a construct like SELECT (SELECT field FROM dbo.fnMyTVF(someParam)) ...

Consider the Query Optimizer your friend. Study Execution Plans well to look at how the Query Optimizer is simplifying your query. And stay away from BEGIN and END if possible.

StreamInsight talk coming up at SQLBits

Rob Farley — Fri, 20 Nov 2009 11:47:24 GMT

My talk on StreamInsight is up next. I’ll try to blog more about that later. For now, I want to mention more about SQLBits itself. This is by far the largest SQL-only conference I’ve attended (I haven’t been to SQL-PASS yet), and it’s great to be involved.

Yesterday I had an all-day seminar about the new items for Developers in SQL 2008. It was a good time – the delegates responded very positively, and many of them have caught up with me since.

But for me, the conference is being a great way of catching up with (and meeting for the first time) a bunch of SQL people that I rarely see. I’ve met people that lived only a few miles from where I grew up, and people that read my blog (Hi!), discovered people who have connections to Adelaide, and even found that my Adelaide friend Martin Cairney (who is also here) has a strange connection to Donald Farmer (of Microsoft), that their parents shared a back fence or something… Now Trevor Dwyer tells me a colleague of his knows me from somewhere… the world is very small here.

My StreamInsight talk will be interesting I hope. I have some stuff to show off, and I plan to involve the audience a little as well. If you’re at SQLBits and feel like being involved in an interactive session, then definitely come along. I want to hear from people in the audience who have dabbled with StreamInsight and also other vendors’ Complex Event Processing offerings. This is a brand new technology from Microsoft, and there will be a large range of adoption levels in the room.

More SQL Conferences coming up, including SQL Bits and SQL Down Under

Rob Farley — Thu, 27 Aug 2009 12:06:33 GMT

I know I won’t be there, as I’m a million miles away in Australia, but being from the UK myself, I always have an interest in the UK SQL community and in particular, events like SQL Bits.

This is the fifth SQL Bits conference, and they keep getting larger and larger. I’ve heard it’s now the largest SQL-focussed event in Europe. It’s going to be in South Wales (that’s OLD South Wales, not New South Wales), in November. I’m sure the area is lovely, good beaches ‘n all that… but considering it’s late November in Wales, I think you’ll be going for the SQL content, not the scenery.

Of course, if you are in New South Wales, then you ought to be thinking slightly earlier, in particular, the second weekend in October. The third SQL Code Camp is being held in Wagga, with many regular speakers (like myself) and quite a few new ones too.

These two events are clearly the significant SQL events in the last quarter of the year. I’m sure no-one cares about SQL PASS, after all. (I do wish I was going to this one, but I won’t be. I plan to go one year, but I was in the US that week last year, and I don’t plan to be away from home for two birthdays in a row. Maybe next year. It is the biggest SQL event in the world, with great speakers from everywhere, including many good friends of mine.)

No matter where you are in the world, there are SQL events that you should be going to. Professional development is really important for your career, and you shouldn’t neglect it. That being said, make sure you find me at TechEd Australia.

Still learning… foreign keys don’t need to reference a primary key

Rob Farley — Tue, 04 Aug 2009 23:09:15 GMT

…but you should still have a primary key on every table of course.

It’s just that I only recently discovered that you can have a foreign key that references something else, so long as it’s known to be unique through a unique index / constraint.

The scripts here demonstrate this in SQL Server 2005 and beyond.

create table testunique (id int identity(1,1) primary key, otherid int);
go
create unique index ixOther on testunique(otherid);
go
create table testFK (id int identity(1,1) primary key, someid int)
go
alter table testFK add constraint fkTest foreign key (someid) references testunique(otherid)
go

And then if I try to drop the ixOther index, I get an error saying:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index 'testunique.ixOther'. It is being used for FOREIGN KEY constraint enforcement.

So I guess this is another reason not to blindly remove indexes that aren’t mentioned in sys.dm_db_index_usage_stats

SSRS: Removing the Navigation link using an Expression

Rob Farley — Tue, 04 Aug 2009 13:22:59 GMT

There are times when you want to have a Navigation property of a textbox (typically providing a link to a URL or other report), but you don’t always want the link to be there. Sometimes you just want it to be an ordinary textbox.

This particularly applies when you’re using a Matrix, and you don’t want the Subtotal rows to have the navigation links. Previously I’ve blogged about using InScope to control various properties. But the thing that I hadn’t noticed was how to make the link actually disappear. I could make it point at somewhere less useful (like the current report), but I didn’t want the cursor to change.

And then my friend (and fellow MVP) Jason Strate told me that if you make the Expression give the result of “Nothing”, then this does the trick.

So try something like: =iif(inscope("matrix1_SalesPersonID"), “http://someurl.com”, Nothing)

Thanks, Jason!

Counting consecutive instances with DENSE_RANK

Rob Farley — Fri, 24 Jul 2009 23:34:52 GMT

It’s hard to think of a practical use for DENSE_RANK(), but here’s one.

It’s one of the ranking functions that was introduced in SQL Server 2005, along with RANK(), NTILE(n) and the incredibly useful ROW_NUMBER(). But apart from producing reports, it’s quite hard to find genuine uses for some of them.

Yesterday I answered a question at StackOverflow (first time – I often answer questions at Experts Exchange and on the MSDN Forums, but never at StackOverflow before). It’s at http://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access/1176255#1176255

The problem is trying to find runs of consecutive days of activity. I solved it using ROW_NUMBER() and the integer value of the day, grouping by the offset between them. When you partition the ROW_NUMBER() by the users, you have a complete solution. It works nicely.

But if people can have multiple entries in a day, then ROW_NUMBER() doesn’t cut it – the offset would change whenever someone didn’t have exactly one record per day. Of course, there might be logic in place to make sure this never happens, and I hope there would be – but if there isn’t the problem just becomes one for DENSE_RANK().

You see, DENSE_RANK() won’t go up for ties. If you have two entries on the same day, they’ll be given the same rank. And then the next day will be as if there had only been one the previous day. If you have ten 107ths, the next DENSE_RANK() is 108 (RANK() would give the next value as 117).

So then my query of:

with numberedrows as
(
select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset;

…becomes:

with numberedrows as
(
select dense_rank() over (partition by UserID order by cast(CreationDate-0.5 as int)) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
from tablename
)
select min(CreationDate), max(CreationDate), datediff(day, min(CreationDate), max(CreationDate)) + 1 as NumConsecutiveDays, count(*) as NumEntries, UserID
from numberedrows
group by UserID, TheOffset;

Bear in mind that I’m having to truncate the CreationDate here. If the values were stored as an date with no time component, I’d be able to use CreationDate without modification, which lends itself much better to indexing strategies. For this scenario, I’d prefer to have an index on a computed column which was the truncated date.

So there you go – a practical use for DENSE_RANK().

The new brand is Microsoft SQL Azure

Rob Farley — Thu, 09 Jul 2009 11:57:12 GMT

It used to be SQL Server Data Services. Then SQL Data Services. Then we started hearing about SQL Services, and now (I hope finally), we have Microsoft SQL Azure instead of SQL Services, and the Microsoft SQL Azure Database instead of SQL Data Services.

http://blogs.technet.com/dataplatforminsider/archive/2009/07/08/microsoft-sql-services-is-now-microsoft-sql-azure.aspx

I think this is a sensible move. For a while now, whenever refer to SQL Services, I have to make it clear that it’s not the Windows Services that run SQL on your server, but that it’s the “in the cloud” offering that Microsoft are bringing out. Now, I can talk about SQL Azure, and it’s very clear what it means. And SQL Azure Database is clear too, as opposed to whatever will come, like (and I’m predicting here, haven’t heard anything) SQL Azure Analytics or SQL Azure Reporting.

This is definitely a positive move, and should hopefully help people realise that this is not the same as SQL Server, help them approach their SQL Azure platform slightly differently to their On-Premise equivalent.

For example… At the moment, the SQL Azure Database platform is limited in size. I hope it will soon be unlimited, but for the time being there is a limit which will affect development decisions. If your database suddenly needs to store a lot more data than you expected, you may need to hurriedly work out how you’re going to handle this. But I think having a name which reflects the Azure nature will prompt developers into making this decision earlier.

I’m looking forward to getting my hands on SQL Azure, and seeing how my job will change because of it.

Excel dates counted differently and a reliable way of working out the day of the week

Rob Farley — Sun, 05 Jul 2009 11:11:28 GMT

Following my recent post about 40,000 days, I got a couple of emails telling me that Excel disagrees about when the 40,000th day is. And this is true – Excel counts Day 40000 as July 6th 2009, not July 7th.

Unfortunately for Excel users, they’re wrong. And it’s down to the fact that Excel thinks that 1900 was a leap-year, when we all know it wasn’t. You see, leap-years only fall on the century year if the year is divisible by 400. So 2000 was a leap-year, but 1900 wasn’t, and 2100 won’t be either.

What’s more, this has been a problem for ages, and it was a conscious decision to introduce the bug. Essentially, Lotus 1-2-3 thought 1900 was a leap-year, and to be consistent with the market leader of the day, Microsoft treated 1900 as a leap-year too. And nowadays, we have a situation where backwards compatibility is more important. Today, if you have an application which knows that day 40000 is July 7th, then you’d better not treat your date that same way in Excel (or vice-versa).

The thing that I find really amazing with this is that Excel tells me that Feb 29, 1900 was a Wednesday. But it didn’t exist – so what happened that week? Actually (and you can check other calendars for this, including Windows’ one), Feb 28th was a Wednesday, and Excel gets the day of the week wrong for the first 59 days of its calendar.

I know you don’t care, but perhaps you should – in case you ever write an application that needs to know what day of the week it is.

Working out the day of the week is really trivial. For instance, in SQL Server, you can generally ask for the DATEPART(dw,…) of the date in question, and get a number back, telling you what day of the week it is. It’ll tell you 1 for the 1st day of the week, 2 for the 2nd, and so on.

Which is great, until you find that someone in your organisation says that Sunday is the first of the week, but someone else insists that it’s Monday. In the movie industry, I think Thursday is the first day of the week. So then, when is the 5th day? In SQL we have @@DATEFIRST, which helps a lot, but a method I like to use is to count the number of days since a known Sunday (or whatever), and take the “mod 7”. If that’s zero, I’m an exact number of weeks since that known Sunday. It works nicely, and it’s simple enough for everyone to understand (and it works regardless of location or other changeable settings).

But if you had picked your “known Sunday” in early 1900 using Excel, you’d’ve got it wrong, and your data might not work if you push your system out to SQL later (so pick something later – like 1901). I recently dealt with a date dimension that someone had put together in Excel and imported into SQL – if this data had gone back to 1900, then there would’ve certainly been errors in it (for a start, the import wouldn’t’ve worked because SQL would’ve complained that Feb 29, 1900 wasn’t a valid date).

My preference with date dimensions is to use a lot of computed columns, and only ever populate a single field. It works nicely, and it’s almost no effort to extend the table to include extra dates when required.

40,000 days since the beginning of ‘time’

Rob Farley — Wed, 01 Jul 2009 00:12:33 GMT

Next Tuesday feels like a significant day. Not only is it 7/7, and the fourth anniversary of the London bombings (in 2005), but it’s 40,000 days since the beginning of time. Well, not proper time, but since the start of the 1900s.

SELECT DATEDIFF(day,'19000101','20090707')+1 AS DaysOf1900s

So if Day 1 is Jan 1, 1900 (as many systems tend to use – SQL actually considers it Day 0), Day 40000 is 7/7/2009.

I actually rate the start of the twentieth century as a very significant event in the human race. In 1900 the world was changing faster than it had ever done before, and no-one can deny the significance of the twentieth century in history. I’m sure change will continue to become faster, but I found it interesting recently when I noticed that we were approaching this ‘milestone’ of sorts.

Access’ DISTINCTROW keyword explained – it means WHERE EXISTS

Rob Farley — Thu, 11 Jun 2009 11:25:15 GMT

Steve Koop spoke recently at the Adelaide SQL Server User Group, talking about things which don’t convert particularly nicely when upsizing from Microsoft Access to SQL Server 2008. I think this is a really important thing for SQL people to know, as there seem to be many Access databases living in even the largest organisations.

One of the things he mentioned was DISTINCTROW. I’ve never really known what DISTINCTROW does, so I asked him. He sent me a link which explained the difference between DISTINCTROW and DISTINCT, and it described as “DISTINCTROW works on records, not just individual fields”. This might be good for some people, but I wanted to know a little more.

http://msdn.microsoft.com/en-us/library/aa140015.aspx says “The DISTINCTROW keyword is similar to the DISTINCT keyword except that it is based on entire rows, not just individual fields.” – but it also goes on to say a little more, confirming my suspicions. “It is useful only when based on multiple tables, and only when you select fields from some, but not all, of the tables.”

So DISTINCTROW is more about the JOIN type than anything else. It only applies if you are querying multiple tables, but not returning fields from all of them. So it’s a SEMI JOIN to the unused tables, which you write SQL Server using a WHERE EXISTS clause. It’s not really like DISTINCT at all – it’s about doing a Join without seeing the ‘duplication’ effect, clearly only feasible if you’re not returning columns from the other table.

If you’re not sure what a Semi Join is, then just think about the WHERE EXISTS clause, and it should become clear. If you look at the execution plan of a query in SQL Server that uses WHERE EXISTS, you’ll see that it’s doing a Semi Join. And if you’re looking at queries which use DISTINCTROW, consider changing them to WHERE EXISTS instead.

Putting a Calculated Member into a Display Folder

Rob Farley — Tue, 09 Jun 2009 11:10:25 GMT

There are some blog posts that are there to inform other people – this isn’t one of these. This is something I always seem to forget, and I’m hoping that writing it in here will cause me to never forget again. It’s the knot in my handkerchief, or the writing on my hand.

I put an SSAS (2005) Calculated Member in a cube, and then have trouble trying to get it into a Display Folder, or associating it with a Measure Group…

I always hit F4 and go hunting through the properties list… repeatedly pull down the drop down that says [Measures], looking for it… until I eventually remember the extra little button on the toolbar. The one in the picture on the right. It’s the button between the Script button and the Check Syntax button… the button that always seems to escape out of my head, driving me crazy every few months.

Seriously cheap exams in Australia

Rob Farley — Wed, 06 May 2009 02:56:41 GMT

If you’re an MCP in Australia and you haven’t passed any exams over the past couple of years (since July 1, 2007), then Microsoft has an offer at the moment to let you do an exam for only US$25 (until June 30, 2009). Ok, so that means the price really depends on the value of the Aussie dollar, but either way, it’s not a bad opportunity.

The offer is only on for a very short time, but why not check out http://www.learnandcertify.com/mcpupgrade/ and see what you can do? I’m thinking it’s a nice opportunity to knock over one of those Upgrade exams to get yourself from MCITP:SQL2005 to MCITP:SQL2008. You don’t get a second shot with this one, and you can only get one voucher – but it’s so cheap you may as well try it.

SQL Server 2008 SP1 download

Rob Farley — Wed, 08 Apr 2009 01:01:49 GMT

You can get it from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

For all those people who have been waiting for SP1 before considering a migration to SQL Server 2008, it has now been released.

There’s also a new version of the Feature Pack for SQL Server 2008, available from: http://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

Time zone limbo

Rob Farley — Wed, 01 Apr 2009 09:00:08 GMT

Australia is currently in an interesting week for time zones.

Up until a couple of years ago, Daylight Savings finished on the last Sunday in March. That’s when the clocks got put back to Standard Time, as the Australian summer ended. Last year though, this got extended by a week, until the first Sunday in April. A similar change was made in October, changing the start of Daylight Savings from the last weekend of October to the first weekend of October. We now have six months of summer instead of five (although weather-wise, it’s a lot more…)

That’s fine – most people have patched their machines happily, and don’t have a problem. My mobile phone is an old O2 XDA, running Windows Mobile 2003 (I once upgraded to a newer device, but a washing machine had an argument with it and won). Unfortunately, i don’t think there’s a patch for WM2003, and so this week my phone (and hence, my alarms) thinks that I’m an hour out.

It’s fine when I’m in Melbourne or Sydney – I can set the time zone to be Magadan (which is in Russia), and the problem goes away. All good – I don’t really care where my phone thinks I am, just so long as the time is right.

The problem is when I’m in Adelaide… Adelaide which is normally in GMT+0930 (yes, on the half-hour), but this week is still in GMT+1030. According to my mobile device, there is nowhere in the world that is GMT+1030 this week. So instead I’ve had to change my alarms to wake me up half an hour later, whilst I pretend I’m in Siberia. I recently learned that the Russian for “Bless You” (ie, that thing you say when someone sneezes) is “Bud Zdorov” (literally "Be Healthy”, and I apologise for the spelling. ‘Bud’ rhymes with ‘Good’). I’m not sure it’s quite enough to get me through though.

One day I plan to visit Kathmandu, where the time zone is on the quarter-hour. Then I can return to the normality of Adelaide’s half-hour time zone.

I’ve written about the pain of daylight savings before, particularly around the pain of storing datetime fields in a database. Today i read a post from Bart Duncan, recommending the use of datetimeoffset. I thoroughly agree with him, although I wonder how long it will be before people make this a priority.

Converting to (and from) Julian Date (YYDDD) format in T-SQL

Rob Farley — Wed, 25 Mar 2009 07:51:00 GMT

I often get asked how to convert a datetime into Julian Date format in T-SQL. People have differing opinions about what Julian means, but the one I got asked about most recently meant YYDDD, as often used by mainframe systems (I think this is Julian Date, as opposed to Julian Day which is the number of days since 4713BC). SQL Server doesn’t have a TO_JULIAN function, but we can make one easily enough.

So we’re wanting to express a date as YYDDD, where YY is the two-digit form of the year, and DDD is the number of days since Dec 31st of the previous year (ie, the DDDth day of the year).

Using the DATEPART function can get each part. YY for the year, and DY for the day of the year. I’m going to use @date as a variable here, of type datetime. Using the date type in SQL 2008 would work just the same.

SELECT DATEPART(yy, @date), DATEPART(dy, @date)

However, to make sure that we have the year in two-digits only, we should convert this to a string and get the rightmost two characters.

SELECT RIGHT(CAST(DATEPART(yy, @date) AS char(4)),2)

We also need to pad the DDD with zeroes – which I’ll do by putting three zeroes in front of the number and getting the three rightmost characters.

SELECT RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3)

Concatenating the YY and the DDD, we now have a TO_JULIAN function.

CREATE FUNCTION dbo.to_julian(@date datetime) RETURNS char(5) AS
BEGIN
RETURN (SELECT RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3))
END

Converting back again isn’t too hard – it’s just a matter of pulling the numbers out of the 5-character string. I’m going to assume we have a char(5) called @julian.

We need to split the string up first.

SELECT LEFT(@julian,2), RIGHT(@julian,3)

The first bit becomes the year easily enough

SELECT CONVERT(datetime, LEFT(@julian,2) + '0101', 112)

The second half can be cast to a number, and then added back (subtracting one to get the maths right) using DATEADD.

SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) - 1, CONVERT(datetime, LEFT(@julian,2) + '0101', 112))

So now we have a FROM_JULIAN function:

CREATE FUNCTION dbo.from_julian(@julian char(5)) RETURNS datetime AS
BEGIN
RETURN (SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) - 1, CONVERT(datetime, LEFT(@julian,2) + '0101', 112)))
END

Easy stuff really, just a matter of thinking about what we mean by a particular format.