Browse by Tags

All Tags » sql (RSS)
Tue, Dec 29 2009 8:14

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...
Posted by Rob Farley | with no comments
Filed under:
Thu, Dec 24 2009 15:14

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...
Posted by Rob Farley | with no comments
Filed under: ,
Mon, Dec 21 2009 20:18

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...
Posted by Rob Farley | with no comments
Filed under: , , , ,
Fri, Dec 18 2009 9:02

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...
Posted by Rob Farley | 2 comment(s)
Filed under: , ,
Tue, Dec 8 2009 18:31

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...
Posted by Rob Farley | 2 comment(s)
Filed under: , ,
Sat, Dec 5 2009 21:32

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...
Posted by Rob Farley | with no comments
Filed under: , , ,
Fri, Nov 20 2009 21:47

StreamInsight talk coming up at SQLBits

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...
Posted by Rob Farley | with no comments
Filed under: , , , ,
Thu, Aug 27 2009 23:06

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

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...
Posted by Rob Farley | 1 comment(s)
Filed under: , , , , ,
Wed, Aug 5 2009 10:09

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

…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...
Posted by Rob Farley | with no comments
Filed under:
Wed, Aug 5 2009 0:22

SSRS: Removing the Navigation link using an Expression

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...
Posted by Rob Farley | with no comments
Filed under: ,
Sat, Jul 25 2009 10:34

Counting consecutive instances with DENSE_RANK

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...
Posted by Rob Farley | 2 comment(s)
Filed under:
Thu, Jul 9 2009 22:57

The new brand is Microsoft SQL Azure

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...
Posted by Rob Farley | 1 comment(s)
Filed under: ,
Sun, Jul 5 2009 22:11

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

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...
Posted by Rob Farley | 4 comment(s)
Filed under: ,
Wed, Jul 1 2009 11:12

40,000 days since the beginning of ‘time’

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...
Posted by Rob Farley | 2 comment(s)
Filed under: ,
Thu, Jun 11 2009 22:25

Access’ DISTINCTROW keyword explained – it means WHERE EXISTS

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...
Posted by Rob Farley | with no comments
Filed under: , ,
Tue, Jun 9 2009 22:10

Putting a Calculated Member into a Display Folder

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...
Posted by Rob Farley | 2 comment(s)
Filed under: ,
Wed, May 6 2009 13:56

Seriously cheap exams in Australia

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...
Posted by Rob Farley | with no comments
Filed under: , , , ,
Wed, Apr 8 2009 12:01

SQL Server 2008 SP1 download

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...
Posted by Rob Farley | 1 comment(s)
Filed under: ,
Wed, Apr 1 2009 20:00

Time zone limbo

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...
Posted by Rob Farley | 6 comment(s)
Filed under: , ,
Wed, Mar 25 2009 18:51

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

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...
Posted by Rob Farley | 4 comment(s)
Filed under:
More Posts Next page »